Re: DBD::Oracle & 10.1 client
On Fri, 2005-10-21 at 20:47 +0100, Tim Bunce wrote: > > Ah, I didn't spot the attached trace. Sorry. > > OCIStmtExecute(831e534,8323594,831e5a8,0,0,0,0,0)=SUCCESS > OCIAttrGet(8323594,OCI_HTYPE_STMT,bfffe906,0,10,831e5a8)=SUCCESS > dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0) > dbd_st_fetch 2 fields... > OCIStmtFetch(8323594,831e5a8,1,2,0)=SUCCESS > dbih_setup_fbav for 2 fields => 0x829bfac > dbd_st_fetch 2 fields SUCCESS > 0 (rc=0): '?.' > 1 (rc=0): '51' > > Sure looks like an Oracle problem. > > Tim. Just to follow up, it was an Oracle problem, what problem I am not sure. We ended up putting a 9i client on the server, which works. The funny thing is that the 10g client that was on that server would work with another 10g Db. The only difference is the one db was on an Alpha and the other one (the one that worked) was on a Linux server. -- Scott T. Hildreth <[EMAIL PROTECTED]>
Re: DBD::Oracle & 10.1 client
On Fri, Oct 21, 2005 at 04:07:20PM +0100, Tim Bunce wrote: > On Thu, Oct 20, 2005 at 03:41:01PM -0500, Scott T. Hildreth wrote: > > To add more info, I was told this problem only occurs if there is only > > one row returned. If more than one row is returned, the arrayref is > > fine. I changed the selectall_arrayref to selectrow_array and the data > > (one row returned) was fine. I'm posting to dev as well, just in case > > this is a known bug. > > It's not. (And even if it were, dbi-dev isn't meant for reporting bugs. > The dbi-users list is fine for that.) > > > I copied the table to a 9i instance and the ran > > the code below, and it worked fine. So it seems to be related to 10g. > > And since the DBD::Oracle code was the same, that implies it's not a > DBD::Oracle bug. Does seem very odd though. > > A level 5 trace will show you what Oracle is returning for each field of > each row. If that shows the bad data then it's almost certainly an > Oracle bug. If it doesn't (ie it shows different values being returned > than what eventually gets returned by selectall_arrayref) then it's a > DBD::Oracle or DBI bug - but at the moment I doubt that. Ah, I didn't spot the attached trace. Sorry. OCIStmtExecute(831e534,8323594,831e5a8,0,0,0,0,0)=SUCCESS OCIAttrGet(8323594,OCI_HTYPE_STMT,bfffe906,0,10,831e5a8)=SUCCESS dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0) dbd_st_fetch 2 fields... OCIStmtFetch(8323594,831e5a8,1,2,0)=SUCCESS dbih_setup_fbav for 2 fields => 0x829bfac dbd_st_fetch 2 fields SUCCESS 0 (rc=0): '?.' 1 (rc=0): '51' Sure looks like an Oracle problem. Tim.
Re: DBD::Oracle & 10.1 client
On Thu, Oct 20, 2005 at 03:41:01PM -0500, Scott T. Hildreth wrote:
> To add more info, I was told this problem only occurs if there is only
> one row returned. If more than one row is returned, the arrayref is
> fine. I changed the selectall_arrayref to selectrow_array and the data
> (one row returned) was fine. I'm posting to dev as well, just in case
> this is a known bug.
It's not. (And even if it were, dbi-dev isn't meant for reporting bugs.
The dbi-users list is fine for that.)
> I copied the table to a 9i instance and the ran
> the code below, and it worked fine. So it seems to be related to 10g.
And since the DBD::Oracle code was the same, that implies it's not a
DBD::Oracle bug. Does seem very odd though.
A level 5 trace will show you what Oracle is returning for each field of
each row. If that shows the bad data then it's almost certainly an
Oracle bug. If it doesn't (ie it shows different values being returned
than what eventually gets returned by selectall_arrayref) then it's a
DBD::Oracle or DBI bug - but at the moment I doubt that.
Tim.
> Thu, 2005-10-20 at 14:07 -0500, Scott T. Hildreth wrote:
> > I am going to investigate more, but I thought I would post the question
> > to see if anyone has run into this problem.
> >
> > I have co-workers that connecting to a remote Oracle DB (10.1), I
> > compiled DBD::Oracle 1.16 with the 10.1 client libraries. The data
> > returned from a selectall_arrayref is 2 fields a filename and a
> > quantity. Here is the isolated code,
> >
> > 286:my $load_file_ar = $dbh->selectall_arrayref(q{
> > 287 select FILENAME, NUM_RECORDS
> > 288 from LOAD_DATA_SRC_FILE
> > 289 where LOAD_ID = ?
> > 290and DATA_UPDATE_ID = ?
> > 291 order by FILE_ID
> > 292 }, undef, $load_id, $update_id);
> >
> > $load_id = 19 & $update_id = 51.
> >
> > ...here is the data returned.
> >
> > DB<4> x $files_ar
> > 0 ARRAY(0x8536328)
> >0 ARRAY(0x85e40e0)
> > 0 (binary data)
> > 1 51
> >
> > what ever $update_id's value is, this is always returned in the [1] position
> > of the the arrayref and the filename is always binary data. This works fine
> > with sqlplus.
> >
> > I have tried it with Perl 5.8.[167] always the same results. The database
> > resides on an Alpha and the clients are on Linux. Trace doesn't show
> > anything
> > unusual. I don't know if this is an NLS-utf8 issue. Any ideas?
> >
> > Thanks,
> > STH
> >
> > Trace- level 9 is attached.
> >
> --
> Scott T. Hildreth <[EMAIL PROTECTED]>
Re: DBD::Oracle & 10.1 client
To add more info, I was told this problem only occurs if there is only
one row returned. If more than one row is returned, the arrayref is
fine. I changed the selectall_arrayref to selectrow_array and the data
(one row returned) was fine. I'm posting to dev as well, just in case
this is a known bug. I copied the table to a 9i instance and the ran
the code below, and it worked fine. So it seems to be related to 10g.
Thu, 2005-10-20 at 14:07 -0500, Scott T. Hildreth wrote:
> I am going to investigate more, but I thought I would post the question
> to see if anyone has run into this problem.
>
> I have co-workers that connecting to a remote Oracle DB (10.1), I
> compiled DBD::Oracle 1.16 with the 10.1 client libraries. The data
> returned from a selectall_arrayref is 2 fields a filename and a
> quantity. Here is the isolated code,
>
> 286:my $load_file_ar = $dbh->selectall_arrayref(q{
> 287 select FILENAME, NUM_RECORDS
> 288 from LOAD_DATA_SRC_FILE
> 289 where LOAD_ID = ?
> 290and DATA_UPDATE_ID = ?
> 291 order by FILE_ID
> 292 }, undef, $load_id, $update_id);
>
> $load_id = 19 & $update_id = 51.
>
> ...here is the data returned.
>
> DB<4> x $files_ar
> 0 ARRAY(0x8536328)
>0 ARRAY(0x85e40e0)
> 0 (binary data)
> 1 51
>
> what ever $update_id's value is, this is always returned in the [1] position
> of the the arrayref and the filename is always binary data. This works fine
> with sqlplus.
>
> I have tried it with Perl 5.8.[167] always the same results. The database
> resides on an Alpha and the clients are on Linux. Trace doesn't show anything
> unusual. I don't know if this is an NLS-utf8 issue. Any ideas?
>
> Thanks,
> STH
>
> Trace- level 9 is attached.
>
--
Scott T. Hildreth <[EMAIL PROTECTED]>
DBD::Oracle & 10.1 client
I am going to investigate more, but I thought I would post the question
to see if anyone has run into this problem.
I have co-workers that connecting to a remote Oracle DB (10.1), I
compiled DBD::Oracle 1.16 with the 10.1 client libraries. The data
returned from a selectall_arrayref is 2 fields a filename and a
quantity. Here is the isolated code,
286:my $load_file_ar = $dbh->selectall_arrayref(q{
287 select FILENAME, NUM_RECORDS
288 from LOAD_DATA_SRC_FILE
289 where LOAD_ID = ?
290and DATA_UPDATE_ID = ?
291 order by FILE_ID
292 }, undef, $load_id, $update_id);
$load_id = 19 & $update_id = 51.
...here is the data returned.
DB<4> x $files_ar
0 ARRAY(0x8536328)
0 ARRAY(0x85e40e0)
0 (binary data)
1 51
what ever $update_id's value is, this is always returned in the [1] position
of the the arrayref and the filename is always binary data. This works fine
with sqlplus.
I have tried it with Perl 5.8.[167] always the same results. The database
resides on an Alpha and the clients are on Linux. Trace doesn't show anything
unusual. I don't know if this is an NLS-utf8 issue. Any ideas?
Thanks,
STH
Trace- level 9 is attached.
--
Scott T. Hildreth <[EMAIL PROTECTED]>
DBI::db=HASH(0x829bca0) trace level set to 9 in DBI 1.39-nothread (pid
25442)
-> selectall_arrayref for DBD::Oracle::db
(DBI::db=HASH(0x82fec14)~0x829bca0 '
select FILENAME, NUM_RECORDS
from LOAD_DATA_SRC_FILE
where LOAD_ID = ?
and DATA_UPDATE_ID = ?
order by FILE_ID
' undef 19 51)
>> prepare DISPATCH (DBI::db=HASH(0x829bca0) rc1/1 @3 g0 ima201
pid#25442) at /home/demosrc/pmod/DBcommon.pm line 286 via tst_demosrc.pl line 19
1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x829bca0)~INNER '
select FILENAME, NUM_RECORDS
from LOAD_DATA_SRC_FILE
where LOAD_ID = ?
and DATA_UPDATE_ID = ?
order by FILE_ID
' undef)
dbih_setup_handle(DBI::st=HASH(0x829bee0)=>DBI::st=HASH(0x829bec8),
DBD::Oracle::st, 829beec, Null!)
dbih_make_com(DBI::db=HASH(0x829bca0), 829d568, DBD::Oracle::st, 208, 0)
thr#0
dbih_setup_attrib(DBI::st=HASH(0x829bec8), Err, DBI::db=HASH(0x829bca0))
SCALAR(0x81f9860) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x829bec8), State, DBI::db=HASH(0x829bca0))
SCALAR(0x81f98c0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x829bec8), Errstr, DBI::db=HASH(0x829bca0))
SCALAR(0x81f9890) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x829bec8), TraceLevel,
DBI::db=HASH(0x829bca0)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x829bec8), FetchHashKeyName,
DBI::db=HASH(0x829bca0)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x829bec8), HandleError,
DBI::db=HASH(0x829bca0)) undef (not defined)
dbd_preparse scanned 2 distinct placeholders
OCIHandleAlloc(83092d8,8363608,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(8323594,831e5a8,'
select FILENAME, NUM_RECORDS
from LOAD_DATA_SRC_FILE
where LOAD_ID = :p1
and DATA_UPDATE_ID = :p2
order by FILE_ID
',134,1,0)=SUCCESS
OCIAttrGet(8323594,OCI_HTYPE_STMT,836360c,0,24,831e5a8)=SUCCESS
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
OCIStmtExecute(831e534,8323594,831e5a8,0,0,0,0,16)=SUCCESS
OCIAttrGet(8323594,OCI_HTYPE_STMT,bfffe28c,0,18,831e5a8)=SUCCESS
OCIParamGet(8323594,4,831e5a8,83636b0,1)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636c6,0,2,831e5a8)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636c4,0,1,831e5a8)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636c8,0,5,831e5a8)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636ca,0,6,831e5a8)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636cb,0,7,831e5a8)=SUCCESS
OCIAttrGet(83251e4,OCI_DTYPE_PARAM,83636d8,bfffe288,4,831e5a8)=SUCCESS
fbh 1: 'FILENAME' NULLable, otype 1-> 5, dbsize 250/251, p250.s0
OCIParamGet(8323594,4,831e5a8,83636f0,2)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,8363706,0,2,831e5a8)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,8363704,0,1,831e5a8)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,8363708,0,5,831e5a8)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,836370a,0,6,831e5a8)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,836370b,0,7,831e5a8)=SUCCESS
OCIAttrGet(83251c0,OCI_DTYPE_PARAM,8363718,bfffe288,4,831e5a8)=SUCCESS
fbh 2: 'NUM_RECORDS'NULLable, otype 2-> 5, dbsize 22/134,
p0.s-127
OCIAttrSet(8323594,OCI_HTYPE_STMT,bfffe284,4,11,831e5a8)=SUCCESS
OCIDefineByPos(8323594,83636b4,831e5a8,1,8364360,251,5,83589e0,83589f0,8358a00,0)=SUCCESS
OCIDefineByPos(8323594,83636f4,831e5a8,2,8363b68,134,5,8363698,8363
