Sujan's blog

Exceptions in PL/SQL : 5

Sujan Haldar's photo
Sujan Haldar
·

6 min read

  • 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.
PL/SQL