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

oracle

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

In <CLOB> 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        |);

Best Answer

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                                    
*/
Related Question