Re: Bind Error - DBD::Oracle or DBI ???

[Steve Baldwin]

OK, grasping at straws here.  Any chance it might be a Mac (OS/X) thing?
Given that I can reproduce it at will, is there anything I can do to  
get to the bottom of this?

On 18/04/2009, at 9:15 AM, sco...@pythian.com wrote:

well with this code

select(STDOUT);
$|=1;
p("Using DBI $DBI::VERSION and DBD::Oracle $DBD::Oracle::VERSION");


$dbh->trace(0);
my $sth = $dbh->prepare(q(
    SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
    FROM    TABLE(sb_func(?))
    ));


$sth->bind_param(1, 10);
p('Doing execute');
$sth->execute;
my $rows = 0;
p('Starting fetch');
while (my $row = $sth->fetchrow_arrayref) {
    ++$rows;
}
p("Fetch complete : $rows rows");
$dbh->disconnect;

and here is my output

Using DBI 1.607 and DBD::Oracle 1.23
Doing execute
Starting fetch
Fetch complete : 1 rows

cheers

John,

I see the same behaviour with the latest trunk.  Is it possible it is
due to some interaction with the latest DBI (I'm at 1.607 and you're
at 1.601) ? ..

au-stb-101-144:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.23
DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD
ERROR: error possibly near <*> indicator at char 80 in '
    SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
    FROM    TABLE(sb_func(<*>?))
    ') [for Statement "
    SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
    FROM    TABLE(sb_func(?))
    "] at ./no-bind.plx line 21.

On 18/04/2009, at 3:51 AM, sco...@pythian.com wrote:

Ran this through my system (windows)  and I get the correct results

I am using the latest 'Trunk' version of DBD::Oracle

you might want to try it and see what you get

you can find it at

http://svn.perl.org/modules/dbd-oracle/trunk

my dbi is 1.601

cheers
John

John,

Try this ...

(from sqlplus)

create or replace type sb_o as object (d_val date, n_val number);
/
create or replace type sb_ot as table of sb_o;
/
create or replace function sb_func(i_p1 in number) return sb_ot
pipelined as
--
   l_row       sb_o;
--
begin
   pipe row (sb_o(sysdate, 123.456));
   return;
end;
/

(change the program SQL to ...)

my $sth = $dbh->prepare(q(
   SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
   FROM    TABLE(sb_func(?))
   ));

When I run it I get this ...

au-stb-101-144:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.22
DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD
ERROR: error possibly near <*> indicator at char 80 in '
   SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
   FROM    TABLE(sb_func(<*>?))
   ') [for Statement "
   SELECT  TO_CHAR(d_val, 'DD-Mon-YYYY HH24:MI:SS')
   FROM    TABLE(sb_func(?))
   "] at ./no-bind.plx line 21.

Hope this helps.  Let me know if there's anything else I can do.

Steve

On 17/04/2009, at 8:39 PM, sco...@pythian.com wrote:

I will be working on this today.

Having trouble receating it as I do not have the scema for

msc$stats_report_p.schema_top_n(?))

can you get me that function or ne like it that returns something?


Cheers
John Scoles

Here's a bit more information that may help.  It looks like a
DBD::Oracle problem, but I could be wrong.

I did some tweaking of the statement to see what works and what
doesn't.  I tried using the ? placeholder instead of the :var and
found something weird is going on.

If I set my statement to this ...

my $sth = $dbh->prepare(q(
  SELECT  TO_CHAR(stats_date)
  FROM    TABLE(msc$stats_report_p.schema_top_n(?))
  ));
$sth->bind_param(1, 10);
p('Doing execute');
$sth->execute;
my $rows = 0;
p('Starting fetch');
while (my $row = $sth->fetchrow_arrayref) {
  ++$rows;
}
p("Fetch complete : $rows rows");

Everything works ...

stbaldwins-macbook-pro:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.22
Doing execute
Starting fetch
Fetch complete : 1032 rows

However, if I change the statement to this ...

my $sth = $dbh->prepare(q(
  SELECT  TO_CHAR(stats_date, 'J')
  FROM    TABLE(msc$stats_report_p.schema_top_n(?))
  ));
(only added the [, 'J'], everything else the same)

I get this ...

stbaldwins-macbook-pro:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.22
DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD
ERROR: error possibly near <*> indicator at char 88 in '
  SELECT  TO_CHAR(stats_date, 'J')
  FROM    TABLE(msc$stats_report_p.schema_top_n(<*>?))
  ') [for Statement "
  SELECT  TO_CHAR(stats_date, 'J')
  FROM    TABLE(msc$stats_report_p.schema_top_n(?))
  "] at ./no-bind.plx line 21.

