Home » SQL & PL/SQL » SQL & PL/SQL » Invalid cursor while inserting rows (Oracle 10.2G)
Invalid cursor while inserting rows [message #393129] Fri, 20 March 2009 07:14 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,

I have some code that is executing a lot of dynamic statements - however I'm dumping all the error back traces and stack traces for exceptions. It seems that there is an exception thrown at the line, where INSERT is present:
currentTimestamp := CURRENT_TIMESTAMP;
INSERT INTO LogTable(EventTimestamp,Details,CallStack,ErrorStack,ErrorBackTrace)
VALUES
(currentTimestamp, details, CallStack, ErrStack, ErrBackTrace);

ORA-01001: invalid cursor

What is more interesting: this line works for most of the time (the insert is called about 5000 times while only about 50 times exception is thrown). The code is executed from a procedure that is using PRAGMA AUTONOMOUS_TRANSACTION;

Another problem that I have observed is:
ORA-01804: failure to initialize timezone information
This is thrown during the execution of code like bellow:
FUNCTION execute (c IN PLS_INTEGER) RETURN PLS_INTEGER IS
   RESULT PLS_INTEGER;
   t0 TIMESTAMP;
   t1 TIMESTAMP;
BEGIN -- EXECUTE
   t0 := CURRENT_TIMESTAMP;
   RESULT := DBMS_SQL.EXECUTE(c);
   t1 := CURRENT_TIMESTAMP;
   do_some_stuff;
   RETURN RESULT;
END execute;


Any ideas what might be wrong?
Re: Invalid cursor while inserting rows [message #393136 is a reply to message #393129] Fri, 20 March 2009 08:17 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Post your cursor statement.
Re: Invalid cursor while inserting rows [message #393137 is a reply to message #393136] Fri, 20 March 2009 08:19 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
gentlebabu wrote on Fri, 20 March 2009 14:17

Post your cursor statement.

Sorry if that was not clear - the exception occurs on INSERT INTO (...) which is executed as SQL code inside PL/SQL code. This is NOT executed as dynamic code.
I am handling all the exceptions that could occur during my calls to DBMS_SQL.* or EXECUTE IMMEDIATE.
Exact example of error dump:
ORA-01001: invalid cursor
ORA-06512: at "MY_PACKAGE.MY_PROC", line 123
ORA-06512: at "MY_PACKAGE.MY_PROC", line 435
ORA-06512: at line 75

MY_PROC line 123 is:
INSERT


[Updated on: Fri, 20 March 2009 08:25]

Report message to a moderator

Re: Invalid cursor while inserting rows [message #393139 is a reply to message #393137] Fri, 20 March 2009 08:35 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

I don't know your exact script. As per error message.

Quote:
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.


Also refer

http://www.techonthenet.com/oracle/errors/ora06512.php

Babu

[Updated on: Fri, 20 March 2009 08:36]

Report message to a moderator

Re: Invalid cursor while inserting rows [message #393140 is a reply to message #393129] Fri, 20 March 2009 08:44 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks,
As I have mentioned above - I got 2 exceptions in fact.
One is INVALID_CURSOR - and I had similar problem before (I had a cursor leak and reached the MAXOPENCURSORS limit). However the limit is set to 400 and I'm reaching up to 120 cursors opened per session at once (usually this is bellow 100).
I will update this post if I can find the cause. I suspect that this second exception might be related. According to the "action" for this exception: "Please contact Oracle Customer Support.".
Previous Topic: Finding the time diffrence
Next Topic: EXECUTE IMMEDIATE trouble in stored procedures
Goto Forum:
  


Current Time: Sun Dec 04 02:13:20 CST 2016

Total time taken to generate the page: 0.15063 seconds