Extracting chucnks of CLOB data

[kskid]

oracle 8i

I have never worked on a table.field with a data type of CLOB.

I am writing a PL/SQL procedure and need to extract the data in blocks of 3760 characters.

Any ideas would be very much appreciated.

Thanks,
-LW

 

[Thargy]

kskid,

I wrestle with CLOB's almost every day.

The only sensible way to deal with them is to write some pl/sql to dice 'n slice them into the chunks that you want.

Bear in mind that a CLOB may contain gigabytes of data, so on average, sql plus is not the best weapon in your arsenal. If you just want to get the data out, then substr will do nicely.

I use routines which size the CLOB, and then use substr to get the data out in the size I can handle. Since CLOBs can and will contain any characters, you should watch out for @, \ & and any other special characters which may occur.

If you're storing xml in a CLOB (WHY?? - dunno, but it happens) bear in mind that it is case sensitive, and therefore any clever editors (such as TOAD) will totally wreck it for you.

Also, bear in mind that sqlplus has a 2,499 character limit, so your intention to extract 3760 characters at once will stop it dead in its tracks.

Don't you just love CLOB's .....

T

Grinding away at things Oracular

 

[taupirho]

This link may help

http://www.cs.umbc.edu/help/oracle8/server.815/a68001/dbms_lob.htm

 

[SantaMufasa]

John (Thargtheslayer), I'm puzzled by the limitation to which you refer. I know that SQL*Plus has limitations, but, as you can see with my proof-of-concept, below, SQL*Plus can certainly deal with output of at least 3760 characters:

select length(
'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
... another 72 lines identical to the above
||'12345678901'
) string from dual;

STRING
------
  3761

So, Kid should be fine with what s/he wants to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,kid,

my apologies to both for failing to be crystal clear. I extract CLOB data automatically, for subsequent inclusion in a sql plus loading script, so I have to remember to keep the extracted line length below the limitations of sql plus, to prevent the loading routine from failing.

(As an aside, I use the same data to load a sql server db, and osql has a 1,000 character line limit!)

Do you have any good ideas for extracting CLOB's to DML insert statements? At the moment, as I alluded to above, I am escaping escape characters, escaping the @ symbol (as these all occur in my CLOB data).

In the loading routine, I create an insert statement to provide a marker, and then update the CLOB by appending chunks of 2,000 characters to the initially inserted value. That way, no matter how big it gets, I can always load it from sql plus.

Grinding away at things Oracular

 

[SantaMufasa]

Your technique of appending (via UPDATEs) in 2,000 character pieces is certainly viable.

Do you have any good ideas for extracting CLOB's to DML insert statements?

I'm sure we could suggest some ideas. I would need to know a few more specifications about the CLOB-to-DML task.


If, for example, you are simply wanting to do DML that clones CLOB data into another CLOB column of the same or another table, then that is as simple as either:

UPDATE <target table> SET <CLOB column name> =
    (SELECT <CLOB column name> FROM <source table>
      WHERE <some joining condition>)
 WHERE <some limiting condition>;

or

INSERT INTO <target table>
    SELECT <column list>
      FROM <source table>;

Please post the scenario that gets more complicated than this for CLOB columns, and I'm sure we can collaborate a solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]