User Defined Exception Handler

Suppose a programmer creates a user defined exception handler Invalid_Number in declare block with type EXCEPTION and raise this exception in begin block. Then how will you access the user defined exception and same system defined exception (i.e Inavlid_Number)

Subashpanda
Profile Answers by Subashpanda Questions by Subashpanda
Feb 7th, 2011
3
4932

Questions by Subashpanda answers by Subashpanda

PL/SQL

Answer

Showing Answers 1 - 3 of 3 Answers

promisinganuj
Profile Answers by promisinganuj

Feb 10th, 2011

The system defined exceptions in Oracle are tied to Oracle error codes. For this case, system defined error INVALID_NUMBER is tied with error code -01722.

Now, when a user defines the same error "INVALID_NUMBER" using EXCEPTION, the SQLCODE is overridden with new value of 1 and SQLERRM as "User-Defined Exception". Later, when this error is raised, the system defined error code can no longer be accessed as it has been changed to 1. You can tie any other error code/message with it by using PRAGMA EXCEPTION_INIT and raise_application_error.

So, as soon as you have declared an exception using EXCEPTION, the system defined error code can no longer be accessed within the scope of that plsql block.