Split PL/SQL VARCHAR2 variable into chunks of 4000 each

The procedure below splits a varchar2 variable, declared
in PL/SQL, into several 4000 varchar2 chunks, which can then
be inserted into a table, since the maximum length of a
varchar2 column is 4000 characters whereas the maximum
length of a PL/SQL varchar2 variable is 32767 characters.

First create a test table;
create table xdq (str varchar2(4000), chunk_id number);

Procedure to split the string into 4000 character chunks;
create or replace procedure xdq_Ins (p_Str in varchar2) as

STR_EMPTY   exception;
l_Clob      clob        := to_clob(p_Str);  -- convert to a clob
l_Len       pls_integer := dbms_lob.getlength(l_Clob); --str length
l_Chunk_Cnt pls_integer := ceil(l_Len/4000); -- number of chunks

begin
  if (l_Len < 1) then
    raise STR_EMPTY;
  end if;

  insert into xdq
   (str
   ,Chunk_ID)
   (
    select dbms_lob.substr(l_Clob, 4000, ((level-1) * 4000) + 1)
          ,level
    from   dual
    connect by level <= l_Chunk_Cnt
   );
  
  exception
    when STR_EMPTY then
      raise_application_error('No string to insert', -20088);
end xdq_Ins;
/


Now the test

SQL> begin
  2    xdq_Ins(rpad('x',5001,'x'));
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select length(str) str_len, chunk_id
  2  from xdq;

   STR_LEN   CHUNK_ID
---------- ----------
      4000          1
      1001          2