ORA-01036 when 2 variables have similar names.

[gregoryliss]

Given the following script:

Code: Select all

VARIABLE v1 VARCHAR2(30);
VARIABLE v11 VARCHAR2(30);

DECLARE
   l_cnt NUMBER;
BEGIN
   SELECT COUNT(*)
   INTO l_cnt
   FROM dual
   WHERE 'DUMMY' = :v11;
END;
/

Create a TOraParam for each variable and use a TOraSQL to execute the block and you'll get an ORA-01036 error. If you change v1 to X, the error doesn't occur. It seems to only occur with variables having similar names.

[AlexP]

Hello,

Please specify the following information so that I am able to help you:
- the exact version of ODAC;
- the name and the version of your IDE;
- send a complete small sample to demonstrate the problem to alexp*devart*com.

[gregoryliss]

ODAC version 7.00.0.3
Delphi 2009 Version 12.0.3420.21218

Tested on 11.2.0 Oracle client against an XE 10.2.0.1.0 database and a 11.1.0.7.0 database.

Sample project sent offline

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Param: TOraParam;
  OraSession1: TOraSession;
  Qry: TOraSQL;
begin
   OraSession1 := TOraSession.Create(nil);
   try
     OraSession1.ThreadSafety := True;
     OraSession1.Options.CharLength := 0;
     OraSession1.ConnectDialog := ConnectDialog;
     OraSession1.AutoCommit := True;
     OraSession1.Options.EnableNumbers := True;
     OraSession1.LoginPrompt := True;
     OraSession1.Options.UseUnicode := True;
     OraSession1.Connect;
     Qry := OraSession1.OraSQL;
     if OraSession1.Connected then
     begin
        Qry.SQL.Clear;
        qry.sql.Text := memo1.Lines.Text;
        qry.params.clear;
        Param := TOraParam.Create(Qry.Params, ptInputOutput);
        Param.Name := edtParam1.text;
        Param.DataType := ftWideString;
        Param.Size := 30;
        Param.Value := '';
        Param := TOraParam.Create(Qry.Params, ptInputOutput);
        Param.Name := edtParam2.text;
        Param.DataType := ftWideString;
        Param.Size := 30;
        Param.Value := '';
        qry.prepare;
        qry.execute;
     end;
   finally
      OraSession1.Disconnect;
      OraSession1.Free;
   end;
end;

[AlexP]

Hello,

I have received your mail.
But if I change variable name from 'v1' to 'x', I've got the same error.
It is connected with the incorrect number of parameters in the PL/SQL statement and the number of created parameters (the number of parameters should be the same).
So if you change your PL/SQL statement in the following way:

DECLARE
l_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_cnt
FROM dual
WHERE 'DUMMY' = :v11 OR 'DUMMY' = :v1;
END;

the error will not occur.