Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01001: invalid cursor (Oracle 11g (11.1.0), XP)
ORA-01001: invalid cursor [message #539041] Wed, 11 January 2012 01:10 Go to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
We have a package procedure called from SSRS , which returns a REFCURSOR to the SSRS.


Sometimes it throws the error. The user Refresh the SSRS session and again run the query , the error does not happen

Following is the error number

ORA-01001: invalid cursor

We have not used any static cursor , so this is not happening because we have opened a cursor and not closed it.

The Cursor used is REF CURSOR and the output parameter is declared as

---------------
p_ref_cursor OUT SYS_REFCURSOR
----------


The Procedure returns the Select query in the REF CURSOR.

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539042 is a reply to message #539041] Wed, 11 January 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First remove all WHEN OTHERS in your code and you will know where the error comes from.

Regards
Michel

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539043 is a reply to message #539042] Wed, 11 January 2012 01:42 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
I have a WHEN OTHERS exception, which return a dummy value in the REF CURSOR. If it is goes to WHEN OTHERS block it should have returned that instead of throwing this error right ?

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539045 is a reply to message #539043] Wed, 11 January 2012 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't understand what you wrote.
Just comment the WHEN OTHERS, it is a bug in your code, read the link to know why.

Regards
Michel

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539046 is a reply to message #539043] Wed, 11 January 2012 01:56 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
What is "dummy value"? Probably not correctly opened cursor, as FETCHing from it (non-open cursor) causes the exception you complain.
www.orafaq.com/wiki/ORA-01001
Anyway, you should really remove WHEN OTHERS clause as Michel suggested. With this question you proved, that your exception "handling" is not better than the standard Oracle one - you just hid the error (so you cannot spot it at all) and propagated it further (hence ORA-01001). Would it not be better to let the exception (whatever it caused it) raise instead? After investigating it, you may be able to eliminate its cause.

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539076 is a reply to message #539046] Wed, 11 January 2012 04:22 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
What I meant was if any such error happened it should have captured in WHEN OTHERS exception and returned the following in Ref cursor. It did'nt doo that instead it throwed ORA-01001 error.

And not happens always. We were not able to reproduce it the second time. So my question is this any bug?


---------------------------------------------
EXCEPTION WHEN OTHERS THEN 
 INSERT INTO ACTIVITY_LOG VALUES ( V_ERR_MSG);
 OPEN P_REF_CURSOR FOR SELECT 'ERROR  - SEE ACTIVITY LOG' FROM DUAL;
END PROC;

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539086 is a reply to message #539076] Wed, 11 January 2012 04:40 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just let the error propogate normally?

Report message to a moderator

Re: ORA-01001: invalid cursor [message #539088 is a reply to message #539076] Wed, 11 January 2012 04:48 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
As you did not post where exactly that error was raised, it is hard to suggest anything more. So, I will only concentrate on those pieces of code, which you posted.

The INSERT statement is most probably wrong - it will be rolled back if anything wrong happens in that transaction.
It seems you like scratching your left ear with your right hand (log errors somewhere and dig it afterward), but I consider it to be less comfortable than simple letting the exception raise.

Good luck in your attempts with finding out errors which you hid beforehand.

Report message to a moderator

Previous Topic: Connect By not working with nested queries in from clause.
Next Topic: Select statement is blocking a delete statement (merged 2)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Apr 05 18:44:27 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.