Forum OpenACS Q&A: Read CLOB from Oracle into Tcl?

Request notifications

Collapse
Posted by Andrew Piskorski on
I need to take a CLOB out of Oracle and stuff it into a Tcl variable.
To my surprise, it looks like the
<a href="http://www.arsdigita.com/free-tools/oracle-driver">Oracle
driver</a>
(release-2_6) does <em>not</em> provide a way to do this!  It has
<code>ns_ora write_clob</code> for writing a clob directly to the
connection, and <code>clob_get_file</code> to write to a file - but
that's it.

<p>
The Postgres driver (v. 2.0.1, postgres.c rev. 1.32) on the other
hand, has <code>ns_pg blob_get</code>, which "returns the value of the
blob to the Tcl caller".

<p>
So before I go and start hacking my Oracle driver, is this really
true?  I didn't just miss it, did I?  It seems odd that no one else
would have had this problem before...

Collapse
Posted by Don Baccus on
I think it's true.  I added that to the PG driver because I needed it for a personal site.

Before you get started hacking ora8.c, would you e-mail me?  I've changed some things (OCI broke the way aD was reading LONGs somewhere between 8.1.0 and 8.1.6, I fixed it so Schema Browser would work as Oracle uses LONGs throughout the internal datamodel).  You should base your work on that and we should then stick it up here under the SDM.

Collapse
Posted by mark dalrymple on
just read 'em like varchars.  Evil black magic only happens on the insert / update.
Collapse
Posted by Don Baccus on
Oh, sheesh,  yes of course ... thanks Mark.  What was I thinking?  I knew that and do it all the time!  My birthday's next month, senility is creeping in ...
Collapse
Posted by Ryan Gallimore on
Don, can you clarify how to read clobs like varchars? to_char(v_clob) would not be able to handle anything greater than 4000 chars.
Collapse
Posted by Jeff Rogers on
IIRC, the oracle driver recognizes clob locators in the select result and issues the appropriate OCI calls under the covers to fetch the lob. So a plain ordinary select including a lob column should just work.

The only catch is that there ends up being an extra roundtrip to the oracle server for each locator, i.e., probably each row, which can be very slow compared to to_char(lob) or dbms_lob.substr(lob)

Collapse
Posted by Ryan Gallimore on
I fixed the problem of reading back a large clob by changing the LobBufferSize in my config.tcl to 500000.