Insert into select batch loading error Solution

Insert into select batch loading error solution when using insert into select to batch load data, the entire insert statement cannot be executed because some data does not meet the loading conditions, all rollback. In this case, you can use the DML Error Log feature to solve this problem. Www.2cto.com
You only need to create a log table and add the dml error logging statement when using the dml statement to record the error rows to the log table without affecting the data already loaded into the table. Finally, correct the data that cannot be loaded.
The procedure is as follows: 1. You can create a log table using the DBMS_ERRLOG package or manually create a log table. If you use dbms_errlog.create_error_log to create a log table, all columns in the source table are added to the columns to be recorded by default. 2. Execute insert and add the error logging statement. 3. query the log table and modify the data that cannot be loaded. Under www.2cto.com, an example of batch loading insert into select is provided: Step 1: create a log table and create a test table target_t [SQL] create table target_t (id number (4), namevarchar2 (2000 )); create source table source_t [SQL] create table source_t as select level as id, 'name' | level as name from dual connect by level <= 10004; use the stored procedure to create error logging table target_err_t use DBMS_ERRLOG.CREATE_ERROR_LOG to automatically complete all columns in the table to the log table. (The table name is case-insensitive. It seems that OracleDabase is developing in a better direction.) [SQL] EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG (dml_table_name => 'target _ t ', err_log_table_name => 'target _ err_log '); check the table structure [SQL] dexter @ ORCL> desc target_err_log; Name Null? Type primary ORA_ERR_NUMBER $ NUMBER ORA_ERR_MESG $ VARCHAR2 (2000) ORA_ERR_ROWID $ ROWID primary $ VARCHAR2 (2) ORA_ERR_TAG $ VARCHAR2 (2000) ID VARCHAR2 (4000) NAME VARCHAR2 (4000) step 2: Execute insert. Here, the error logging statement log errors into target_err_log ('daily _ load') reject limit 4 is added. The target_err_log is the name of the error LOG table, and the 'daily _ load' is added' Corresponds to ORA_ERR_TAG $ in the log table, which is convenient for distinguishing each operation. 4 In reject limit 4 indicates that up to four errors can be encountered. However, a maximum of five records can be inserted in the error log table (because only the fifth error is aborted ). In addition, the insert operation and the log table operation are not in the same transaction, that is, the current insert operation commit and rollback will not affect the records in the log table. [SQL] insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily _ load') REJECT LIMIT 4; * ERROR at line 1: ORA-01438: value larger than specifiedprecision allowed for this column col ORA_ERR_NUMBER $ for 999999 col usage $ for a40 col ORA_ERR_ROWID $ for a10 col usage $ for a10 col ORA_ERR_TAG $ for a20 col id for a20 col name a20 set lines 150 dexter @ ORCL> sel Ect * from target_err_log; ORA_ERR_NUMBER $ items $ export ORA_ERR_TAG $ id name indication ---------- too large limit 1438 ORA-01438: value larger than specified p I daily_load 10000 name10000 re1_allowed for this column 1438 ORA-01438: value larger than specified p I dail Y_load 10001 name10001 re1_allowed for this column 1438 ORA-01438: value larger than specified p I daily_load 10002 name10002 re1_allowed for this column 1438 ORA-01438: value larger than specified p I daily_load 10003 name10003 remo-allowed for this column 1438 ORA-01438: value larger than specified p I daily_load 10004 name10004 remo-allowed for this column A total of five pieces of data that cannot be loaded, complete records [SQL] insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily _ load2') REJECT LIMIT 5; 9999 rows created. dexter @ ORCL> select count (*) from target_t; COUNT (*) ---------- 9999 supports rollback dexter @ ORCL> rollback; Rollback complete. dexter @ ORCL> select count (*) from target_t; COUNT (*) ---------- 0 dexter @ ORCL> insert into target_t select * from source_t log errors into target _ Err_log ('daily _ load2') reject limit 5; 9999 rows created. dexter @ ORCL> commit; Commit complete. dexter @ ORCL> select count (*) from target_t; COUNT (*) ---------- 9999 dexter @ ORCL> select count (*) from target_t; COUNT (*) ---------- 15 I will not talk about modifying data in step 3. Dml error logging also supports UPDATE, MERGE, and DELETE statements. Interpretation of fields in the appendix log table Column Name Data Type DescriptionORA_ERR_NUMBER $ NUMBER Oracle error numberORA_ERR_MESG $ VARCHAR2 (2000) Oracle errormessage textORA_ERR_ROWID $ ROWID Rowid of the row inerror (for update and delete) ORA_ERR_OPTYP $ VARCHAR2 (2) Type ofoperation: insert (I), update (U), delete (D) Note: errors from the update clause and insert clause of a MERGEoperation are distinguished by the Uand Ivalues. ORA_ERR_TAG $ VARCHAR2 (2000) Value of thetag supplied by the user in the error logging clause