Oracle PLSQL exception can be termed as a condition that terminates the execution of plsql block.
Oracle PLSQL gives us the functionality to handle the exception that can crop up in any Oracle PLSQL block and this functionality is called as Exception Handling.
Using plsql exception handling we can avoid the code to end abruptly.
Whenever an exception occurs, a message that details the cause of the plsql exception can be received.
A PLSQL Exception Message typically consists of 3 parts:
- Type of PLSQL Exception
- Error Code
- Error Message
By handling plsql exceptions in a block we can make sure that the execution of that plsql block is not ended abruptly.
Oracle syntax for plsql exception handling in a plsql block is:
DECLARE
—Declaration Section
BEGIN
–Executable Statements
EXCEPTION
WHEN exception_1 THEN
–Exception_1 handling statements
WHEN exception_2 THEN
–Exception_2 handling statements
.
.
WHEN exception_N THEN
—-Exception_N handling statements
WHEN OTHERS THEN
–Generic exception handling statements
END;
Whenever any exception is raised or encountered, Oracle PLSQL searches for the appropriate plsql exception handler in the exception handling section.
For instance, in the above syntax of plsql exception, if exception_1 is encountered then oracle plsql will execute the exception_1 handling statements to handle the exception and if exception_2 is encountered then oracle plsql will execute the exception_2 handling statements to handle the exception.
As it’s not possible to determine all the exceptions that may happen at runtime, the “WHEN OTHERS” part of the plsql exception handling will be used to manage or handle any plsql exception that has not been handled explicitly.
Only one plsql exception can be raised in any plsql block which means that once an exception is encountered or raised then the execution of that plsql block will be terminated after the plsql exception is handled.
If we have a nested PLSQL block as shown below:
DECLARE
–Declaration Section
BEGIN
DECLARE
–Declaration Section
BEGIN
–Execution code
EXCEPTION
–Exception section
END;
EXCEPTION
–Exception Section
END;
Now, if the plsql exception is encountered or raised in the inner plsql block then the exception should be handled in the plsql exception handling section of the inner plsql block else the control moves to the exception handling section of the next upper plsql block.
If none of the plsql block handles the plsql exception then the execution of the plsql block will be terminated unexpectedly.
Oracle PLSQL exception can be of the following types:
Please click on the links above to know each type of exception is detail.