working with PL/SQL REF CURSORs

From: Adam Rothberg <adamr1000_at_hotmail.com>
Date: 28 Oct 2002 10:48:53 -0800
Message-ID: <68326679.0210281048.4e7f1796@posting.google.com>

I need some help working with PL/SQL REF CURSORS.

I understand how they are used to pass a recordset back to ADO from an ASP page. What I would like to do is call the same procedure (that is returning the REF CURSOR as an IN OUT parameter) from another PL/SQL procedure in the same schema and get at the data in the REF CURSOR. This way I can use the same proc for the ADO call from ASP, as well as another PL/SQL procedure to get at the same data.

Here's what I have:

Proc that returns the REF CURSOR:

---

PACKAGE PKG_THEPACKAGE AS
        TYPE t_cursor IS REF CURSOR ;
.
.
.

PROCEDURE P_RPT_SOMEDATA
(

	dtStartDate IN DATE,
	dtEndDate IN DATE,
	io_cursor IN OUT t_cursor

)
IS
v_cursor t_cursor;

	OPEN v_cursor
	FOR 
	SELECT s.COL1, s.COL2, s.COL3
	FROM SOMETABLE s
	WHERE s.THEDATE BETWEEN dtStartDate AND dtEndDate

	io_cursor := v_cursor;

END P_RPT_SOMEDATA;
...

Proc that calls the above proc:

---

PROCEDURE P_GET_SOME_DATA(dtDate IN DATE) IS
szCol1 SOMETABLE.COL1%TYPE;
csrMetrics t_cursor;
BEGIN
        PKG_THEPACKAGE.P_RPT_SOMEDATA( dtDate - 20, dtDate, csrMetrics);         

• ????? now what do i do to get at the rows in csrMetrics ?????

END; I am unclear about the syntax, please help. Thank you. Received on Mon Oct 28 2002 - 12:48:53 CST