[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cursor invalid ora - 1001 error

Posted on 2006-05-14
5
Medium Priority
?
6,423 Views
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
0
Comment
Question by:srinims
  • 2
  • 2
5 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 16680547
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. 10.2.0.1 if
you can achieve this.
0
 
LVL 3

Author Comment

by:srinims
ID: 16682283
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
0
 
LVL 6

Accepted Solution

by:
DLyall earned 1500 total points
ID: 16682808
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.
 
 
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.

0
 
LVL 6

Expert Comment

by:DLyall
ID: 16697625
:srinims

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

Author Comment

by:srinims
ID: 16698021
hi,
 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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month20 days, 10 hours left to enroll

868 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