Using the REF Data Type with Oracle Databases

The adapter supports the use of the REF data type (cursor) as an OUT parameter only in an Oracle stored procedure. The adapter returns a result in the same way as it does a result set from a SELECT query. The exact usage depends on the driver used.

When you use DataDirect Connect® (Merant) drivers, the procedure is called by leaving the REF cursor parameter out of the procedure's call list.

For example, the ORDER_DEMO.FindOrders procedure with an input price of $1.00 can be called through the request program with the following request:

{stmt={sql="{call ORDER_DEMO.FindOrders(?)}" bind={position=1
type="IN" data=1}}}

The REF cursor's data will be returned in the results section of the reply.

Considering the following PL/SQL procedure:

-- Create a REF cursor procedure
CREATE OR REPLACE PACKAGE ORDER_DEMO AS
TYPE OrderCurTyp IS REF CURSOR RETURN REPLYTEST%ROWTYPE;
PROCEDURE FindOrders (price IN NUMBER, order_cv IN OUT
OrderCurTyp);
END ORDER_DEMO;
CREATE OR REPLACE PACKAGE BODY ORDER_DEMO AS
PROCEDURE FindOrders (price IN NUMBER, order_cv IN OUT
OrderCurTyp) IS
BEGIN
OPEN order_cv FOR SELECT * FROM REPLYTEST where ID > price;
END FindOrders;
END ORDER_DEMO;