Returning cursor from PL / SQL objects

In following example, function returns the ref cursor based on the input parameter

CREATE OR REPLACE FUNCTION REF_CURSOR_TEST (P_DEPTNO NUMBER)
RETURN SYS_REFCURSOR
IS
CUR_DEPT_COUNT SYS_REFCURSOR;

BEGIN
OPEN CUR_DEPT_COUNT FOR SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO = P_DEPTNO GROUP BY DEPTNO;
RETURN CUR_DEPT_COUNT;
END;
/

PL/SQL block to handle the returned cursor

DECLARE
VDEPTNO NUMBER;
VDCOUNT NUMBER;
TCUR SYS_REFCURSOR;
BEGIN
TCUR := REF_CURSOR_TEST(10);

LOOP
FETCH TCUR INTO VDEPTNO, VDCOUNT;
EXIT WHEN TCUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VDEPTNO );
DBMS_OUTPUT.PUT_LINE(VDCOUNT);
END LOOP;

CLOSE TCUR;
END;
/

NOTE: We do not need to open the cursor, as it is already open. But one should close it at the end.

Advertisements
This entry was posted in Oracle PL/SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s