Oracle: Extract lines from a CLOB

Using START WITH and CONNECT BY

Create a table that stores a clob which is identified by an id:

create table tq84_text (
   id    number primary key,
   txt   clob
);

Github repository oracle-patterns, path: /SQL/datatypes/lob/clob/to-lines/table.sql

Insert three records. The third insert (id=3) creates a clob that is longer than 32K.

insert into tq84_text values (1,
q'{First line
second line
third line
}');

insert into tq84_text values (2,
q'{foo
bar
baz}');

declare
   c clob;
begin

   dbms_lob.createtemporary(c, false);

   for i in 1 .. 10 loop

       dbms_lob.append(c, to_char(i, 'fm9999') || ': ' || lpad('.', 4000, '.') || case when mod(i,2) = 0 then chr(13) end || chr(10));

   end loop;

   insert into tq84_text values (3, c);

end;
/

Github repository oracle-patterns, path: /SQL/datatypes/lob/clob/to-lines/data.sql

The following start with … connect by query extracts the lines from the clob in the table:

select
   regexp_substr(txt, '.+', 1, level, 'm') line
from
   tq84_text
start with
   id = 2
connect by
   prior id = id                                 and
   level   <= regexp_count(prior txt, '.+' , 1, 'm')
;

Github repository oracle-patterns, path: /SQL/datatypes/lob/clob/to-lines/select.sql

If selecting with id = 3, the error ORA-01436: CONNECT BY loop in user data is thrown.

Oracle: Extract lines from a CLOB

Using START WITH and CONNECT BY

See also