SQL%BULK_EXCEPTIONS attribute in BULK COLLECT

In PL/SQL, the SQL%BULK_EXCEPTIONS attribute is used to handle exceptions that occur during bulk operations, particularly with the FORALL statement. This attribute allows you to capture and analyze exceptions that may happen when inserting, updating, or deleting multiple rows at once.

Key Concepts of SQL%BULK_EXCEPTIONS

SQL%BULK_EXCEPTIONS: This attribute contains information about any exceptions that occurred during a bulk operation.

Count: SQL%BULK_EXCEPTIONS.COUNT gives the number of exceptions that occurred during the most recent bulk operation.

Error Index: SQL%BULK_EXCEPTIONS(i).ERROR_INDEX gives the index of the collection element that caused the error.

Error Message: SQL%BULK_EXCEPTIONS(i).ERROR_MESSAGE provides the error message associated with the exception.

Example of Using SQL%BULK_EXCEPTIONS

Here’s a practical example demonstrating how to use SQL%BULK_EXCEPTIONS in a PL/SQL block with a FORALL statement:

DECLARE
TYPE emp_tab IS TABLE OF NUMBER; -- Collection for employee IDs
emp_ids emp_tab := emp_tab(101, 102, 103, 104, 105); -- Sample data

-- Variables to capture exceptions
bulk_exceptions_count INTEGER;
error_index PLS_INTEGER;
error_message VARCHAR2(4000);

BEGIN
-- Attempt to insert employee IDs into the employees table
FORALL i IN emp_ids.FIRST .. emp_ids.LAST
INSERT INTO employees (id) VALUES (emp_ids(i));

-- Check for exceptions after the bulk operation
bulk_exceptions_count := SQL%BULK_EXCEPTIONS.COUNT;

IF bulk_exceptions_count > 0 THEN
    -- Loop through each exception
    FOR i IN 1 .. bulk_exceptions_count LOOP
        error_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; -- Get the index of the failed operation
        error_message := SQL%BULK_EXCEPTIONS(i).ERROR_MESSAGE; -- Get the error message
        DBMS_OUTPUT.PUT_LINE('Error occurred at index: ' || error_index || ' - ' || error_message);
    END LOOP;
ELSE
    DBMS_OUTPUT.PUT_LINE('All operations completed successfully.');
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

Explanation of the Example

1. Declaration of Collection: A collection of employee IDs (emp_ids) is declared.

2. FORALL Statement: The FORALL statement is used to insert all employee IDs into the employees table.

3. Bulk Exception Handling:

After the FORALL operation, the count of bulk exceptions is checked.

If exceptions occurred, a loop iterates through SQL%BULK_EXCEPTIONS to retrieve the index and error message for each exception.

The errors are printed using DBMS_OUTPUT.PUT_LINE.

1. General Exception Handling: Any unexpected exceptions that are not captured by SQL%BULK_EXCEPTIONS are handled in the WHEN OTHERS clause.

Common Scenarios for Using SQL%BULK_EXCEPTIONS

Data Integrity Violations: Such as inserting duplicate values where unique constraints exist.

Type Conversion Errors: When trying to insert incompatible data types into columns.

Foreign Key Violations: When trying to insert records that reference non-existing rows in other tables.

Other SQL Errors: Such as exceeding limits on string length or numeric precision.

Summary

Using SQL%BULK_EXCEPTIONS provides a robust way to handle errors during bulk operations in PL/SQL. It allows for better diagnostics and error handling, ensuring that your PL/SQL applications can gracefully recover from errors and provide meaningful feedback. If you have specific use cases or further questions about SQL%BULK_EXCEPTIONS, feel free to ask!