Author: byterock
Date: Thu Jan 15 09:51:55 2009
New Revision: 12446
Modified:
dbd-oracle/trunk/Changes
dbd-oracle/trunk/Oracle.pm
dbd-oracle/trunk/dbdimp.c
dbd-oracle/trunk/dbdimp.h
dbd-oracle/trunk/oci8.c
Log:
Fix for a reported issue from Martin Evans regarding very slow cursor fetch.
The root issue was that a ref cursor cannot buffer more than 1 row when it has
lobs this was the default for all ref cursors which makes them all slow. This
way only ones with lobs will be slow.
Modified: dbd-oracle/trunk/Changes
==============================================================================
--- dbd-oracle/trunk/Changes (original)
+++ dbd-oracle/trunk/Changes Thu Jan 15 09:51:55 2009
@@ -1,6 +1,7 @@
=head1 Changes in DBD-Oracle 1.23(svn rev #####)
- Added rt.cpan.org Ticket #=42328 ora_objects attribute for extended embedded
objects support from tnt at netsafe.cz
- Fix for rt.cpan.org Ticket #=42328 user defined types from different schema
in describe_obj from tnt at netsafe.cz
+ Added a change from a suggestion from Martic Evans for makeing ref cursors
faster.
+ Added rt.cpan.org Ticket #=42328 ora_objects attribute for extended embedded
objects support from Tomas Pokorny
+ Fix for rt.cpan.org Ticket #=42328 user defined types from different schema
in describe_obj from Tomas Pokorny
Added a README for sun suggested by Jim McCullars
Clean up of white space and formating to 4 tabs from John Scoles
Fix for GCC 4.3 warnings from Eric Simon
Modified: dbd-oracle/trunk/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/Oracle.pm (original)
+++ dbd-oracle/trunk/Oracle.pm Thu Jan 15 09:51:55 2009
@@ -3990,10 +3990,11 @@
DBD::Oracle supports only the 'selection' of embedded objects of the following
types OBJECT, VARRAY
and TABLE in any combination. Support is seamless and recursive, meaning you
-need only supply a simple SQL statement to get all the values in an embedded
object
-as an array of scalars.
+need only supply a simple SQL statement to get all the values in an embedded
object.
+You can either get the values as an array of scalars or they can be returned
into a DBD::Oracle::Object.
-For example, given this type and table;
+
+Array example, given this type and table;
CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
@@ -4059,6 +4060,51 @@
print "\n";
}
+Object example, given this object and table;
+
+ CREATE OR REPLACE TYPE Person AS OBJECT (
+ name VARCHAR2(20),
+ age INTEGER)
+ ) NOT FINAL;
+
+ CREATE TYPE Employee UNDER Person (
+ salary NUMERIC(8,2)
+ );
+
+ CREATE TABLE people (id INTEGER, obj Person);
+
+ INSERT INTO people VALUES (1, Person('Black', 25));
+ INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
+
+The following code will access the data;
+
+ $dbh{'ora_objects'} =>1;
+
+ $sth = $dbh->prepare("select * from people order by id");
+ $sth->execute();
+
+ # object are fetched as instance of DBD::Oracle::Object
+ my ($id1, $obj1) = $sth->fetchrow();
+ my ($id2, $obj2) = $sth->fetchrow();
+
+ # get full type-name of object
+ print $obj1->type_name."44\n"; # 'TEST.PERSON' is printed
+ print $obj2->type_name."4\n"; # 'TEST.EMPLOYEE' is printed
+
+ # get attribute NAME from object
+ print $obj1->attr('NAME')."3\n"; # 'Black' is printed
+ print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
+
+ # get all atributes as hash reference
+ my $h1 = $obj1->attr; # returns {'NAME' => 'Black', 'AGE' => 25}
+ my $h2 = $obj2->attr; # returns {'NAME' => 'Smith', 'AGE' => 44,
+ # 'SALARY' => 5000 }
+
+ # get all attributes (names and values) as array
+ my @a1 = $obj1->attributes; # returns ('NAME', 'Black', 'AGE', 25)
+ my @a2 = $obj2->attributes; # returns ('NAME', 'Smith', 'AGE', 44,
+ # 'SALARY', 5000 )
+
So far DBD::Oracle has been tested on a table with 20 embedded Objects,
Varrays and Tables
nested to 10 levels.
Modified: dbd-oracle/trunk/dbdimp.c
==============================================================================
--- dbd-oracle/trunk/dbdimp.c (original)
+++ dbd-oracle/trunk/dbdimp.c Thu Jan 15 09:51:55 2009
@@ -2395,7 +2395,7 @@
if (status != OCI_SUCCESS) {
oci_error(sth, imp_sth->errhp, status, "OCIBindByName
SQLT_RSET");
return 0;
- }
+ }
ENTER;
SAVETMPS;
Modified: dbd-oracle/trunk/dbdimp.h
==============================================================================
--- dbd-oracle/trunk/dbdimp.h (original)
+++ dbd-oracle/trunk/dbdimp.h Thu Jan 15 09:51:55 2009
@@ -72,17 +72,17 @@
int is_child; /* if this is child from a
ref cursor or SP*/
ub2 stmt_type; /* OCIAttrGet
OCI_ATTR_STMT_TYPE */
U16 auto_lob; /* use auto lobs*/
- int pers_lob; /*use dblink for lobs only
for 10g Release 2. or later*/
- int clbk_lob; /*use dblink for lobs only
for 10g Release 2. or later*/
- int piece_lob; /*use piece fetch for lobs*/
- ub4 piece_size; /*used in callback to set
the size of the piece to get*/
- int has_lobs; /* Statement has bound LOBS*/
-
+ int pers_lob; /*use dblink for lobs
only for 10g Release 2. or later*/
+ int clbk_lob; /*use dblink for lobs
only for 10g Release 2. or later*/
+ int piece_lob; /*use piece fetch for
lobs*/
+ ub4 piece_size; /*used in callback to
set the size of the piece to get*/
+ int has_lobs; /*Statement has bound LOBS */
+ int ret_lobs; /*Statement returns
LOBS */
lob_refetch_t *lob_refetch;
- int nested_cursor; /* cursors fetched from
SELECTs */
- AV *bind_tuples; /* Bind tuples in array
execute, or NULL */
- int rowwise; /* If true,
bind_tuples is list of */
- /* tuples,
otherwise list of columns. */
+ int nested_cursor; /* cursors fetched from
SELECTs */
+ AV *bind_tuples; /* Bind tuples in array
execute, or NULL */
+ int rowwise; /* If true,
bind_tuples is list of */
+ /*
tuples, otherwise list of columns. */
/* Input Details */
char *statement; /* sql (see sth_scan)
*/
HV *all_params_hv; /* all params, keyed by
name */
Modified: dbd-oracle/trunk/oci8.c
==============================================================================
--- dbd-oracle/trunk/oci8.c (original)
+++ dbd-oracle/trunk/oci8.c Thu Jan 15 09:51:55 2009
@@ -1043,7 +1043,7 @@
}
else {
/* Call a special rebinder for cursor ref "in" params */
- return(pp_rebind_ph_rset_in(sth, imp_sth, phs));
+ return(pp_rebind_ph_rset_in(sth, imp_sth, phs));
}
}
@@ -2293,7 +2293,7 @@
imp_sth->cache_rows = SvIV(imp_drh->ora_cache);
}
- if (imp_sth->is_child){ /*ref cursors and sp only one row is allowed*/
+ if (imp_sth->is_child && imp_sth->ret_lobs){ /*ref cursors and sp only
one row is allowed*/
cache_rows =1;
cache_mem =0;
@@ -2873,25 +2873,25 @@
case ORA_BLOB: /* BLOB
*/
case 114: /* BFILE
*/
fbh->ftype = fbh->dbtype;
-
+ imp_sth->ret_lobs = 1;
/* do we need some addition size logic here?
(lab) */
if (imp_sth->pers_lob){ /*get as one peice
fasted but limited to how big you can get.*/
- fbh->pers_lob = 1;
- fbh->disize =
fbh->disize+long_readlen; /*user set max value for the fetch*/
+ fbh->pers_lob = 1;
+ fbh->disize =
fbh->disize+long_readlen; /*user set max value for the fetch*/
if (fbh->dbtype == ORA_CLOB){
- fbh->ftype = SQLT_CHR;
+ fbh->ftype = SQLT_CHR;
}
else {
fbh->ftype = SQLT_LVB; /*Binary
form seems this is the only value where we cna get the length correctly*/
}
}
else if (imp_sth->clbk_lob){ /*get by peice
with callback a slow*/
- fbh->clbk_lob = 1;
+ 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*/
+ 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;
}
@@ -2903,11 +2903,11 @@
fbh->fetch_func = fetch_clbk_lob;
}
else if (imp_sth->piece_lob){ /*get by peice
with polling slowest*/
- fbh->piece_lob = 1;
+ 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*/
+ 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;
}
