Board index » delphi » Oracle 7.3 Returning a result set from a stored procedure

Oracle 7.3 Returning a result set from a stored procedure

Please can anyone help.

I am aware of how to achieve this with Interbase but need the same
ability in Oracle 7.3. I am using Delphi 3.02 which claims this
ability as one of it's new features. It does not, however, come with
an example.

Is there an Oracle equivalent of the Interbase SUSPEND command or is a
different approach required.

If anyone can help, especially if you can post an example to the group
I would be very grateful indeed.

CD

 

Re:Oracle 7.3 Returning a result set from a stored procedure


On Tue, 06 Jan 1998 17:17:49 GMT, charles.bai...@fly.{*word*269}.com

Quote
(Claire B Downes) wrote:
>If anyone can help, especially if you can post an example to the group

First, create a package:

CREATE PACKAGE MYPKG
IS
  TYPE CursorType IS REF CURSOR;
END MyPkg;

Then the procedure:

CREATE PROCEDURE RETURN_RESULT_SET
 (oCursor IN OUT MyPkg.CursorType) AS
BEGIN
  open oCursor for select * from SomeTable;
END;

Then in Delphi:

1. Set the TStoredProc parameter as type Cursor.
2. At this point, treat the TStoredProc component like a TQuery.
    (Use the Open method instead of ExecProc.)

-Todd Barry

Re:Oracle 7.3 Returning a result set from a stored procedure


Quote
Todd Barry wrote:
> First, create a package:

> CREATE PACKAGE MYPKG
> IS
>   TYPE CursorType IS REF CURSOR;
> END MyPkg;

> Then the procedure:

> CREATE PROCEDURE RETURN_RESULT_SET
>  (oCursor IN OUT MyPkg.CursorType) AS
> BEGIN
>   open oCursor for select * from SomeTable;
> END;

> Then in Delphi:

> 1. Set the TStoredProc parameter as type Cursor.
> 2. At this point, treat the TStoredProc component like a TQuery.
>     (Use the Open method instead of ExecProc.)

> -Todd Barry

Todd,

I need some help from you.

I create some procedure like this:

DECLARE
TYPE MyTableType IS TABLE OF DBTable%ROWTYPE INDEX BY BINARY_INTEGER;
-- DBTable is some table from Oracle database
CREATE PROCEDURE MyProc(ID IN BINARY_INTEGER, ResultTable OUT
MyTableType) is
BEGIN
 Select * INTO ResultTable FROM DBTable WHERE DBTable.ID = ID;
 -- process some action on ResultTable and then I would get ResultTable
on my Delphi client

END:

Is it possible to get result set from the Oracle stored procedure as
PL/SQL table?

Thanks,
Roman Kalinin

Other Threads