DBI:Oracle Dealing with longs and Clobs

[nationavon]

Can anyone point me to some decent examples or documentation dealing with longs and clobs. I don's even know where to start. I need to know how to insert, select and update using DBI:Oracle.

 

[MikeLacey]

From the DBD::Oracle documentation which should be somewhere like: file:///C:/Perl/html/site/lib/DBD/Oracle.html#handling lobs

on your system.

Handling LOBs
When fetching LOBs, they are treated just like LONGs and are subject to $sth->{LongReadLen} and $sth->{LongTruncOk}. Note that with OCI 7 DBD::Oracle pre-allocates the whole buffer (LongReadLen) before constructing the returned column. With OCI 8 it grows the buffer to the amount needed for the largest LOB to be fetched so far.

When inserting or updating LOBs some major magic has to be performed behind the scenes to make it transparent. Basically the driver has to refetch the newly inserted 'LOB Locators' before being able to write to them. However, it works, and I've made it as fast as possible, just one extra server-round-trip per insert or update after the first. For the time being, only single-row LOB updates are supported. Also passing LOBS to PL/SQL blocks doesn't work.

To insert or update a large LOB, DBD::Oracle has to know in advance that it is a LOB type. So you need to say:

$sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
The ORA_CLOB and ORA_BLOB constants can be imported using

use DBD::Oracle qwora_types);
or just use the corresponding integer values (112 and 113).

To make scripts work with both Oracle7 and Oracle8, the Oracle7 DBD::Oracle will treat the LOB ora_types as LONGs without error. So in any code you may have now that looks like

$sth->bind_param($idx, $value, { ora_type
Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[nationavon]

I found the documentation but don't understand the syntax.

$sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });

what are $field_num
$lob_value
?

 

[hmerrill]

It sounds like you're not familiar with Perl DBI - read the DBI docs. On linux I do that by doing "perldoc DBI", but I'm not sure how to do that, or where to find the DBI perldocs on windows.

I'd also recommend "Programming the Perl DBI" book by Tim Bunce.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com