Cursor invalid ora - 1001 error

Posted on 2006-05-14
Last Modified: 2010-10-05
we have upgradaed the database from oracle 8i to oracle 10g. after this some store procedures failing(ora 1001, cluster invalid). I'm new to oracle and pro C. pls help me to odd out the cause.
The same thing was worked finely with the old database.
Currently we are checking the queries and procedures with evaluation version of oracle 10g.

srini ms
Question by:srinims
    LVL 47

    Expert Comment

    How have you upgraded from 8i to 10g?
    The best way is using Export/Import utilities.

    Be aware that 10g Release 1 has many bugs.
    Try to use 10g Release 2 Upgrade 2, i.e. if
    you can achieve this.
    LVL 3

    Author Comment

    actually. I haven't used any import/export utilities.
    I don't need to update my database.
    I have to update my tools, which is using the database. thru pro C Code.
    The pro*C code is giving some problem while doing some update operation.
    currently I'm planning to re-precompile the proC code with Oracle-10g proC compiler.
    I don't think so it'll correct my problem
    LVL 6

    Accepted Solution

    Here is an extract from metalink about possible causes and workarounds. Something in there may help.

    This bulletin describes several common causes of the 'ORA-01001: Invalid  
    Cursor' error.  It includes descriptions of several bugs and their  
    workarounds, as well as some of the more common coding mistakes that  
    can lead to this error.

                      COMCommon 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.
           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
            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
        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 of RDBMS and version 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).
           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).
           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.
           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.
           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.
           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
        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.
           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.

    LVL 6

    Expert Comment


    Have you made any progress with this?  Do you need any more assistance or can you assign points and close this question?
    LVL 3

    Author Comment

     sorry for the delay. I have corrected the problem by recompiling all proc file with the oracle 10g proc compiler.
    the problem is due to oracle 10g uses the bigger structure for the cursor handler. instead of int, they have replace long in lot of places.
    Any how, I'll give the points for your effort. Thanks for the answer

    srini ms

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now