DECLARE
-- Declare collections for valid and invalid data
TYPE t_valid_data IS TABLE OF your_table%ROWTYPE; -- Adjust the type to your
table structure
TYPE t_invalid_data IS TABLE OF your_table%ROWTYPE;
l_valid_data t_valid_data;
l_invalid_data t_invalid_data;
-- Declare a variable for exception handling
l_error_msg VARCHAR2(4000);
BEGIN
-- Assume the collection l_valid_data is populated with data for bulk insert
-- Initialize the valid and invalid data collections
l_valid_data := t_valid_data(
-- Populate with valid data rows, for example:
your_table%ROWTYPE('value1', 'value2', 'value3'),
your_table%ROWTYPE('value4', 'value5', 'value6')
);
-- Initialize the invalid data collection
l_invalid_data := t_invalid_data();
-- Start the bulk insert using FORALL with exception handling
FORALL i IN INDICES OF l_valid_data SAVE EXCEPTIONS
BEGIN
-- Insert valid data into the target table
INSERT INTO your_table (col1, col2, col3)
VALUES (l_valid_data(i).col1, l_valid_data(i).col2,
l_valid_data(i).col3);
EXCEPTION
WHEN OTHERS THEN
-- Capture the exception and log invalid data
l_invalid_data.EXTEND;
l_invalid_data(l_invalid_data.COUNT) := l_valid_data(i);
-- Optionally, you can store the error message in a log table or
variable
l_error_msg := SQLERRM;
-- Log invalid data into the error log table
INSERT INTO error_log_table (col1, col2, col3, error_message)
VALUES (l_valid_data(i).col1, l_valid_data(i).col2,
l_valid_data(i).col3, l_error_msg);
END;
-- Now insert all valid rows without error
COMMIT; -- Commit the valid rows if no issues
-- Optionally, log the invalid data to the error table
FOR i IN 1..l_invalid_data.COUNT LOOP
INSERT INTO error_log_table (col1, col2, col3, error_message)
VALUES (l_invalid_data(i).col1, l_invalid_data(i).col2,
l_invalid_data(i).col3, 'Invalid data error');
END LOOP;
-- Commit any changes made to the error log table
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Handle any general errors that might occur in the process
ROLLBACK; -- Optionally, rollback the transaction if required
-- Log error and raise
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
/
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
insert into emp values(num_tab(i));
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' || SQLERRM(-SQL
%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;
/
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-02290: check constraint (.) violated
Error 2 occurred during iteration 6
Oracle error is ORA-02290: check constraint (.) violated
Error 3 occurred during iteration 10
Oracle error is ORA-02290: check constraint (.) violated
PL/SQL procedure successfully completed.
