How to extract values within an Oracle CLOB field and turn them into table rows?

Question

Within my Oracle PL/SQL procedure, I am storing a stream of values within one CLOB field where the values are separated by a carriage return/line feed. The values within my CLOB field can vary in size.

An example stream of data that I might have might look like this:

AAAA
BBBB
CCCC
DDDD
EEEE
FFFF

As mentioned above, each line in this example is separated by a carriage return/line feed but is stored all within one CLOB field, i.e. ELEMENT which is of type CLOB.

What I would like is to be able to read this ELEMENT field, take each value, prior to each carriage return/line feed and insert it as one row into a database table, i.e:

MY_TABLE (ID NUMBER, MY_ELEMENT VARCHAR2(256))

So using the data above, I would like the following end result:

MY_TABLE:

1, AAAA
2, BBBB
3, CCCC
4, DDDD
5, EEEE
6, FFFF

The problem I am having is that I am unsure how to extract the values within my ELEMENT CLOB field and turn them into individual rows as described above.

Is there a max size for the clob? – OldProgrammer Sep 22 '14 at 14:57 — OldProgrammer, Sep 22 '14 at 14:57
I have not set the max size so the max size will be 4Gb. – tonyf Sep 22 '14 at 15:00 — tonyf, Sep 22 '14 at 15:00

score 0 · Answer 1 · answered Sep 22 '14 at 21:38

You have to build a while loop reading the CLOB using the dbms_lob package and make chunks searching the return carriage chr(10). Example code:

PROCEDURE chopClob(l_clob CLOB) IS
offset number := 1;
ChunkSize number := 32767;
len    number := dbms_lob.getlength(l_clob);
l_buffer varchar2(32767);
i NUMBER := 1;
chunkSize NUMBER;
BEGIN 
  IF ( dbms_lob.isopen(l_clob) != 1 ) THEN
        dbms_lob.open(l_clob, 0);
  END IF;
  chunkSize := instr(l_clob, chr(10), offset);
  WHILE ( offset < len ) LOOP
        dbms_lob.read(l_clob, ChunkSize, offset, l_buffer);
        dbms_output.put_line('Line '||i||':'||l_buffer);
        /* Put your code here :) */
        offset := offset + ChunkSize;
        i := i + 1;
 END LOOP;

 IF ( dbms_lob.isopen(l_clob) = 1 ) then
        dbms_lob.close(l_clob);
 END IF; 
end chopClob;

score 0 · Answer 2 · edited May 23 '17 at 11:46

Here's a way that works in 11gR2 due to the recursive subquery factoring feature more info, but I'm afraid I cannot explain it as I need to study it myself yet! I was intrigued by the challenge and after finding Jack Douglas' post here: https://dba.stackexchange.com/questions/10893/oracle-read-from-clob-column-line-by-line-and-insert-to-the-table I tweaked it for your scenario. Note that the line number of the CLOB data row becomes the ID in the new table.

Set up:

create table tbl_with_clob_col(id integer, element clob);
create table tbl_parsed(id integer, my_element varchar2(100));

insert into tbl_with_clob_col(id, element) values (1, 'AAAA'||chr(10)||'BBBB'||chr(10)||'CCCC');

Parse the CLOB data and insert it into a new table:

insert into tbl_parsed (
select line#, line from (
with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(element), '^.*?$', 1, 1, 'm'), 
     substr(to_char(element), regexp_instr(element, '$', 1, 1, 1, 'm')+1)
  from tbl_with_clob_col
  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 line#, line from w order by id, line#
));

Proof:

SQL> select * from tbl_parsed;

        ID MY_ELEMENT
---------- --------------------------------------------------
         1 AAAA
         2 BBBB
         3 CCCC

3 rows selected.

See the SQL Fiddle example.

If you see any caveats please let us know.

How to extract values within an Oracle CLOB field and turn them into table rows?

2 Answers2