Exception Handling in PLSQL

In Oracle SQL, exception handling is an essential feature of PL/SQL (Procedural Language/SQL) that allows developers to handle runtime errors and exceptions in a structured way. The sequence of exception handling blocks in Oracle SQL typically follows these steps:

1. Declare Section

This section is optional and is used to declare variables and exceptions. You can declare user-defined exceptions here.

1. Begin Section

The main execution section of the PL/SQL block where the SQL statements and procedural code are written. This is where you write the logic that may raise exceptions.

1. Exception Section

This section handles exceptions that occur in the Begin section. You can define specific handlers for different types of exceptions or a general handler for all exceptions.

Example Structure of Exception Handling in PL/SQL

Here’s a basic structure of how the exception handling blocks are organized in PL/SQL:

DECLARE
-- Declare variables
v_salary NUMBER;
v_bonus NUMBER;

-- Declare user-defined exceptions
insufficient_funds EXCEPTION;

BEGIN
-- Main execution block
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

-- Example condition that may raise an exception
IF v_salary < 1000 THEN
    RAISE insufficient_funds;  -- Raising user-defined exception
END IF;

v_bonus := v_salary * 0.1;  -- Calculating bonus

EXCEPTION
-- Handling specific exception
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Insufficient funds for bonus calculation.');

-- Handling predefined exceptions
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');

WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;

Explanation of Each Section

1. Declare Section

Variables: You can declare variables that will be used in the BEGIN section.

User-defined Exceptions: You can declare exceptions that are specific to your application logic.

1. Begin Section

This section contains the core logic of your PL/SQL block.

SQL operations (like SELECT, INSERT, UPDATE, etc.) are performed here.

Logic checks can be implemented to determine if certain conditions lead to exceptions, such as checking if a salary is below a certain threshold and raising a user-defined exception.

1. Exception Section

This section captures exceptions that occur in the BEGIN block.

Specific Exception Handling: You can handle specific exceptions using their names (like NO_DATA_FOUND).

User-defined Exception Handling: You can manage user-defined exceptions (like insufficient_funds in the example).

General Exception Handling: The WHEN OTHERS clause catches all exceptions that are not explicitly handled by the preceding clauses. It is a good practice to log or display the error message using SQLERRM, which provides the error message associated with the error number.

Common Predefined Exceptions in Oracle SQL

Some common predefined exceptions you might handle include:

NO_DATA_FOUND: Raised when a SELECT INTO statement returns no rows.

TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row.

ZERO_DIVIDE: Raised when there is an attempt to divide a number by zero.

INVALID_NUMBER: Raised when a conversion function (like TO_NUMBER) fails due to invalid input.

Best Practices for Exception Handling in PL/SQL

Specificity: Handle exceptions as specifically as possible to avoid masking other errors.

Logging: Consider logging errors to a table for auditing or troubleshooting.

User Feedback: Provide meaningful feedback to users when exceptions occur.

Performance: Minimize the use of exception handling for control flow; use it primarily for actual error conditions.

By following this sequence of exception handling blocks in Oracle SQL, you can ensure that your PL/SQL code is robust, maintainable, and capable of gracefully handling errors.