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;
}
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.