ORA-01475 must reparse cursor to change bind variable datatype

Guest · Post by **Guest** » Tue 11 Oct 2005 08:26

Hi,

when executing follwing query i get this error.
Only with orale 8.1.7 not with oracle 9.2.
I'm using ODAC version 5.55.0.21.

Thanks for your help.

procedure TTransferProcess.SelectTransfers;
begin
with fQryHdlTrans do begin
Close;
Unprepare;
if Tag TagParsed then begin
SQL.Text :=
'SELECT A.* '+
' FROM VITSUSER.SFC_TRANSFERS A '+
' WHERE A.PROCESSID = :LOCKID ';
Tag := TagParsed;
end;
Prepare;
ParamByName('LOCKID').AsInteger := fProcessId;
Open;
end;
end;

Alex · Post by **Alex** » Thu 13 Oct 2005 06:53

Sorry, we cannot reproduce your problem. We need more detailed description i.e. your Oracle client vrsion and complete sample to demonstrate your problem with script to create server objects.
Please, send a message to our ODAC support e-mail address.

Guest · Post by **Guest** » Thu 27 Oct 2005 12:06

When i change the parameter to a string value it works,
but with a integer value i get the above error.
Even if the field is an integer value.

I have send you a mail with details.
Hopefully you can help me out.

Alex · Post by **Alex** » Fri 28 Oct 2005 13:02

We don't receive your mail, may be you should try again. Please see support e-mail address in Support.txt file that supplied with ODAC.

Guest · Post by **Guest** » Fri 28 Oct 2005 13:20

I just have send it again, i have send it to support@crlab dot com with
subject ORA-01475 must reparse cursor to change bind variable datatype

Ain't that the right adress?

Centric Programmer · Post by **Centric Programmer** » Mon 07 Nov 2005 12:11

I have the same problem. Was a answer generated from this post ?
If so can you pleas post this?
In my case the SQl gets changed runtime so i dont even use parameters.
I have tryed to make this solution.

with dmkad.qukadsubjekt do
begin
Active := FALSE;
UnPrepare ;
SQL[5] := sql_string;
Prepare ;
Active := TRUE; // Reparse error here
end;

But its not working the strange this is that it works after the SQL has been opend once. Is there something i can call to make sure its reparsed ?

Centric programmer · Post by **Centric programmer** » Mon 07 Nov 2005 12:18

Btw the last error i got like this was couse of dynamic SQL and linked querys the masterdetail was cousing this.
This case its a master detail again so i gess i just need to look some more.
We need the dynamic SQL to change the data and tables used to present the detailed data. I hope you guys find the answer.
Please keep me informed to at pieter.valentijn(At)centric.nl

Centric programmer · Post by **Centric programmer** » Mon 07 Nov 2005 13:04

Apperently setting the detail param type design time did the trick for me.
Im if cource not sure if it will work for you.

Alex · Post by **Alex** » Mon 07 Nov 2005 15:35

I have reproduced your problem. It seems like this is Oracle 8.1.7 issue and doesn't appear on oracle servers of higher versions. The problem is in parameters' datatype binding. When you call Prepare for SQL with parameters of udefined types, ODAC assumes that their type is VARCHAR2 and binds them like parameters of this type. After preparing you implicitly change parameter's datatype to integer by calling "ParamByName('LOCKID').AsInteger := 10;" so when you open or execute SQL this parameter binds like an integer one. On execution Oracle returns an Ora - 01475 error because different parameter datatypes are used during preparation and execution.
The solution of this problem is to define parameter datatype before preparation i.e.

Code: Select all

    ParamByName('LOCKID').DataType := ftInteger;
    Prepare;
    ParamByName('LOCKID').AsInteger := 10;
    Open;