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

Abstract: How to pass a REF CURSOR to an Oracle stored procedure.
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;
Then when you create the procedure:
CREATE PROCEDURE RETURN_RESULT_SET
 (oCursor IN OUT MyPkg.CursorType) AS
BEGIN
  open oCursor for select * from SomeTable;
END;
In Delphi or C++Builder, set the TStoredProc parameter DataType as type ftCursor. At this point you should treat the TStoredProc component like a TQuery and use the Open method instead of ExecProc.

You should also be sure you have the latest BDE installed.

Published on: 4/12/2002 12:00:00 AM

Server Response from: ETNASC03

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.