Bulk collect exceptions

To perform the above Bulk Collect operation, we need to create a sample employees table and insert some sample data. Here's the SQL to create the table, insert data, and then perform the operation with the Bulk Collect.

1. Create the employees table:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER,
    department_id NUMBER
);

2. Insert some sample data into the employees table:

INSERT INTO employees 
(employee_id, first_name, last_name, salary, department_id)
VALUES (101, 'John', 'Doe', 1500, 10);

INSERT INTO employees 
(employee_id, first_name, last_name, salary, department_id)
VALUES (102, 'Jane', 'Smith', 950, 20);

INSERT INTO employees 
(employee_id, first_name, last_name, salary, department_id)
VALUES (103, 'Alice', 'Johnson', 2000, 30);

INSERT INTO employees 
(employee_id, first_name, last_name, salary, department_id)
VALUES (104, 'Bob', 'Brown', 3000, 40);

INSERT INTO employees 
(employee_id, first_name, last_name, salary, department_id)
VALUES (105, 'Charlie', 'Davis', 800, 50);

-- Committing the data
COMMIT;

3. Running the Bulk Collect Example

Example with SQL%BULK_EXCEPTIONS

DECLARE
    -- Define a table type to hold employee data
    TYPE emp_table IS TABLE OF employees%ROWTYPE;
    l_emps emp_table; -- Collection to hold the rows from the SELECT query

BEGIN
    -- Perform BULK COLLECT to fetch all employee records
    BEGIN
        SELECT * 
        BULK COLLECT INTO l_emps
        FROM employees;

    EXCEPTION
        WHEN OTHERS THEN
            -- If there is a general exception, log the error message
            DBMS_OUTPUT.PUT_LINE('Error during BULK COLLECT operation: ' || SQLERRM);
    END;

    -- Loop through the fetched employee data and perform some processing
    FOR i IN 1 .. l_emps.COUNT LOOP
        BEGIN
            -- Simulate an operation (e.g., updating or processing each employee)
            IF l_emps(i).salary < 1000 THEN
                -- If an invalid salary is found, raise an exception
                RAISE_APPLICATION_ERROR(-20001, 'Salary too low for employee: ' || l_emps(i).employee_id);
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                -- Log the error and capture the row index where the exception occurred
                DBMS_OUTPUT.PUT_LINE('Error processing employee ID: ' || l_emps(i).employee_id || 
                                     ' with error: ' || SQLERRM);
        END;
    END LOOP;

    -- After processing, check for bulk exceptions using SQL%BULK_EXCEPTIONS
    IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            -- Log each exception that occurred during the BULK COLLECT
            DBMS_OUTPUT.PUT_LINE('Exception occurred in row ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || 
                                 ' with error code ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No bulk exceptions occurred.');
    END IF;

    -- Output message indicating completion
    DBMS_OUTPUT.PUT_LINE('Bulk collect and processing completed.');
END;

Explanation:

BULK COLLECT: The BULK COLLECT fetches all rows from the employees table and stores them in the l_emps collection.
SQL%BULK_EXCEPTIONS: This is the key part. After the BULK COLLECT operation, if any exception occurs, you can access the SQL%BULK_EXCEPTIONS collection to find out which rows caused the error.

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: This gives the index of the row that caused the exception.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE: This gives the error code for that row.

Exception Handling: In this example, if an employee has a salary below 1000, we manually raise an exception (RAISE_APPLICATION_ERROR). This simulates an error during the bulk collect operation.
Logging Bulk Exceptions: After processing the bulk collect operation, the SQL%BULK_EXCEPTIONS collection is checked. If there are any exceptions, we log each exception's row number and error code.

Expected Output (Example):

Assuming some employees have a salary below 1000, the output could look like this:

Error processing employee ID: 101 with error: ORA-20001: Salary too low for employee: 101
Error processing employee ID: 102 with error: ORA-20001: Salary too low for employee: 102
Exception occurred in row 1 with error code -20001
Exception occurred in row 2 with error code -20001
Bulk collect and processing completed.

Key Concepts:

SQL%BULK_EXCEPTIONS: This system variable holds details of exceptions that occur during a bulk operation. It allows you to access the index of the row and the error code that caused the exception.
ERROR_INDEX: Identifies which row in the collection caused the error.
ERROR_CODE: Provides the error code of the exception raised during the bulk operation.
Graceful Handling: Even if an error occurs in one or more rows, the rest of the rows are processed successfully, and the exceptions for faulty rows are captured and logged.

This approach ensures that you can handle errors on a per-row basis, without aborting the entire BULK COLLECT operation, which is essential when dealing with large datasets.

Blog

Bulk collect exceptions

Pranav Bakare

1. Create the employees table:

2. Insert some sample data into the employees table:

3. Running the Bulk Collect Example

Key Concepts:

Join Our Newsletter. No Spam, Only the good stuff.

Related