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

pipelined Date function

The requirement for this function is to
list the months ahead of the month supplied as parameters (both  numbers).
The number of months ahead of the month supplied would also
be supplied as a parameter.


create or replace type dtTable as table of date;
/
create or replace function p (pYear       in number
                             ,pMonth      in number
                             ,pMth_Ahead  in pls_integer) 

  return dtTable pipelined as

 l_In_Date date := to_date(to_char(pYear)||to_char(pMonth), 'yyyymm');

begin

  for i in (select add_months(l_In_Date,(level-1)) dt
            from dual
            connect by level <= pMth_Ahead) loop
    pipe row(i.dt);
  end loop;
  return;
end;
/
SQL> select p.column_Value as Month from  table(p(2008,9,6)) p;

MONTH
---------
01-SEP-08
01-OCT-08
01-NOV-08
01-DEC-08
01-JAN-09
01-FEB-09

6 rows selected.