Exception Handling in PL/SQL

What is Exception Handling in PL/SQL?

In PL/SQL, an error that happens while the program runs is an exception.

Programmers can capture these occurrences using exception blocks in PL/SQL, and the error condition is handled appropriately.

Two types of exceptions exist:

1. System-defined Exceptions
2. User-defined Exceptions

Syntax for exception handling

Below is the syntax for exception handling in pl sql

Note: When other keyword should only be used at the conclusion of the exception handling block because when the control executes the when others, the block will be closed, and no subsequent exception handling code will be executed.

The syntax represents the basic structure of an exception-handling block in PL/SQL. It is used to handle exceptions that may occur during the execution of PL/SQL code. Here's an explanation of each part:

1. DECLARE: This section is used to declare variables, cursors, and other PL/SQL constructs used within the block's executable section.
2. BEGIN: This is the beginning of the executable section. It contains the actual PL/SQL statements and commands you want to execute. If an exception occurs during the execution of these statements, the control will transfer to the appropriate exception handler.
3. EXCEPTION: This keyword marks the beginning of the exception handling section. It is followed by one or more exception handlers.
4. WHEN exception1 THEN statement1: This is an exception handler. It specifies a particular exception (exception1) and the action to be taken (statement1) if that exception is raised during the execution of the executable section.
5. WHEN exception2 THEN statement2: Similarly, you can have multiple exception handlers to handle different exceptions with specific actions.
6. [WHEN others THEN]: This is an optional part. If none of the specific exception handlers match the raised exception, the control will transfer to the WHEN OTHERS handler (if it is defined). The WHEN OTHERS handler can handle generic or default exceptions.

This structure allows you to define how your PL/SQL code should respond to different exceptions. You can have specific handlers for specific exceptions and a generic handler for any other unhandled exceptions. This helps in gracefully handling errors and exceptions in your PL/SQL programs.

Example of exception handling

In this example:

1. We declare two variables, num1 and num2, and initialize num2 with a value of 0.
2. Inside the BEGIN block, we attempt to divide num1 by num2, which will cause a "division by zero" exception.
3. We then have an exception handling section:
4. WHEN ZERO_DIVIDE THEN is a specific exception handler for the "division by zero" exception.
5. WHEN OTHERS THEN is a generic handler for any other unhandled exceptions.
6. If a "division by zero" exception occurs, the first handler will be triggered, printing an error message. If any other unexpected exception occurs, the generic handler will be triggered, printing a different error message.
7. Using exception handling like this allows you to gracefully handle errors and exceptions in your PL/SQL code, ensuring that your application doesn't terminate unexpectedly due to errors.

Raising Exceptions

When an internal database issue occurs, the server automatically raises exceptions, but programmers can also directly raise exceptions using the command RAISE. The simple syntax for raising an exception is as follows:

The previously mentioned syntax can be used to raise either an Oracle standard exception or a user-defined exception. The following section will provide an example of raising a user-defined exception. The Oracle standard exceptions can also be raised in a similar manner.

User-defined Exceptions

You can create your exceptions in PL/SQL based on the requirements of your program. The function DBMS_STANDARD.RAISE_APPLICATION_ERROR or a RAISE statement must be used to specify and raise a user-defined exception expressly.

User-defined exceptions in PL/SQL allow you to define and handle custom exception conditions in your code. Here's an example of how to define and use a user-defined exception:

In this example:

1. We declare a user-defined exception named insufficient_balance.
2. We initialize account_balance to 1000 and withdrawal_amount to 2000.
3. Inside the BEGIN block, we check if there's enough balance for the withdrawal. If not, we raise the insufficient_balance exception.
4. We have an exception handling section:
   • WHEN insufficient_balance THEN is a specific handler for our user-defined exception. It prints an error message.
   • WHEN OTHERS THEN is a generic handler for any other unhandled exceptions.
5. If the withdrawal amount exceeds the account balance, the insufficient_balance exception is raised, and the corresponding error message is printed. Otherwise, the withdrawal is processed, and the new balance is displayed.

User-defined exceptions allow you to create custom error conditions specific to your application's logic and provide a way to handle them gracefully.

Pre-defined Exceptions

Many pre-defined exceptions are available in PL/SQL and are used if a program breaks a database rule. For instance, when a SELECT INTO operation returns no rows, the predefined exception NO_DATA_FOUND is raised. A handful of the significant pre-defined exceptions are listed in the following table: