Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Already Open (Oracle 10g)
Cursor Already Open [message #338231] Mon, 04 August 2008 04:49 Go to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Good Morning,
i'm getting the error ORA-06511 but i don't know what is the cursor opened.
Suppose i've this situation
Procedure PRC_Main (param ...) is
[declaration]
Begin
...

-- Call the external procedures
pkg_first.first_procedure (....);
pkg_second.second_procedure (....);
-- and so on

Exception when others then
-- display error code and error message
End PRC_Main;

The question: anyone knows if is it possible to get the cursor name and close it. Or anynone knows if is it possible to close all cursors? Or do i have to manage the error inside each package?
Thanks in advance.

Regards,
Giulio
Re: Cursor Already Open [message #338235 is a reply to message #338231] Mon, 04 August 2008 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ No, it is not possible.
2/ Disconnect
3/ You have to correctly code. This error should not happen in a correct program, you should know when you open, when use and when you close your cursor.

Regards
Michel
Re: Cursor Already Open [message #338241 is a reply to message #338231] Mon, 04 August 2008 05:24 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, you might consider removing
Exception
  when others then                        --> why? why?!?
  -- display error code and error message
Oracle will raise an exception naturally; using the WHEN OTHERS is meaningless in a case you provided.
Re: Cursor Already Open [message #338261 is a reply to message #338231] Mon, 04 August 2008 06:03 Go to previous messageGo to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi Michel,
Hi LittleFoot,

the question is because i don't know what is the cursor that reamins opened after an unexpected error.
The code inside the packages called from the procedure is written using this schema:
Open cursor-n;
Loop
fetch cursor-n into variables;
exit when cursor-n%notfound;
statements
...

End loop;
Close cursor-n;

if something happens (for example, error due to data) in the statements inside the loop, i do not manage the error and the cursors remains opened.

What i want to know is just the name of the cursor, to try to understand where the code doesn't manage the errors.
Thank you for your interesting.

Ciao,
Giulio
Re: Cursor Already Open [message #338266 is a reply to message #338261] Mon, 04 August 2008 06:35 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
anyone knows if is it possible to get the cursor name and close it


I think that Michel already have given you the answer.

Why don't you close the cursor within the exception clause ?
IF CURSORNAME%ISOPEN THEN
   CLOSE CURSORNAME;
END IF;


You might want to read about Cursor For Loops
Re: Cursor Already Open [message #338282 is a reply to message #338266] Mon, 04 August 2008 07:49 Go to previous messageGo to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi,
yes i know that Michel is quite right.
I even knew the instruction that you suggest to me.
Want i would like is the name of "Cursorname". But i've understood that is not possible in pl/sql, so i'll review the code inside the packages.
Thanks to everyone.

Ciao,
Giulio
Re: Cursor Already Open [message #338292 is a reply to message #338282] Mon, 04 August 2008 08:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
While you are at it, read up on 21st century cursor loops.
Don't open-fetch-close manually, use for <record> in <cursor> loops.
They will handle open and close for you. Then get rid of all your unnecessary (in fact: error-prone or even erroneous) exception handlers and you have hardened your code by at least 50% with very very little effort.
Re: Cursor Already Open [message #338439 is a reply to message #338292] Tue, 05 August 2008 02:08 Go to previous message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Ok Frank, thanks for your post.

Ciao,
Giulio
Previous Topic: help code required
Next Topic: Confusion Regarding Rownum
Goto Forum:
  


Current Time: Wed Dec 07 12:56:01 CST 2016

Total time taken to generate the page: 0.07636 seconds