Page 1 of 1
ORA-01036 when 2 variables have similar names.
Posted: Wed 05 Jan 2011 15:55
by 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.
Posted: Thu 06 Jan 2011 08:47
by 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.
Posted: Thu 06 Jan 2011 14:36
by 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;
Posted: Thu 06 Jan 2011 15:26
by 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.