Exception handling in PL/SQL in Oracle

Exception handling in PL/SQL in Oracle

Exception handling in PL/SQL allows you to gracefully handle errors and exceptions that occur during the execution of your code. It enables you to detect errors, take corrective actions, and continue processing or gracefully terminate the program. Here's how exception handling works in PL/SQL:

1. Types of Exceptions:

   • Predefined Exceptions: These are exceptions that are predefined by Oracle. Examples include NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.
   • User-Defined Exceptions: These are exceptions that you define explicitly in your PL/SQL code using the DECLARE section.

2. Syntax:

   • The basic structure of PL/SQL exception handling includes the BEGIN, EXCEPTION, and END blocks.
   • The BEGIN block contains the main body of your code.
   • The EXCEPTION block contains exception handlers that specify actions to take when specific exceptions occur.
   • The END block marks the end of the exception handling block.

3. Handling Exceptions:

   • You can handle exceptions using EXCEPTION blocks, which specify what actions to take when certain exceptions occur.
   • Exception handlers can include logging errors, displaying error messages, performing rollback operations, or raising other exceptions.
   • You can have multiple EXCEPTION blocks to handle different types of exceptions.

4. RAISE Statement:

   • The RAISE statement is used to explicitly raise exceptions within your PL/SQL code.
   • You can raise predefined exceptions or user-defined exceptions based on specific conditions or error conditions encountered in your code.

5. Exception Propagation:

   • If an exception is not handled within a block, it propagates to the enclosing block or to the caller of the PL/SQL block.
   • If the exception is not handled at all, the program terminates, and an error message is displayed.

6. Exception Handlers:

   • Exception handlers can include various actions such as logging the error, rolling back transactions, raising additional exceptions, or providing customized error messages.
   • You can use DBMS_OUTPUT.PUT_LINE to display error messages or log them into a table.

Here's a simple example of PL/SQL exception handling:

BEGIN -- Main body of your code INSERT INTO your_table (column1) VALUES ('value1'); -- Exception block EXCEPTION -- Handler for specific exception WHEN DUP_VAL_ON_INDEX THEN -- Handle duplicate value error DBMS_OUTPUT.PUT_LINE('Duplicate value encountered.'); -- Rollback transaction ROLLBACK; -- Handler for all other exceptions WHEN OTHERS THEN -- Log error DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); -- Rollback transaction ROLLBACK; END;

In this example, if a duplicate value error (DUP_VAL_ON_INDEX) occurs during the INSERT statement, the exception handler will display a message and rollback the transaction.

For all other exceptions (WHEN OTHERS), it will log the error and rollback the transaction