How to Handle Exceptions in PLSQL? With Example

Handle Exceptions in PLSQL

Exception handling is a key feature of PL/SQL (Procedural Language/Structured Query Language) that helps applications avoid abrupt termination and gracefully handle runtime problems. It ensures the stability and resilience of Oracle applications by offering an organised method for identifying and handling fault situations.

The blocks that make up a PL/SQL program might be named (such as functions, packages, triggers, or procedures) or anonymous. Usually, each block is divided into three primary sections:

Declarations: This is where cursors, variables, and constants are defined.
Executable Commands: The required portion that contains the SQL statements and program logic is called Executable Commands.
Exception Handling: Customised treatment for error scenarios is provided via the optional Exception treatment section.

Before the END command, which ends the PL/SQL block, the Exception Handling section starts with the keyword EXCEPTION. A runtime problem, often known as a “exception,” in a PL/SQL block’s Executable Commands section causes the regular command flow to abruptly halt and shift control to the EXCEPTION section. This area is then searched by PL/SQL for an exception handler that corresponds to the error that was detected. Should a match be discovered, the statements linked to that handler are carried out.

When a WHEN OTHERS clause is present and no specific handler matches, the general handler will be used. You are unable to resume the regular command processing within the executable portion where the problem occurred after encountering an exception. You can insert the possibly troublesome statements inside a nested BEGIN…EXCEPTION…END block to go on processing in the same logical flow after an error.

Two primary categories of exceptions are offered by Oracle:

System-defined exceptions: When the PL/SQL runtime engine identifies an incorrect state, it usually raises system-defined exceptions, which are predefined by Oracle. Many have names (NO_DATA_FOUND, for example), and others are recognised by their error numbers.
Programmer-defined exceptions: Programmer-defined exceptions are those that are unique to the business rules of the program and are expressly stated by the developer. PRAGMA EXCEPTION_INIT can be used to link them to particular Oracle errors, and RAISE_APPLICATION_ERROR can be used to raise them with custom error messages.

The built-in SQLCODE and SQLERRM functions in PL/SQL can also be used to retrieve the error number and message of the most recent exception that was raised. These are very helpful for diagnosing and recording in generic exception handlers like WHEN OTHERS.

Predefined Exceptions

PL/SQL provides a collection of frequently used preset exceptions that match particular Oracle errors. Developers can apply specific logic for predicted issues by catching these exceptions.

WHEN NO_DATA_FOUND

Error Code: ORA-01403, SQLCODE = +100.
Description: This exception is brought up in a number of situations:
- When an implicit cursor, such as a SELECT INTO query, returns no rows. This frequently occurs when a query is made for a specific record that may not exist.
- In local associative arrays, when referring to an uninitialised row.
- When reading past the end of a BFILE with DBMS_LOB or a file with the UTL_FILE package.
Usage: An implicit SELECT INTO query that is supposed to produce exactly one row but may return none at all requires handling NO_DATA_FOUND. For instance, an exception will be triggered if you are attempting to access product data by an ID and that ID is not present.

WHEN TOO_MANY_ROWS

Error Code: ORA-01422, SQLCODE = -1422.
Description: When more than one row is returned by a SELECT INTO command, this exception is triggered. Because a SELECT INTO command is intended to fill a single PL/SQL variable or record, it expressly demands that the query yield a single row.
Usage: This exception, like NO_DATA_FOUND, needs to be handled when utilising implicit cursors in situations when you anticipate a unique result but the data may not meet that expectation or your when clause is insufficiently restrictive. An explicit cursor or BULK COLLECT should be used if you plan to retrieve more than one row.

WHEN DUP_VAL_ON_INDEX

Error Code: ORA-00001, SQLCODE = -1.
Description: When a column or group of columns that are restricted by a unique index (such as a primary key constraint or a unique constraint) are attempted to contain duplicate values, an INSERT or UPDATE statement raises this exception.
Usage: In batch loading or data entry procedures, this is a frequent exception. By handling it, your application can either note the problem for future evaluation or notify the user about the duplicate item instead of failing the transaction altogether.

WHEN VALUE_ERROR

Error Code: ORA-06502, SQLCODE = -6502.
Description:This generic exception indicates that there is an issue with data conversion, truncation, or improper constraining of character and numeric data in PL/SQL. Some examples are as follows:
- If the character string is not convertable, an attempt is made to assign it to a NUMBER variable.
- A VARCHAR2 variable cannot hold a string that is too long.
- Assigning a value that surpasses the accuracy or scale of a NUMBER variable results in a numeric overflow.
Usage: Frequently signifies that the application logic has data types or sizes that are not compatible. To stop unexpected behaviour or data corruption, it’s critical to discover it early. If this issue happens inside a SQL DML statement, INVALID_NUMBER may be thrown instead.

