OraFAQ Forum: SQL & PL/SQL » how to close open cursor when exception

Home » SQL & PL/SQL » SQL & PL/SQL » how to close open cursor when exception (oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to close open cursor when exception [message #657612]

Tue, 15 November 2016 19:40

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

BlackSwan
Messages: 26766
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

Michel Cadot
Messages: 68641
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

cookiemonster
Messages: 13920
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

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Correct, and we could add implicit cursors. Smile

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

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

Michel Cadot
Messages: 68641
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

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

cookiemonster
Messages: 13920
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

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

cookiemonster
Messages: 13920
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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:56:44 CDT 2024