Split CLOB content to lines (new line character as split character)
Below procedure is to split CLOB data into array of Lines separated by new line character.
DECLARE
l_clob_data CLOB;
i NUMBER :=0;
BEGIN
l_clob_data CLOB;
i NUMBER :=0;
BEGIN
-- To get the view definition into clob
SELECT DBMS_METADATA.get_ddl ('VIEW', 'ORG_ORGANIZATION_DEFINITIONS')
into l_clob_data
from dual;
into l_clob_data
from dual;
FOR rec IN (WITH clob_table(c) as (SELECT l_clob_data c FROM DUAL),
recurse(text,line) as (SELECT regexp_substr(c, '.+', 1, 1) text,1 line
FROM clob_table
UNION ALL
SELECT regexp_substr(c, '.+', 1, line+1),line+1
FROM recurse r,clob_table
WHERE line<regexp_count(c, '.+'))
SELECT text,line FROM recurse) LOOP
dbms_output.put_line(i||'='||rec.text);
i := i+1;
END LOOP;
END;
i := i+1;
END LOOP;
END;
Comments
Post a Comment