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 -- To get the view definition into clob SELECT DBMS_METADATA.get_ddl ('VIEW', 'ORG_ORGANIZATION_DEFINITIONS') 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 ...