Bug with pl/sql boolean parameters

[AV]

Hello,

There is a bug with pl/sql boolean parameters in ODAC 8.2.8.
I'm using Oracle 11.2 and Delphi XE2:

---

Code: Select all

create or replace procedure scott.b_test (b boolean) is
begin
  null;
end;

----

Code: Select all

var
  LSession: TOraSession;
  LQuery: TOraQuery;
begin
  LSession := TOraSession.Create(nil);
  LQuery := TOraQuery.Create(nil);
  try
    LSession.ConnectString := 'user/password@server';
    LSession.Connect;
    LQuery.SQL.Text := 'begin scott.b_test(:b); end;';
    LQuery.ParamByName('b').AsBoolean := True;
    LQuery.Session := LSession;
    LQuery.ExecSQL;
  finally
    LSession.Free;
    LQuery.Free;
  end;
end;

---

The error is:
PLS-00306: wrong number or types of arguments in call to 'B_TEST'

[AlexP]

hello,

The Boolean types in PL/SQL and SQL are different, therefore, when attempting to use parameters, you get this error, as attempting to use SQL Boolean type in a procedure. To solve this problem, you should use type cast in the following way

Code: Select all

var
  LSession: TOraSession;
  LQuery: TOraQuery;
begin
  LSession := TOraSession.Create(nil);
  LQuery := TOraQuery.Create(nil);
  try
    LSession.ConnectString := 'user/password@server';
    LSession.Connect;
    LQuery.SQL.Text := 'declare  v_B boolean := sys.DIUTIL.INT_TO_BOOL(:B);  begin scott.b_test(v_B); end;';
    LQuery.ParamByName('b').AsBoolean := True;
    LQuery.Session := LSession;
    LQuery.ExecSQL;
  finally
    LSession.Free;
    LQuery.Free;
  end;

[AV]

Thanks for the answer, yes, that is a possible workaround, to pass a number, and then convert it to boolean, but as there is no 'oracle-sql boolean' type and there is only 'pl/sql boolean' type, i consider following behaviour as a bug

http://docs.oracle.com/cd/E11882_01/app ... m#CJACJGBG

Because SQL has no data type equivalent to BOOLEAN, you cannot..