Oracle + ADO - stored procedure problem

From: Edgar <_mark__at__something_.com>
Date: Wed, 28 Mar 2001 17:53:49 -0500
Message-ID: <tXtw6.171772$Z2.2098455@nnrp1.uunet.ca>

Hello,
I use Oracle stored procedure which builds interim table out of many other tables and
finally it retrieves a record set from that table (select *). At one of my installation this
stored procedure is running about 5 times slower from Delphi application ( through ADO )
than from Oracle PL/SQL console. I did not have this before. Please see more details at the bottom.
I believe it is a matter of configuring ADO and/or Oracle.

Does anybody hav a clue what I should be looking for ?

An additional and useful informatoin could be that to return a cursor from stored procedure to ADO I use this construction

     ( ACURSOR IN OUT MY_TYPE.TCUR ) AS
    begin

      /* build my UserData table here */
      OPEN ACURSOR FOR SELECT * FROM USERDATA;

    end;

    where MY_TYPE.TCUR is a package ( defined in "Pacage" ) in this way :

     AS
         TYPE TCUR IS REF CURSOR;

    END; I found this as the only way to retrieve cursor from stored procedure to ADO.
I also noticed that this stored procedure works fine with ADO but does not want to work from PL/SQL console, could anybody tell my why ? Is it possible to
create stored procedure which retrieves dataset and runs correctly in PL/SQL and with ADO ?

I appreciate any help.

Marek. Received on Wed Mar 28 2001 - 16:53:49 CST