Help - Search - Members - Calendar
Full Version: Periodic ORA-01001 Error
Oracle DBA Forums > Oracle > Oracle Forum
D Liske
I have inherited an Oracle Database (10.2) that is periodically firing off an ORA-01001: Invalid Cursor from inside an insert trigger. On an insert, the following function is used to create a simple string indicating the presence of data in a few other tables....This function is part of a package. This error will occur for a few minutes for a user --- then magically go away. It's not happening very often, once every few months.

My question is: What are the possible causes of an ORA-01001 for the following cursor? From what I've read, usually you get this error when you try to read an unopened cursor. I've had no luck reproducing the problem.

CODE
function GetPeripheralStatus(GuidID IN initiative.guid_identifier%type)
return initiative.status_flag%type is

sStatusString initiative.status_flag%type;

cursor PeripheralIndexRead is -- Peripheral Index tables
select 0 as Peripheral_Index --
from dual
where exists (select 1
from initiative i
where i.parent_guid_identifier = GuidID)
union
select 1 as Peripheral_Index
from dual
where exists (select 1
from attachment a
where a.owner_guid = GuidID)
union
select ct.PERIPHERAL_INDEX as Peripheral_Index
from COMMENT_TYPE ct
where ct.GUID_IDENTIFIER in
(select distinct (rc.COMMENT_TYPE_GUID_IDENTIFIER)
from RECORD_COMMENT rc
where OWNER_GUID = GuidID)
union
select lt.PERIPHERAL_INDEX as Peripheral_Index
from LIST_TYPE lt
where lt.GUID_IDENTIFIER in
(select distinct (li.LIST_TYPE_GUID_IDENTIFIER)
from LIST_ITEM li
inner join LIST_ITEM_OWNER_XREF lx on li.GUID_IDENTIFIER =
lx.GUID_IDENTIFIER
where lx.OWNER_GUID = GuidID);


begin

if GuidID is null
then
return null;
end if;

sStatusString := rpad('0',255,'0');

for recPeripheralIndexRead in PeripheralIndexRead -- loop through Peripheral Index records

loop
sStatusString := peak7.stuff(sStatusString,
recPeripheralIndexRead.Peripheral_Index + 1,
1,
'1');

end loop; -- recOpenRead in OpenRead
return sStatusString;
end GetPeripheralStatus;


Thanks
Dean
HAL9000
"What are the possible causes of an ORA-01001"

http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm

The ORA-01001 error occurs when:

a host language program call gave an invalid cursor for use
the value of the MAXOPENCURSORS option in the precompiler command was too small
You can fix the ORA-01001 error by:

Check your problematic call statement for any issues
Specify a correct LDA area or open the cursor as required
As a last resort, increase the MAXOPENCURSORS option value before precompiling
As a note, the ORA-01001 error does not exist in Oracle 10g, according to the Oracle documentation.


--------------------------------------------------------------------------------

OraFaq.com has the following to say about the ORA-01001 error:

This is 100% a program logic problem. You have either forgotten to code an open statement before using a cursor, or have not noticed that the cursor has been closed and have tried to continue using it. The following checklist may help identify the fault:

Make sure you have an OPEN statement prior to using any explicit cursors.

Make sure that you do not have a misplaced CLOSE statement.

If you need to do a sequence of OPEN...CLOSE...OPEN...CLOSE (perhaps because you need to reset bind variables or to commit updates inside a loop) check your logic flow and make sure there are no fetches between the 1st CLOSE and the 2nd OPEN.

If you have nested loops, check that a condition in an inner loop is not being missed which allows control to pass unexpectedly to a CLOSE in an outer loop

Remember that a PL/SQL FOR loop does an implicit OPEN and CLOSE. If you take a routine with a FOR loop and change it to a WHILE loop you must remember to code the OPEN and CLOSE.
burleson
Hi Dean,

The sporadic nature of the ORA-01001 error is sometimes due to RAM memory issues ( not enough, bugs).


CODE
ORA-01001 invalid cursor

Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.
  
Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.
aussie_dba
Dean,

This from MetaLink:

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.
D Liske
I've read through those articles before, and I'm not really sure if any of the items apply -- but thank you anyway. Most posts I've read say that this is purely program logic -- so that's why I posted the function. We don't use a precompiler, these items are all just PLSQL. I had the customer check the cursors --- there were 58 open cursors at the last occurance of the error and this is what they send back:

CODE
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     500
session_cached_cursors               integer     200


They checked the oracle alert logs and nothing was in there and no other trace files were generated.

This problem does seem isolated to a single customer, and based on the randomish nature of it, it makes sense that it might be RAM related. But the error ALWAYS is reported in the exact same line of code, so that makes me wonder about that.

Thanks for your responses,
Dean
D Liske
When you say "bugs" are you talking about Oracle bugs, or bugs in our code that are using up too much RAM? Should I suggest to the customer that they increase the RAM on their server?

[quote name='burleson' date='Jul 23 2008, 11:04 AM' post='39561']
Hi Dean,

The sporadic nature of the ORA-01001 error is sometimes due to RAM memory issues ( not enough, bugs).
[code]
ORA-01001 invalid cursor
burleson
Hi Dean,

>> When you say "bugs" are you talking about Oracle bugs, or bugs in our code that are using up too much RAM?

As I recall, there area few bugs related to memory leaks, but they may not apply to you.

I would check, and open an SR. This is not a trivial issue.

*******************************************************************

>> Should I suggest to the customer that they increase the RAM on their server?

Maybe. Have you run a one-minute duration STATSPACK report during the periods when this happens?

That would be a good idea. Once you get the report, paste it into http://www.statspackanalyzer.com and report the findings.

It's possible that this happens when the RAM gets fully utilized, and more RAM would be a workaround, if it's indeed a bug.

*******************************************************************

>> Most posts I've read say that this is purely program logic

If that were true, it would abort every time.
D Liske
I searched the Metalink bugs and found a strangely familiar bug where IF a cursor fails once for whatever reason, it will continue to fail with a "ORA-01001" until the session is closed.

My application is an ASP.NET one, with a connection pool. My current theory is that occasionally one of the connections may get into this state. This might explain the periodic nature of this error, and why it occasionally will affect more than one user. It also might explain why it goes away --- the connections have a "lifetime" so eventually they are released and the problem goes away.

There are a few holes in this theory -- OK I'll admit, I'm kind of grasping at this point so don't laugh, but do provide feedback.

1. I believe the connections are reset each time they are used. Shouldn't this reset behave much like a connection close? If so, shouldn't this failure appear once and then go away (It doesn't)?

2. This bug does require an initial failure for this cursor. I'm not sure what that would be, nor has it been reported.

A workaround for this bug is to set the SESSION_CACHED_CURSORS to 0. This database isn't real busy, do you think it's worth suggesting to my customer?

I'm going to do some more testing to try to recreate this behavior --- see if it actually does happen in my test environment the way it does at my customer's. I'll post my results. Any insight or suggestions anyone might wish to post would be appreciated.

Thanks
Dean
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.