Article #28616: Getting a result set from an Oracle stored procedure in Delphi or C++Builder.

Question:

How do I get a result set from an Oracle Stored Procedure?

Answer:

A REF CURSOR is required to return a result set from a stored procedure. You will need to put the procedure in a package. To create a package use the following SQL command:

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

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