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=SIMILAR/FORCE - Product RDBMS Server - Release 10.2.0.3 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. Thank you for your support. Pascal -----Message d'origine----- De : John Scoles [mailto:[EMAIL PROTECTED] Envoyé : mercredi 30 avril 2008 12:55 À : MONSCHEIN,PASCAL Cc : [email protected] 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 . [EMAIL PROTECTED] 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 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 >> >> >> > > >
