Strange error "Ora-01001 Invalid cursor" in the procedure

Question

Yesterday, I was working on a strange error in our production procedure. Execution failed

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

After some digging, I found that the problem was in the routine that opened this cursor. I fixed the error by adding the sys_refcursor output parameter to the routine. To clarify the situation, consider the 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 fine, but on test_fail I get a message

ORA-01001: Invalid cursor

I can not find information about this. Can someone explain why this code is not working?

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 seems to be bug 7174888, or at least something closely related to it. The description for this is "ORA-6504 raised when sys_refcursor was passed to another procedure", but I can do this if I changed test_fail to make a selection:

  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 error report fixes the selection and closing problem.

Initialize the ref cursor to a value other than NULL at the highest level at which it will be accessible

  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

Interest Ask! Just wanted to add a few things.

For me, the real problem depends on IS_OPEN to determine if the cursor is valid or not. Oracle can throw INVALID_CURSOR for many reasons, and it is possible that the "open" cursor is invalid. It seems reasonable to assume that the open cursor must be valid (and therefore we can extract from it or perform other operations, for example, a simple close), but this is not necessary.

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

INVALID_CURSOR may be raised for other reasons (for example, go beyond the maximum open cursors or sometimes open the cursor and wait a while before trying to use it).

All that said, there is no test "ISVALID", which I know. Imo's best approach is to open, select, and close cursors within the same program or subroutine. Creating a procedure whose responsibility is to simply OPEN, the cursor is a bit strange for me (but I'm sure there is some reason), and can cause difficulties to explain the problems (like this one). If you must have another program that opens the cursor for you, you might want to enter code that extracts and ultimately closes the cursor in an anonymous block and catches the INVALID_CURSOR exception.

Just my wanderings; -)