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