Cursor Variable

Cursor variables can be assigned to another cursor variable, once any of the cursor is closed, automatically the cursor area becomes unavailable and the fetch operation leads to invalid cursor error.

With SYS_REFCUROSR:

DECLARE
    c1     SYS_REFCURSOR;
    c2     SYS_REFCURSOR;
    v_name VARCHAR2(20);
BEGIN
    OPEN c1 FOR
      SELECT table_name
      FROM   user_tables;

    c2 := c1;

    CLOSE c1;

    FETCH c2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor

Below, we will close cursor C2

DECLARE
    c1     SYS_REFCURSOR;
    c2     SYS_REFCURSOR;
    v_name VARCHAR2(20);
BEGIN
    OPEN c1 FOR
      SELECT table_name
      FROM   user_tables;

    c2 := c1;

    CLOSE c2;

    FETCH c2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor


-- The same applies the cursor variables defined using type.

DECLARE
    TYPE type_ref IS REF CURSOR;
    TYPE type_ref2 IS REF CURSOR;
    rc1    TYPE_REF;
    rc2    TYPE_REF2;
    v_name VARCHAR2(20);
BEGIN
    OPEN rc1 FOR
      SELECT table_name
      FROM   user_tables;

    rc2 := rc1;

    CLOSE rc2;

    FETCH rc2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor

-- Below, we will close rc1

DECLARE
    TYPE type_ref IS REF CURSOR;
    TYPE type_ref2 IS REF CURSOR;
    rc1    TYPE_REF;
    rc2    TYPE_REF2;
    v_name VARCHAR2(20);
BEGIN
    OPEN rc1 FOR
      SELECT table_name
      FROM   user_tables;

    rc2 := rc1;

    CLOSE rc1;

    FETCH rc2 INTO v_name;

    dbms_output.Put_line(v_name);
END;

/

ORA-01001: invalid cursor