Re: Problem with ORA-01001: Invalid Cursor Message

Seems the problem is too many open cursors.

Cursors are used even if you do not explicitly "declare" a cursor - all statements not using an explicit cursor, including SELECT statements, use implicit cursors. Possibly, the programs are not releasing the cursors they obtain, thus causing random problems depending on the number of users / programs running and the number of open cursors.

Try increasing the value of the MAXOPENCURSORS option before precompiling. You can also experiment with the HOLD_CURSOR and RELEASE_CURSOR options. (HOLD_CURSOR = NO, RELEASE_CURSOR = YES, but these may have a performance implication)

It may also be necessary to increase the value of the OPEN_CURSORS in init.ora.

You can also try rewriting the code to "handle" the ORA-01001 error, so that the execution goes ahead even if the ROLLBACK fails.

Anup Jalan
Rave Technologies
www.rave-tech.com

ctaliercio_at_yahoo.com (Chris) wrote in message news:<8cc07162.0301211322.62acdf61_at_posting.google.com>...
> I have a question for the group:
>
> We are connecting to an Oracle 8i (8.1.6) database via Pro*Cobol
> (1.8). Is there any logical reason that you should have to ROLLBACK in
> a declared database before you actually CONNECT to it? I've inherited
> some code that I am trying to make sense out of, and it essentially
> breaks down as follows:
>
> EXEC SQL DECLARE DBCONN1 DATABASE END-EXEC.
> EXEC SQL DECLARE DBCONN2 DATABASE END-EXEC.
>
> EXEC SQL AT DBCONN1 ROLLBACK WORK RELEASE END-EXEC.
> EXEC SQL AT DBCONN2 ROLLBACK WORK RELEASE END-EXEC.
>
> .
> . (farther down the line)
> .
>
> EXEC SQL CONNECT :USERINFO AT DBCONN1 USING :STR END-EXEC.
> EXEC SQL CONNECT :USERINFO AT DBCONN2 USING :STR END-EXEC.
>
> Since both of the ROLLBACK statements produce ORA-01012 (not
> connected) errors, I commented them out. I was still able to connect
> to the database without any problems, however when I rolled it into
> production it was another story.
>
> We have approximately 300 users (each obviously using 2 connections to
> the DB). As soon as this new code hit production, we started getting
> random ORA-01001 (invalid cursor) errors. The odd part is that the
> statement generating the error had nothing to do with a cursor - it
> was a SELECT ... FOR UPDATE NOWAIT statement. The only thing tying
> these errors together was that they always occurred on the same table.
> As soon as I "un"commented the ROLLBACK statements and put that code
> back into production, the errors stopped.
>
> My large concern is this: we are migrating to Oracle 9i this weekend.
> I cannot connect to the database if those ROLLBACK statements are left
> in the code, however I cannot have the random invalid cursor problem
> coming up either.
>
> Does anyone have a logical explanation as to why this may be
> happening? I'd appreciate any help I can get on this one.
>
> Thanks,
> Chris
Received on Wed Jan 22 2003 - 12:45:06 CET