#96 Execute overloaded procedure

[Androschuk Oleksandr]

Instruments:
ZEOSDBO-7.1.3a-stable
Lazarus v1.2.4-fpc-2.6.4-win32
Database: Personal Oracle Database 11g Release 11.2.0.3.0 - 64bit

Problem:
If I have in Oracle package with 2 overload procedure with different parameters. When I try executed one with 1 parameters, really executed other procedure with 3 parameters.

procedure looks like:
Update_Doc(id, qty, price);
Update_Doc(row);

Example:

with TZQuery.Create(nil) do
try
  Connection := <SomeConnection>;
  SQL.Text := 'begin
                 <schema_name>.PKG_DOCUMENTS.Update_Doc('1');
               end;'
  ExecSQL;  <- error with parameters count
finally
  Free;
end;

Error text:

SQL Error: OCI_ERROR: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'Update_Doc'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

Note:
PL/SQL procedure looks like:

CREATE OR REPLACE PACKAGE PKG_DOCUMENTS AS
  PROCEDURE Update_Doc(id NUMBER, qty NUMBER, price NUMBER);
  PROCEDURE Update_Doc(row NUMBER);
END Documents;
/
CREATE OR REPLACE PACKAGE BODY PKG_DOCUMENTS AS

    PROCEDURE  Update_Doc(id NUMBER, qty NUMBER, price NUMBER) IS
    BEGIN
        ...;
    END Update_Doc;

    PROCEDURE  Update_Doc(row NUMBER) IS
    BEGIN
        ...;
    END Update_Doc;

END PKG_DOCUMENTS;
/

[EgonHugeist]

IIRC should you call:

with TZQuery.Create(nil) do
try
Connection := <someconnection>;
SQL.Text := 'begin
<schema_name>.PKG_DOCUMENTS.Update_Doc(1); <- remove the quotes!!!!
end;'
ExecSQL; <- error with parameters count
finally
Free;
end;</schema_name></someconnection>

OCI can't determine a overload if you use the parameters wrongly... So remove the quotes around you in values...

Michael

[Androschuk Oleksandr]

This is a great idea, but in the application I set parameters like this:

with TZQuery.Create(nil) do
try
  Connection := <SomeConnection>;
  SQL.Text := 'begin
                 <schema_name>.PKG_DOCUMENTS.Update_Doc(:Param1);
               end;'
  ParamByName('Param1') := 1; 
  ExecSQL;  <- error with parameters count
finally
  Free;
end;

TZQuery component read metadata using sql:

SELECT *
FROM ALL_ARGUMENTS...

There is column OVERLOAD.
If I have only one procedure there - null value.
If I have 2 or more overload procedures in column OVERLOAD we have value 1...(procedure count). Maybe in components ignored this column, and take all parameter list.

[EgonHugeist]

Just a note: Didn't hat the time do digging in...

[EgonHugeist]

related to #68

[EgonHugeist]

Sorry for the delay.. Am i doing something diffentent?

My testcase:

procedure ZTestCompOracleBugReport.TestTicket96;
var
Query: TZQuery;
begin
if SkipForReason(srClosedBug) then Exit;
Query := CreateQuery;
try
Query.ParamCheck := True;
Query.SQL.Text := 'begin ';
Query.SQL.Add(' P_TICKET96.Update_Doc(:Param1);');
Query.SQL.Add('end;');
Query.ParamByName('Param1').AsInteger := 1;
Query.ExecSQL;
Query.SQL.Text := 'begin ';
Query.SQL.Add(' P_TICKET96.Update_Doc(:Param1, :Param2, :Param3);');
Query.SQL.Add('end;');
Query.ParamByName('Param1').AsInteger := 1;
Query.ParamByName('Param2').AsInteger := 2;
Query.ParamByName('Param3').AsInteger := 3;
Query.ExecSQL;
Query.SQL.Text := 'begin ';
Query.SQL.Add(' P_TICKET96.Update_Doc(:Param1);');
Query.SQL.Add('end;');
Query.ParamByName('Param1').AsInteger := 1;
Query.ExecSQL;
finally
Query.Free;
end;
end;

Works like a charme.. Ideas?

[EgonHugeist]

Not reproducable and no feedback...
Reopen if you disagree.