3

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        |);
1
  • 1
    See here
    – Gaius
    Jan 18, 2012 at 16:59

1 Answer 1

2

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                                    
*/
3
  • nb this is 11g+ only due to the recursive with. Also regex is not the only/fastest approach, just what I'm used to. Jan 18, 2012 at 18:00
  • I got the following error: ORA-32033: unsupported column aliasing on "with w("
    – kupa
    Jan 20, 2012 at 10:09
  • sorry! it is a new 11gR2 feature, not 11g as I thought Jan 20, 2012 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.