Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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        |);
share|improve this question
1  
See here –  Gaius Jan 18 '12 at 16:59
add comment

1 Answer

up vote 2 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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