Exceptions in the Declarative Section !

In pl/sql programming , we all know that exceptions occuring in the executable section of the code , Here i'll discuss about the exception occuring in the declarative section.

Here is the sample code that'll raise an ORA-06502. PL/SQL: numeric or value error.[Copy Paste the code and try executing it].

DECLARE
local_variable NUMBER(3) := 9999;
BEGIN
DBMS_OUTPUT.PUT_LINE('Im in Executable Section');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Exception Section');
END;

The above given code snippet will raise an error when you try executing it. This is because, in the declarative part the precision is specified as NUMBER(3) for local variable and initialized to a constant of large size. This causes an numeric or value error ,which in turn looks for a exception section for handling the exception raised.

If the exception is raised in the executable part of the code, it will be handled by exception section in the block, in this scenario exception occured in the declarative part, and since there is no exception section to handle it , control comes out of the block of code and the exceptin becomes an unhandled exception and you will receive the error message in the console.

Such kind of exception can also be handled in a simple way. [Copy paste the code and try executing it].

BEGIN -- Outer Executable Block

DECLARE
local_variable NUMBER(3) := 9999;
BEGIN
DBMS_OUTPUT.PUT_LINE('Im in Executable Section');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Inner Exception Section');
END;

EXCEPTION -- Outer Exeception Block
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Outer Exception Section');
END;

In this case, the exception is raised in the declarative section. Compiler searches for the exception block to handle the exception , it'll not find anything in the same block, but here there is another exception block to handle the exception raised. Now this part of outer exception section will be executed and the exception raised in the declarative section will be handled properly.