Strange error "Ora-01001 Invalid cursor" in procedure

Question

Yesterday I worked on a strange bug in our production procedure. Execution failed on statement

if v_cursor%isopen then
  close v_cursor; -- here was an error 
end if;

After some digging into I discovered that problem was in subprogram that opened this cursor. I fixed bug by adding output parameter sys_refcursor in subprogram. To clarify situation consider following test code:

procedure nested_test(test  number,
                        p_cur out sys_refcursor)
  is  
    procedure nested_procedure_fail is
    begin      
      open p_cur for
        select 1, 2, 3, 4
          from dual
         where 1 = 0;
    end;

    procedure nested_procedure_success(p_cur out sys_refcursor) is
    begin
      open p_cur for
        select 1, 2, 3, 4
          from dual
         where 1 = 0;
    end;

  begin
    if test = 1 then
      nested_procedure_fail;
    else
      if test = 2 then
        nested_procedure_success(p_cur => p_cur);
      else
        open p_cur for
          select 6, 7, 8, 9
            from dual
           where 1 = 1;
      end if;
    end if;
  end;

  procedure test_fail is
    v_cur sys_refcursor;
  begin
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      close v_cur;
    end if;
  end;

  procedure test_success is
    v_cur sys_refcursor;
  begin
    nested_test(test => 2, p_cur => v_cur);
    if v_cur%isopen then
      close v_cur;
    end if;
  end;

If I try to run test_success everything is OK, but on test_fail I receive a message

ORA-01001: Invalid cursor

I cannot find any information about this. Can anyone explain why this code fails?

Oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Answer 1

This appears to be bug 7174888, or at least something closely related to it. The description for that is 'ORA-6504 raised when sys_refcursor passed to another procedure', but I can make that happen too if I change test_fail to do a fetch:

  procedure test_fail is
    v_cur sys_refcursor;
    a number;
    b number;
    c number;
    d number;
  begin
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      fetch v_cur into a,b,c,d;
      close v_cur;
    end if;
  end;

I get ORA-06504: PL/SQL: Return types of Result Set variables or query do not match.

The workaround in the bug report cures both the fetch and close problem.

Initialize the ref cursor to a non-NULL value at the highest level at which it will be accessed

  begin
    /* Dummy open to avoid bug 7174888 */
    open v_cur for 'select 1 from dual';
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      fetch v_cur into a,b,c,d;
      close v_cur;
    end if;
  end;

Answer 2

An interesting question! Just wanted to add a few things.

To me, the real issue is in depending on IS_OPEN to determine if a cursor is valid or not. Oracle can throw INVALID_CURSOR for many reasons, and it is possible to have an "open" cursor that is not valid. Seems reasonable to assume that an open cursor must be valid (and we can therefore fetch from it or do other operations, like a simple close), but this isn't necessarily the case.

For example, you cannot use cursor variables in remote procedure calls (via dblinks). This same example, even using Alex's workaround, would fail if the open was called on 1 db instance and the fetch on another (if nested_test, any version, was defined on db_A and then called from db_B). The test for ISOPEN, however, would still return TRUE, but then trying to use the cursor (fetch) would fail.

INVALID_CURSOR can be raised for other reasons (like going beyond the max open cursors, or sometimes opening a cursor and waiting a while before trying to use it).

All that said, there is no "ISVALID" test that I know of. The best approach imo is to open, fetch and close cursors within the same program or subprogram. Creating a procedure whos responsibility is to just OPEN a cursor is a bit strange to me (but I'm sure there was some reason), and can cause hard to explain issues (like this one). If you must have another program open a cursor for you, then you might want to enclose the code that fetches and eventually closes the cursor in an anonymous block and catch the INVALID_CURSOR exception.

Just my ramblings ;-)