WHEN ZERO_DIVIDE

Error Code: ORA-01476, SQLCODE = -1476.
Description: A program that tries to divide an integer by zero raises this exception.
Usage: Crucial for division-based computations where the divisor may dynamically evaluate. By handling it, mathematical errors can be avoided and alternate reasoning can be used, like setting a default value or omitting the computation.

WHEN OTHERS

Description: The catch-all exception handler is this one. Any EXCEPTION section’s last handler must be this one. It captures any exception that the WHEN clauses before it haven’t specifically addressed.
Usage: WHEN OTHERS guarantees that no error is left entirely unchecked, which is essential for developing reliable programs. It is recommended that this handler use SQLCODE and SQLERRM to obtain details about the particular problem that occurred. The user can then see this information, log it, or utilise it to raise a more specific application problem. But be careful it can “swallow” problems, which could conceal the core reason from users or other blocks if it isn’t reported or raised again. A WHEN OTHERS handler that does not finish in RAISE or RAISE_APPLICATION_ERROR generates a warning PLW-06009.

Example: Exception Handling in PL/SQL

Let’s illustrate these concepts with an example involving a PRODUCT_STOCK table.

First, create the PRODUCT_STOCK table and insert some initial data:

-- Enable DBMS_OUTPUT to see messages
SET SERVEROUTPUT ON SIZE 1000000;

-- Drop table if it already exists to ensure a clean start
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE PRODUCT_STOCK';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN -- ORA-00942: table or view does not exist
         RAISE;
      END IF;
END;
/

-- Create the table
CREATE TABLE PRODUCT_STOCK (
    PRODUCT_ID     NUMBER(5)     PRIMARY KEY,
    PRODUCT_NAME   VARCHAR2(20)  NOT NULL,
    STOCK_QUANTITY NUMBER(10),
    PRICE          NUMBER(8,2)
);

-- Insert initial values
INSERT INTO PRODUCT_STOCK (PRODUCT_ID, PRODUCT_NAME, STOCK_QUANTITY, PRICE) VALUES (101, 'Laptop', 150, 1200.50);
INSERT INTO PRODUCT_STOCK (PRODUCT_ID, PRODUCT_NAME, STOCK_QUANTITY, PRICE) VALUES (102, 'Mouse', 300, 25.00);
INSERT INTO PRODUCT_STOCK (PRODUCT_ID, PRODUCT_NAME, STOCK_QUANTITY, PRICE) VALUES (103, 'Keyboard', 200, 75.25);
COMMIT;

Now, let’s implement a PL/SQL block with multiple nested BEGIN...EXCEPTION...END blocks to demonstrate various exception handlers. Each nested block handles a specific error, allowing the main program flow to continue.

DECLARE
    v_product_id     PRODUCT_STOCK.PRODUCT_ID%TYPE;
    v_product_name   PRODUCT_STOCK.PRODUCT_NAME%TYPE;
    v_stock_quantity PRODUCT_STOCK.STOCK_QUANTITY%TYPE;
    v_price          PRODUCT_STOCK.PRICE%TYPE;
    v_result         NUMBER;
    v_long_string    VARCHAR2(50); -- Will cause VALUE_ERROR for PRODUCT_NAME
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Starting PL/SQL Exception Handling Example ---');

-- --- 1. Demonstrating ZERO_DIVIDE ---
    BEGIN
        v_stock_quantity := 10;
        v_result := 100 / (v_stock_quantity - 10); -- This will cause division by zero
        DBMS_OUTPUT.PUT_LINE('Result of division: ' || v_result);
    EXCEPTION
        WHEN ZERO_DIVIDE THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught ZERO_DIVIDE: Attempted to divide by zero. ***');
            v_result := 0; -- Handle the error gracefully
    END;
    DBMS_OUTPUT.PUT_LINE(' '); -- Separator for output clarity

-- --- 2. Demonstrating VALUE_ERROR ---
    BEGIN
        v_long_string := 'This is a very long product name that exceeds 20 characters';
        
        -- Attempt to update a product name with a too-long string
        UPDATE PRODUCT_STOCK
        SET PRODUCT_NAME = v_long_string
        WHERE PRODUCT_ID = 101;
        
        DBMS_OUTPUT.PUT_LINE('Product name updated successfully to: ' || v_long_string);
    EXCEPTION
        WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught VALUE_ERROR: String too long for PRODUCT_NAME column. ***');
            ROLLBACK; -- Rollback the problematic update
    END;
    DBMS_OUTPUT.PUT_LINE(' ');

