Exceptions in PL/SQL

• Exceptions are designed to handle runtime errors in PL/SQL.

• When a PL/SQL block encounters an error during execution, it raises an exception and terminates the process.

• Exceptions can occur in:

  • Anonymous blocks (causing immediate termination)

  • Subprograms (functions, procedures, etc.)

  • Other platforms (e.g., Oracle Forms, Java)

• If exceptions are not handled, they will crash the program or propagate to the calling environment.

Attributes of Exceptions

• Exceptions provide two main attributes:

  1. Error Code – A unique identifier for the error (e.g., ORA-01403 for "No data found").

  2. Error Message – A brief description of the issue.

Raising Exceptions

Exceptions can be raised in two ways:

1. Implicitly – Oracle server detects the error and raises an exception automatically.

2. Explicitly – Developers raise custom exceptions based on specific business rules.

Exception Handling Methods

There are three ways to handle exceptions:

1. Trap and Handle – Catch the exception and take corrective actions.

2. Propagate – Pass the exception to the calling program or environment for handling.

3. Handle and Propagate – Take corrective actions but also pass the error to the caller.

Types of Exceptions in PL/SQL

1. Predefined Oracle Server Errors

   • Oracle has over 10,000 error codes, but about 20 common ones are predefined with names.

   • Example: ORA-01403 (No Data Found) is predefined as NO_DATA_FOUND.

2. Non-predefined Oracle Server Errors

   • These do not have predefined names and must be declared explicitly in the declaration section.

3. User-defined Errors

   • These are custom errors related to business logic.

   • Example: Raising an exception if salary exceeds $20,000.

Handling Predefined Exceptions

The exception section is placed before the END keyword in a PL/SQL block.

Syntax:

BEGIN
    -- Your PL/SQL code here
EXCEPTION
    WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
    WHEN too_many_rows THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee found with the same name.');
END;

Handling Multiple Exceptions

If a PL/SQL block can encounter multiple exceptions, each must have a separate handler.

EXCEPTION
    WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('No record found.');
    WHEN too_many_rows THEN
        DBMS_OUTPUT.PUT_LINE('Multiple records found. Please refine the query.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred. Contact support.');

Key Points:

• If an exception is caught, the remaining handlers are skipped.

• WHEN OTHERS THEN is a generic handler that catches all unhandled exceptions.

• Exception handlers must be unique; two handlers cannot trap the same error.

Fetching Error Code and Message

PL/SQL provides built-in functions to fetch error details:

• SQLCODE: Returns the error number.

• SQLERRM: Returns the error message.

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE || ' - ' || SQLERRM);

Nested Exception Blocks

In real-world scenarios, preventing abrupt execution termination is crucial. To handle exceptions for specific operations without terminating the entire block, nested exception blocks can be used.

DECLARE v_department_name VARCHAR2(100);
BEGIN
    BEGIN
        SELECT department_id INTO v_department_name FROM employees WHERE first_name = 'Steven';
    EXCEPTION
        WHEN too_many_rows THEN
            v_department_name := 'Error in department name';
    END;
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_department_name);
END;

Handling Non-Predefined Exceptions in PL/SQL

• Non-predefined exceptions are errors that do not have specific names in the Oracle Server.

• Oracle has around 10,000 error codes that are not predefined.

• These exceptions cannot be trapped directly by their error codes (except using WHEN OTHERS THEN, which is too generic).

• To handle them effectively, we must declare them and assign a specific error code.

Declaring a Non-Predefined Exception

• Declare an exception variable.

cannot_update_to_null EXCEPTION;

• Assign an error code to the exception using PRAGMA EXCEPTION_INIT.

PRAGMA EXCEPTION_INIT(cannot_update_to_null, -1407);

• PRAGMA is a compiler directive that gives instructions to the compiler.

• EXCEPTION_INIT tells the compiler to associate the given error code with the exception name.

Example

declare
  cannot_update_to_null exception;
  pragma exception_init(cannot_update_to_null,-01407);
