Handling FORALL Exceptions
Since version 9 it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows - the exceptions - are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.
The error text is not stored. The documentation says:
The error text is not stored. The documentation says:
The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM ...
Looks reasonable, but in our shop we validate lots of business rules with triggers. When a business rule is violated we do a RAISE_APPLICATION_ERROR(-20000,'APP-12345');
At client side (Webforms) the error message is looked up in the messages table and a friendly message is given. When coding a FORALL with SAVE EXCEPTIONS in such an environment, the error messages become useless as can be seen in the next example:
rwijk@ORA11G> create table mytable
2 ( id number(4)
3 , name varchar2(30)
4 )
5 /
Tabel is aangemaakt.
rwijk@ORA11G> create trigger mytable_bri
2 before insert on mytable
3 for each row
4 begin
5 if :new.id = 2
6 then
7 raise_application_error(-20000,'APP-12345');
8 elsif :new.id = 9
9 then
10 raise_application_error(-20000,'APP-98765');
11 end if;
12 end;
13 /
Trigger is aangemaakt.
rwijk@ORA11G> alter table mytable add constraint mytable_ck1 check (id <> 6)
2 /
Tabel is gewijzigd.
rwijk@ORA11G> declare
2 e_forall_error exception;
3 pragma exception_init(e_forall_error,-24381)
4 ;
5 type t_numbers is table of mytable.id%type;
6 l_numbers t_numbers := t_numbers(1,2,3,4,5,6,7,8,9,10)
7 ;
8 begin
9 forall i in 1..l_numbers.count save exceptions
10 insert into mytable
11 ( id
12 , name
13 )
14 values
15 ( l_numbers(i)
16 , 'Name' || to_char(l_numbers(i))
17 )
18 ;
19 exception
20 when e_forall_error then
21 for i in 1..sql%bulk_exceptions.count
22 loop
23 dbms_output.put_line('SQLCODE: ' || sql%bulk_exceptions(i).error_code);
24 dbms_output.put_line('SQLERRM: ' || sqlerrm(-sql%bulk_exceptions(i).error_code));
25 dbms_output.new_line;
26 end loop;
27 end;
28 /
SQLCODE: 20000
SQLERRM: ORA-20000:
SQLCODE: 2290
SQLERRM: ORA-02290: CHECK-beperking (.) is geschonden.
SQLCODE: 20000
SQLERRM: ORA-20000:
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> select id, name from mytable
2 /
ID NAME
---------- ------------------------------
1 Name1
3 Name3
4 Name4
5 Name5
7 Name7
8 Name8
10 Name10
7 rijen zijn geselecteerd.
Note how the SQLERRM message doesn't return anything useful and that the name of the check constraint has disappeared. This is really annoying and can't be circumvented easily in 9i. For better error messages we would have to go back to row by row processing. And that means: very slow.
However, version 10gR2 introduced a feature called DML error logging. I remember reading about it more than two years ago here on Tom Kyte's blog. In this entry and in the documentation you only see examples using SQL, not PL/SQL examples using FORALL. But luckily this works as well:
rwijk@ORA11G> rollback
2 /
Rollback is voltooid.
rwijk@ORA11G> exec dbms_errlog.create_error_log('mytable')
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> declare
2 type t_numbers is table of mytable.id%type;
3 l_numbers t_numbers := t_numbers(1,2,3,4,5,6,7,8,9,10)
4 ;
5 begin
6 forall i in 1..l_numbers.count
7 insert into mytable
8 ( id
9 , name
10 )
11 values
12 ( l_numbers(i)
13 , 'Name' || to_char(l_numbers(i))
14 )
15 log errors reject limit unlimited
16 ;
17 for r in
18 ( select ora_err_number$
19 , ora_err_mesg$
20 from err$_mytable
21 )
22 loop
23 dbms_output.put_line('SQLCODE: ' || to_char(r.ora_err_number$));
24 dbms_output.put_line('SQLERRM: ' || r.ora_err_mesg$);
25 dbms_output.new_line;
26 end loop
27 ;
28 end;
29 /
SQLCODE: 20000
SQLERRM: ORA-20000: APP-12345
ORA-06512: in "RWIJK.MYTABLE_BRI", regel 4
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.MYTABLE_BRI'
.
SQLCODE: 2290
SQLERRM: ORA-02290: CHECK-beperking (RWIJK.MYTABLE_CK1) is geschonden.
SQLCODE: 20000
SQLERRM: ORA-20000: APP-98765
ORA-06512: in "RWIJK.MYTABLE_BRI", regel 7
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.MYTABLE_BRI'
.
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> select id, name from mytable
2 /
ID NAME
---------- ------------------------------
1 Name1
3 Name3
4 Name4
5 Name5
7 Name7
8 Name8
10 Name10
7 rijen zijn geselecteerd.
And you do get to see the error messages (APP-12345 and APP-98765) and the name of the check constraint. Unfortunately, our shop still uses 9.2.0.7...
UPDATE
It is worth knowing that there are some restrictions when working with the DML error logging clause. From the 11g documentation (same as in 10gR2 documentation):
Restrictions on DML Error Logging
* The following conditions cause the statement to fail and roll back without invoking the error logging capability:
o Violated deferred constraints.
o Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
o Any update operation UPDATE or MERGE that raises a unique constraint or index violation.
* You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.
o If you create or modify the corresponding error logging table so that it contains a column of an unsupported type, and if the name of that column corresponds to an unsupported column in the target DML table, then the DML statement fails at parse time.
o If the error logging table does not contain any unsupported column types, then all DML errors are logged until the reject limit of errors is reached. For rows on which errors occur, column values with corresponding columns in the error logging table are logged along with the control information.
No comments:
Post a Comment