-- --- 3. Demonstrating DUP_VAL_ON_INDEX ---
    BEGIN
        -- Attempt to insert a record with an existing primary key
        INSERT INTO PRODUCT_STOCK (PRODUCT_ID, PRODUCT_NAME, STOCK_QUANTITY, PRICE)
        VALUES (101, 'Tablet', 50, 500.00);
        DBMS_OUTPUT.PUT_LINE('Successfully inserted duplicate product ID.');
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught DUP_VAL_ON_INDEX: Attempted to insert duplicate PRODUCT_ID 101. ***');
            ROLLBACK; -- Rollback the problematic insert
    END;
    DBMS_OUTPUT.PUT_LINE(' ');

-- --- 4. Demonstrating NO_DATA_FOUND ---
    BEGIN
        -- Attempt to select a non-existent product
        SELECT PRODUCT_NAME, PRICE
        INTO v_product_name, v_price
        FROM PRODUCT_STOCK
        WHERE PRODUCT_ID = 999; -- Non-existent ID
        
        DBMS_OUTPUT.PUT_LINE('Found product: ' || v_product_name || ' with price: ' || v_price);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught NO_DATA_FOUND: No product found with ID 999. ***');
            v_product_name := 'N/A';
            v_price := 0;
    END;
    DBMS_OUTPUT.PUT_LINE(' ');

-- --- 5. Demonstrating TOO_MANY_ROWS ---
    BEGIN
        -- This query is designed to return multiple rows for the SELECT INTO
        -- (assuming multiple products have STOCK_QUANTITY > 0, which they do)
        SELECT PRODUCT_NAME
        INTO v_product_name
        FROM PRODUCT_STOCK
        WHERE STOCK_QUANTITY > 0;
        
        DBMS_OUTPUT.PUT_LINE('Found product (expected one): ' || v_product_name);
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught TOO_MANY_ROWS: Query returned more than one product. ***');
            v_product_name := 'Multiple products';
    END;
    DBMS_OUTPUT.PUT_LINE(' ');

-- --- 6. Demonstrating WHEN OTHERS ---
    BEGIN
        -- Attempt an invalid SQL statement
        EXECUTE IMMEDIATE 'SELECT * FROM NON_EXISTENT_TABLE';
        DBMS_OUTPUT.PUT_LINE('Successfully queried non-existent table.');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('*** Caught WHEN OTHERS: An unexpected error occurred. ***');
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
    END;
    DBMS_OUTPUT.PUT_LINE(' ');

    DBMS_OUTPUT.PUT_LINE('--- PL/SQL Exception Handling Example Finished ---');
    COMMIT;

EXCEPTION
    -- Outer block catch-all, if any unexpected errors propagate
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('!!! An unhandled exception occurred in the main block. !!!');
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
        ROLLBACK;
END;
/

-- Verify the final state of the table
SELECT * FROM PRODUCT_STOCK;

Output of the Example:

--- Starting PL/SQL Exception Handling Example ---
*** Caught ZERO_DIVIDE: Attempted to divide by zero. ***
 
*** Caught VALUE_ERROR: String too long for PRODUCT_NAME column. ***
 
*** Caught DUP_VAL_ON_INDEX: Attempted to insert duplicate PRODUCT_ID 101. ***
 
*** Caught NO_DATA_FOUND: No product found with ID 999. ***
 
*** Caught TOO_MANY_ROWS: Query returned more than one product. ***
 
*** Caught WHEN OTHERS: An unexpected error occurred. ***
SQLCODE: -942, SQLERRM: ORA-00942: table or view does not exist
 
--- PL/SQL Exception Handling Example Finished ---
PL/SQL procedure successfully completed.

PRODUCT_ID PRODUCT_NAME         STOCK_QUANTITY      PRICE
---------- -------------------- -------------- ----------
       101 Laptop                          150    1200.50
       102 Mouse                           300      25.00
       103 Keyboard                        200      75.25

This output demonstrates how each nested BEGIN...EXCEPTION...END block effectively catches and handles specific runtime errors, allowing the main PL/SQL program to continue its execution and provide meaningful feedback, rather than failing entirely. The final SELECT statement confirms the state of the table, showing that problematic DML operations were rolled back locally within their exception handlers.

Page Content

Tutorials