Hi,
We were recently trying to pass an array to a stored procedure.
It worked fine the first call, but subsequent calls were receiving
the wrong array size, and with old array entries.
After some investigation, it looks like once an array parameter
is used with a given size array, it ignores any future array length.
i.e.
@array_of_something = (1, 2, 3, 4, 5);
$sth->bind_param(1, \...@array_of_something, {
ora_type => ORA_NUMBER_TABLE,
ora_internal_type => SQLT_INT });
$sth->execute();
@array_of_something = (6, 7, 8);
$sth->bind_param(1, \...@array_of_something, {
ora_type => ORA_NUMBER_TABLE,
ora_internal_type => SQLT_INT });
$sth->execute();
...doesn't do what is expected. The stored proceedure
receives an array of (6, 7, 8, 4, 5), in the second execute().
In the process of debugging that, we grabbed newer versions of DBD::Oracle,
to check if a more recent version had a fix, and lacking that, creating
a patch against svn.
Turned out we ran into a second bug, our dev system has recent Oracle
libraries on it, but our db is Oracle 9.2, and (after investigation) it
looks like there is a known issue with Oracle 10.2 libs, doing a ping
against 9.2, which causes a listener crash (I think).
The fix for that was to revert to the server version check if the server
is too old.
So, I've attached a patch, plus a test case for the first problem.
The patch is a patch against svn, 14564. It was tested on a Linux
box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
Linux.
If I've screwed up formatting, or need to do this differently, let me
know, and I'll try to fix it. If the fixes are wrong, I'm all ears
on that too.
--
Tim Oertel |"Why should I be content to simply
VP Engineering | live in this world, when I, as a
Ashergroup, Inc. | human being, can CREATE it?!"
585-586-0020 | IM via google/XMPP: ma...@jabber.com
testcase.tgz
Description: Binary data
Index: ocitrace.h
===================================================================
--- ocitrace.h (revision 14564)
+++ ocitrace.h (working copy)
@@ -77,6 +77,14 @@
: stat
#endif
+#define OCIServerRelease_log_stat(sc,errhp,b,bl,ht,ver,stat)\
+ stat =OCIServerRelease(sc,errhp,b,bl,ht,ver);\
+ (DBD_OCI_TRACEON) \
+ ? PerlIO_printf(DBD_OCI_TRACEFP,\
+ "%sOCIServerRelease(%p)=%s\n",\
+ OciTp, sc,oci_status_name(stat)),stat \
+ : stat
+
#define OCIServerVersion_log_stat(sc,errhp,b,bl,ht,stat)\
stat =OCIServerVersion(sc,errhp,b,bl,ht);\
(DBD_OCI_TRACEON) \
Index: oci8.c
===================================================================
--- oci8.c (revision 14564)
+++ oci8.c (working copy)
@@ -4506,3 +4506,27 @@
imp_sth->lob_refetch = NULL;
}
+ub4
+ora_db_version(SV *dbh, imp_dbh_t *imp_dbh)
+{
+ sword status;
+ text buf[2];
+ ub4 vernum;
+
+ if( imp_dbh->server_version > 0 ) {
+ return imp_dbh->server_version;
+ }
+
+
+ /* XXX should possibly create new session before ending the old so
*/
+ /* that if the new one can't be created, the old will still work.
*/
+ OCIServerRelease_log_stat(imp_dbh->svchp, imp_dbh->errhp, buf, 2,
+ OCI_HTYPE_SVCCTX, &vernum , status);
+ if (status != OCI_SUCCESS) {
+ oci_error(dbh, imp_dbh->errhp, status,
"OCISessionServerRelease");
+ return 0;
+ }
+ imp_dbh->server_version = vernum;
+ return vernum;
+}
+
Index: dbdimp.c
===================================================================
--- dbdimp.c (revision 14564)
+++ dbdimp.c (working copy)
@@ -409,6 +409,8 @@
DBD_ATTRIB_GET_IV( attr, "ora_drcp_incr", 13, svp,
imp_dbh->pool_incr);
#endif /*ORA_OCI_112*/
+ imp_dbh->server_version = 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);
@@ -1637,7 +1639,6 @@
/* If no number of entries to bind specified,
* set phs->array_numstruct to the scalar(@array) bound.
*/
- if( phs->array_numstruct <= 0 ){
/* av_len() returns last array index, or -1 is array is empty */
int numarrayentries=av_len( arr );
if( numarrayentries >= 0 ){
@@ -1647,7 +1648,6 @@
phs->array_numstruct);
}
}
- }
/* Fix charset */
csform = phs->csform;
if (trace_level >= 2 || dbd_verbose >= 3 ){
@@ -2036,7 +2036,6 @@
/* If no number of entries to bind specified,
* set phs->array_numstruct to the scalar(@array) bound.
*/
- if( phs->array_numstruct <= 0 ){
/* av_len() returns last array index, or -1 is array is empty */
int numarrayentries=av_len( arr );
if( numarrayentries >= 0 ){
@@ -2046,7 +2045,6 @@
phs->array_numstruct);
}
}
- }
/* Calculate each bound structure maxlen.
* maxlen(int) = sizeof(int);
* maxlen(double) = sizeof(double);
@@ -3054,7 +3052,22 @@
if( (phs->ftype == ORA_VARCHAR2_TABLE) ||
(phs->ftype == ORA_NUMBER_TABLE) ||
(phs->ftype == 1)) /*ORA_VARCHAR2*/ {
- /* Supported */
+ /* Supported */
+
+ /* Reload array-size-related attributes */
+ if (attribs) {
+ SV **svp;
+
+ if ( (svp=hv_fetch((HV*)SvRV(attribs),
"ora_maxdata_size", 16, 0)) != NULL) {
+ phs->maxdata_size = SvUV(*svp);
+ }
+ if ( (svp=hv_fetch((HV*)SvRV(attribs),
"ora_maxarray_numentries", 23, 0)) != NULL) {
+
phs->ora_maxarray_numentries=SvUV(*svp);
+ }
+ if ( (svp=hv_fetch((HV*)SvRV(attribs),
"ora_internal_type", 17, 0)) != NULL) {
+
phs->ora_internal_type=SvUV(*svp);
+ }
+ }
}else{
/* All the other types are not supported */
croak("Array bind is supported only for
ORA_%%_TABLE types. Unable to bind '%s'.",phs->name);
Index: dbdimp.h
===================================================================
--- dbdimp.h (revision 14564)
+++ dbdimp.h (working copy)
@@ -61,6 +61,8 @@
int max_nested_cursors; /* limit on cached nested cursors per stmt */
int array_chunk_size; /* the max size for an array bind */
+ ub4 server_version; /* version of Oracle server */
+
};
#define DBH_DUP_OFF sizeof(dbih_dbc_t)
Index: Oracle.xs
===================================================================
--- Oracle.xs (revision 14564)
+++ Oracle.xs (working copy)
@@ -263,20 +263,28 @@
PREINIT:
D_imp_dbh(dbh);
sword status;
-#if !defined(ORA_OCI_102)
+#if defined(ORA_OCI_102)
+ ub4 vernum;
+#endif
text buf[2];
-#endif
+
CODE:
+ /*when OCIPing not available,*/
/*simply does a call to OCIServerVersion which should make 1 round
trip*/
- /*later I will replace this with the actual OCIPing command*/
/*This will work if the DB goes down, */
/*If the listener goes down it is another case as the Listener is
needed to establish the connection not maintain it*/
/*so we should stay connected but we cannot get nay new connections*/
{
-#if !defined(ORA_OCI_102)
+#if !defined(ORA_OCI_102)
OCIServerVersion_log_stat(imp_dbh->svchp,imp_dbh->errhp,buf,2,OCI_HTYPE_SVCCTX,status);
#else
+ vernum = ora_db_version(dbh,imp_dbh);
+ /* OCIPing causes server failures if called against server ver < 10.2 */
+ if (((int)((vernum>>24) & 0xFF) < 10 ) || (((int)((vernum>>24) & 0xFF)
== 10 ) && ((int)((vernum>>20) & 0x0F) < 2 ))){
+
OCIServerVersion_log_stat(imp_dbh->svchp,imp_dbh->errhp,buf,2,OCI_HTYPE_SVCCTX,status);
+ } else {
OCIPing_log_stat(imp_dbh->svchp,imp_dbh->errhp,status);
+ }
#endif
if (status != OCI_SUCCESS){
XSRETURN_IV(0);
