PL/SQL Exception is a run-time error caused due to many things that occur in a database application. It can be due to wrong data stored in variables, incorrect data accessed from database or inability to fetch the required data.
Exceptions are different from syntax errors and they are encountered only when a PL/SQL block is executed. Exceptions are not detected when the PL/SQL block is compiled. Syntax errors occur when you write statements violating syntactic rules of PL/SQL.
You will not be able to execute a block with syntax errors. PL/SQL compiler will list down such errors along with the line and column number of PL/SQL statements.
If your code has exceptions, it will compile without listing them. You will be able to execute the block, but as soon as an exception is encountered the block will terminate mentioning the error code and error description.
PL/SQL Exception Handling- Where?
To inhibit exceptions from halting an application abruptly, they are handled in EXCEPTION section of PL/SQL block. It’s discussed in the basic structure of a PL/SQL block. In the PL/SQL Exception section you can write code that what you want your PL/SQL block to do when an exception is raised. To do this you must be able to visualize the possible runtime errors.
For example you are told to write a PL/SQL block to accept a student’s ID to calculate his score and grade. What do you want the code to do if the student ID is incorrect? What do you want it to do if the student has missed his exams? These are some situations that may generate exceptions in your PL/SQL block.
What is an Exception Handler?
If you are able to predict the possible situations that may stop your PL/SQL block to execute, then these are the exceptions. For these you must add the relevant Exception Handler. It is the set of statements to be executed on encountering an exception. Exception handlers are added in the EXCEPTION Section of the PL/SQL code.
How many Exception Handlers can be there in a Block?
You can include as many as Exception handlers in your PL/SQL Block as you need. Every exception in your code must have a separate exception handler. Basically application users must be given a user friendly message that conveys them what went wrong in the application or the data they entered. The application must exits gracefully without confusing the user with complex error codes or decryptions.
What happens when an Exception is encountered?
An exception is always encountered or raised in the executable section. When an exception is raised the control is passed to the EXCEPTION section. If the exception handler is defined, its handler statements are executed. If the exception handler is missing, Oracle takes care of it and displays system defined messages to the user.
The exception-handling section is placed after the executable section of the block. The exception block handles only the first exception. If there are other exceptions in block they are never raised. Occurrence of first exception switches control to Exception Section and after that the code block is terminated.
Syntax of PL/SQL Exception Handler – Exception Section
WHEN EXCEPTION_NAME THEN
Advantages of Exception Handling
It is always a good practice to add Exception Section and Exception Handlers for all possible exceptions. In addition to clean code you have following benefits.
- When an exception section is added in a PL/SQL block, the program executes to completion. It does not terminate prematurely.
- Adding an exception section isolates all exception-handling routines. A single section for all runtime error makes it is easy to understand and maintain the application.
Exceptions implement event-driven processing of errors. It lets a programmer understand the type of actions of a user can do that can lead to runtime errors.