Forall: An Example of saving exceptions clause

For errors in a large number of DML operations, besides using the DML error logging feature to record errors during DML, use the batch SQL statement forall save
Exceptions is a good choice. The usage of the DML error logging feature is relatively simple than the save exceptions of forall, and there are also some shortcomings, such as every DML operated
Objects need to create corresponding log tables, which is not conducive to centralized management. This article describes DML error logging, which is not conducive to centralized management.
.

The following example is from a practical application. The original table name and the actual processing process are displayed in a simple example.
1. Assume that two different databases are set to database A and database B (for simplicity, this is demonstrated on the same database ).
2. synchronize data from some tables of database A to the target table corresponding to database B.
3. If a specific record error occurs during synchronization, write the error information (including the table name and primary key) generated by the record to the log table, and update the synchronization status of the original table to N. Otherwise, update to y.
4. If the error message is not caused by insert, write the process name and corresponding error information to the log table.

If you are familiar with batch SQL, please read the following. Otherwise, please refer to the knowledge required in this article:

Batch SQL forall statements
Bulk collect clause for batch SQL
Initialization and assignment of PL/SQL Sets
PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

I. Create a demo Environment
To simplify the process, the Demo code below is completed on the same database. to complete it on different databases, you only need to set the DB link.

--> Create the source table emp_source for demonstration. The data is from Scott. empscott @ cnmmbo> Create Table emp_source as select empno, ename, Sal from EMP; Table created. --> adds an is_sync field to the source table to record whether synchronization is successful. Scott @ cnmmbo> alter table emp_source add is_sync char (1); table altered. --> Create the target table Scott @ cnmmbo> Create Table emp_dest as select empno, ename, Sal from emp_source where 1 = 0; Table created. --> Create a log table Scott @ cnmmbo> Create Table err_log_tbl (log_seq number (12) not null, log_time date not null, 2 sp_name varchar2 (100 ), table_name varchar2 (30), table_pk varchar2 (30), err_msg varchar2 (2000); table created. --> Add constraints for the table emp_dest to trigger an error during the Insert Process. Scott @ cnmmbo> alter table emp_dest add constraint ck_sal check (SAL> 500); table altered. scott @ cnmmbo> alter table emp_dest modify (ename constraint nn_ename not null); table altered. --> Create a sequencecreate sequence Scott. err_seq start with 20 maxvalue 999999999999999999999999999 minvalue 0 nocycle nocache noorder; --> Create a function to obtain sequencecreate or replace function Scott. gen_new_err_seq return err_log_tbl.log_seq % typeis newrecid err_log_tbl.log_seq % type; begin select err_seq.nextval into newrecid from dual; return newrecid; end ;/

Ii. Use the following PL/SQL block demonstration

--> The following DEMO code can be encapsulated into the package declare c_sp_name constant varchar2 (50): = 'anonymity _ plsql_block '; c_process_name constant varchar2 (20): = 'ins _ emp_dest '; c_table_name varchar2 (30): = 'emp_dest'; debugpos bo_common_pkg.debug_pos_type: = 0; v_limit pls_integer: = 5; --> define the limit on the number of extracted rows in the cursor fetch, because emp_source has few records, set it to 5 err_msg varchar2 (1000); cursor cur_emp --> declares that the cursor retrieves data from the source table is select empno, ename, Sal from emp_source where is_sync is null; type emp_tab_type is table of cur_emp % rowtype; emp_tab emp_tab_type; --> declare the type err_rec_type is table of nested % rowtype; err_tab err_rec_type: = err_rec_type (); --> declare a nested table based on err_log_tab and initialize sub_proc_exp exception; --> two exceptions are defined: bulk_error exception; Pragma exception_init (bulk_error,-24381); begin debugpos: = 10; open cur_emp; loop begin debugpos: = 20; fetch cur_emp --> use the cursor fetch data to coexist in the nested table bulk collect into emp_tab limit v_limit; exit when emp_tab.count = 0; debugpos: = 30; --> The following assignment statements are used to simulate the error message emp_tab generated by insert (2 ). ename: = rpad (emp_tab (2 ). ename, 15, '*'); emp_tab (3 ). SAL: = 100; emp_tab (7 ). SAL: = NULL; debugpos: = 40; forall I in 1 .. emp_tab.count --> forall clause inserts data, and uses the save exceptions clause save exceptions insert into emp_dest values emp_tab (I); Exception when bulk_error then --> The following is the debugpos processing for bulk_error: = 50; for I in 1 .. in the SQL % bulk_exceptions.count loop --> for loop, the data information err_tab.extend required for inserting err_log_tbl is generated; err_tab (I ). log_seq: = gen_new_err_seq; err_tab (I ). log_time: = sysdate; err_tab (I ). sp_name: = c_process_name; err_tab (I ). table_name: = c_table_name; err_tab (I ). table_pk: = to_char (emp_tab (SQL % bulk_exceptions (I ). error_index ). empno); err_tab (I ). err_msg: = substr (sqlerrm (-SQL % bulk_exceptions (I ). error_code), 1,300); End loop; debugpos: = 60; forall I in 1 .. err_tab.count --> insert error information to err_log_tbl insert into err_log_tbl values err_tab (I); debugpos: = 70; for I in 1 .. err_tab.count --> for records that fail to be inserted, update the source table to indicate that the synchronization failed loop update emp_source e set is_sync = 'n' where exists (select 1 from err_log_tbl d Where E. empno = D. table_pk); End loop; when others then err_msg: = substr (sqlerrm, 1,200); Raise sub_proc_exp; end loop; close cur_emp; debugpos: = 80; --> author: robinson Cheng --> blog: http://blog.csdn.net/robinson_0612 update emp_source e --> set is_sync = 'y' where exists (select 1 from emp_dest d Where E. empno = D. empno) and is_sync is null; Exception --> the outer exception processes the error information and writes it to the err_log_tbl log file when sub_proc_exp then err_msg: = substr ('<err @' | c_sp_name | '-Debug pos:' | to_char (debugpos) | '>-' | sqlerrm, 1,500) | ''| err_msg; insert into err_log_tbl values (gen_new_err_seq, sysdate, c_sp_name, null, null, err_msg); dbms_output.put_line ('error MSG is: '| err_msg ); when others then err_msg: = substr ('<err @' | c_sp_name | '-Debug pos:' | to_char (debugpos) | '>-' | sqlerrm, 1,500); insert into err_log_tbl values (gen_new_err_seq, sysdate, c_sp_name, null, null, err_msg); dbms_output.put_line ('error MSG is: '| err_msg); end;/1. handling non-insert exceptions Scott @ cnmmbo >@test_bulk_ins_err --> the Demo code above is saved to the @ test_bulk_ins_err. SQL file error MSG is: <err @ anonymity_plsql_block-Debug pos: 30>-user-defined exception ORA-06502: PL/SQL: numeric or value error: the error message above character string buffer too small is that the declared emp_tab nested table is based on the source table, therefore, the data buffer is too small. PL/SQL procedure successfully completed.2. The exception generated at bulk_error in the internal exception sets the code debugpos: = 30; comment out Scott @ cnmmbo> alter table emp_source add constraint ck_is_sync check (is_sync in ('C', 'E'); table altered. scott @ cnmmbo> @ test_bulk_ins_errerror MSG is: <err @ anonymity_plsql_block-Debug pos: 80>-ORA-02290: Check constraint (Scott. ck_is_sync) violatedpl/SQL procedure successfully completed. as the is_sync column of the source table has restrictions, only C or E values are allowed, while the bulk_error column is updated to N. Therefore, an error message is displayed. Let's take a look at err_log_tbl, the error messages generated when two source tables are not inserted to the target table have been recorded in the log table Scott @ cnmmbo> select * From err_log_tbl; log_seq log_time sp_name table_name table_pk err_msg ------- --------- hour -------------- hour 21 11-aug-12 hour <err @ warn-Debug pos: 3 0>-user-defined exception ORA-06502: PL/sq l: numeric or value error: character string B uffer too small 22 11-aug-12 anonymity_plsql_block <err @ anonymity_plsql_block-Debug pos: 8 0>-ORA-02290: Check constraint (Scott. ck_ I s_sync) violated 3. Exception generated by the forall clause during insert Scott @ cnmmbo> ALTER TABLE emp_source drop constraint ck_is_sync; --> Delete the check constraint table altered OF THE is_sync column on emp_source. scott @ cnmmbo> @ test_bulk_ins_err --> synchronize source table records to the target table PL/SQL procedure successfully completed. scott @ cnmmbo> select * From emp_dest; empno ename Sal ---------- 7369 Smith 800 7499 Allen 1600 7521 ward 1250 7566 Jones 2975 7654 Martin 1250 7698 Blake 2850 Clark 7782 2450 Scott 7788 34171.88 King 7839 5000 Turner 7844 1500 Adams 7876 1100 james 950 7902 Ford 3000 7934 Miller 130014 rows selected. scott @ cnmmbo> select * From emp_source; --> we can see that the target table already has data and the source table Status column is updated with empno ename Sal I -----------7369 Smith 800 y 7499 Allen 1600 y 7521 ward 1250 y 7566 Jones 2975 y 7654 Martin 1250 y 7698 Blake 2850 y 7782 Clark 2450 y 7788 Scott 34171.88 y 7839 King 5000 y 7844 Turner 1500 y 7876 Adams 1100 y 7900 James 950 y 7902 Ford 3000 y 7934 Miller 1300 Y14 rows selected. --> the following three records are added to the source table to simulate the exception Scott @ cnmmbo> insert into emp_source select 1111, 'robinson ', 2000, null from dual; 1 row created. scott @ cnmmbo> insert into emp_source select 2222, null, 1000, null from dual; 1 row created. scott @ cnmmbo> insert into emp_source select 3333, 'Jackson ', 100, null from dual; 1 row created. scott @ cnmmbo> commit; Commit complete. scott @ cnmmbo> @ test_bulk_ins_err --> when the record is executed again, it is found that only the record with empno 1111 is inserted, the target tables PL/SQL procedure successfully completed are not synchronized due to errors in both 2222 and 3333. scott @ cnmmbo> select * From emp_dest where empno in (1111,2222, 3333); empno ename Sal ---------- -------- 1111 Robinson 2000scott @ cnmmbo> select * From emp_where source empno in (1111,2222, 3333); empno ename Sal I -----------1111 Robinson 2000 y 2222 1000 N 3333 Jackson 100 N --> check the error log, records inserted to the target table are not successfully written to the log, which contains the table name and primary key. The error message Scott @ cnmmbo> select * From err_log_tbl corresponding to the insert operation; log_seq log_time sp_name table_name table_pk err_msg ------- --------- hour ---------------- hour 21 11-aug-12 hour <err @ warn-Debug pos: 3 0>-user-defined exception ORA-06502: PL/sq l: numeric or value error: character string B uffer too small 22 11-aug-12 anonymity_plsql_block <err @ anonymity_plsql_block-Debug pos: 8 0>-ORA-02290: Check constraint (Scott. ck_ I s_sync) violated 23 11-aug-12 ins_emp_dest emp_dest 2222 ORA-01400: cannot insert null into () 24 11-aug-12 ins_emp_dest emp_dest 3333 ORA-02290: Check constraint (.) violated

Iii. Summary
1. The above code successfully realizes the synchronization problem between data (different databases use dB link), and records the error information during synchronization to the log table
2. For multi-Table synchronization, the above method facilitates centralized error information management. The second is the use of the Status flag in the source table to facilitate the determination of the synchronization success of the corresponding records.
3. Combined with the forall and bulk collect into batch SQL methods, the limit clause is used to reduce memory overhead when a set is opened with a cursor.
4. The debugpos variable used to identify the abnormal location of the error is used to locate the location or the lines of code that cause the exception.
5. When handling error information, you can use to_char (emp_tab (SQL % bulk_exceptions (I). error_index). empno) to obtain the primary key value of the table.

Iv. More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan