Example 8-21 Aliasing from Assigning Cursor Variables to Same Work Area

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  c1 EmpCurTyp;
  c2 EmpCurTyp;
  PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS
    emp_rec employees%ROWTYPE;
  BEGIN
    OPEN emp_cv1 FOR SELECT * FROM employees;
    emp_cv2 := emp_cv1;
    FETCH emp_cv1 INTO emp_rec;  -- fetches first row
    FETCH emp_cv1 INTO emp_rec;  -- fetches second row
    FETCH emp_cv2 INTO emp_rec;  -- fetches third row
    CLOSE emp_cv1;
    DBMS_OUTPUT.put_line('The following raises an invalid cursor');
--  FETCH emp_cv2 INTO emp_rec; raises invalid cursor when get_emp_data is called
  END;
BEGIN
  get_emp_data(c1, c2);
END;
/

Example 8-21 Aliasing from Assigning Cursor Variables to Same Work Area

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  c1 EmpCurTyp;
  c2 EmpCurTyp;
  PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS
    emp_rec employees%ROWTYPE;
  BEGIN
    OPEN emp_cv1 FOR SELECT * FROM employees;
    emp_cv2 := emp_cv1;
    FETCH emp_cv1 INTO emp_rec;  -- fetches first row
    FETCH emp_cv1 INTO emp_rec;  -- fetches second row
    FETCH emp_cv2 INTO emp_rec;  -- fetches third row
    CLOSE emp_cv1;
    DBMS_OUTPUT.put_line('The following raises an invalid cursor');
--  FETCH emp_cv2 INTO emp_rec; raises invalid cursor when get_emp_data is called
  END;
BEGIN
  get_emp_data(c1, c2);
END;
/