Common Causes of ORA-01001: invalid cursor
When working with the Oracle precompilers, you may receive an ORA-01001 error.
The Error Messages and Codes Manual states that ORA-01001 is caused by an
invalid cursor. The same manual contains some information which may be of help,
but its references to Oracle Call Interface solutions may be confusing
to the programmer who is using the precompilers rather than the Oracle Call
Interfaces. Sources of ORA-01001 vary from a simple typing error to memory
mismanagement by the program. The latter source is difficult to isolate and
requires analysis of the program.
This article lists the known causes of ORA-01001 and potential solutions/work-
arounds specifically for users of the Oracle precompilers.
1) OPENING A CURSOR AFTER RECONNECT.
Here is a scenario:
1) Exec sql connect....
2) Terminate the connection from the communication manager
3) Exec sql open cursor.... -> that will result in ORA-06607
(expected)
4) Exec sql rollback work release
5) Exec sql connect (as in step 1)
6) Exec sql open cursor as in step 3)..... returns the ORA-01001
ANALYSIS: Note that if the connection is terminated after the open cursor
statement ORA-01001 is not returned and program works as
expected.
BUGS: bug number 54998 (generic)
bug number 103870 (IBM RS6000)
bug number 72838 (Siemens-Nixdorf MX300/MX500)
bug number 145525 (Motorola 68K Unix)
WHEN REPORTED: version 6.0.30.3 of RDBMS and version 1.3.18.1 of PRO*.
WORKAROUND: Force the use of a new cursor by coding the select twice and
execute the second select only if you have tried the first
select while not connected. This workaround is not really a
solution since it only addresses the case when the network
goes down once.
WHEN FIXED: In version 6.0.36 of the RDBMS (verified). The fix is in
libsql.a that was released with versions 1.4 of the
precompilers. Some patches were released for versions of the
precompilers prior to 1.4 (for more information see the
corresponding port specific bug listed above).
2) INADEQUATE ERROR CHECKING.
Here is a scenario:
1) Exec sql declare cursor...
2) Exec sql open cursor...
3) No error checking is included in the program.
4) Exec sql fetch cur into ......causes ORA-01001.
ANALYSIS: What has occured in the above scenario is that the open statement
has failed due to reason one or another. Had the program included
error handling such as an 'EXEC SQL WHENEVER SQLERROR' statement
before the open statement, ORA-01001 would not have occurred.
Since the open is not successful, the fetch fails. The sqlcode
following the open cursor statement would contain the actual
error code.
SOLUTION: Handle the error after the statement that caused it. This can be
done by having an 'EXEC SQL WHENEVER SQLERROR' statement in
effect, or by explictly checking for errors yourself (by checking
sqlca.sqlcode, for example).
3) ERROR IN OPEN CURSOR STATEMENT.
Here is a scenario:
1) Declare cursor (explicit static cursor)
2) Open cursor using :var1 :var2 ... resulted in ORA-01001
ANALYSIS: User error. USING clause is valid only with dynamic SQL methods.
It is invalid with static SQL.
4) INADEQUATE ERROR CHECKING REVISITED.
Here is a scenario:
1) Exec sql connect...
2) Other exec sql statements follow .....gave ORA-01001.
ANALYSIS: User gave the wrong username and password in the exec sql connect
statement. Thus connection was made to the wrong account and
objects referenced were non existent. This coupled with lack of
error handling resulted in ORA-01001.
In general, any error in a SQL statement that goes unhandled
will eventually cause an ORA-01001 at a later time.
5) PREPARING A CURSOR IN A FUNCTION AND EXECUTING IT IN ANOTHER.
Here is a scenario:
1) Prepare cursor in a function
2) A function is called that opens the cursor and does a fetch.
3) The function in step 2 is called again to do another fetch
and ORA-01001 results.
ANALYSIS: What has occured in the above scenario is that between calls to
the function that does the fetch the cursor cache is being
overwritten and the prepared statement is being lost. This is
most likely due to a user error since customers have been
able run the above scenario successfully.
WORKAROUND: Possible workaround in this situation is to put the prepare,
open, and fetch statements in one function. If the error still
persists, contact Oracle Customer Support.
6) PREPARING A CURSOR C1 THEN EXECUTING OTHER SQL STATEMENTS THEN EXECUTING
C1.
Here is a scenario:
1) Preparing a cursor (possibly opening it and even doing a fetch)
2) Executing several SQL statements unrelated to the cursor in 1.
3) Fetching from the cursor in 1 results in ORA-01001.
ANALYSIS: What has occured here is that the parsed statement is lost,
because it has been aged out of the cursor cache.
WORKAROUND: Increasing the value of the precompiler option MAXOPENCURSORS
option (which will increase the size of the cache) and/or
specifying HOLD_CURSOR=yes and RELEASE_CURSOR=no in the program
right before executing all the statements in 2. This will
keep the cursor from being aged out of the cache. For more
information on cursor cache management, see Appendix E of the
Programmer's Guide to the Oracle Precompilers (version 1.4 or
1.5).
7) OPENING A CURSOR AFTER CLOSING IT WHEN MAXOPENCURSORS HAS BEEN EXCEEDED
Here is a scenario:
1) Declare and prepare a number of cursors that exceed MAXOPENCURSORS
2) Open the first cursor
3) Close the first cursor
4) Reopen the first cursor...this gives ORA-01001
ANALYSIS: This behavior is incorrect. The precompiler options HOLD_CURSOR
and RELEASE_CURSOR have no effect. Note this only occured when
the number of cursors in 1 exceeded MAXOPENCURSORS and with
dynamic SQL.
BUGS: bug number 69876 (generic)
WHEN REPORTED: version 6.0.33 of RDBMS and version 1.3.20 of PRO*.
WORKAROUND: Increase the value of the MAXOPENCURSORS option.
WHEN FIXED: In version 6.0.36 of the RDBMS and version 1.4.9 of PRO*.
8) The following is applicable to cases when XA/TUXEDO are used.
CURSOR NOT FREED EVEN IF 'RELEASE_CURSOR=YES' OPTION IS SPECIFIED
Here is a scenario (1):
1) Exec sql update emp set sal = :s where current of cursor1
2) Repeating the above statement gives ORA-01001.
Here is another scenario (2):
1) EXEC SQL DELETE.......a row that does not exist gives ORA-01403.
2) Somehow a row has been inserted in the table that meets the where
condition in the delete statement.
3) Repeat step 1) gives ORA-01001.
Here is a general scenario(3):
1) EXEC SQL statement that uses implicit cursor fails.
2) Repeating the execution of the same statement causes ORA-01001.
ANALYSIS: The above behavior is incorrect. The reason ORA-01001 is being
issued is that the cursor is not closed when the first statement
in all the above scenarios fails (no data found...etc).
BUGS: bugs number 138610 & 137893 cover scenario (1) (generic)
bug number 147228 covers scenario (2) (generic)
bug number 140724 covers scenario (3) (generic)
WHEN REPORTED: In version 7.0.11 of the RDBMS and version 1.5.5 of PRO*.
WORKAROUND: None.
WHEN FIXED: In version 7.0.12 of the RDBMS (verified). Fixed in
SQLLIB 1.4.12/1.5.7, released with PRO* 1.4.11/1.5.6.
If you encounter ORA-01001 and none of the above causes apply, here are a
few things you should look at and try before calling Oracle Customer Support:
1) Increase the value of the MAXOPENCURSORS precompiler option.
2) Check that the value of the OPEN_CURSORS parameter in the Oracle
initialization file INIT.ORA is greater than the value of MAXOPENCURSORS.
The default value for OPEN_CURSORS is 50.
3) Any open cursors that will not be executed again should be closed. This
could be an alternate option for increasing the value of MAXOPENCURSORS.
4) In version 1.3 of the precompilers increase the value of the AREASIZE
precompiler option. Previous cases indicate values from 32K to 512K could
be used.
5) Increase the size of the CONTEXT_AREA parameter in the INIT.ORA file.
6) To assist in debugging a trace file might be beneficial. To generate
one, add the following statements at the beginning of your program:
EXEC SQL alter session
set events '10233 trace name context forever, level 10';
EXEC SQL alter session
set events '1001 trace name processstate 10';
The first will generate some useful statistics on memory usage and the
second statement will give the process state when the invalid cursor
error occured.
The above article listed the common causes of ORA-01001. There are other
possible causes of this error, depending on your application. Thus if all the
above does not help contact Oracle Customer Support. Please note that in some
cases the ORA-01001 error occurs sporadically and is difficult to pinpoint,
especially when it is due to memory mismanagement.
