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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

HR    | AAA        | <CLOB>

In <CLOB> there is the following data:


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

HR    | AAA        |( A NUMBER
HR    | AAA        |);
share|improve this question
See here – Gaius Jan 18 '12 at 16:59
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?


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');


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

Your Answer


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.