Thanks for your help,

Steve

On 14/04/2009, at 6:56 AM, Steve Baldwin wrote:

Any comments/feedback on this?

On 09/04/2009, at 11:43 AM, Steve Baldwin wrote:

Hi,

I have a program that worked fine under DBI 1.52 and  
DBD::Oracle
1.22.  Moving to the latest DBI 1.607 but the same  
DBD::Oracle, I
get a bind error.

Here's a sample program :

#!/usr/bin/perl -w
use strict;
use warnings;
use DBI qw();
use DBD::Oracle;

sub p{
printf @_;
print "\n";
}

select(STDOUT);
$|=1;
p("Using DBI $DBI::VERSION and DBD::Oracle
$DBD::Oracle::VERSION");

my $uidpwd = 'usr/p...@db';
my $dbh = DBI->connect('dbi:Oracle:', $uidpwd, '',  
{RaiseError =>
1, PrintError => 0});

$dbh->trace(5);
my $sth = $dbh->prepare(q(
SELECT  'z'
FROM    TABLE(msc$stats_report_p.schema_top_n(:x))
WHERE   1=0
));
$sth->bind_param(':x', 10);
p('Doing execute');
$sth->execute;
my $rows = 0;
p('Starting fetch');
while (my $row = $sth->fetchrow_arrayref) {
++$rows;
}
p("Fetch complete : $rows rows");
$dbh->disconnect;

Here's the output from DBI 1.52, DBD::Oracle 1.22 ...

[stbald...@audev01 dev][2]$ ./no-bind.plx
Using DBI 1.52 and DBD::Oracle 1.22
DBI::db=HASH(0x19e4bc60) trace level set to 0x0/5 (DBI @
0x0/240518168576) in DBI 1.52-ithread (pid 1554)
-> prepare for DBD::Oracle::db
(DBI::db=HASH(0x19e4b270)~0x19e4bc60 '
SELECT  'z'
FROM    TABLE(msc$stats_report_p.schema_top_n(:x))
WHERE   1=0
') thr#19bae010

dbih_setup_handle
(DBI::st=HASH(0x19e4be10)=>DBI::st=HASH(0x19e4bf50),
DBD::Oracle::st, 19e4be20, Null!)
dbih_make_com(DBI::db=HASH(0x19e4bc60), 19e533e0,
DBD::Oracle::st, 448, 0) thr#19bae010
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), Err,
DBI::db=HASH(0x19e4bc60)) SCALAR(0x19d3b700) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), State,
DBI::db=HASH(0x19e4bc60)) SCALAR(0x19d3b780) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), Errstr,
DBI::db=HASH(0x19e4bc60)) SCALAR(0x19d3b740) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), TraceLevel,
DBI::db=HASH(0x19e4bc60)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), FetchHashKeyName,
DBI::db=HASH(0x19e4bc60)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), HandleSetErr,
DBI::db=HASH(0x19e4bc60)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x19e4bf50), HandleError,
DBI::db=HASH(0x19e4bc60)) undef (not defined)
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
Describe col #1 type=96(CHAR)
Described col  1: dbtype 96(CHAR), scale 0, prec 1, nullok 1,
name
'Z'
     : dbsize 1, char_used 1, char_size 1, csid 873, csform 1,
disize 1
fbh 1: ''Z''        NULLable, otype  96->  5, dbsize 1/2, p1.s0
row cache OCI_ATTR_PREFETCH_ROWS 1042,  
OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=0, rs_array_size=1
dbd_describe'd 1 columns (row bytes: 1 max, 1 est avg, cache:
1042)
<- prepare= DBI::st=HASH(0x19e4be10) at ./no-bind.plx line 20
-> bind_param for DBD::Oracle::st
(DBI::st=HASH(0x19e4be10)~0x19e4bf50 ':x' 10) thr#19bae010
dbd_bind_ph(): bind :x <== 10 (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :x as NULL (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0, inout 0)
dbd_rebind_ph_char() (1): bind :x <== 10 (size 2/8/0, ptype
5(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :x <== '10' (size 2/8, otype
1(VARCHAR), indp 0, at_exec 1)
 bind :x as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :x <== 10 (in, not-utf8, csid 873->0- 
>873,
ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
<- bind_param= 1 at ./no-bind.plx line 25
Doing execute
-> execute for DBD::Oracle::st
(DBI::st=HASH(0x19e4be10)~0x19e4bf50) thr#19bae010
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
  in  ':x' [0,0]: len  2, ind 0, value=10
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at ./no-bind.plx line 27
Starting fetch
-> fetchrow_arrayref for DBD::Oracle::st
(DBI::st=HASH(0x19e4be10)~0x19e4bf50) thr#19bae010
dbd_st_fetch 1 fields...
dbd_st_fetch no-more-data
<- fetchrow_arrayref= undef at ./no-bind.plx line 30
Fetch complete : 0 rows
-> disconnect for DBD::Oracle::db
(DBI::db=HASH(0x19e4b270)~0x19e4bc60) thr#19bae010
<- disconnect= 1 at ./no-bind.plx line 34
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x19e4bf50)~INNER)
thr#19bae010
<- DESTROY= undef
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x19e4bc60)~INNER)
thr#19bae010
<- DESTROY= undef

