SYS_REFCURSOR Vs REF CURSOR

How to Use the SYS_REFCURSOR and REF CURSOR in Oracle Packages and Procedures

SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it and can be used in 9i or higher. SYS_REFCURSOR can be used to:

• Delcare a cursor variable in an Oracle stored procedure/function;
• Pass cursors from and to an Oracle stored procedure/function.

Exampel 1: Getting a cursor out from an Oracle Package/ProcedureThe following Oracle stored procedure demonstrates how to get a REFCURSOR cursor variable out from a package/procedure:

Package Specification Definition:

CREATE OR REPLACE PACKAGE XX_BI_PKG AS
PROCEDURE xx_ar_inv_proc(p_recordset OUT SYS_REFCURSOR);
END;
Package body definition:CREATE OR REPLACE PACKAGE BODY XX_BI_PKG IS
PROCEDURE xxnsn_ar_inv_proc(p_recordset OUT sys_refcursor)
AS
BEGIN
OPEN p_recordset FOR SELECT RCT.trx_date
,COUNT(RCT.customer_trx_id)
,SUM(APS.amount_due_original)
FROM ra_customer_trx_all RCT
,ar_payment_schedules_all APS
WHERE RCT.customer_trx_id = APS.customer_trx_id
AND APS.class = 'INV'
AND APS.status = 'OP'
GROUP BY RCT.trx_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END xx_ar_inv_proc;
END;

The following PL/SQL statement is used to harness the above Oracle stored procedure:

DECLARE
v_cursor sys_refcursor;
v_date varchar2(100);
v_count number(12);
v_amount number(12);
BEGIN
XX_BI_PKG.xx_ar_inv_proc(p_recordset =>v_cursor);
LOOP
FETCH v_cursor
INTO v_date, v_count, v_amount;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_date ' ' v_count ' ' v_amount);
END LOOP;
CLOSE v_cursor;
END;