This content has been marked as final. Show 4 replies
SB2013 wrote:when all else fails, Read The Fine Manual
Ii have to insert lakhs of records from a source table to another table through bulk processing .If I am getting exception for some of the records then what I can do so that the insertion will not stop but the records for which I am getting the errors will be recorded somewhere and my execution will continue till the last record insertion.
After the execution I can go and check for which records the insertion failed and why?
Can anyone help me out in this?
Your suggestion will behighly appreciated.
you may be using the wrong tool to accomplish the task. I would look at DBMS_PARALLEL_EXECUTE (11gR2) to move these records. Depending on how many records per "chunK" it would process most and when you have errors, you can see which records had errors and process them separately and fix whatever issue caused the failure.
If you still need to use bulk collect http://www.ltgtfy.com/?1=bulk+collect+error+processing and you should find the answer to your question.
You could save the exceptions by using "SAVE EXCEPTIONS" clause in FOR ALL statement and using SQL%BULK_EXCEPTIONS cursor attributes.
see the below link for more understanding the exceptions on bulk processing...
hope this helps!
The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. Since the above table does not allow nulls, these rows will result in an exception. The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete. In this case, the exception handler just loops through the SQL%BULK_EXCEPTION cursor attribute to see what errors occured.
SET SERVEROUTPUT ON
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab(l_tab.last).id := i;
-- Cause a failure.
l_tab(50).id := NULL;
l_tab(51).id := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
-- Perform a bulk operation.
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO exception_test
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()
PL/SQL procedure successfully completed.