PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.
To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement. You should also provide an exception handler to track the exceptions that occurred during the bulk operation.
Example 11-9 shows how you can perform a number of DML operations, without stopping if some operations encounter errors. In the example, EXCEPTION_INIT is used to associate the dml_errors exception with the ORA-24381 error. The ORA-24381 error is raised if any exceptions are caught and saved after a bulk operation.
All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:
%BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration of the FORALL statement during which the exception was raised.
%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.
The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM as shown in Example 11-9.
You might need to work backward to determine which collection element was used in the iteration that caused an exception. For example, if you use the INDICES OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to %BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the VALUES OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches %BULK_EXCEPTIONS(i).ERROR_INDEX, and then use that element's value as the subscript to find the erroneous element in the original collection. For examples showing how to find the erroneous elements when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://www.oracle.com/technology/tech/pl_sql/.
If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.
Example 11-9 Bulk Operation That Continues Despite Exceptions
-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < '30-DEC-94';
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || 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('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
/
DROP TABLE emp_temp;
The output from the example is similar to:
Number of statements that failed: 2
Error #1 occurred during iteration #7
Error message is ORA-12899: value too large for column
Error #2 occurred during iteration #13
Error message is ORA-12899: value too large for column
In Example 11-9, PL/SQL raises predefined exceptions because updated values were too large to insert into the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returned 2, and the contents of SQL%BULK_EXCEPTIONS were (7,12899) and (13,12899).
To get the Oracle error message (which includes the code), the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE was negated and then passed to the error-reporting function SQLERRM, which expects a negative number.
2 comments:
Hi,
How do we know the exact record on which we got the error?
Suppose you are inserting millions of records in chunk and on some of the records throw Unique/Primary key violation error. In this case we have to update such problematic records. like
DECLARE
v1 number;
v2 varchar2(20);
v3 date;
v4 varchar2(20);
BEGIN
FOR I In (SELECT a, b, c, d from sometable where k = 'something') LOOP
Begin
Insert into thistable ( col1, col2 , col3, col4) values (i.a, i.b, i.c , i.d );
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE thistable
SET col3 = i.c,
col4 = i.d
WHERE col1 = i.a
AND col2 = i.b;
END;
END LOOP;
END;
/
Thhis approach is classical and not good for million of records. How it will do with BULK COLLECT & FOR ALL ?
Regards,
Touqeer
Hi Taoqueer,
Sorry couldn't saw your post earlier.
BULK EXCEPTION is for allowing a continuous DML operation even if some records raise exception.
What you are requesting is different. I have just tried to perform what you are asking:
create table test_tomkt_raw
(c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(20));
create table test_mkt
(c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(20));
create table test_mkt_log
(c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(20));
insert into test_tomkt_raw VALUES( 'A','B','C');
insert into test_tomkt_raw VALUES( 'A','B','C');
insert into test_tomkt_raw VALUES( 'D','E','F');
insert into test_tomkt_raw VALUES( 'R','BD','AC');
insert into test_tomkt_raw VALUES( 'AQ','SB','AC');
insert into test_tomkt_raw VALUES( 'AA','BA','CA');
insert into test_tomkt_raw VALUES( 'A','B','C');
insert into test_tomkt_raw VALUES( 'D','E','F');
ALTER TABLE test_mkt ADD PRIMARY KEY ON (C1,C2,C3);
create or replace procedure p_insert_mkt
is
exc_e1 EXCEPTION;
PRAGMA EXCEPTION_INIT(exc_e1, -1);
cursor c1 is select c1,c2,c3 from test_tomkt_raw;
r1 test_mkt%rowtype;
begin
open c1;
loop
fetch c1 into r1;
if c1%found then
begin
insert into test_mkt (c1,c2,c3)
select r1.c1, r1.c2, r1.c3 from dual;
exception
when exc_e1 THEN
insert into test_mkt_log ( c1,c2,c3)
select r1.c1, r1.c2, r1.c3 from dual;
end;
end if;
end loop;
commit;
end p_insert_mkt;
I have made the procedure for handling duplicate record handling.
You can consider this and make the procedure are per your need.
I hope it helped you.
Post a Comment