Read/write clob object greater than 32k

From: <hlevine_at_my-dejanews.com>
Date: Tue, 26 Jan 1999 23:02:10 GMT
Message-ID: <78lhhd$kas$1@nnrp1.dejanews.com>

I've read several Usenet posts on reading/writing to an Oracle8 CLOB column using dbms_lob.write and dbms_lob.read. Most of the replies by Thomas Kyte have been exteremely informative, however none seem to work for my application. Some of the solutions posted here have suggested reading in 32k chunks, and outputting each chunk to the screen using dbms_output.put_line. I'm using Allaire Coldfusion 4.0 for Solaris 2.6. I have the option of using the Intersolv Oracle8 ODBC driver and SQL*Net, or the native Oracle driver. Obviously, both fail when trying to insert a string over 32K into a LONG datatype. The information I've read so far has stressed using the CLOB datatype. So my problem is as follows: I excecute a SQL statement like SELECT FIELD1, FIELD2 FROM SOME_TABLE WHERE ID=1; The results of that query are then stored in coldfusion variables named FIELD1 and FIELD2. How can I read from a CLOB field in 32K chunks, and have the results of that query be passed back to my application the same way variables are passed back to my web application during a normal "SELECT xxx from yyy where zzz" type query. Also, can I update a clob field by passing sql from my web application to PL/SQL in 32K chunks?

The following syntax works in Coldfusion for LONG datatypes of less than 32K.

DECLARE long_text1 LONG;
BEGIN
     long_text1 := '#cold_fusion_variable#';

     UPDATE notes SET
     SOME_LONG_FIELD = long_text1
     WHERE ID = 16;

END; Can I pass multiple variables of 32K each to PL/SQL, and use SQL to concatenate the strings into one long piece of data to be inserted into a CLOB field? Or can I make serveral update statements which append the content of of a clob field?

Basically, I'm looking for a way to read/write clob data and pass it back to my Coldfusion server as a variable.

Any help would be greatly appreciated.

Thanks in advance,
Howard Levine
hlevine!!@!!versity.com
(!! added to prevent spam, remove when sending correspondence)

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 26 1999 - 17:02:10 CST