utils.sql

CREATE OR REPLACE TYPE ARRAY AS TABLE OF CLOB;--VARCHAR2(32760);
/
-- Required: TYPE ARRAY AS TABLE OF VARCHAR2(1000)
CREATE OR REPLACE PACKAGE UTILS IS
  SUBTYPE MEMO IS CLOB;--VARCHAR2(32760);
  FUNCTION SPLIT(pattern CLOB, replacement CLOB) RETURN ARRAY; 
END UTILS;
/

CREATE OR REPLACE PACKAGE BODY UTILS IS 

  FUNCTION SPLIT (
     pattern CLOB
    ,replacement CLOB
  ) RETURN ARRAY IS 
    pos     number :=0;
    tmp MEMO := replacement;
    strings ARRAY:=ARRAY();  
  BEGIN 
    WHILE (instr(tmp,pattern,1,1)>0) LOOP
      pos := instr(tmp,pattern,1,1);
      strings.extend(1);strings(strings.count()):= substr(tmp,1,pos);
      tmp := substr(tmp,pos+1,length(tmp));
    END LOOP;
    strings.extend(1);strings(strings.count()):= tmp;
    RETURN strings; 
  END SPLIT; 

END UTILS;
/
-- Test
SELECT 
   rownum
  ,column_value
FROM
  TABLE(UTILS.SPLIT(' ','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus quis lectus metus, at posuere neque. Sed pharetra nibh eget orci convallis at posuere leo convallis. Sed blandit augue vitae augue scelerisque bibendum. Vivamus sit amet libero turpis, non venenatis urna. In blandit, odio convallis suscipit venenatis, ante ipsum cursus augue.;Et mollis nunc diam eget sapien. Nulla facilisi. Etiam feugiat imperdiet rhoncus. Sed suscipit bibendum enim, sed volutpat tortor malesuada non. Morbi fringilla dui non purus porttitor mattis. Suspendisse quis vulputate risus. Phasellus erat velit, sagittis sed varius volutpat, placerat nec urna. Nam eu metus vitae dolor fringilla feugiat. Nulla.;Facilisi. Etiam enim metus, luctus in adipiscing at, consectetur quis sapien. Duis imperdiet egestas ligula, quis hendrerit ipsum ullamcorper et. Phasellus id tristique orci. Proin consequat mi at felis scelerisque ullamcorper. Etiam tempus, felis vel eleifend porta, velit nunc mattis urna, at ullamcorper erat diam dignissim ante. Pellentesque justo risus.'));
  --TABLE(UTILS.SPLIT('_',rpad(rpad('#',4000,'#'),4001,'!')));
/
SELECT to_number(rpad(rpad('0',4000,'0'),4001,'1')) as "support of 4001 text length" FROM dual;