CODEWITHSUNDEEP
xmloracle-databaseplsqlsubstrclob
q4za4
q4za4

Reputation: 652

oracle, xmltype/clob substr loop

I have a clob with large xmltype. My goal is to cut this xml into smaller parts, because i need to transfer large xml (over 35000 chars) into another system through file. What i have done so far:

    create table test_clob(
    id number(3),
    val clob);

and by code

    declare
      cl         clob;
      xm         xmltype;
      response   xmltype;
      l_length   pls_integer;
      l_loop_cnt pls_integer;
    begin

      select ltR_clob 
      into   cl
      from   table1
      WHERE  cal1= 50470071;

      l_length   := length(cl);
      l_loop_cnt := ceil(l_length / 1000);
      dbms_output.put_line( 'len: '||l_length|| ' loop_cnt: '||l_loop_cnt);
      for rec in 1 .. l_loop_cnt
      loop

       dbms_output.put_line('update test_clob set val = val || ''' ||
                          dbms_lob.substr(cl, 1 + ((rec-1) *1000) , rec * 1000)||''';');
                          dbms_output.new_line;
      end loop;


    end;
    /

it supposed to look like in the file/output 

    update ... set val = val || part1xml;
    update ... set val = val || part2xml;

etc...

problem is that this update parts are in random order or parts are repeated f.e.

update test_clob set val = val || 'attribute>
      <attribute>
        <name>val1</name>
        <value>0</value>
      </attribute>
    </attributes>...'

update test_clob set val = val || 'attribute>
      <attribute>
        <name>val1</name>
        <value>0</value>
      </attribute>
    </attributes>...'

OR bad order:

       '...<source-ids>
                  <identifier>
                    <person-id>11111111111111111</person-id>
              ';

        update test_clob set val = val || 'erson-id>0123545543334</person-id>
                    <source-system>THREER</source-system>
                  </identifier>'

Any ideas? im losing my head why this is happening. Or mayby someone have another idea how tu put large throw large xml into file so that in another db there won't be a problem to insert it?

Upvotes: 0

Views: 556

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Your arguments to dbms_lob.substr() are wrong. Firstly you probably have the offset and amount the wrong way round - it's the reverse of plain substr() for some reason. And then you're using rec*1000 as the amount, when really you just want 1000 - otherwise each chunk gets longer, which isn't what you want, and would continue to overlap.

So:

    dbms_output.put_line('update test_clob set val = val || ''' ||
                         dbms_lob.substr(cl, 1000, 1 + ((rec-1) * 1000)) ||''';');

Depending on the tools you are using, there may be built-in tools to do this; SQL Developer and SQLcl can generate insert statements that split CLOB up into manageable chunks, for instance, with:

select /*insert*/ id, val from test_clob;

or

set sqlformat insert;
select id, val from test_clob;

Read more about that client functionality. Other clients probably have similar tricks.

Upvotes: 1

Related Questions