What is exception? What are the types of exceptions?

by

Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.

In PL/SQL, an exception is an error or an unexpected event that occurs during the execution of a program. When such an error occurs, the normal flow of the program is disrupted, and an exception is raised. PL/SQL provides a robust exception-handling mechanism to deal with errors and maintain the integrity of the program.

There are two main types of exceptions in PL/SQL:

  1. Predefined (or System) Exceptions: These are exceptions that are already defined by Oracle. They cover common errors like division by zero, invalid cursor operations, and others. Examples include ZERO_DIVIDE, NO_DATA_FOUND, and TOO_MANY_ROWS.Example:
    BEGIN
    -- Some code that might cause an exception
    EXCEPTION
    WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Division by zero error.');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
    END;
  2. User-Defined Exceptions: These are exceptions that you define based on your specific application needs. You can create your own exceptions using the DECLARE section and raise them using the RAISE statement.Example:
    DECLARE
    custom_exception EXCEPTION;
    BEGIN
    -- Some code that might raise the user-defined exception
    IF some_condition THEN
    RAISE custom_exception;
    END IF;
    EXCEPTION
    WHEN custom_exception THEN
    DBMS_OUTPUT.PUT_LINE('Custom exception occurred.');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
    END;

It’s important to handle exceptions appropriately to ensure that your PL/SQL programs can gracefully recover from errors and continue executing, or terminate gracefully when necessary. The WHEN OTHERS clause is often used as a catch-all for any unhandled exceptions, but it’s generally recommended to handle specific exceptions whenever possible for more precise error management.