Home » SQL & PL/SQL » SQL & PL/SQL » Does exception no_data_found requires for this? (Oracle 9i/10g)
Does exception no_data_found requires for this? [message #339289] Thu, 07 August 2008 04:03 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
...
OPEN RC1 FOR
        SELECT  in_dest1,in_dest2
        FROM DUAL;
        exception
            when no_data_found then
                raise;
            when others then
                raise;
        end;   




Does no_data_found exception requires??

Thanks in advance,
Oli

[Updated on: Thu, 07 August 2008 04:04]

Report message to a moderator

Re: Does exception no_data_found requires for this? [message #339294 is a reply to message #339289] Thu, 07 August 2008 04:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you don't fetch anything, the no_data_found will never be raised.
Quite easy to test: add a "where 1 = 2".


Quote:
when others
then
raise;


Don't do that.
Do NOT catch the WHEN OTHERS, unless you are planning to handle the exception.
If you leave it like this, the stack-trace will be rewritten. The error will be raised from this line, and nothing will be retrievable with respect to the original location of the error.

[Updated on: Thu, 07 August 2008 04:19]

Report message to a moderator

Re: Does exception no_data_found requires for this? [message #339297 is a reply to message #339294] Thu, 07 August 2008 04:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Frank wrote on Thu, 07 August 2008 04:17
Since you don't fetch anything, the no_data_found will never be raised.
Quite easy to test: add a "where 1 = 2".


Quote:
when others
then
raise;


Don't do that.
Do NOT catch the WHEN OTHERS, unless you are planning to handle the exception.
If you leave it like this, the stack-trace will be rewritten. The error will be raised from this line, and nothing will be retrievable with respect to the original location of the error.


Thanks for the update.To my understanding, while using a cursor
NO_DATA_FOUND is useless as exception will never be raised for this. Need your suggesion if I am wrong here...

Regards,
Oli

Re: Does exception no_data_found requires for this? [message #339306 is a reply to message #339297] Thu, 07 August 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
NO_DATA_FOUND is useless as exception will never be raised for this.

Unless there is no row in dual.

Regards
Michel
Re: Does exception no_data_found requires for this? [message #339315 is a reply to message #339306] Thu, 07 August 2008 04:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 07 August 2008 11:42
Quote:
NO_DATA_FOUND is useless as exception will never be raised for this.

Unless there is no row in dual.

Regards
Michel


Please explain?
That could only be caused if some internal bit uses dual to create the ref cursor.

Also, having no row in dual is NOT something that needs to be mentioned as a possibility. We could add such a comment to about 90% of the threads Smile
Re: Does exception no_data_found requires for this? [message #339324 is a reply to message #339315] Thu, 07 August 2008 05:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

Please explain?
That could only be caused if some internal bit uses dual to create the ref cursor.

Also, having no row in dual is NOT something that needs to be mentioned as a possibility. We could add such a comment to about 90% of the threads Smile



Quote:

That could only be caused if some internal bit uses dual to create the ref cursor


-- I didnt get you here.Any articles?

Quote:

To my knowledge for cursor we dont have to use exception.


Does exception need to be raised if we are using Cursor?

Regards,
Oli
Re: Does exception no_data_found requires for this? [message #339332 is a reply to message #339315] Thu, 07 August 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I like to be complete in the answers and some (stup...) guy can create a dual table in his schema. Smile

Regards
Michel

[Updated on: Thu, 07 August 2008 05:21]

Report message to a moderator

Re: Does exception no_data_found requires for this? [message #339334 is a reply to message #339324] Thu, 07 August 2008 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NO_DATA_FOUND can't be raised because of the cursor loop but it can because of what inside the cursor loop.

Regards
Michel
Re: Does exception no_data_found requires for this? [message #339375 is a reply to message #339334] Thu, 07 August 2008 06:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So there is no need to catch NO_DATA_FOUND at the indicated place.
Re: Does exception no_data_found requires for this? [message #339404 is a reply to message #339375] Thu, 07 August 2008 07:41 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thank you very much!
Previous Topic: Query
Next Topic: Correlated Subqueries
Goto Forum:
  


Current Time: Sat Dec 03 18:21:07 CST 2016

Total time taken to generate the page: 0.14941 seconds