Author: byterock
Date: Tue Jul 8 11:02:52 2008
New Revision: 11499
Modified:
dbd-oracle/trunk/Oracle.pm
dbd-oracle/trunk/dbdimp.c
dbd-oracle/trunk/dbdimp.h
dbd-oracle/trunk/oci8.c
dbd-oracle/trunk/ocitrace.h
Log:
updated and cleaned up pod, added some more debugging and fixed little bug on
how ora_versbose was set on the prepare methods.
Modified: dbd-oracle/trunk/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/Oracle.pm (original)
+++ dbd-oracle/trunk/Oracle.pm Tue Jul 8 11:02:52 2008
@@ -318,6 +318,7 @@
ora_charset => undef,
ora_ncharset => undef,
ora_session_mode => undef,
+ ora_verbose => undef,
};
}
@@ -1370,6 +1371,19 @@
$dbh = DBI->connect ($dsn, $user, $passwd,
{ora_charset => 'AL32UTF8'});
+=item ora_verbose
+
+Use this value to enable DBD::Oracle only tracing. Simply
+either set the ora_verbose attribute on the connect() method to the trace
level you desire like this
+
+ my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});
+
+or set it directly on the DB handle like this;
+
+ $dbh->{ora_verbose} =6';
+
+In both cases the DBD::Oracle trace level to 6, which is this level that will
trace most of the calls to OCI.
+
=head2 Database Handle Attributes
=item ora_ph_type
@@ -1440,17 +1454,6 @@
Note that this attribute also applies to C<execute_array>, since that
method is implemented using C<execute_for_fetch>.
-=item ora_verbose
-
-Use this value to enable DBD::Oracle only tracing. Works the same way as
DBI->trace(level), simply
-set the oar_verbose attribute on the connect() to the trace level you desire.
-
-For example:
-
- my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});
-
-Will set the DBD::Oracle trace level to 6, which is this level that will trace
most of the calls to OCI.
-
=back
=head2 Prepare Attributes
@@ -1479,14 +1482,14 @@
BLOB column in most circumstances. If false, fetching retrieves the
Oracle "LOB Locator" of the CLOB or BLOB value.
-See L</Handling LOBs> for more details.
+See L</LOBs and LONGs> for more details.
See also the LOB tests in 05dbi.t of Oracle::OCI for examples
of how to use LOB Locators.
=item ora_pers_lob
-If true the L</Simple Fetch> method for the L</Data Interface for Persistent
LOBs> will be
-used for LOBs.
+If true the L</Simple Fetch for CLOBs and BLOBs> method for the L</Data
Interface for Persistent LOBs> will be
+used for LOBs rather than the default method L</Data Interface for LOB
Locators>.
=item ora_clbk_lob
@@ -1528,6 +1531,10 @@
See L</Prefetching Rows> for more details.
+=item ora_verbose
+
+Use this value to enable DBD::Oracle only tracing. Simply set the attribute
to the trace level you desire.
+
=back
=head2 Placeholder Binding Attributes
@@ -1563,7 +1570,7 @@
See L</Binding Cursors> for the correct way to use ORA_RSET.
-See L</Handling LOBs> for how to use ORA_CLOB and ORA_BLOB.
+See L</LOBs and LONGs> for how to use ORA_CLOB and ORA_BLOB.
See L</SYS.DBMS_SQL datatypes> for ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE.
@@ -1602,6 +1609,7 @@
Specify internal data representation. Currently is supported only for
ORA_NUMBER_TABLE.
+
=back
=head2 Trailing Spaces
@@ -2759,20 +2767,24 @@
number of rows to prefetch otherwise the number of rows will be limited to
memory size. Likewise if the RowCacheSize is less than 1 it
is not included in the computing of the prefetch rows.
-=head1 LOBs
-The key to working with LOBs it to remember the value of an Oracle LOB column
is not the content of the LOB. It's a
-'LOB Locator' which, after being selected or inserted needs extra processing
to read or write the content of the LOB.
+=head1 LOBs and LONGs
-DBD::Oracle now offers three interfaces to carry our this extra processing
requirement for LOB data,
+The key to working with LOBs (CLOB, BLOBs) is to remember the value of an
Oracle LOB column is not the content of the LOB. It's a
+'LOB Locator' which, after being selected or inserted needs extra processing
to read or write the content of the LOB. There are also legacy LONG types
(LONG, LONG RAW, VARCHAR2)
+which are presently deprecated by Oracle but are still in use. These LONG
types do not utilize a 'LOB Locator' and also are more limited in
+functionality than CLOB or BLOB fields.
+
+DBD::Oracle now offers three interfaces to LOB and LONG data,
=item L</Data Interface for Persistent LOBs>
-With this interface DBD::Oracle handles your data directly utilizing regular
OCI calls, Oracle itself takes care of the LOB Locator operations.
+With this interface DBD::Oracle handles your data directly utilizing regular
OCI calls, Oracle itself takes care of the LOB Locator operations in the case
of
+BLOBs and CLOBs treating them exactly as if they were the same as the legacy
LONG or LONG RAW types.
=item L</Data Interface for LOB Locators>
-With this interface DBD::Oracle handles your data utilizing LOB Locators OCI
calls, so DBD::Oracle takes care of the LOB Locator operations.
+With this interface DBD::Oracle handles your data utilizing LOB Locator OCI
calls so it only works with CLOB and BLOB datatypes. With this interface
DBD::Oracle takes care of the LOB Locator operations for you.
=item L</LOB Locator Method Interface>
@@ -2783,38 +2795,79 @@
Generally speaking the interface that you will chose will be dependant on what
end you are trying to achieve. All have their benefits and
drawbacks.
-One point to remember when working with LOBs is if your LOB column is NULL
then there is no LOB Locator that
-DBD::Oracle can work with so it will return a
+One point to remember when working with LOBs (CLOBs, BLOBs) is if your LOB
column can be in one of three states;
+
+=item NULL
+
+The table cell is created, but the cell holds no locator or value.
+If your LOB field is in this state then there is no LOB Locator that
DBD::Oracle can work with so it will return a
DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
error.
-To correct this you must use an SQL UPDATE statement to reset the LOB column
to a non-NULL (or empty LOB) value with an SQL like this;
+To correct this you must use an SQL UPDATE statement to reset the LOB column
to a non-NULL (or empty LOB) value with either EMPTY_BLOB or EMPTY_CLOB as in
this example;
UPDATE lob_example
SET bindata=EMPTY_BLOB()
WHERE bindata IS NULL.
+=item Empty
+
+A LOB instance with a locator exists in the cell, but it has no value. The
length of the LOB is zero. In this case DBD::Oracle will return 'undef' for the
field.
+
+=item Populated
+
+A LOB instance with a locator and a value exists in the cell. You actually get
the LOB value.
+
=head2 Data Interface for Persistent LOBs
-Oracle 9iR1 and later extended the OCI API to work directly with LOB
datatypes. In other words you can treat all LOB type data as if it was
-a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind,
and define operations on LOBs using the same techniques
-you would use on other datatypes that store character or binary data. There
are fewer round trips to the server as no 'LOB Locators' are
+This is the original interface for LONG and LONG RAW datatypes and from Oracle
9iR1 and later the OCI API was extended to work directly with the other LOB
datatypes.
+In other words you can treat all LOB type data (BLOB, CLOB) as if it was a
LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind,
and define operations on LOBs using the same techniques
+you would use on other datatypes that store character or binary data. In some
cases there are fewer round trips to the server as no 'LOB Locators' are
used, normally one can get an entire LOB is a single round trip.
-=head3 Simple Fetch
+=head3 Simple Fetch for LONGs and LONG RAWs
-As the name implies this is the simplest way to use this interface.
DBD::Oracle just attempts to get your LOB as a single large piece.
-To use this interface set the 'ora_pers_lob' attribute of the statement handle
to '1' with the prepare method, as well
-set the database handle's 'LongReadLen' attribute to a value that will be the
larger than the expected size of the LOB. If the size of the LOB exceeds
-the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation' error.
To stop this set the database handle's 'LongTruncOk' attribute to '1'.
+As the name implies this is the simplest way to use this interface.
DBD::Oracle just attempts to get your LONG datatypes as a single large piece.
+There are no special settings, simply set the database handle's 'LongReadLen'
attribute to a value that will be the larger than the expected size of the LONG
or LONG RAW.
+If the size of the LONG or LONG RAW exceeds the 'LongReadLen' DBD::Oracle
will return a 'ORA-24345: A Truncation' error. To stop this set the database
handle's 'LongTruncOk' attribute to '1'.
The maximum value of 'LongReadLen' seems to be dependant on the physical
memory limits of the box that Oracle is running on.
So far this seems to be about 15MB for 32bit system and xxxMB for 64bit. If
you run into an 'ORA-01062: unable to allocate memory for define buffer'
error try setting the size of 'LongReadLen' to a lower value.
For example give this table;
+ CREATE TABLE test_long (
+ id NUMBER,
+ long1 long)
+
+this code;
+
+ $dbh->{LongReadLen} = 2*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL);
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
+
+Will select out all of the long1 fields in the table as long as they are all
under 2MB in length. A value in long1 longer than this will throw an error.
Adding this line;
+
+ $dbh->{LongTruncOk}=1;
+
+before the execute will return all the long1 fields but they will be truncated
at 2MBs.
+
+=head3 Simple Fetch for CLOBs and BLOBs
+
+To use this interface for CLOBs and LOBs datatypes set the 'ora_pers_lob'
attribute of the statement handle to '1' with the prepare method, as well
+set the database handle's 'LongReadLen' attribute to a value that will be the
larger than the expected size of the LOB. If the size of the LOB exceeds
+the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation' error.
To stop this set the database handle's 'LongTruncOk' attribute to '1'.
+The maximum value of 'LongReadLen' seems to be dependant on the physical
memory limits of the box that Oracle is running on in the same way that LONGs
and LONG RAWs are.
+
+For example give this table;
+
CREATE TABLE test_lob (id NUMBER,
clob1 CLOB,
clob2 CLOB,
@@ -2843,13 +2896,14 @@
=head3 Piecewise Fetch with Callback
-With a piecewise callback fetch DBD::Oracle sets up a function that will
'callback' to the DB during the fetch and gets your LOB piece by piece. To use
this interface set the 'ora_clbk_lob'
-attribute of the statement handle to '1' with the prepare method. Next set the
'ora_piece_size' to the size of the piece that
+With a piecewise callback fetch DBD::Oracle sets up a function that will
'callback' to the DB during the fetch and gets your LOB (LONG, LONG RAW, CLOB,
BLOB) piece by piece.
+To use this interface set the 'ora_clbk_lob' attribute of the statement handle
to '1' with the prepare method. Next set the 'ora_piece_size' to the size of
the piece that
you want to return on the callback. Finally set the database handle's
'LongReadLen' attribute to a value that will be the larger than the expected
-size of the LOB. Like the L</Simple Fetch> if the size of the LOB exceeds the
is 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
+size of the LOB. Like the L</Simple Fetch for LONGs and LONG RAWs> and
L</Simple Fetch for CLOBs and BLOBs> the if the size of the LOB exceeds the is
'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
or set the 'LongReadLen' to a higher value. With this interface the value of
'ora_piece_size' seems to be constrained by the same memory limit as found on
-the L</Simple Fetch>. If you encounter an 'ORA-01062' error try setting the
value of 'ora_piece_size' to a smaller value. The value for 'LongReadLen' is
-dependant on the version and setting of the Oracle DB you are using, it ranges
from 8GBs in 9iR1 up to 128 terabytes with 11g.
+the Simple Fetch interface. If you encounter an 'ORA-01062' error try setting
the value of 'ora_piece_size' to a smaller value. The value for 'LongReadLen'
is
+dependant on the version and settings of the Oracle DB you are using. In
theory it ranges from 8GBs
+in 9iR1 up to 128 terabytes with 11g but you will also be limited by the
physical memory of your PERL instance.
Using the table from the last example this code;
@@ -2866,18 +2920,32 @@
}
Will select out all of the LOBs in the table as long as they are all under
20MB in length. If the LOB is longer than 5MB (ora_piece_size) DBD::Oracle will
fetch it in at least 2 pieces to a
-maximum of 4 pieces (4*5MB=20MB). Like the L</Simple Fetch> Lobs longer than
20MB will throw an error.
+maximum of 4 pieces (4*5MB=20MB). Like the Simple Fetch examples Lobs longer
than 20MB will throw an error.
+
+Using the table from the first example (LONG) this code;
+
+ $dbh->{LongReadLen} = 20*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
+
+Will select all of the long1 fields from table as long as they are is under
20MB in length. If the long1 filed is longer than 5MB (ora_piece_size)
DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4*5MB=20MB). Like the other examples long1 fields longer
than 20MB will throw an error.
=head3 Piecewise Fetch with Polling
-With a polling piecewise fetch DBD::Oracle iterates (Polls) over the LOB
during the fetch getting your LOB piece by piece. To use this interface set the
'ora_piece_lob'
+With a polling piecewise fetch DBD::Oracle iterates (Polls) over the LOB
during the fetch getting your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece.
To use this interface set the 'ora_piece_lob'
attribute of the statement handle to '1' with the prepare method. Next set the
'ora_piece_size' to the size of the piece that
you want to return on the callback. Finally set the database handle's
'LongReadLen' attribute to a value that will be the larger than the expected
-size of the LOB. Like the L</Piecewise Fetch with Callback> and L</Simple
Fetch> if the size of the LOB exceeds the is 'LongReadLen' you can use the
'LongTruncOk' attribute to truncate the LOB
+size of the LOB. Like the L</Piecewise Fetch with Callback> and Simple Fetches
if the size of the LOB exceeds the is 'LongReadLen' you can use the
'LongTruncOk' attribute to truncate the LOB
or set the 'LongReadLen' to a higher value. With this interface the value of
'ora_piece_size' seems to be constrained by the same memory limit as found on
-the L</Simple Fetch> and L</Piecewise Fetch with Callback> and the value for
'LongReadLen' is can be the same as in a L</Piecewise Fetch with Callback>.
+the L</Piecewise Fetch with Callback>.
-Using the table from the L</Simple Fetch> example this code;
+Using the table from the example above this code;
$dbh->{LongReadLen} = 20*1024*1024; #20 meg
$SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
@@ -2894,7 +2962,21 @@
Will select out all of the LOBs in the table as long as they are all under
20MB in length. If the LOB is longer than 5MB (ora_piece_size) DBD::Oracle will
fetch it in at least 2 pieces to a
maximum of 4 pieces (4*5MB=20MB). Like the other fetch methods LOBs longer
than 20MB will throw an error.
-=head3 Binding for Updates and Inserts
+Finally with this code;
+
+ $dbh->{LongReadLen} = 20*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
+
+Will select all of the long1 fields from table as long as they are is under
20MB in length. If the long1 field is longer than 5MB (ora_piece_size)
DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4*5MB=20MB). Like the other examples long1 fields longer
than 20MB will throw an error.
+
+=head3 Binding for Updates and Inserts for CLOBs and BLOBs
To bind for updates and inserts all that is required to use this interface is
to set the statement handle's prepare method
'ora_type' attribute to 'SQLT_CHR' in the case of CLOBs and NCLOBs or
'SQLT_BIN' in the case of BLOBs as in this example for an insert;
@@ -2913,7 +2995,7 @@
$sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
$sth->execute();
-So far the only limit reached with this form of insert is the BLOBs must be
under 2GB in size.
+So far the only limit reached with this form of insert is the LOBs must be
under 2GB in size.
=head3 Support for Remote LOBs;
@@ -3000,7 +3082,9 @@
=head3 Simple Usage
When fetching LOBs they are, by default, made to look just like LONGs with
this interface and
-are subject to the LongReadLen and LongTruncOk attributes.
+are subject to the LongReadLen and LongTruncOk attributes. The value for
'LongReadLen' is
+dependant on the version and settings of the Oracle DB you are using. In
theory it ranges from 8GBs
+in 9iR1 up to 128 terabytes with 11g but you will also be limited by the
physical memory of your PERL instance.
When inserting or updating LOBs some I<major> magic has to be performed
behind the scenes to make it transparent. Basically the driver has to
@@ -3084,27 +3168,32 @@
$sth->execute;
-=head3 Persistent & Locator Interface Caveats
+=head2 Persistent & Locator Interface Caveats
Now that one has the option of using the Persistent or the Locator interface
for LOBs the questions arises
which one to use. For starters, if you want to access LOBs over a dblink you
will have to use the Persistent
interface so that choice is simple. The question of which one to use after
that is a little more tricky.
-It basically boils down to a choice between size and speed.
+It basically boils down to a choice between LOB size and speed.
-The Callback and Polling piecewise fetches are very slow
+The Callback and Polling piecewise fetches are very very slow
when compared to the Simple and the Locator fetches but they can handle very
large blocks of data. Given a situation where a
-very very large LOB is to be read the Locator fetch could time out while
either of the piecewise fetches will not.
+large LOB is to be read the Locator fetch may time out while either of the
piecewise fetches will not.
-With the Simple fetch you are limited by physical memory of you server but it
runs a little faster than the Locator, as there are fewer round trips
-to the server. So if you have small LOBs and need to save a little banwidth
this is the one to use.
+With the Simple fetch you are limited by physical memory of your server but it
runs a little faster than the Locator, as there are fewer round trips
+to the server. So if you have small LOBs and need to save a little bandwidth
this is the one to use. It you are going after large LOBs then the Locator
interface is the one to use.
If you need to update more than row of with LOB data then the Persistent
interface can do it while the Locator can't.
+If you encounter a situation where you have to access the legacy LOBs (LONG,
LONG RAW) and the values are to large for you system then you can use
+the Callback or Polling piecewise fetches to get all of the data.
+
Not all of the Persistent interface has been implemented yet, the following
are not supported;
1) Piecewise, polling and callback binds for INSERT and UPDATE operations.
- 2) Piecewise, array binds for SELECT, INSERT and UPDATE operations.
+ 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
+Most of the time you should just use the LOB Locator interface as this is in
one that has the best combination of speed and size.
+
All this being said if you are doing some critical programming I would use the
L</Data Interface for LOB Locators> as this gives you very
fine grain control of your LOBs, of course the code for this will be somewhat
more involved.
Modified: dbd-oracle/trunk/dbdimp.c
==============================================================================
--- dbd-oracle/trunk/dbdimp.c (original)
+++ dbd-oracle/trunk/dbdimp.c Tue Jul 8 11:02:52 2008
@@ -365,6 +365,15 @@
ub2 new_charsetid = 0;
ub2 new_ncharsetid = 0;
/* check to see if DBD_verbose or ora_verbose is set*/
+ if (DBD_ATTRIB_TRUE(attr,"dbd_verbose",11,svp))
+ DBD_ATTRIB_GET_IV( attr, "dbd_verbose", 11, svp, dbd_verbose);
+ if (DBD_ATTRIB_TRUE(attr,"ora_verbose",11,svp))
+ DBD_ATTRIB_GET_IV( attr, "ora_verbose", 11, svp, dbd_verbose);
+
+
+
+ /*if (DBD_ATTRIB_GET_IV( attribs, "dbd_verbose", 11, svp, dbd_verbose);
+ DBD_ATTRIB_GET_IV( attribs, "ora_verbose", 11, svp,
dbd_verbose);
if ( (svp=DBD_ATTRIB_GET_SVP(attr, "dbd_verbose",11)) && SvOK(*svp) ||
(svp=DBD_ATTRIB_GET_SVP(attr, "ora_verbose",11)) && SvOK(*svp))
dbd_verbose =(int)svp;
@@ -430,7 +439,7 @@
imp_dbh->get_oci_handle = oci_db_handle;
if (DBIS->debug >= 6 || dbd_verbose >= 6)
- dump_env_to_trace();
+ dump_env_to_trace();
if ((svp=DBD_ATTRIB_GET_SVP(attr, "ora_envhp", 9)) && SvOK(*svp)) {
if (!SvTRUE(*svp)) {
@@ -1593,8 +1602,8 @@
}
if (!phs->csid_orig) { /* get the default csid Oracle would use */
- OCIAttrGet_log_stat(phs->bndhp, OCI_HTYPE_BIND, &phs->csid_orig, (ub4)0
,
- OCI_ATTR_CHARSET_ID, imp_sth->errhp, status);
+ OCIAttrGet_log_stat(phs->bndhp, OCI_HTYPE_BIND,
&phs->csid_orig, (ub4)0 ,
+ OCI_ATTR_CHARSET_ID, imp_sth->errhp, status);
}
/* if app has specified a csid then use that, else use default */
@@ -1605,23 +1614,23 @@
csid = utf8_csid; /* not al32utf8_csid here on purpose */
if (trace_level >= 3 || dbd_verbose >= 3 )
- PerlIO_printf(DBILOGFP, "dbd_rebind_ph_varchar2_table(): bind %s <== %s
"
- "(%s, %s, csid %d->%d->%d, ftype %d, csform %d->%d, maxlen %lu,
maxdata_size %lu)\n",
- phs->name, neatsvpv(phs->sv,0),
- (phs->is_inout) ? "inout" : "in",
- flag_data_is_utf8 ? "is-utf8" : "not-utf8",
- phs->csid_orig, phs->csid, csid,
- phs->ftype, phs->csform, csform,
- (unsigned long)phs->maxlen, (unsigned long)phs->maxdata_size);
+ PerlIO_printf(DBILOGFP, "dbd_rebind_ph_varchar2_table(): bind
%s <== %s "
+ "(%s, %s, csid %d->%d->%d, ftype %d, csform %d->%d,
maxlen %lu, maxdata_size %lu)\n",
+ phs->name, neatsvpv(phs->sv,0),
+ (phs->is_inout) ? "inout" : "in",
+ flag_data_is_utf8 ? "is-utf8" : "not-utf8",
+ phs->csid_orig, phs->csid, csid,
+ phs->ftype, phs->csform, csform,
+ (unsigned long)phs->maxlen, (unsigned long)phs->maxdata_size);
if (csid) {
- OCIAttrSet_log_stat(phs->bndhp, (ub4) OCI_HTYPE_BIND,
- &csid, (ub4) 0, (ub4) OCI_ATTR_CHARSET_ID, imp_sth->errhp, status);
- if ( status != OCI_SUCCESS ) {
- oci_error(sth, imp_sth->errhp, status,
ora_sql_error(imp_sth,"OCIAttrSet (OCI_ATTR_CHARSET_ID)"));
- return 0;
- }
+ OCIAttrSet_log_stat(phs->bndhp, (ub4) OCI_HTYPE_BIND,
+ &csid, (ub4) 0, (ub4) OCI_ATTR_CHARSET_ID, imp_sth->errhp,
status);
+ if ( status != OCI_SUCCESS ) {
+ oci_error(sth, imp_sth->errhp, status,
ora_sql_error(imp_sth,"OCIAttrSet (OCI_ATTR_CHARSET_ID)"));
+ return 0;
+ }
}
if (phs->maxdata_size) {
@@ -2180,14 +2189,14 @@
if (DBIS->debug >= 2 || dbd_verbose >=2 ) {
- char *val = neatsvpv(phs->sv,0);
+ char *val = neatsvpv(phs->sv,10);
PerlIO_printf(DBILOGFP, "dbd_rebind_ph_char() (1): bind %s <==
%.1000s (", phs->name, val);
if (!SvOK(phs->sv))
PerlIO_printf(DBILOGFP, "NULL, ");
PerlIO_printf(DBILOGFP, "size %ld/%ld/%ld, ",
(long)SvCUR(phs->sv),(long)SvLEN(phs->sv),phs->maxlen);
- PerlIO_printf(DBILOGFP, "ptype %d, otype %d%s)\n",
- (int)SvTYPE(phs->sv), phs->ftype,
+ PerlIO_printf(DBILOGFP, "ptype %d(%s), otype %d%s)\n",
+ (int)SvTYPE(phs->sv), sql_typecode_name(phs->ftype),phs->ftype,
(phs->is_inout) ? ", inout" : "");
}
@@ -2248,11 +2257,12 @@
if (DBIS->debug >= 3 || dbd_verbose >=3) {
UV neatsvpvlen = (UV)DBIc_DBISTATE(imp_sth)->neatsvpvlen;
- PerlIO_printf(DBILOGFP, "dbd_rebind_ph_char() (2): bind %s <==
'%.*s' (size %ld/%ld, otype %d, indp %d, at_exec %d)\n",
+ char *val = neatsvpv(phs->sv,10);
+ PerlIO_printf(DBILOGFP, "dbd_rebind_ph_char() (2): bind %s <==
'%.*s' (size %ld/%ld, otype %d(%s), indp %d, at_exec %d)\n",
phs->name,
(int)(phs->alen > neatsvpvlen ? neatsvpvlen : phs->alen),
- (phs->progv) ? phs->progv : "",
- (long)phs->alen, (long)phs->maxlen, phs->ftype, phs->indp,
at_exec);
+ (phs->progv) ? val: "",
+ (long)phs->alen, (long)phs->maxlen,
phs->ftype,sql_typecode_name(phs->ftype), phs->indp, at_exec);
}
return 1;
@@ -2471,9 +2481,9 @@
ub2 csid;
if (trace_level >= 5 || dbd_verbose >= 5 )
- PerlIO_printf(DBILOGFP, "dbd_rebind_ph() (1): rebinding %s as
%s (%s, ftype %d, csid %d, csform %d, inout %d)\n",
- phs->name, (SvPOK(phs->sv) ? neatsvpv(phs->sv,0) :
"NULL"),(SvUTF8(phs->sv) ? "is-utf8" : "not-utf8"),
- phs->ftype, phs->csid, phs->csform, phs->is_inout);
+ PerlIO_printf(DBILOGFP, "dbd_rebind_ph() (1): rebinding %s as
%s (%s, ftype %d (%s), csid %d, csform %d, inout %d)\n",
+ phs->name, (SvPOK(phs->sv) ? neatsvpv(phs->sv,10) :
"NULL"),(SvUTF8(phs->sv) ? "is-utf8" : "not-utf8"),
+ phs->ftype,sql_typecode_name(phs->csid), phs->csform,
phs->is_inout);
switch (phs->ftype) {
@@ -2580,12 +2590,12 @@
if (trace_level >= 3 || dbd_verbose >= 3 )
PerlIO_printf(DBILOGFP, "dbd_rebind_ph(): bind %s <== %s "
- "(%s, %s, csid %d->%d->%d, ftype %d, csform %d->%d, maxlen %lu,
maxdata_size %lu)\n",
- phs->name, neatsvpv(phs->sv,0),
+ "(%s, %s, csid %d->%d->%d, ftype %d (%s), csform %d->%d, maxlen
%lu, maxdata_size %lu)\n",
+ phs->name, neatsvpv(phs->sv,10),
(phs->is_inout) ? "inout" : "in",
(SvUTF8(phs->sv) ? "is-utf8" : "not-utf8"),
phs->csid_orig, phs->csid, csid,
- phs->ftype, phs->csform, csform,
+ phs->ftype,sql_typecode_name(phs->ftype), phs->csform, csform,
(unsigned long)phs->maxlen, (unsigned long)phs->maxdata_size);
@@ -2658,8 +2668,8 @@
croak("Can't bind ``lvalue'' mode scalar as inout parameter
(currently)");
if (DBIS->debug >= 2 || dbd_verbose >=2) {
- PerlIO_printf(DBILOGFP, "dbd_bind_ph(): bind %s <== %s (type
%ld",
- name, neatsvpv(newvalue,0), (long)sql_type);
+ PerlIO_printf(DBILOGFP, "dbd_bind_ph(): bind %s <== %s (type
%ld (%s)",
+ name, neatsvpv(newvalue,0),
(long)sql_type,sql_typecode_name(sql_type));
if (is_inout)
PerlIO_printf(DBILOGFP, ", inout 0x%lx, maxlen %ld",
(long)newvalue, (long)maxlen);
Modified: dbd-oracle/trunk/dbdimp.h
==============================================================================
--- dbd-oracle/trunk/dbdimp.h (original)
+++ dbd-oracle/trunk/dbdimp.h Tue Jul 8 11:02:52 2008
@@ -290,12 +290,15 @@
int oci_error_err _((SV *h, OCIError *errhp, sword status, char *what, sb4
force_err));
#define oci_error(h, errhp, status, what) oci_error_err(h, errhp, status,
what, 0)
char *oci_stmt_type_name _((int stmt_type));
+char *oci_typecode_name _((int typecode));
+char *sql_typecode_name _((int dbtype));
char *oci_status_name _((sword status));
char *oci_mode _((ub4 mode));
char *oci_bind_options _((ub4 options));
char *oci_define_options _((ub4 options));
char *oci_hdtype_name _((ub4 hdtype));
char *oci_exe_mode _((ub4 mode));
+char *oci_col_return_codes _((int rc));
int dbd_rebind_ph_lob _((SV *sth, imp_sth_t *imp_sth, phs_t *phs));
int dbd_rebind_ph_nty _((SV *sth, imp_sth_t *imp_sth, phs_t *phs));
Modified: dbd-oracle/trunk/oci8.c
==============================================================================
--- dbd-oracle/trunk/oci8.c (original)
+++ dbd-oracle/trunk/oci8.c Tue Jul 8 11:02:52 2008
@@ -66,6 +66,42 @@
return SvPVX(sv);
}
+/* SQL Types we support for placeholders basically we support types that can
be returned as strings */
+char *
+sql_typecode_name(int dbtype) {
+ dTHX;
+ SV *sv;
+ switch(dbtype) {
+ case 0: return "DEFAULT (varchar)";
+ case 1: return "VARCHAR";
+ case 2: return "NVARCHAR2";
+ case 5: return "STRING";
+ case 8: return "LONG";
+ case 21: return "BINARY FLOAT os-endian";
+ case 22: return "BINARY DOUBLE os-endian";
+ case 23: return "RAW";
+ case 24: return "LONG RAW";
+ case 96: return "CHAR";
+ case 97: return "CHARZ";
+ case 100: return "BINARY FLOAT oracle-endian";
+ case 101: return "BINARY DOUBLE oracle-endian";
+ case 106: return "MLSLABEL";
+ case 102: return "SQLT_CUR OCI 7 cursor variable";
+ case 112: return "SQLT_CLOB / long";
+ case 113: return "SQLT_BLOB / long";
+ case 116: return "SQLT_RSET OCI 8 cursor variable";
+ case ORA_VARCHAR2_TABLE:return "ORA_VARCHAR2_TABLE";
+ case ORA_NUMBER_TABLE: return "ORA_NUMBER_TABLE";
+ case ORA_XMLTYPE: return "ORA_XMLTYPE or SQLT_NTY";/* SQLT_NTY
must be carefull here as its value (108) is the same for an embedded object
Well realy only XML clobs not embedded objects */
+
+ }
+ sv = sv_2mortal(newSVpv("",0));
+ sv_grow(sv, 50);
+ sprintf(SvPVX(sv),"(UNKNOWN SQL TYPECODE %d)", dbtype);
+ return SvPVX(sv);
+}
+
+
char *
oci_typecode_name(int typecode){
@@ -260,6 +296,23 @@
return SvPVX(sv);
}
+char *
+oci_col_return_codes(int rc)
+{
+ dTHX;
+ SV *sv;
+ switch (rc) {
+ case 1406: return "TRUNCATED";
+ case 0: return "OK";
+ case 1405: return "NULL";
+ case 1403: return "NO DATA";
+
+ }
+ sv = sv_2mortal(newSVpv("",0));
+ sv_grow(sv, 50);
+ sprintf(SvPVX(sv),"UNKNOWN RC=%d)", rc);
+ return SvPVX(sv);
+}
char *
oci_hdtype_name(ub4 hdtype)
@@ -513,12 +566,15 @@
DBD_ATTRIB_GET_IV( attribs, "ora_check_sql", 13, svp,
ora_check_sql);
DBD_ATTRIB_GET_IV( attribs, "ora_exe_mode", 12, svp,
imp_sth->exe_mode);
DBD_ATTRIB_GET_IV( attribs, "ora_prefetch_memory", 19, svp,
imp_sth->prefetch_memory);
- DBD_ATTRIB_GET_IV( attribs, "dbd_verbose", 11, svp,
dbd_verbose);
- DBD_ATTRIB_GET_IV( attribs, "ora_verbose", 11, svp,
dbd_verbose);
-
+ DBD_ATTRIB_GET_IV( attribs, "ora_verbose", 11, svp, dbd_verbose);
+
+ if (!dbd_verbose)
+ DBD_ATTRIB_GET_IV( attribs, "dbd_verbose", 11, svp,
dbd_verbose);
+
+
}
-
+
/* scan statement for '?', ':1' and/or ':foo' style placeholders
*/
if (ora_placeholders)
dbd_preparse(imp_sth, statement);
@@ -640,9 +696,9 @@
*indpp = &phs->indp;
*piecep = OCI_ONE_PIECE;
if (DBIS->debug >= 3 || dbd_verbose >=3)
- PerlIO_printf(DBILOGFP, " in '%s' [%lu,%lu]: len %2lu,
ind %d%s, value='%s'\n",
+ PerlIO_printf(DBILOGFP, " in '%s' [%lu,%lu]: len %2lu,
ind %d%s, value=%s\n",
phs->name, ul_t(iter), ul_t(index), ul_t(phs->alen),
phs->indp,
- (phs->desc_h) ? " via descriptor" :
"",neatsvpv(phs->sv,0));
+ (phs->desc_h) ? " via descriptor" :
"",neatsvpv(phs->sv,10));
if (!tuples_av && (index > 0 || iter > 0))
croak(" Arrays and multiple iterations not currently supported
by DBD::Oracle (in %d/%d)", index,iter);
return OCI_CONTINUE;
@@ -837,33 +893,32 @@
#ifdef UTF8_SUPPORT
if (fbh->ftype == 94) {
- if (datalen > imp_sth->long_readlen) {
- ub4 bytelen = ora_utf8_to_bytes((ub1*)p,
(ub4)imp_sth->long_readlen, datalen);
+ if (datalen > imp_sth->long_readlen) {
+ ub4 bytelen = ora_utf8_to_bytes((ub1*)p,
(ub4)imp_sth->long_readlen, datalen);
- if (bytelen < datalen) { /* will be truncated */
- int oraperl = DBIc_COMPAT(imp_sth);
- if (DBIc_has(imp_sth,DBIcf_LongTruncOk)
- || (oraperl && SvIV(imp_drh->ora_trunc))) {
- /* user says truncation is ok */
- /* Oraperl recorded the truncation in ora_errno so we
*/
- /* so also but only for Oraperl mode handles.
*/
- if (oraperl) sv_setiv(DBIc_ERR(imp_sth), 1406);
- } else {
- char buf[300];
- sprintf(buf,"fetching field %d of %d. LONG value truncated
from %lu to %lu. %s",
- fbh->field_num+1, DBIc_NUM_FIELDS(imp_sth),
ul_t(datalen), ul_t(bytelen),
- "DBI attribute LongReadLen too small and/or
LongTruncOk not set");
- oci_error_err(sth, NULL, OCI_ERROR, buf, 24345); /*
appropriate ORA error number */
- sv_set_undef(dest_sv);
- return 0;
- }
+ if (bytelen < datalen ) { /* will be truncated */
+ int oraperl = DBIc_COMPAT(imp_sth);
+ if (DBIc_has(imp_sth,DBIcf_LongTruncOk) ||
(oraperl && SvIV(imp_drh->ora_trunc))) {
+ /* user says truncation is ok */
+ /* Oraperl recorded the truncation in
ora_errno so we */
+ /* so also but only for Oraperl mode
handles. */
+ if (oraperl) sv_setiv(DBIc_ERR(imp_sth),
1406);
+ } else {
+ char buf[300];
+ sprintf(buf,"fetching field %d of %d. LONG
value truncated from %lu to %lu. %s",
+ fbh->field_num+1,
DBIc_NUM_FIELDS(imp_sth), ul_t(datalen), ul_t(bytelen),
+ "DBI attribute LongReadLen too
small and/or LongTruncOk not set");
+ oci_error_err(sth, NULL, OCI_ERROR, buf,
24345); /* appropriate ORA error number */
+ sv_set_undef(dest_sv);
+ return 0;
+ }
- if (DBIS->debug >= 3 || dbd_verbose >=3)
- PerlIO_printf(DBILOGFP, " fetching field %d of %d.
LONG value truncated from %lu to %lu.\n",
- fbh->field_num+1, DBIc_NUM_FIELDS(imp_sth),
- ul_t(datalen), ul_t(bytelen));
- datalen = bytelen;
- }
+ if (DBIS->debug >= 3 || dbd_verbose >=3)
+ PerlIO_printf(DBILOGFP, " fetching field %d
of %d. LONG value truncated from %lu to %lu.\n",
+ fbh->field_num+1, DBIc_NUM_FIELDS(imp_sth),
+ ul_t(datalen), ul_t(bytelen));
+ datalen = bytelen;
+ }
}
sv_setpvn(dest_sv, p, (STRLEN)datalen);
if (CSFORM_IMPLIES_UTF8(fbh->csform))
@@ -2557,6 +2612,12 @@
fbh->name = SvPVX(fbh->name_sv);
fbh->ftype = 5; /* default: return as null terminated
string */
+
+
+ if (DBIS->debug >= 4 || dbd_verbose >= 4)
+ PerlIO_printf(DBILOGFP, "Describe col #%d
type=%d(%s)\n",i,fbh->dbtype,sql_typecode_name(fbh->dbtype));
+
+
switch (fbh->dbtype) {
/* the simple types */
case 1: /* VARCHAR2
*/
@@ -2592,23 +2653,86 @@
break;
case 8: /* LONG
*/
- if ( CSFORM_IMPLIES_UTF8(fbh->csform) &&
!CS_IS_UTF8(fbh->csid) )
- fbh->disize = long_readlen * 4;
- else
- fbh->disize = long_readlen;
+
+ if (imp_sth->clbk_lob){ /*get by peice with callback
a slow*/
+
+ fbh->clbk_lob = 1;
+ fbh->define_mode = OCI_DYNAMIC_FETCH;
/* piecwise fetch*/
+ fbh->disize =
imp_sth->long_readlen; /*user set max value for the fetch*/
+ fbh->piece_size =
imp_sth->piece_size; /*the size for each piece*/
+ fbh->fetch_cleanup =
fetch_cleanup_pres_lobs; /* clean up buffer before each fetch*/
+
+ if (!imp_sth->piece_size){ /*if not set use
max value*/
+
imp_sth->piece_size=imp_sth->long_readlen;
+ }
+
+ fbh->ftype = SQLT_CHR;
+ fbh->fetch_func = fetch_clbk_lob;
+
+ } else if (imp_sth->piece_lob){ /*get by peice
with polling slowest*/
+
+ fbh->piece_lob = 1;
+ fbh->define_mode = OCI_DYNAMIC_FETCH;
/* piecwise fetch*/
+ fbh->disize =
imp_sth->long_readlen; /*user set max value for the fetch*/
+ fbh->piece_size =
imp_sth->piece_size; /*the size for each piece*/
+ fbh->fetch_cleanup =
fetch_cleanup_pres_lobs; /* clean up buffer before each fetch*/
+
+ if (!imp_sth->piece_size){ /*if not set
use max value*/
+
imp_sth->piece_size=imp_sth->long_readlen;
+ }
+ fbh->ftype = SQLT_CHR;
+ fbh->fetch_func = fetch_get_piece;
+ }else {
+
+ if ( CSFORM_IMPLIES_UTF8(fbh->csform)
&& !CS_IS_UTF8(fbh->csid) )
+ fbh->disize = long_readlen * 4;
+ else
+ fbh->disize = long_readlen;
/* not governed by else: */
- fbh->dbsize = (fbh->disize>65535) ? 65535 :
fbh->disize;
- fbh->ftype = 94; /* VAR form */
- fbh->fetch_func = fetch_func_varfield;
- ++has_longs;
+ fbh->dbsize = (fbh->disize>65535) ?
65535 : fbh->disize;
+ fbh->ftype = 94; /* VAR form */
+ fbh->fetch_func = fetch_func_varfield;
+ ++has_longs;
+
+ }
break;
case 24: /* LONG RAW
*/
- fbh->disize = long_readlen * 2;
- fbh->dbsize = (fbh->disize>65535) ? 65535 :
fbh->disize;
- fbh->ftype = 95; /* VAR form */
- fbh->fetch_func = fetch_func_varfield;
- ++has_longs;
+ if (imp_sth->clbk_lob){ /*get by peice with
callback a slow*/
+
+ fbh->clbk_lob = 1;
+ fbh->define_mode =
OCI_DYNAMIC_FETCH; /* piecwise fetch*/
+ fbh->disize =
imp_sth->long_readlen; /*user set max value for the fetch*/
+ fbh->piece_size =
imp_sth->piece_size; /*the size for each piece*/
+ fbh->fetch_cleanup =
fetch_cleanup_pres_lobs; /* clean up buffer before each fetch*/
+
+ if (!imp_sth->piece_size){ /*if not
set use max value*/
+
imp_sth->piece_size=imp_sth->long_readlen;
+ }
+
+ fbh->ftype = SQLT_BIN;
+ fbh->fetch_func = fetch_clbk_lob;
+
+ } else if (imp_sth->piece_lob){ /*get by peice
with polling slowest*/
+
+ fbh->piece_lob = 1;
+ fbh->define_mode =
OCI_DYNAMIC_FETCH; /* piecwise fetch*/
+ fbh->disize =
imp_sth->long_readlen; /*user set max value for the fetch*/
+ fbh->piece_size =
imp_sth->piece_size; /*the size for each piece*/
+ fbh->fetch_cleanup =
fetch_cleanup_pres_lobs; /* clean up buffer before each fetch*/
+
+ if (!imp_sth->piece_size){ /*if
not set use max value*/
+
imp_sth->piece_size=imp_sth->long_readlen;
+ }
+ fbh->ftype = SQLT_BIN;
+ fbh->fetch_func =
fetch_get_piece;
+ }else {
+ fbh->disize = long_readlen * 2;
+ fbh->dbsize = (fbh->disize>65535) ?
65535 : fbh->disize;
+ fbh->ftype = 95; /* VAR form */
+ fbh->fetch_func = fetch_func_varfield;
+ ++has_longs;
+ }
break;
case 11: /* ROWID
*/
@@ -2734,9 +2858,9 @@
if (DBIS->debug >= 3 || dbd_verbose >= 3)
PerlIO_printf(DBILOGFP,
- " col %2d: dbtype %d, scale %d, prec %d, nullok %d, name
%s\n"
+ "Described col %2d: dbtype %d(%s), scale %d, prec %d,
nullok %d, name %s\n"
" : dbsize %d, char_used %d, char_size %d, csid
%d, csform %d, disize %d\n",
- i, fbh->dbtype, fbh->scale, fbh->prec,
fbh->nullok, fbh->name,
+ i, fbh->dbtype,
sql_typecode_name(fbh->dbtype),fbh->scale, fbh->prec, fbh->nullok, fbh->name,
fbh->dbsize, fbh->len_char_used,
fbh->len_char_size, fbh->csid, fbh->csform, fbh->disize);
if (fbh->ftype == 5) /* XXX need to handle wide chars
somehow */
@@ -2971,7 +3095,7 @@
av = DBIS->get_fbav(imp_sth);
if (DBIS->debug >= 3 || dbd_verbose >= 3) {
- PerlIO_printf(DBILOGFP, " dbd_st_fetch %d fields %s\n",
num_fields, oci_status_name(status));
+ PerlIO_printf(DBILOGFP, " dbd_st_fetched %d fields with
status of %d(%s)\n", num_fields,status, oci_status_name(status));
}
ChopBlanks = DBIc_has(imp_sth, DBIcf_ChopBlanks);
@@ -2986,13 +3110,17 @@
ub1*
row_data=&fb_ary->abuf[0]+(fb_ary->bufl*imp_sth->rs_array_idx);
SV *sv = AvARRAY(av)[i]; /* Note: we (re)use the SV in the AV
*/;
+
+ if (DBIS->debug >= 4 || dbd_verbose >= 4) {
+ PerlIO_printf(DBILOGFP, " field #%d with
rc=%d(%s)\n",i+1,rc,oci_col_return_codes(rc));
+ }
+
if (rc == 1406 /* field was truncated
*/
&& ora_dbtype_is_long(fbh->dbtype)/* field is a LONG
*/
){
int oraperl = DBIc_COMPAT(imp_sth);
D_imp_dbh_from_sth ;
D_imp_drh_from_dbh ;
-
if (DBIc_has(imp_sth,DBIcf_LongTruncOk) || (oraperl &&
SvIV(imp_drh -> ora_trunc))) {
/* user says truncation is ok */
/* Oraperl recorded the truncation in ora_errno so we
*/
@@ -3002,10 +3130,11 @@
}
/* else fall through and let rc trigger failure below */
}
-
- if (rc == 0 || /* the normal case*/
- ( rc == 1406 &&
DBIc_has(imp_sth,DBIcf_LongTruncOk))/*or a trunckated record when using 10.2
Persistent Lob interface*/
- ) {
+
+ if (rc == 0 || /* the normal case*/
+ (rc == 1406 && DBIc_has(imp_sth,DBIcf_LongTruncOk))/*Field
Truncaded*/
+ ) {
+
if (fbh->fetch_func) {
if (!fbh->fetch_func(sth, fbh, sv)){
@@ -3064,7 +3193,7 @@
}
if (DBIS->debug >= 5 || dbd_verbose >= 5){
- PerlIO_printf(DBILOGFP, "\n %p (rc=%d): %s\n",
av, i,neatsvpv(sv,0));
+ PerlIO_printf(DBILOGFP, "\n %p (field=%d): %s\n",
av, i,neatsvpv(sv,10));
}
}
return (err) ? Nullav : av;
Modified: dbd-oracle/trunk/ocitrace.h
==============================================================================
--- dbd-oracle/trunk/ocitrace.h (original)
+++ dbd-oracle/trunk/ocitrace.h Tue Jul 8 11:02:52 2008
@@ -49,8 +49,8 @@
stat =OCIStmtSetPieceInfo(ptr,OCI_HTYPE_DEFINE,errhp, buf, blen,
p,indp,rc);\
(DBD_OCI_TRACEON) \
? PerlIO_printf(DBD_OCI_TRACEFP,\
- "%sOCIStmtSetPieceInfo_log_stat(%p,%p,%d,%s,%d)=%s\n",\
- OciTp,
(void*)errhp,fbh,piece,buf,blen,oci_status_name(stat)),stat \
+ "%sOCIStmtSetPieceInfo_log_stat(%p,%p,%d,%d)=%s\n",\
+ OciTp,
(void*)errhp,fbh,piece,blen,oci_status_name(stat)),stat \
: stat
