Home » SQL & PL/SQL » SQL & PL/SQL » how to close open cursor when exception (oracle 11)
how to close open cursor when exception [message #657612] Tue, 15 November 2016 19:40 Go to next message
yk107
Messages: 15
Registered: October 2015
Junior Member
Hello
I am trying to find answer.
PL/SQL script has open cursors. Should cursors to be closed if exception occurs and script exit.
Thanks

Report message to a moderator

Re: how to close open cursor when exception [message #657613 is a reply to message #657612] Tue, 15 November 2016 21:13 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
yk107 wrote on Tue, 15 November 2016 17:40
Hello
I am trying to find answer.
PL/SQL script has open cursors. Should cursors to be closed if exception occurs and script exit.
Thanks
post proof about cursor existence after script exits.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: how to close open cursor when exception [message #657618 is a reply to message #657612] Wed, 16 November 2016 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, you must have an exception handler to close opened cursors otherwise then remain opened until the session ends.
This is a known security hole when you share session pool as following users could see data of a previous ones.

Report message to a moderator

Re: how to close open cursor when exception [message #657621 is a reply to message #657618] Wed, 16 November 2016 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends on whether you are talking about ref cursors or explicit cursors.

Report message to a moderator

Re: how to close open cursor when exception [message #657622 is a reply to message #657621] Wed, 16 November 2016 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Correct, and we could add implicit cursors. Smile

Report message to a moderator

Re: how to close open cursor when exception [message #657624 is a reply to message #657622] Wed, 16 November 2016 05:53 Go to previous messageGo to next message
yk107
Messages: 15
Registered: October 2015
Junior Member
thank you for help

Report message to a moderator

Re: how to close open cursor when exception [message #657625 is a reply to message #657624] Wed, 16 November 2016 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So what is your conclusion?

Report message to a moderator

Re: how to close open cursor when exception [message #657628 is a reply to message #657625] Wed, 16 November 2016 07:57 Go to previous messageGo to next message
yk107
Messages: 15
Registered: October 2015
Junior Member
I will close cursor inside exception , before close I will check if it is open

Thanks

Report message to a moderator

Re: how to close open cursor when exception [message #657629 is a reply to message #657628] Wed, 16 November 2016 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you talking about ref cursors or explicit cursors?
If it's explicit then you don't need to do that.

Report message to a moderator

Re: how to close open cursor when exception [message #657631 is a reply to message #657629] Wed, 16 November 2016 08:55 Go to previous messageGo to next message
yk107
Messages: 15
Registered: October 2015
Junior Member
explicit cursors

Report message to a moderator

Re: how to close open cursor when exception [message #657632 is a reply to message #657631] Wed, 16 November 2016 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then you don't need to. Oracle closes them automatically when they go out of scope.

Report message to a moderator

Re: how to close open cursor when exception [message #657636 is a reply to message #657632] Wed, 16 November 2016 12:57 Go to previous message
yk107
Messages: 15
Registered: October 2015
Junior Member
Thank you !
I will change script back !

Report message to a moderator

Previous Topic: Calling procedure from trigger
Next Topic: Oracle 11g Ntile function on top of Dense_rank
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Nov 14 07:55:22 CST 2018
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.