ORA-01036 with bind variable named 'n' and UseUnicode

[mstaszew]

Version 6.80
10g client (8i, 9i, and 11g work fine)
10g database although this doesn't seem to matter
OraCall.OCIUnicode is True
TOraSession.UseUnicode is True

We are seeing ORA-01036. The problem appears to be related to a bind variable named 'n' I have tested with several queries and the variable datatype and value doesn't seem to matter. Other variable names may also cause this, but this is the case that we are seeing. We unfortunately have many variables by this name. If there is a workaround that can be posted that would help greatly.

Steps to reproduce:

1. Create a TOraSession with UseUnicode and 10g client
2. Create/Execute a query as...

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Qry: TSmartQuery;
begin
  Qry := TSmartQuery.Create(nil);
  try
    Qry.Session := OraSession1;
    Qry.SQL.Text := 'SELECT * FROM DUAL where 0 = :n';
    Qry.Params.ParamByName('n').DataType := ftInteger;
    Qry.Params.ParamByName('n').Value := 0;
    Qry.Active := True;
  finally
    Qry.Free;
  end;
end;

3. ORA-01036 error occurs

Thanks,
Michael

[Plash]

This is a bug in the Oracle client. You can do one of the following:

- don't use OCIUnicode;
- upgrade Oracle client to 11g;
- rename all your parameters.

[mstaszew]

We did not experience this problem in 6.70. I am awaiting confirmation on that from one of the other developers on the team, but we have been using the Unicode flags since December 2008 or so and the first time that this has come up has been with the 6.80 upgrade.

Thanks,
Michael

[Plash]

The problem occurs only if 'n' is the last character in SQL. If you change your query like the following

Code: Select all

     SELECT * FROM DUAL where :n = 0

the problem does not occur.

Old ODAC version added a space after the end of SQL. This prevented the problem.

So you can rewrite your statement. We'll also add a fix with a space in the next ODAC version.