Developer Rants

This is link to an article that explains how to Oracle stored procedures to return recordsets.

The main trick is to add:

Extended Properties=”PLSQLRSet=1″ to your connection string.

The cursor itself must be defined as a ref cursor :

CREATE OR REPLACE
PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR
END;

Oracle 9i introduced the SYS_REFCURSOR type for this purpose.

Here is a valid example:

CREATE OR REPLACE
PROCEDURE USP_GET_UNIT(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_precordset FOR
       SELECT UNIT, DESCRIPTION FROM UNIT;
END;

On the delphi side don’t add the cursor as a parameter.