From Oracle FAQ
Jump to: navigation, search

ORA-01001: Invalid cursor

What causes this error?[edit]

An ORA-01001 error occurs when a PL/SQL or a 3GL program attempts to use a cursor which has not yet been opened, or which has already been closed.

How to fix it[edit]

This is 100% a program logic problem (unless an Oracle bug, such as bug 6823287). Mostly, you will have either forgotten to code an open statement before using a cursor, or have not noticed that the cursor has been closed and have tried to continue using it. The following checklist may help identify the fault:

  • Make sure you have an OPEN statement prior to using any explicit cursors.
  • Make sure that you do not have a misplaced CLOSE statement.
  • If you need to do a sequence of OPEN...CLOSE...OPEN...CLOSE (perhaps because you need to reset bind variables or to commit updates inside a loop) check your logic flow and make sure there are no fetches between the 1st CLOSE and the 2nd OPEN.
  • If you have nested loops, check that a condition in an inner loop is not being missed which allows control to pass unexpectedly to a CLOSE in an outer loop
  • Remember that a PL/SQL FOR loop does an implicit OPEN and CLOSE. If you take a routine with a FOR loop and change it to a WHILE loop you must remember to code the OPEN and CLOSE.