Error - DBD::Oracle / ORA-1008 problem with placeholders

Hello all,
=20
I encouter an error with DBD::Oracle and Oracle instances 10g that I
dont understand :
DBD::Oracle::st execute failed: ORA-01008: not all variables bound
(DBD ERROR: OCIStmtExecute)

This script works fine with Oracle 9i.
I compiled DBD::Oracle (1.19) based on Oracle 9i (on Sun-Solaris10) -
DBI 1.58

I tried to compile DBD::Oracle (1.21) based on Oracle 10g (on
Sun-Solaris10) with the same error.
I tried to use DBI_TRACE=3D2 or 3 but I dont find the trace usefull.

My tests against Oracle instances :
9i  (new install)      - ok (it is always ok with 9i)
10g (new install)      - bad
10g (migrated from 9i) - bad

My tests against placeholders :
If I used placeholders like ":param1", it works the first time, then
it will failed in general

My tests against the SQL query :
If I comment the first column returned (the TO_CHAR(...) AS DT, it
works the first time
then it failed

So the problem seems to be with CACHE or something like that ?


My script is :
#
------------------------------------------------------------------------
use DBI;
$dsn=3D" dbi:Oracle:host=3Dmy_server;port=3D1521;
sid=3Dmy_sid";
my $query =3D "
SELECT
TO_CHAR(TO_DATE(col1,'DD/MM/YY'),'YYYYMMDD') AS DT,
col2,col3,col4 FROM  table1 WHERE     SUBSTR(col1,4) =3D ?=20
";
$DBH =3D DBI->connect($dsn,"toto","toto",{PrintError =3D> 0, RaiseError =
=3D>
1});
$STH =3D $DBH -> prepare($extraction_query);
my $month_MMAA=3D"03/08";
#$STH->bind_param(":pr1", $month_MMAA);
$STH->bind_param(1, $month_MMAA);
$STH->execute();
DBI::dump_results($STH);
$STH->finish();
$DBH->disconnect();
#
------------------------------------------------------------------------

I need your help

Regards

Pascal

Can you give me what the table schema is?
I would like to know what  datatypes

col1,col2,col3,col4


are so I can try and recreate the error.

As well what operation system are you using and which version of DBI?

anyway just looking at the SQL it doesn't look quite right

cheers
John Scoles

pmonschein@groupe-casino.fr wrote:
> Hello all,
>
> I encouter an error with DBD::Oracle and Oracle instances 10g that I
> dont understand :
>   DBD::Oracle::st execute failed: ORA-01008: not all variables bound
> (DBD ERROR: OCIStmtExecute)
>
> This script works fine with Oracle 9i.
> I compiled DBD::Oracle (1.19) based on Oracle 9i (on Sun-Solaris10) -
> DBI 1.58
>
> I tried to compile DBD::Oracle (1.21) based on Oracle 10g (on
> Sun-Solaris10) with the same error.
> I tried to use DBI_TRACE=2 or 3 but I dont find the trace usefull.
>
> My tests against Oracle instances :
>   9i  (new install)      - ok (it is always ok with 9i)
>   10g (new install)      - bad
>   10g (migrated from 9i) - bad
>
> My tests against placeholders :
>   If I used placeholders like ":param1", it works the first time, then
> it will failed in general
>
> My tests against the SQL query :
>   If I comment the first column returned (the TO_CHAR(...) AS DT, it
> works the first time
>   then it failed
>
> So the problem seems to be with CACHE or something like that ?
>
>
> My script is :
> #
> ------------------------------------------------------------------------
> use DBI;
> $dsn=" dbi:Oracle:host=my_server;port=1521;sid=
my_sid";
> my $query = "
>   SELECT
>     TO_CHAR(TO_DATE(col1,'DD/MM/YY'),'YYYYMMDD') AS DT,
>     col2,col3,col4 FROM  table1 WHERE     SUBSTR(col1,4) = ?
> ";
> $DBH = DBI->connect($dsn,"toto","toto",{PrintError => 0, RaiseError =>
> 1});
> $STH = $DBH -> prepare($extraction_query);
> my $month_MMAA="03/08";
> #$STH->bind_param(":pr1", $month_MMAA);
> $STH->bind_param(1, $month_MMAA);
> $STH->execute();
>  DBI::dump_results($STH);
> $STH->finish();
> $DBH->disconnect();
> #
> ------------------------------------------------------------------------
>
> I need your help
>
> Regards
>
> Pascal
>
>

Hello John,

Please find the table definition :=20
create table owner1.table1  (
col0                CHAR(8)  not null,
col1                CHAR(5)  not null,
col2                CHAR(2)  not null,
col3                CHAR(3)  not null,
col4                NUMBER(5,2)   not null,
col5                CHAR(1),
col6                NUMBER(18,3),
col7                CHAR(1),
col8                NUMBER(18,3),
col9                DATE     default SYSDATE not null
)

System information :
Unix / SUN or AIX (the case is not depending on OS, I can reproduce it =
in all Unix)
Oracle 10gR2 PatchSet 2 (10.2.0.3.0) with 2 patches installed 5556081 =
and 5557962

The SQL is correct syntaxically and works fine when I execute it in =
sqlplus.
The only question I had is about col0 that is containing data like this =
format "dd/mm/yy" but is a string. But ...=20

I analyzed this issue again and have news about it :

The problem is depend on an Oracle parameter : CURSOR_SHARING.
On our instances this parameter is set sometimes at the default (EXACT) =
and sometimes to FORCE.
So the problem is the difference of the behaviour between Oracle 9i and =
Oracle 10g.
With Oracle 9i, the value of the parameter could be EXACT or FORCE, the =
Perl script works correctly
With Oracle 10g, if the value is FORCE, the Perl script report ORA-1008 =
error, else if the value is EXACT is works correctly.
=3D> so I have a workaround, it is to add and "alter session" after the =
DBH->connect.

There is an Oracle patch that should fix Oracle 10g behaviour
5254759 for 10.2.0.3 - ORA-12801/ORA-1008 OCCURS ON A PARALLEL QUERY =
WITH BIND VARIABLES
But I didn't tested it, yet.

Regards
Pascal
=20

-----Message d'origine-----
De : John Scoles [mailto:scoles@pythian.com]=20
Envoy=E9 : mardi 29 avril 2008 17:12
=C0 : MONSCHEIN,PASCAL
Cc : dbi-users@perl.org
Objet : Re: Error - DBD::Oracle / ORA-1008 problem with placeholders

Can you give me what the table schema is?
I would like to know what  datatypes

col1,col2,col3,col4


are so I can try and recreate the error.

As well what operation system are you using and which version of DBI?

anyway just looking at the SQL it doesn't look quite right

cheers
John Scoles

pmonschein@groupe-casino.fr wrote:
> Hello all,
> =20
> I encouter an error with DBD::Oracle and Oracle instances 10g that I=20
> dont understand :
>   DBD::Oracle::st execute failed: ORA-01008: not all variables bound=20
> (DBD ERROR: OCIStmtExecute)
>
> This script works fine with Oracle 9i.
> I compiled DBD::Oracle (1.19) based on Oracle 9i (on Sun-Solaris10) -=20
> DBI 1.58
>
> I tried to compile DBD::Oracle (1.21) based on Oracle 10g (on
> Sun-Solaris10) with the same error.
> I tried to use DBI_TRACE=3D2 or 3 but I dont find the trace usefull.
>
> My tests against Oracle instances :
>   9i  (new install)      - ok (it is always ok with 9i)
>   10g (new install)      - bad
>   10g (migrated from 9i) - bad
>
> My tests against placeholders :
>   If I used placeholders like ":param1", it works the first time, then =

> it will failed in general
>
> My tests against the SQL query :
>   If I comment the first column returned (the TO_CHAR(...) AS DT, it=20
> works the first time
>   then it failed
>
> So the problem seems to be with CACHE or something like that ?
>
>
> My script is :
> #
> ----------------------------------------------------------------------
> --
> use DBI;
> $dsn=3D" dbi:Oracle:host=3Dmy_server;port=3D1521;
sid=3Dmy_sid";
> my $query =3D "
>   SELECT
>     TO_CHAR(TO_DATE(col1,'DD/MM/YY'),'YYYYMMDD') AS DT,
>     col2,col3,col4 FROM  table1 WHERE     SUBSTR(col1,4) =3D ?=20
> ";
> $DBH =3D DBI->connect($dsn,"toto","toto",{PrintError =3D> 0, =
RaiseError =3D>=20
> 1}); $STH =3D $DBH -> prepare($extraction_query); my=20
> $month_MMAA=3D"03/08"; #$STH->bind_param(":pr1", $month_MMAA);=20
> $STH->bind_param(1, $month_MMAA); $STH->execute(); =20
> DBI::dump_results($STH); $STH->finish(); $DBH->disconnect(); #
> ----------------------------------------------------------------------
> --
>
> I need your help
>
> Regards
>
> Pascal
>
>  =20

Thanks Pascal.  I wanted the Table deff so I could exactly mimic your
situation.

I managed to get your code to work on my 10XE DB but I will give this
new Table a shot and see what happens on my 10 linux box later to day or
tomorrow

Would not surprize me if it is Oracle, It would not be the first time
that they had a bug in their code.

For now I might add this to the Readme

cheers
John Scoles .






pmonschein@groupe-casino.fr wrote:
> Hello John,
>
> Please find the table definition :
> 	create table owner1.table1  (
> 	   col0                CHAR(8)  not null,
> 	   col1                CHAR(5)  not null,
> 	   col2                CHAR(2)  not null,
> 	   col3                CHAR(3)  not null,
> 	   col4                NUMBER(5,2)   not null,
> 	   col5                CHAR(1),
> 	   col6                NUMBER(18,3),
> 	   col7                CHAR(1),
> 	   col8                NUMBER(18,3),
> 	   col9                DATE     default SYSDATE not null
> 	)
>
> System information :
>   Unix / SUN or AIX (the case is not depending on OS, I can reproduce it i
n all Unix)
>   Oracle 10gR2 PatchSet 2 (10.2.0.3.0) with 2 patches installed 5556081 an
d 5557962
>
> The SQL is correct syntaxically and works fine when I execute it in sqlplu
s.
> The only question I had is about col0 that is containing data like this fo
rmat "dd/mm/yy" but is a string. But ...
>
> I analyzed this issue again and have news about it :
>
> The problem is depend on an Oracle parameter : CURSOR_SHARING.
> On our instances this parameter is set sometimes at the default (EXACT) an
d sometimes to FORCE.
> So the problem is the difference of the behaviour between Oracle 9i and Or
acle 10g.
> With Oracle 9i, the value of the parameter could be EXACT or FORCE, the Pe
rl script works correctly
> With Oracle 10g, if the value is FORCE, the Perl script report ORA-1008 er
ror, else if the value is EXACT is works correctly.
> => so I have a workaround, it is to add and "alter session" after the DBH-
>connect.
>
> There is an Oracle patch that should fix Oracle 10g behaviour
> 5254759 for 10.2.0.3 - ORA-12801/ORA-1008 OCCURS ON A PARALLEL QUERY WITH 
BIND VARIABLES
> But I didn't tested it, yet.
>
> Regards
> Pascal
>
>
> -----Message d'origine-----
> De : John Scoles [mailto:scoles@pythian.com]
> Envoyé : mardi 29 avril 2008 17:12
> À : MONSCHEIN,PASCAL
> Cc : dbi-users@perl.org
> Objet : Re: Error - DBD::Oracle / ORA-1008 problem with placeholders
>
> Can you give me what the table schema is?
> I would like to know what  datatypes
>
> col1,col2,col3,col4
>
>
> are so I can try and recreate the error.
>
> As well what operation system are you using and which version of DBI?
>
> anyway just looking at the SQL it doesn't look quite right
>
> cheers
> John Scoles
>
> pmonschein@groupe-casino.fr wrote:
> 
>
>
>

Hello

Finally the bug I had was due to Oracle.

The patch Oracle that fix this bug is 5863277
- Desc: ORA-1008 THROWN ON SQL ON SECOND RUN WHEN =
CURSOR_SHARING=3DSIMILAR/FORCE
- Product 	RDBMS Server
- Release 	10.2.0.3=09

Note : the first patch 5254759 that I mentioned in my previous mail and =
that we believed could fix the issue did not work.

Hope that helps.=20
Thank you for your support.

Pascal

-----Message d'origine-----
De : John Scoles [mailto:scoles@pythian.com]=20
Envoy=E9 : mercredi 30 avril 2008 12:55
=C0 : MONSCHEIN,PASCAL
Cc : dbi-users@perl.org
Objet : Re: Error - DBD::Oracle / ORA-1008 problem with placeholders

Thanks Pascal.  I wanted the Table deff so I could exactly mimic your =
situation.

I managed to get your code to work on my 10XE DB but I will give this =
new Table a shot and see what happens on my 10 linux box later to day or =
tomorrow

Would not surprize me if it is Oracle, It would not be the first time =
that they had a bug in their code.

For now I might add this to the Readme

cheers
John Scoles .






pmonschein@groupe-casino.fr wrote:
> Hello John,
>
> Please find the table definition :=20
> 	create table owner1.table1  (
> 	   col0                CHAR(8)  not null,
> 	   col1                CHAR(5)  not null,
> 	   col2                CHAR(2)  not null,
> 	   col3                CHAR(3)  not null,
> 	   col4                NUMBER(5,2)   not null,
> 	   col5                CHAR(1),
> 	   col6                NUMBER(18,3),
> 	   col7                CHAR(1),
> 	   col8                NUMBER(18,3),
> 	   col9                DATE     default SYSDATE not null
> 	)
>
> System information :
>   Unix / SUN or AIX (the case is not depending on OS, I can reproduce =
it in all Unix)
>   Oracle 10gR2 PatchSet 2 (10.2.0.3.0) with 2 patches installed=20
> 5556081 and 5557962
>
> The SQL is correct syntaxically and works fine when I execute it in =
sqlplus.
> The only question I had is about col0 that is containing data like =
this format "dd/mm/yy" but is a string. But ...=20
>
> I analyzed this issue again and have news about it :
>
> The problem is depend on an Oracle parameter : CURSOR_SHARING.
> On our instances this parameter is set sometimes at the default =
(EXACT) and sometimes to FORCE.
> So the problem is the difference of the behaviour between Oracle 9i =
and Oracle 10g.
> With Oracle 9i, the value of the parameter could be EXACT or FORCE,=20
> the Perl script works correctly With Oracle 10g, if the value is =
FORCE, the Perl script report ORA-1008 error, else if the value is EXACT =
is works correctly.
> =3D> so I have a workaround, it is to add and "alter session" after =
the DBH->connect.
>
> There is an Oracle patch that should fix Oracle 10g behaviour
> 5254759 for 10.2.0.3 - ORA-12801/ORA-1008 OCCURS ON A PARALLEL QUERY=20
> WITH BIND VARIABLES But I didn't tested it, yet.
>
> Regards
> Pascal
> =20
>
> -----Message d'origine-----
> De : John Scoles [mailto:scoles@pythian.com] Envoy=E9 : mardi 29 avril =

> 2008 17:12 =C0 : MONSCHEIN,PASCAL Cc : dbi-users@perl.org Objet : Re:=20
> Error - DBD::Oracle / ORA-1008 problem with placeholders
>
> Can you give me what the table schema is?
> I would like to know what  datatypes
>
> col1,col2,col3,col4
>
>
> are so I can try and recreate the error.
>
> As well what operation system are you using and which version of DBI?
>
> anyway just looking at the SQL it doesn't look quite right
>
> cheers
> John Scoles
>
> pmonschein@groupe-casino.fr wrote:
>  =20 
 
RaiseError=20 
>
>
>  =20