Trapping system exceptions

[kalyson]

Hello,
I see that Oracle raises its own exceptions for things like this:

EXECUTE IMMEDIATE l_sql_stmt INTO l_temp_string;

when there is no data found from the query. I don't want to handle all these exceptions in one WHEN DATA_NOT_FOUND exception handler. I think it is incredible that these exceptions can not be differentiated! In Java, such errors can be captured inside a try/catch block. But not here, it seems....

I can do some nasty kludge, like setting a global variable to tell me which function I was in when the error occurred, but does anyone else have an idea?

Thanks,
Kris

[kalyson]

Well, I think I see the answer myself. You can use enclosing local blocks and have a no_data_found exception handler there that raises another exception that throws it to the main block's exception handler.

[debasisdas]

Yes you need to handle those in local blocks because the execution of exception is from inner block to outer block.

[amitpatel66]

Hello,
I see that Oracle raises its own exceptions for things like this:

EXECUTE IMMEDIATE l_sql_stmt INTO l_temp_string;

when there is no data found from the query. I don't want to handle all these exceptions in one WHEN DATA_NOT_FOUND exception handler. I think it is incredible that these exceptions can not be differentiated! In Java, such errors can be captured inside a try/catch block. But not here, it seems....

I can do some nasty kludge, like setting a global variable to tell me which function I was in when the error occurred, but does anyone else have an idea?

Thanks,
Kris

I do not understand what you mean by "I think it is incredible that these exceptions can not be differentiated??". You can differentiate the exceptions in oracle.