[DB-SIG] question on CLOBs in DCOracle

Matthew Vranicar vranicar@fnal.gov
Wed, 31 Jan 2001 18:39:10 -0600

Yes, DCOracle has the tools to handle CLOBS.  But, we only have been able to
make this work with Oracle v8.0.5.  It fails with Oracle 8.1.6 and above.
Alas, we are using the old DCOracle, not the new Alpha version available from
Zope.org since last week.  We are slated to test that soon.

You can find some interesting information on how we're handling clobs at a
link I have on my working web page at Fermilab at
http://www.fnal.gov:8503/vranicar/lob-sample.py.  This was only a test script
when I was learning how I wanted to handle inserting and updating CLOB
columns.  Our final code used similar logic, but there are no guarantees that
this code is 100% bullet proof.  As for reading, it is extremely simple.  Just
do either a fetchmany() or a fetchall() to get the resolved content of your
lob, or if you want the Lob object just do a fetchone().  The Lob object is
needed to do the updates shown in my example.

Also, Perl and the DBDOracle module have a very similar concept.  This is from
other code we have that handles reading CLOBS in Perl/Oraperl.  But,
personally, I think Python and DCOracle were far easier than the perl/oraperl

# These are actually set by external configuration, but hard-coded here
# for the completeness of this example.
my $dblong = 8192; # size of blob chunks ($ora_long)
my $dbtrunc = 1; # raise $ora_error = 1406 if blob > $dblong
my $dbblob = 112; # blob data type code, see OCI doc.
my $dbchunks  = 500;   # max number of blob chunks to retrieve,
                                     # this max number does not seem to be
                                     # correctly, as it gets all chunks.
# Lob truncated, see where and use blob_read.
if ($ora_errno == 1406) {
    for $b (0..$#{data_types}) {
       if ($data_types[$b] == $dbblob) {
          # blob type, use blob_read to fix truncation.
          my $offset = 0;
          my @frags;
          my $k= 0;
          while ($k < $dbchunks) {
              my $frag = $csr->blob_read($data_titles[$b],$offset,$dblong);
              $krok .= "f$k: $frag";
              last unless defined $frag;
              my $len = length $frag;
              $krok .= " ($len";
              last unless $len;
              $offset += $len;
              $krok .= " --> $offset)";
          $dr->[$b] = join("",@frags);

> Subject: [DB-SIG] question on CLOBs in DCOracle
> Date: Wed, 24 Jan 2001 21:07:51 -0800 (PST)
> From: Allan Streib <astreib230@yahoo.com>
> To: db-sig@python.org
> (Sent this a minute ago without a subject; apologies
> if this is a duplicate.)
> Hi,
> I'm looking at having to do some script development in
> the near future which will require dealing with CLOBs
> columns (containing XML) in an Oracle database.  My
> languages choices are Python or Perl.  I don't know
> either one at this point, but that's not my concern.
> From what I've seen,I think I would prefer Python as
> it it seems cleaner, less cryptic and easier to learn
> than Perl.  However, what I read in the DCOracle.txt
> file concerns me a little; I'm hoping for some
> clarification.
> I will be calling Oracle stored procedures to get the
> XML-containing CLOB columns.  I read that
> "Input/output arguments do not support long and long
> raw, date, and rowid variables"  But elsewhere it
> appears that they can be handled using a Buffer.  Can
> anyone clarify this for me, and does it make a
> difference if the CLOB is returned as an IN OUT
> argument as opposed to a cursor argument which
> contains the CLOB column?
> Also do you know if Oraperl has the same or a similar
> issue?
> Thanks.
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - Buy the things you want at great prices.
> http://auctions.yahoo.com/