This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFCFB6.8FE4FE90
Content-Type: text/plain;
charset="iso-8859-1"
Here is something about Lobs from 'perldoc DBD::Oracle':
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 qw(:ora_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 => 8 });
you could change the 8 (LONG type) to ORA_CLOB or ORA_BLOB (112 or 113).
One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has to
be able to tell which parameters relate to which table fields. In all
cases where it can possibly work it out for itself, it does, however, if
there are multiple LOB fields of the same type in the table then you
need to tell it which field each LOB param relates to:
$sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
===
I never get more than 64K data back for LONG's from Perl (perl
5.00503/DBI1.13/DBD::Oracle 1.03, Oracle 8.05 Sun 2.6). I always recommend
to use LOBs when possible.
You can subscrble the DBI Mail list at:
http://www.isc.org/dbi-lists.html
Roger Liu
-----Original Message-----
From: Alex Hillman [mailto:alex_hillman_at_physia.com]
Sent: Monday, June 05, 2000 5:58 PM
To: Multiple recipients of list ORACLE-L
Subject: Longs and LOBs with DBI
Is there any difference in working with LONG datatypes versus LOB datatypes
in PERL DBI? I could not find any. In a sence I need an answer if I change
type of column from LONG to CLOB or BLOB, will I have to do any changes in
PERL code.
Alex Hillman
------_=_NextPart_001_01BFCFB6.8FE4FE90
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Longs and LOBs with DBI</TITLE>
<META content="MSHTML 5.00.2614.3401" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>Here is
something about Lobs from 'perldoc DBD::Oracle':</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000>=====</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000> Handling LOBs</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>
When fetching LOBs, they are treated just like LONGs and are subject
to<BR> $sth->{LongReadLen} and $sth->{LongTruncOk}. Note that
with OCI 7<BR> DBD::Oracle pre-allocates the whole buffer
(LongReadLen) before<BR> constructing the returned column. With OCI
8 it grows the buffer to the<BR> amount needed for the largest LOB
to be fetched so far.</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>
When inserting or updating LOBs some *major* magic has to be
performed<BR> behind the scenes to make it transparent. Basically
the driver has to<BR> refetch the newly inserted 'LOB Locators'
before being able to write to<BR> them. However, it works, and I've
made it as fast as possible, just one<BR> extra server-round-trip
per insert or update after the first. For the</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>
time being, only single-row LOB updates are supported. Also passing
LOBS<BR> to PL/SQL blocks doesn't work.</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000> To
insert or update a large LOB, DBD::Oracle has to know in advance that<BR>
it is a LOB type. So you need to say:</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000> $sth->bind_param($field_num,
$lob_value, { ora_type => ORA_CLOB });</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000> The
ORA_CLOB and ORA_BLOB constants can be imported using</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000> use DBD::Oracle
qw(:ora_types);</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000> or
just use the corresponding integer values (112 and 113).</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000> To
make scripts work with both Oracle7 and Oracle8, the Oracle7<BR>
DBD::Oracle will treat the LOB ora_types as LONGs without error. So in<BR>
any code you may have now that looks like</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>
$sth->bind_param($idx, $value, { ora_type => 8 });</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000> you
could change the 8 (LONG type) to ORA_CLOB or ORA_BLOB (112 or
113).</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>One further
wrinkle: for inserts and updates of LOBs, DBD::Oracle has to<BR>be able to tell
which parameters relate to which table fields. In all<BR>cases where it can
possibly work it out for itself, it does, however, if<BR>there are multiple LOB
fields of the same type in the table then you<BR>need to tell it which field
each LOB param relates to:</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>
$sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo'
});</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000>===</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>I never get
more than 64K data back for LONG's from Perl (perl 5.00503/DBI1.13/DBD::Oracle
1.03, Oracle 8.05 Sun 2.6). I always recommend to use LOBs when
possible.</SPAN></FONT><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>You can
subscrble the DBI Mail list at:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000><FONT size=3>
<P>http://www.isc.org/dbi-lists.html</P></FONT></SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN
class=221523912-06062000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN class=221523912-06062000>Roger
Liu<BR></DIV></SPAN></FONT>
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Alex Hillman
[mailto:alex_hillman_at_physia.com]<BR><B>Sent:</B> Monday, June 05, 2000 5:58
PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Longs
and LOBs with DBI<BR><BR></FONT></DIV>
<P><FONT face=Arial size=2>Is there any difference in working with LONG
datatypes versus LOB datatypes in PERL DBI? I could not find any. In a sence I
need an answer if I change type of column from LONG to CLOB or BLOB, will I have
Received on Tue Jun 06 2000 - 07:55:59 CDT
