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;