Hi,I'm experiencing a similar problem to that reported last month by Jan Carlsson - see for example:
http://www.nntp.perl.org/group/perl.dbi.users/2008/08/msg33156.html This is both a 64-bit Centos 4.6 box: Perl: 5.8.5 DBI: perl-DBI-1.605-1.el4.rf Oracle client: 11.1.0.6 Oracle database: 9.2.0.8 (WE8ISO8859P15) and/or 10.2.0.2.0 (AL32UTF8) and a 32-bit Centos 5.2 box: Perl: 5.8.8 DBI: perl-DBI-1.605-1.el5.rf Oracle client: 11.1.0.6 Oracle database 9.2.0.8 (WE8ISO8859P15) and/or 10.2.0.2.0 (AL32UTF8)With DBD-Oracle 1.21, the below works fine. with DBD-Oracle-1.22 it errors with ORA-24347 errors.
To reproduce, run in the below. I've attached the output from ora_verbose=6 on connecting to the 9.2.0.8 database server.
Cheers,
Chris,
--
DROP TABLE foo;
CREATE TABLE foo (account NUMBER,
quantity NUMBER,
product NUMBER);
CREATE OR REPLACE FUNCTION fn_foo RETURN SYS_REFCURSOR
AS
my_rc SYS_REFCURSOR;
BEGIN
OPEN my_rc FOR
SELECT account,product,SUM(quantity)
FROM foo
GROUP BY account,product;
RETURN my_rc;
END;
/
INSERT INTO foo (account,quantity,product)
VALUES (1,100,1);
INSERT INTO foo (account,quantity,product)
VALUES (1,NULL,2);
INSERT INTO foo (account,quantity,product)
VALUES (2,70,2);
COMMIT;
Associated perl code:
#!/usr/bin/perl -w
use strict;
use Data::Dumper qw(DumperX);
use DBI;
use DBD::Oracle qw(:ora_types);
my
$dbh=DBI->connect('dbi:Oracle:'.$ENV{DATABASE},$ENV{DB_USER},$ENV{DB_PASS},
{AutoCommit => 0, RaiseError => 1, PrintError => 0})
or die "Could not connect to Oracle: ".DBI::errstr();
$dbh->{ora_verbose}=6;
my $rc='';
my $sth=$dbh->prepare("BEGIN :rc:=fn_foo; END;");
$sth->bind_param_inout(':rc',\$rc,0,{ora_type => ORA_RSET});
$sth->execute;
my @res;
while(my $h=$rc->fetchrow_hashref) {
push @res,$h;
}
print DumperX([EMAIL PROTECTED]);
$rc->finish;
$sth->finish;
$dbh->disconnect;
dbd_preparse scanned 1 distinct placeholders
OCIHandleAlloc(8b52b0,92ee08,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(912d48,8f0e38,'BEGIN :rc:=fn_foo; END;',23,1,0)=SUCCESS
OCIAttrGet(912d48,OCI_HTYPE_STMT,92ee1c,0,24,8f0e38)=SUCCESS
dbd_st_prepare'd sql BEGIN (pl1, auto_lob1, check_sql1)
dbd_describe skipped for BEGIN
dbd_bind_ph(): bind :rc <== '' (type 0 (DEFAULT (varchar)), inout 0x847d70,
maxlen 0, attribs: HASH(0x92e520))
dbd_rebind_ph() (1): rebinding :rc as '' (not-utf8, ftype 116 (SQLT_RSET
OCI 8 cursor variable), csid 0, csform 0, inout 1)
dbd_rebind_ph_rset phs->is_inout=1
rebind :rc done with ftype 116 (SQLT_RSET OCI 8 cursor variable)
dbd_st_execute BEGIN (out1, lob0)...
pp_exec_rset bind :rc - allocating new sth...
OCIHandleAlloc(8b52b0,92f1b0,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIBindByName(912d48,92f1a8,8f0e38,":rc",placeh_len=3,value_p=92f1b0,value_sz=0,dty=116,indp=0,alenp=0,rcodep=0,maxarr_len=0,curelep=0
(*=0),mode=DEFAULT,0)=SUCCESS
pp_exec_rset bind :rc - allocated DBI::st=HASH(0x743610)...
Statement Execute Mode is 0 (DEFAULT)
OCIStmtExecute(8f0d60,912d48,8f0e38,1,0,0,0,mode=DEFAULT,0)=SUCCESS
OCIAttrGet(912d48,OCI_HTYPE_STMT,7fbfffefe4,0,9,8f0e38)=SUCCESS
OCIAttrGet(912d48,OCI_HTYPE_STMT,7fbfffefe2,0,10,8f0e38)=SUCCESS
dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out1)
dbd_st_execute(): Analyzing inout parameter ':rc of type=116'
bind :rc - initialising new DBI::st=HASH(0x743610) for cursor 0x92f1b0...
dbd_describe SELECT (implicit, lb 80)...
OCIAttrGet(911cf0,OCI_HTYPE_STMT,7fbfffedec,0,18,8f0e38)=SUCCESS
OCIParamGet(911cf0,4,8f0e38,930ce0,1)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d10,0,2,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d12,0,1,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d28,0,285,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d2a,0,286,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d2c,0,31,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d2e,0,32,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d14,0,5,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d16,0,6,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d17,0,7,8f0e38)=SUCCESS
OCIAttrGet(911b58,OCI_DTYPE_PARAM,930d18,7fbfffede8,4,8f0e38)=SUCCESS
Describe col #1 type=2(NVARCHAR2)
Described col 1: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name
ACCOUNT
: dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize 171
fbh 1: 'ACCOUNT' NULLable, otype 2-> 5, dbsize 22/172, p0.s-127
OCIParamGet(911cf0,4,8f0e38,930d78,2)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930da8,0,2,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930daa,0,1,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dc0,0,285,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dc2,0,286,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dc4,0,31,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dc6,0,32,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dac,0,5,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930dae,0,6,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930daf,0,7,8f0e38)=SUCCESS
OCIAttrGet(911b10,OCI_DTYPE_PARAM,930db0,7fbfffede8,4,8f0e38)=SUCCESS
Describe col #2 type=2(NVARCHAR2)
Described col 2: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name
PRODUCT
: dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize 171
fbh 2: 'PRODUCT' NULLable, otype 2-> 5, dbsize 22/172, p0.s-127
OCIParamGet(911cf0,4,8f0e38,930e10,3)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e40,0,2,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e42,0,1,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e58,0,285,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e5a,0,286,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e5c,0,31,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e5e,0,32,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e44,0,5,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e46,0,6,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e47,0,7,8f0e38)=SUCCESS
OCIAttrGet(911ac8,OCI_DTYPE_PARAM,930e48,7fbfffede8,4,8f0e38)=SUCCESS
Describe col #3 type=2(NVARCHAR2)
Described col 3: dbtype 2(NVARCHAR2), scale 0, prec 0, nullok 1, name
SUM(QUANTITY)
: dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize 171
fbh 3: 'SUM(QUANTITY)' NULLable, otype 2-> 5, dbsize 22/172, p0.s0
OCIAttrSet(911cf0,OCI_HTYPE_STMT,7fbfffede4,4,13,8f0e38)=SUCCESS
OCIAttrSet(911cf0,OCI_HTYPE_STMT,7fbfffede0,4,11,8f0e38)=SUCCESS
row cache OCI_ATTR_PREFETCH_ROWS 1, OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=1, rs_array_size=1
OCIDefineByPos(911cf0,930ce8,8f0e38,1,930ea0,172,5,930f60,930f80,930fa0,mode=DEFAULT,0)=SUCCESS
OCIDefineByPos(911cf0,930d80,8f0e38,2,930fc0,172,5,9302e0,929a30,930a80,mode=DEFAULT,0)=SUCCESS
OCIDefineByPos(911cf0,930e18,8f0e38,3,930aa0,172,5,930b60,930b80,930ba0,mode=DEFAULT,0)=SUCCESS
dbd_describe'd 3 columns (row bytes: 66 max, 12 est avg, cache: 0)
dbd_st_fetch 3 fields...
OCIStmtFetch(911cf0,8f0e38,1,2,4294967296)=SUCCESS_WITH_INFO
OCIAttrGet(911cf0,OCI_HTYPE_STMT,930a44,0,197,8f0e38)=SUCCESS
dbd_st_fetched 3 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
92e700 (field=0): '1'
field #2 with rc=0(OK)
92e700 (field=1): '1'
field #3 with rc=0(OK)
92e700 (field=2): '100'
dbd_st_fetch 3 fields...
dbd_st_fetched 3 fields with status of 1(SUCCESS_WITH_INFO)
field #1 with rc=60499(UNKNOWN RC=60499))
OCIErrorGet(8f0e38,1,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=SUCCESS
OCIErrorGet(8f0e38,2,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=NO_DATA
92e700 (field=0): undef
field #2 with rc=60499(UNKNOWN RC=60499))
OCIErrorGet(8f0e38,1,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=SUCCESS
OCIErrorGet(8f0e38,2,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=NO_DATA
92e700 (field=1): undef
field #3 with rc=60499(UNKNOWN RC=60499))
OCIErrorGet(8f0e38,1,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=SUCCESS
OCIErrorGet(8f0e38,2,"<NULL>",7fbfffe58c,"ORA-24347: Warning of a NULL
column in an aggregate function
",1024,2)=NO_DATA
92e700 (field=2): undef
DBD::Oracle::st fetchrow_hashref failed: ORA-24347: Warning of a NULL column in
an aggregate function (DBD ERROR: ORA-60499 error on field 1 of 3, ora_type 2)
ORA-24347: Warning of a NULL column in an aggregate function (DBD ERROR:
ORA-60499 error on field 2 of 3, ora_type 2)
ORA-24347: Warning of a NULL column in an aggregate function (DBD ERROR:
ORA-60499 error on field 3 of 3, ora_type 2) at ./foo.pl line 22.
dbd_st_destroy
OCIHandleFree(912d48,OCI_HTYPE_STMT)=SUCCESS
dbd_st_finish
OCIStmtFetch(911cf0,8f0e38,0,2,249108103168)=SUCCESS
dbd_st_destroy
OCIHandleFree(911cf0,OCI_HTYPE_STMT)=SUCCESS
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
OCITransRollback(8f0d60,8f0e38,mode=DEFAULT 0)=SUCCESS
OCISessionEnd(8f0d60,8f0e38,929238,mode=DEFAULT 0)=SUCCESS
OCIServerDetach(8f15b8,8f0e38,mode=DEFAULT,0)=SUCCESS
OCIHandleFree(929238,OCI_HTYPE_SESSION)=SUCCESS
OCIHandleFree(8f15b8,OCI_HTYPE_SERVER)=SUCCESS
OCIHandleFree(8f0d60,OCI_HTYPE_SVCCTX)=SUCCESS
OCIHandleFree(8f0e38,OCI_HTYPE_ERROR)=SUCCESS
signature.asc
Description: OpenPGP digital signature
