Oracle: Read from CLOB column line by line and insert to the table

I have CLOB column, and wanna read its data line by line to insert it in another table.

Now let's create the same situation that I have:

Data in a Table MyTable_1

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        | 

In `` there is the following data:

CREATE TABLE AAA
( A NUMBER
);

I want my table to look like this(let's call it MyTable_2) :

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        |CREATE TABLE AAA
HR    | AAA        |( A NUMBER
HR    | AAA        |);

This is not a full answer because it only works if your clob is less than 4000 chars. The clob version (with plain bar instead of to_char(bar) in the query) fails with ORA-00932: inconsistent datatypes: expected - got CLOB - perhaps someone else can explain why exactly?

testbed:

drop table foo;
create table foo(id integer, bar clob);
insert into foo(id, bar) values (1, 'Hello'||chr(10)||'there');
insert into foo(id, bar) values (2, 'Hello'||chr(10)||'there'||chr(10)||'again');


query:

with w(id, line#, line, rest) as (
select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
from foo
union all
select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
from w
where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;
/*
ID LINE# LINE
---------------------- ---------------------- ----------------------------------------
1 1 Hello
1 2 there
2 1 Hello
2 2 there
2 3 again
*/


StackExchange Database Administrators Q#10893, answer score: 2