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

Posted by Andrew Piskorski on 01/30/02 08:10 PM

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...

2: Response to Read CLOB from Oracle into Tcl? (response to 1)

Posted by Don Baccus on 01/30/02 08:59 PM

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.

3: Response to Read CLOB from Oracle into Tcl? (response to 1)

Posted by mark dalrymple on 01/30/02 09:08 PM

just read 'em like varchars. Evil black magic only happens on the insert / update.

4: Response to Read CLOB from Oracle into Tcl? (response to 1)

Posted by Don Baccus on 01/30/02 11:11 PM

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 ...

5: Re: Response to Read CLOB from Oracle into Tcl? (response to 4)

Posted by Ryan Gallimore on 05/29/12 03:23 PM

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.

6: Re: Response to Read CLOB from Oracle into Tcl? (response to 5)

Posted by Jeff Rogers on 05/29/12 08:45 PM

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)

7: Re: Response to Read CLOB from Oracle into Tcl? (response to 6)

Posted by Ryan Gallimore on 05/30/12 12:29 AM

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