PL/SQL REF Cursor Example

PL/SQL REF Cursor Example

DECLARE

   TYPE array_t IS TABLE OF NUMBER

      INDEX BY BINARY_INTEGER;

   r_array     SYS_REFCURSOR;

   rec_array   array_t;

   l_num       NUMBER := 5;

   l_num1      NUMBER := 3;

BEGIN

   OPEN r_array FOR

      'SELECT OBJECT_ID FROM ALL_OBJECTS WHERE ROWNUM < :P_NUM AND OBJECT_ID > :P_N'

      USING l_num, l_num1;

   FETCH r_array

   BULK COLLECT INTO rec_array;

   FOR i IN rec_array.FIRST .. rec_array.LAST

   LOOP

      DBMS_OUTPUT.put_line (rec_array (i));

   END LOOP;

   CLOSE r_array;

END;