Re: Problem with ORA-01001: Invalid Cursor Message

Logically it should not.

However, if for some reason the connection was already in use (maybe some re-entrant code, recursion, or a bug) ROLLBACK RELEASE will not just rollback, it will also release all resources used by the connection and disconnect.

Therefore, try to handle the error raised by the rollback if not connected

(
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. EXEC SQL AT DBCONN1 ROLLBACK WORK RELEASE END-EXEC. EXEC SQL AT DBCONN2 ROLLBACK WORK RELEASE END-EXEC. EXEC SQL WHENEVER SQLERROR <do whatever you want to handle other errors> END-EXEC.
)

Anup Jalan
Rave Technologies
www.rave-tech.com

ctaliercio_at_yahoo.com (Chris) wrote in message news:<8cc07162.0301220710.158d4a72_at_posting.google.com>...
> Anup, I appreciate the advice, however I'm a bit confused. Why would
> an attempt to rollback before you are connected effect the number of
> cursors open at any given time? There have been no changes to any
> Oracle parameters, no changes in the number of users, no change in the
> processes the users are running except the one I mentioned. Why would
> attempting to ROLLBACK before you CONNECT to the database (which
> produces an error anyway) effect the programs in any way? As I stated
> previously - if I leave these ROLLBACK statements in place, I am going
> to have problem connecting to Oracle 9i (I've already tested it). But
> taking them out is hurting my current production environment. Thanks
> in advance.
>
> Chris
>
>
> anup.jalan_at_rave-tech.com (Anup Jalan) wrote in message news:<532afd4.0301220345.5c81f9a7_at_posting.google.com>...
> > 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 Thu Jan 23 2003 - 12:24:27 CET