SET ECHO ON

--
-- rsPLSQLdemo.sql
--

-- Creates a simple procedure which returns a REF CURSOR

CREATE OR REPLACE PACKAGE rsPLSQLdemoPkg AS
--
--
TYPE cursorType IS REF CURSOR; -- This allows my function to return a REF CURSOR
--
--
  FUNCTION rsPLSQLdemo RETURN cursorType;
--
--
  PROCEDURE tester;
--
--
END rsPLSQLdemoPkg;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY rsPLSQLdemoPkg AS
--
--
  FUNCTION rsPLSQLdemo RETURN cursorType 
  IS
  myCursor cursorType;
  BEGIN
    OPEN myCursor FOR
      SELECT TABLE_NAME,COMMENTS
      FROM DICTIONARY
      WHERE TABLE_NAME LIKE 'ALL%TABLES';
  RETURN (myCursor);
  END rsPLSQLdemo;
--
--
  PROCEDURE tester 
  IS
  aCursor cursorType;
  aRow DICTIONARY%ROWTYPE;
  BEGIN
    -- Do drum roll here.  My function returns a cursor, hopefully.
    aCursor := rsPLSQLdemo;

    -- Now check my work,
    -- loop through the cursor to see if anything actually in the cursor.
    LOOP
      FETCH aCursor INTO aRow;
      EXIT WHEN aCursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(aRow.table_name);
    END LOOP;
  END tester;
--
--
END rsPLSQLdemoPkg;
/
SHOW ERRORS

SET SERVEROUTPUT ON
EXEC rsPLSQLdemoPkg.tester