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
                                          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;

Comments

Popular posts from this blog

Oracle WEB ADI Success And Error Counts Not Displayed Correctly

How to fix the error "signer information does not match signer information of other classes in the same package"