Here's the output from DBI 1.607, DBD::Oracle 1.22 ...

au-stb-101-144:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.22
DBI::db=HASH(0x86d920) trace level set to 0x0/5 (DBI @ 0x0/0)  
in
DBI 1.607-ithread (pid 6000)
-> prepare for DBD::Oracle::db  
(DBI::db=HASH(0x86d98c)~0x86d920 '
SELECT  'z'
FROM    TABLE(msc$stats_report_p.schema_top_n(:x))
WHERE   1=0
') thr#800000
New 'DBI::st' (for DBD::Oracle::st,
parent=DBI::db=HASH(0x86d920), id=undef)
dbih_setup_handle 
(DBI::st=HASH(0x86db9c)=>DBI::st=HASH(0x86db54),
DBD::Oracle::st, 8442e8, Null!)
dbih_make_com(DBI::db=HASH(0x86d920), 41f6b0, DBD::Oracle::st,
284, 0) thr#800000
dbih_setup_attrib(DBI::st=HASH(0x86db54), Err,
DBI::db=HASH(0x86d920)) SCALAR(0x844234) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), State,
DBI::db=HASH(0x86d920)) SCALAR(0x844294) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), Errstr,
DBI::db=HASH(0x86d920)) SCALAR(0x844264) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), TraceLevel,
DBI::db=HASH(0x86d920)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), FetchHashKeyName,
DBI::db=HASH(0x86d920)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), HandleSetErr,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), HandleError,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), ReadOnly,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), Profile,
DBI::db=HASH(0x86d920)) undef (not defined)
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
Describe col #1 type=96(CHAR)
Described col  1: dbtype 96(CHAR), scale 0, prec 1, nullok 1,
name
'Z'
     : dbsize 1, char_used 1, char_size 1, csid 873, csform 1,
disize 1
fbh 1: ''Z''        NULLable, otype  96->  5, dbsize 1/2, p1.s0
row cache OCI_ATTR_PREFETCH_ROWS 1042,  
OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=0, rs_array_size=1
dbd_describe'd 1 columns (row bytes: 1 max, 1 est avg, cache:
1042)
<- prepare= DBI::st=HASH(0x86db9c) at ./no-bind.plx line 20
-> bind_param for DBD::Oracle::st
(DBI::st=HASH(0x86db9c)~0x86db54 ':x' 10) thr#800000
dbd_bind_ph(): bind :x <== 10 (type 0 (DEFAULT (varchar)))
Can't bind unknown placeholder ':x' (':x') at ./no-bind.plx  
line
25.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x86db54)~INNER)
thr#800000
<- DESTROY= undef
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x86d920)~INNER)
thr#800000
<- DESTROY= undef

If I change the SELECT list from 'z' to *, I don't get the  
error.
Here's the trace output ...

au-stb-101-144:dev stbaldwin$ ./no-bind.plx
Using DBI 1.607 and DBD::Oracle 1.22
DBI::db=HASH(0x86d920) trace level set to 0x0/5 (DBI @ 0x0/0)  
in
DBI 1.607-ithread (pid 6027)
-> prepare for DBD::Oracle::db  
(DBI::db=HASH(0x86d98c)~0x86d920 '
SELECT  *
FROM    TABLE(msc$stats_report_p.schema_top_n(:x))
WHERE   1=0
') thr#800000
New 'DBI::st' (for DBD::Oracle::st,
parent=DBI::db=HASH(0x86d920), id=undef)
dbih_setup_handle 
(DBI::st=HASH(0x86db9c)=>DBI::st=HASH(0x86db54),
DBD::Oracle::st, 8442e8, Null!)
dbih_make_com(DBI::db=HASH(0x86d920), 41f6b0, DBD::Oracle::st,
284, 0) thr#800000
dbih_setup_attrib(DBI::st=HASH(0x86db54), Err,
DBI::db=HASH(0x86d920)) SCALAR(0x844234) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), State,
DBI::db=HASH(0x86d920)) SCALAR(0x844294) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), Errstr,
DBI::db=HASH(0x86d920)) SCALAR(0x844264) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), TraceLevel,
DBI::db=HASH(0x86d920)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), FetchHashKeyName,
DBI::db=HASH(0x86d920)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), HandleSetErr,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), HandleError,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), ReadOnly,
DBI::db=HASH(0x86d920)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x86db54), Profile,
DBI::db=HASH(0x86d920)) undef (not defined)
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
Describe col #1 type=12((UNKNOWN SQL TYPECODE 12))
Described col  1: dbtype 12((UNKNOWN SQL TYPECODE 12)), scale  
0,
prec 75, nullok 1, name STATS_DATE
     : dbsize 21, char_used 0, char_size 0, csid 0, csform 0,