begin
  UPDATE employees_copy set email = null where employee_id = 100;
exception
  when cannot_update_to_null then
    dbms_output.put_line('You cannot update with a null value!');
end;

Alternative Approach Using WHEN OTHERS THEN

• You can handle the specific error inside a generic handler using SQLCODE:

WHEN OTHERS THEN  
  IF SQLCODE = -1407 THEN  
      DBMS_OUTPUT.PUT_LINE('You cannot update with a null value!');
  ELSE  
      RAISE;  -- Re-raise the exception if it's not the one we expected  
  END IF;

Handling User-Defined Exceptions in PL/SQL

Declaring a User-Defined Exception

• The syntax is similar to non-predefined exceptions, but without an error code.

• Example declaration:

too_high_salary EXCEPTION;

Raising a User-Defined Exception

• To explicitly raise a user-defined exception, use the RAISE statement.

• Example:

IF v_salary_check > 20000 THEN  
    RAISE too_high_salary;  
END IF;

Handling a User-Defined Exception

• The raised exception must be handled in the EXCEPTION section:

EXCEPTION  
    WHEN too_high_salary THEN  
        DBMS_OUTPUT.PUT_LINE('This salary is too high. You need to decrease it.');

Complete Example:

DECLARE  
    too_high_salary EXCEPTION;  
    v_salary_check PLS_INTEGER;  
BEGIN  
    -- Fetch salary of the employee  
    SELECT salary INTO v_salary_check  
    FROM employees  
    WHERE employee_id = 100;  

    -- Check if salary exceeds limit  
    IF v_salary_check > 20000 THEN  
        RAISE too_high_salary;  
    END IF;

    -- If no exception, print success message  
    DBMS_OUTPUT.PUT_LINE('The salary is in an acceptable range.');  

EXCEPTION  
    WHEN too_high_salary THEN  
        DBMS_OUTPUT.PUT_LINE('This salary is too high. You need to decrease it.');  
END;

Introduction to RAISE_APPLICATION_ERROR

• In PL/SQL, predefined and user-defined exceptions are handled within a block.

• However, user-defined exceptions do not have error codes and cannot be raised beyond their block.

• When working with external applications (Oracle Forms, Java, etc.), we need a way to pass business exceptions to the caller.

• Solution: Use RAISE_APPLICATION_ERROR to send custom error messages and codes to the calling application.

Syntax of RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR(error_code, error_message [, keep_error_stack]);

• error_code: Must be between -20000 and -20999 (to avoid conflict with Oracle’s reserved codes).

• error_message: Custom error message (up to 2 KB in length).

• keep_error_stack (optional):

  • TRUE: Keeps previous errors in the stack.

  • FALSE (default): Clears previous errors from the stack.

Example: Raising an Application Error

DECLARE
    v_salary_check PLS_INTEGER;
BEGIN
    -- Fetch employee salary
    SELECT salary INTO v_salary_check FROM employees WHERE employee_id = 100;

    -- Business rule: Salary should not exceed 20,000
    IF v_salary_check > 20000 THEN
        RAISE_APPLICATION_ERROR(-20243, 'The salary of the selected employee is too high.');
    END IF;

    DBMS_OUTPUT.PUT_LINE('Salary is within the acceptable range.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


-- OUTPUT
-- ORA-20243: The salary of the selected employee is too high.

Using RAISE_APPLICATION_ERROR in the Exception Block

DECLARE
    too_high_salary EXCEPTION;
BEGIN
    -- Fetch salary
    SELECT salary INTO v_salary_check FROM employees WHERE employee_id = 100;

    -- Condition to raise exception
    IF v_salary_check > 20000 THEN
        RAISE too_high_salary;
    END IF;

EXCEPTION
    WHEN too_high_salary THEN
        DBMS_OUTPUT.PUT_LINE('Handling salary error internally.');
        RAISE_APPLICATION_ERROR(-20243, 'Salary exceeds limit.');
END;

-- OUTPUT
-- Handling salary error internally.
-- ORA-20243: Salary exceeds limit.