Devart Forums

Posted: **Thu 29 Jun 2006 08:55**

Hello, Core Lab!

We have faced some problem significant for us. When we use BDE TQuery component that contains PL/SQL block with call of procedure returning cursor we always first call Prepare and after that assign parameter values. It works good. Now we try to replace TQuery component by TOraQuery and we get an error described below.

Server side

Code: Select all

create or replace package Z_PkgTest is
  type TRefCursor is REF CURSOR;
  procedure pTest(p_Owner in varchar2, p_CR in out TRefCursor);
end Z_PkgTest;
/
create or replace package body Z_PkgTest is
  procedure pTest(p_Owner in varchar2, p_CR in out TRefCursor) is
  begin
    if p_Owner is null then
      Raise_Application_Error(-20000, 'Wrong argument Owner (is null)');
    end if;
    open p_CR for
      select OBJECT_NAME from SYS.ALL_OBJECTS where OWNER = p_Owner;  
  end;
end Z_PkgTest;
/

Client side

Code: Select all

...
OraQuery: TOraQuery;
...
with OraQuery do begin
  Prepare;
  ParamByName('OWNER').AsString := 'SYS';
  Open;
  ShowMessage(Fields[0].AsString);
  Close;
end;
...

Component OraQuery SQL property contains

Code: Select all

begin
  Z_PkgTest.pTest(p_Owner=>:OWNER, p_CR=>:CR);
end;

Parameter OWNER has type ftString, parameter CR - ftCursor.
At line with Prepare we receive database error "ORA-20000 Wrong argument Owner (is null)" that means our procedure was executed on the Prepare call but not on the Open as we expected.

The question is - how can we avoid execution of PL/SQL block on prepare leaving our calling style unchanged?

ODAC 5.70.0.28 Trial
Delphi 5
Oracle server 9.2.0.4
OCI (client) 7.3.4.0.0

Posted: **Thu 29 Jun 2006 13:56**

You cannot avoid execution for PL/SQL statements with CURSOR parameter when calling Prepare method.
In next versions of ODAC we will try to correct behaviour of Prepare method.

Posted: **Thu 29 Jun 2006 16:59**

Thank You!
Please confirm if it happens only for PL/SQL statements with CURSOR parameters. In that case we can use our own component derived from TOraQuery with overrided method Prepare where we can analyse types of parameters.

Posted: **Fri 30 Jun 2006 08:14**

Execution of PL/SQL statements in Prepare method happens for statements with CURSOR parameters only.

Devart Forums

Prepare method executes procedure returning cursor?

Prepare method executes procedure returning cursor?