Extracting Clob fields

[kizziebutler]

I thought I would ask the experts a question first. I have some data that I need to extract on a daily basis from cron tab. I am spooling this data out to a "|" file, file.csv, but I am having problems with the clob field. Some of my Oracle DBA colleagues have mentioned packages such as dbms_lob to handle the clob field. Any suggestions!!

 

[Thargy]

kizzie,

Because you have mentioned a 'cron tab' I presume that you are running some flavour of UNIX, and are trying to automatically produce extracted data in a CSV file on a regular and frequent basis.

DBMS_LOB is the only way to put data in and out of a CLOB (obviously by their very nature, this excludes BFILES - they're external to the database, so it is possible to alter them externally). You can access a LOB from either SQL or PL/SQL.

As you may already be aware, DBMS_LOB has both INSTR and SUBSTR functions, which I recommend you use to chop the LOB up into small enough sections to populate your csv file. That way you won't get lines thousands of characters in length.

I also recommend the O'Reilly book "Oracle Built-In Packages" which has an excellent section on the care and feeding of LOBS.

You can get some immediate info from

http://www.cs.umb.edu/cs634/ora9idocs/appdev.920/a96591/adl03prg.htm#281893.

I hope this helps you along. Let me know how you get on with this.

Regards

Tharg

Grinding away at things Oracular