Weak cursor in Oracle

This programme shows you we may retrieve the results from a query. It works with a cursor that contains another cursor. The example below is a cursor that retrieves data from employee table, along with data from a cursor that is linked to the employee table. Doing so, we may add data to the employee data. Here some data on dependants are added. Likewise, we may add data on projects that employees worked on etc.

As two cursors are used, we must start two loops: one loop that retrieves data on employee and a second loop that retrieves data on the dependants.

We do not know upfront how the data are returned. Therefore a so-called weak cursor is used. This cursor definition does not return the type data that are returned.

The code to undertake is:

create or replace PROCEDURE FamilyWeakCursor AS 
  TYPE DepType IS REF CURSOR;
  DepCursor DepType;
  EmpLName    employee.LName%TYPE;
  EmpFName    employee.FName%TYPE;
  EmpRELATIONSHIP    dependent.RELATIONSHIP%TYPE;
  EmpDEPENDENT_NAME    dependent.DEPENDENT_NAME%TYPE;
  CURSOR EmpWork IS
    SELECT LName, FName, CURSOR(SELECT RELATIONSHIP, DEPENDENT_NAME
                                 FROM dependent d
                                  WHERE d.essn = e.ssn) AS DepCursor

    FROM employee e
    ORDER BY LName;   

BEGIN
  OPEN EmpWork;
    LOOP
      FETCH EmpWork INTO EmpLName, EmpFName, DepCursor;
      EXIT WHEN EmpWork%NOTFOUND;
      dbms_output.put_line ('Processing here for ' || EmpLname||' ,' || EmpFname );      
      LOOP    
        FETCH DepCursor INTO EmpRELATIONSHIP, EmpDEPENDENT_NAME;      
        EXIT WHEN DepCursor%NOTFOUND;     
        dbms_output.put_line ('Processing here for ' || EmpLname||' ,' || EmpFname || ' and for dependant ' || EmpDEPENDENT_NAME||' (' ||EmpRELATIONSHIP || ')');        
      END LOOP;     
    END LOOP;
  CLOSE EmpWork;
END;
/