ORA-01001: invalid cursor

Recently came across some code that was causing an ORA-010001: invalid cursor error but it seemed like it should work even though it is not really required. Here is an example of the offending code.

FUNCTION test_loop
         RETURN BOOLEAN IS
         CURSOR c_au IS
                     SELECT user_id FROM all_users;
BEGIN
          FOR v_au_rec IN c_au LOOP
                     IF c_au%ISOPEN THEN
                                CLOSE c_au;
                     END IF;
                     RETURN TRUE;
          END LOOP;
          RETURN FALSE;
EXCEPTION
          WHEN OTHERS THEN
                     RAISE;
END;

The example above doesn't do anything sensible but shows the issue. Inside the cursor loop it check if the cursor is open, if it is then close it and the return from the function which will exit the cursor. While it might seem like a good idea to close the cursor if you are going to exit the loop with a return from the function it is not needed. Oracle is smart enough to close the cursor on the way out anyway so there is no need to do it explicitly.

But I thought this should still work without an error but it doesn't.

Running this gives the ORA-010001: invalid cursor error but now where I expected.

It is actually raised as part of the RETURN TRUE;

I assume it is because the cursor is already closed by the explicit CLOSE statement so when it does the implicit close as part of the RETURN FALSE from the loop it fails as the cursor is already gone.

The simple solution is to let Oracle handle the close itself and remove the code to check and close the cursor.