1rem ----------------------------------------------------------------------- 2rem URL: http://www.orafaq.com/scripts/plsql/refcurs.txt 3rem Filename: refcurs.sql 4rem Purpose: Pass result sets (REF CURSOR) between procedures and 5rem functions 6rem Date: 15-Jun-2001 7rem Author: Frank Naude (frank@ibi.co.za) 8rem ----------------------------------------------------------------------- 9 10set serveroutput on 11 12-- Define TYPES package separately to be available to all programming 13-- environments... 14CREATE OR REPLACE PACKAGE types AS 15 TYPE cursortyp is REF CURSOR; -- use weak form 16END; 17/ 18 19-- Create test package to demonstrate passing result sets... 20CREATE OR REPLACE PACKAGE test_ref_cursor AS 21 PROCEDURE main; 22 FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp; 23 PROCEDURE process_cursor(cur types.cursortyp); 24END; 25/ 26show errors 27 28 29CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS 30 31 -- Main program entry point 32 PROCEDURE main IS 33 BEGIN 34 process_cursor( get_cursor_ref(1) ); 35 process_cursor( get_cursor_ref(2) ); 36 END; 37 38 -- Get and return a CURSOR REF/ Result Set 39 FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS 40 cur types.cursortyp; 41 BEGIN 42 if typ = 1 THEN 43 OPEN cur FOR SELECT * FROM emp WHERE ROWNUM < 5; 44 ELSE 45 OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5; 46 END IF; 47 RETURN cur; 48 END; 49 50 -- Process rows for an EMP or DEPT cursor 51 PROCEDURE process_cursor(cur types.cursortyp) IS 52 empRec emp%ROWTYPE; 53 deptRec dept%ROWTYPE; 54 BEGIN 55 LOOP 56 FETCH cur INTO empRec; -- Maybe it was an EMP cursor, try to fetch... 57 EXIT WHEN cur%NOTFOUND; 58 dbms_output.put_line('EMP ROW: '||empRec.ename); 59 END LOOP; 60 EXCEPTION 61 WHEN ROWTYPE_MISMATCH THEN -- OK, so it was't EMP, let's try DEPT. 62 LOOP 63 FETCH cur INTO deptRec; 64 EXIT WHEN cur%NOTFOUND; 65 dbms_output.put_line('DEPT ROW: '||deptRec.dname); 66 END LOOP; 67 END; 68 69END; 70/ 71show errors 72 73 74EXEC test_ref_cursor.main; 75 76