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

Steve Baldwin Fri, 17 Apr 2009 04:37:07 -0700

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_otpipelined 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 (DBDERROR: 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 line25.

 -> 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.

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

Reply via email to