disize 75
fbh 1: 'STATS_DATE' NULLable, otype  12->  5, dbsize 21/76,
p75.s0
Describe col #2 type=1(VARCHAR)
Described col  2: dbtype 1(VARCHAR), scale 0, prec 128,  
nullok 1,
name SERIES_KEY
     : dbsize 128, char_used 1, char_size 128, csid 873, csform
1, disize 128
fbh 2: 'SERIES_KEY' NULLable, otype   1->  5, dbsize 128/129,
p128.s0
Describe col #3 type=2(NVARCHAR2)
Described col  3: dbtype 2(NVARCHAR2), scale 0, prec 0,  
nullok 1,
name SIZE_MB
     : dbsize 22, char_used 0, char_size 0, csid 0, csform 0,
disize 171
fbh 3: 'SIZE_MB'    NULLable, otype   2->  5, dbsize 22/172,
p0.s0
Describe col #4 type=2(NVARCHAR2)
Described col  4: dbtype 2(NVARCHAR2), scale 0, prec 0,  
nullok 1,
name N_ROWS
     : dbsize 22, char_used 0, char_size 0, csid 0, csform 0,
disize 171
fbh 4: 'N_ROWS'     NULLable, otype   2->  5, dbsize 22/172,
p0.s0
row cache OCI_ATTR_PREFETCH_ROWS 135,  
OCI_ATTR_PREFETCH_MEMORY 0
rs_array_init: rs_array_on=0, rs_array_size=1
calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1
dbd_describe'd 4 columns (row bytes: 193 max, 80 est avg,  
cache:
135)
<- prepare= DBI::st=HASH(0x86db9c) at ./no-bind.plx line 20
-> bind_param for DBD::Oracle::st
(DBI::st=HASH(0x86db9c)~0x86db54 ':x' 10) thr#800000
dbd_bind_ph(): bind :x <== 10 (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :x as NULL (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0, inout 0)
dbd_rebind_ph_char() (1): bind :x <== 10 (size 2/4/0, ptype
5(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :x <== '10' (size 2/4, otype
1(VARCHAR), indp 0, at_exec 1)
 bind :x as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :x <== 10 (in, not-utf8, csid 873->0- 
>873,
ftype 1 (VARCHAR), csform 0->0, maxlen 4, maxdata_size 0)
<- bind_param= 1 at ./no-bind.plx line 25
Doing execute
-> execute for DBD::Oracle::st  
(DBI::st=HASH(0x86db9c)~0x86db54)
thr#800000
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
  in  ':x' [0,0]: len  2, ind 0, value=10
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at ./no-bind.plx line 27
Starting fetch
-> fetchrow_arrayref for DBD::Oracle::st
(DBI::st=HASH(0x86db9c)~0x86db54) thr#800000
dbd_st_fetch 4 fields...
dbd_st_fetch no-more-data
<- fetchrow_arrayref= undef at ./no-bind.plx line 30
Fetch complete : 0 rows
-> disconnect for DBD::Oracle::db
(DBI::db=HASH(0x86d98c)~0x86d920) thr#800000
<- disconnect= 1 at ./no-bind.plx line 34
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x86db54)~INNER)
thr#800000
<- DESTROY= undef
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x86d920)~INNER)
thr#800000
<- DESTROY= undef

I don't know if this is a DBI error or a DBD::Oracle not- 
playing-
nice error.  Any help would be much appreciated.

Thanks,

Steve

This email is intended solely for the use of the addressee and
may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify  
the
sender and delete the email.



This email is intended solely for the use of the addressee and  
may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.



This email is intended solely for the use of the addressee and  
may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.






This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.






This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.






This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.