How to read a cursor inside another cursor in PL / SQL

Question

I have to complete a development made by someone that left the company, I trying to understand a cursor built inside another cursor.. something like this :

CURSOR c_fund as select col_1, 
       col_2, 
       CURSOR(select col_10 * {some formula} clo_n, 
                     col_m 
               from table2 
              where col_8 is not null)
(...)

How can I read then inner cursor? When I execute the query of principal cursor I have this result :

COL_1        COL_2   CURSOR
-------      ------  -------
11619187    604441  (CURSOR)
11619187    604434  (CURSOR)
11619187    604439  (CURSOR)

When I double-click (on TOAD) on the CURSOR data, this popups a new window showing data :

CLO_N   COL_M
-----   ---------------
009511  1g0-M-TPT_BNM
009511  1g0-Q--213_BNM
009511  C--F_R*P_C:R:CT
009511  1g0-M--16_BNM
009511  P*RF_DI-TR
009511  *CR
009511  BOWLING
009511  C--F_BNM
009511  C:PIT:L
009511  N:V
009511  INF--IGN

Answer 1

Cursor variables to the rescue! Check out the code example below. That should give you enough to get you going (relies on the standard HR schema, which you can intall from here: https://github.com/oracle/db-sample-schemas).

CREATE OR REPLACE PROCEDURE curexpr_test (
   p_locid NUMBER
)
IS
   TYPE refcursor IS REF CURSOR;

/* Notes on CURSOR expression:

   1. The query returns only 2 columns, but the second column is
      a cursor that lets us traverse a set of related information.

   2. Queries in CURSOR expression that find no rows do NOT raise
      NO_DATA_FOUND.
*/
   CURSOR all_in_one_cur
   IS
      SELECT l.city
            ,CURSOR (SELECT d.NAME
                           ,CURSOR (SELECT e.last_name
                                      FROM employee e
                                     WHERE e.department_id =
                                                            d.department_id)
                                                                  AS ename
                       FROM department d
                      WHERE l.location_id = d.loc_id
                    ) AS dname
        FROM locations l
       WHERE l.location_id = p_locid;

   department_cur   refcursor;
   employee_cur     refcursor;
   v_city           locations.city%TYPE;
   v_dname          department.NAME%TYPE;
   v_ename          employee.last_name%TYPE;
BEGIN
   OPEN all_in_one_cur;

   LOOP
      FETCH all_in_one_cur
       INTO v_city
           ,department_cur;

      EXIT WHEN all_in_one_cur%NOTFOUND;

      -- Now I can loop through deartments and I do NOT need to
      -- explicitly open that cursor. Oracle did it for me.
      LOOP
         FETCH department_cur
          INTO v_dname
              ,employee_cur;

         EXIT WHEN department_cur%NOTFOUND;

         -- Now I can loop through employee for that department.
         -- Again, I do need to open the cursor explicitly.
         LOOP
            FETCH employee_cur
             INTO v_ename;

            EXIT WHEN employee_cur%NOTFOUND;
            DBMS_OUTPUT.put_line (v_city || ' ' || v_dname || ' '
                                  || v_ename
                                 );
         END LOOP;

         CLOSE employee_cur;  
      END LOOP;

      CLOSE department_cur;   
   END LOOP;

   CLOSE all_in_one_cur;
END;
/