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 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 ...
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.
=> 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:[EMAIL PROTECTED]
Envoyé : mardi 29 avril 2008 17:12
À : MONSCHEIN,PASCAL
Cc : [email protected]
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
[EMAIL PROTECTED] 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
>
>
