PL/SQL Cursor variable - REF CURSOR

In previous post we discussed about implicit & explicit cursor and also understood how it is different from cursor variable.Lets reiterate it again, both explicit cursor and implicit cursor are associated with a specific SQL DML statement(they are tied to specific queries), whereas cursor variable can refer to multiple DML statements (off course SELECT statement) throughout session. Read The main agenda of this post is to understand the declaration and uses of cursor variable.

Cursor variable -- a reference to a cursor object or query in the database.

Cursor variable is a pointer/reference to SQL work area and its value is the address of the that work area.Since it has address of SQL work area, it can accommodate multiple select statements(as shown here) returning different result sets(rows from different table).

Syntax and life cycle of cursor variable:-

Cursor variable is of type REF CURSOR/ SYS_REFCURSOR. General syntax of cursor type & variable declaration and life cycle of cursor variable is as  is as follows: first create type of cursor then create variable of that type termed as cursor variable followed by FETCH and close.
Note:- Once cursor is closed, if try to fetch rows it will throws error ORA-01001: invalid cursor.

/* Create the cursor type.- Return type is optional */
TYPE cur_type IS REF CURSOR [RETURN table%ROWTYPE];

/* Declare a cursor variable of that type. */
cur_var cur_type;

/* Open the cursor variable, associating with it a SQL statement. */
OPEN cur_type FOR <SELECT statement>;

/* Fetch from the cursor variable. */
FETCH cur_var INTO <table_rec_type/use%ROWTYPE>;

/* Close the cursor object associated with variable. */
CLOSE cur_var;

Cursor variable and Cursor object :- 

Cursor variable dynamically binds with select statement when OPEN FOR syntax is executed
Note:- When a cursor variable is created in PL/SQL program unit, it is just a reference variable. Actual cursor object (the result set identified by the cursor SQL statement) is created when OPEN FOR is executed for select query and assigned to a cursor variable. See the following diagram to understand difference between cursor variable and cursor object:-

Cursor variable and cursor Object creation in PL/SQL- cursor variable is reference to cursor Object

Lets write a sample program to understand how do we create concrete cursor type and  its variable to fetch records:-

DECLARE 
  /*Cursor type declaration*/
  TYPE v_empcur_type IS REF CURSOR;-- RETURN EMPLOYEES%ROWTYPE;
  /*cursor variable declaration - no select statement binding here*/
  cur_var v_empcur_type;
  v_deptId   NUMBER(5) := 90;
  v_locId NUMBER(5) := 1800;
  v_emp_row EMPLOYEES%ROWTYPE;
  v_dept_row DEPARTMENTS%ROWTYPE;
BEGIN
  /*First binding:- OPEN refcursor for a select statement */
  OPEN cur_var FOR select * from employees where
                DEPARTMENT_ID = v_deptId;
  dbms_output.put_line('--First binding output--');
  dbms_output.put_line('FIRST_NAME' || '        '|| 'EMAIL');
  dbms_output.put_line('-------------     ---------------');
LOOP
    FETCH cur_var INTO v_emp_row;
      EXIT WHEN cur_var%NOTFOUND;
    dbms_output.put_line(v_emp_row.FIRST_NAME || '         
              ' || v_emp_row.EMAIL||'@devinline.com');
  END LOOP;
/*Second binding :- OPEN refcursor for another select statement */
  OPEN cur_var FOR select * from DEPARTMENTS where
                LOCATION_ID = v_locId;
  dbms_output.put_line('--Second binding output --');
  dbms_output.put_line('DEPARTMNET_NAME' || '        '|| 'MANAGER_ID');
  dbms_output.put_line('-------------     ---------------');
  LOOP
      FETCH cur_var INTO v_dept_row;
        EXIT WHEN cur_var%NOTFOUND;
      dbms_output.put_line(v_dept_row.DEPARTMENT_NAME || '         
                ' || v_dept_row.MANAGER_ID);
    END LOOP;
  CLOSE cur_var;
END;

==========Sample output========================
--First binding output--
FIRST_NAME        EMAIL
-------------     ---------------
Steven      
              SKING@devinline.com
Neena      
              NKOCHHAR@devinline.com
Lex      
              LDEHAAN@devinline.com
NIKHIL      
              NIKSR@devinline.com

--Second binding output --
DEPARTMNET_NAME        MANAGER_ID
-------------       ---------------
Marketing       201

Purchasing       114
========================================
In above program, cursor type is created followed by a cursor variable and two variable one of type employee row and another of department row type is created. In begin block, cursor is opened and associated it with a select statement. Fetch cursor in Loop and display name and email. Again same cursor is bind with another select statement and again fetch and display rows from it. Above program unit uses same cursor variable to refer two select statement one after another, it is most important and noticeable feature of cursor variable.

Question:- Why error "ORA-01001: invalid cursor " is thrown, if try to fetch rows after cursor has been closed ?
Answer:- Always remember, cursor does nor contain value, it is just a pointer to result set. Once we execute close command, pointer to result set is removed so , cursor is just a variable now and no cursor object is attached to it, so it throws error.

Note:-

1. Cursor variable also has same set of attributes as explicit cursor : ISOOPEN, FOUND, NOTFOUND, ROWCOUNT. Refer this table for more detail about cursor attributes.
2. Cursor variable can be of two types: Strong type and Weak type.If RETURN clause is added while creating cursor type it is termed as Strong type, however if is missing then that type is called Weak type.Read Difference between strong type and weak type. in more detail.
3. We can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. If either of cursor variable participating in assignment operation are weak then compile time check cannot be achieved. And if there is a type mismatch or both cursor variable are not structurally same then error is reported at runtime.
   If both cursor variable is strong type, compile time check is done and error is reported if both are structurally not same.

   --Both cursor variable are strong type,compiler time check for structure compitablity
   Type emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
   Type dept_cur_type IS REF CURSOR RETURN departments%ROWTYPE;
   emp_cur_var emp_cur_type;
   dept_cur_var dept_cur_type;
   .....
   /*compile time error for below assignment: PLS-00382: expression is of wrong type*/
   emp_cur_var := dept_cur_var;
   

   Assignment operation is allowed but cursor variable equality, inequality, or nullity check using comparison operators is not allowed. 
4. A cursor object(a query/result-set) may be referenced by two cursor variable.
5. NULL cannot be assigned to a cursor variable.
6. Cursor variables cannot be declared in a package since they do not have a persistent state.

Read also:
1. Difference between strong cursor type and weak cursor type
2. Difference between SYS_REFCURSOR and REF CURSOR
3. Difference between explicit cursor and cursor variable
4. REF CURSOR, SQL and PL/SQL interaction
Previous: PL/SQL Implicit & Explicit Cursor Next: PL/SQL program unit- Procedure and Function