问题描述
当我们尝试在分区表中插入数据时使用DML错误日志记录时，我们面临一些问题。
在我们的程序中，我们从某些目标表中加载来自不同表的数据。在我们的一个程序中，我们在一个表中插入数据
• 是分区表
拥有一个唯一的索引
·对一个或多个列没有null约束
当前，在将数据插入最终表之前，我们不会删除违反NULL/唯一约束的记录。当我们尝试插入将违反NULL约束或唯一约束的记录时，我们会在错误日志表中获得一些重复项。
例如。如果我们有2条违反NULL约束的记录和一条违反唯一约束的记录，那么错误日志表应该有3条记录。但是这里我们有4或5条记录。
Oracle版本详细信息-
Oracle数据库11g企业版版本11.2.0.3.0-64位生产PL/SQL版本11.2.0.3.0-生产核心11.2.0.3.0生产
以下是创建表以复制此问题的示例查询
我知道我们可以解决tmp_table本身的这些问题，并删除导致问题的记录。但是我想知道这里出了什么问题？只有当我们在分区表上使用它时，才会发生这种情况。如果我们从最终表中删除分区，问题将得到解决。
我们在许多程序中遵循类似的逻辑和表/索引设置，我想避免修改这些程序。是预期的行为吗？
在一个oracle论坛中，我看到错误表中的唯一约束对于直接路径插入的工作方式不同。我相信我不是在这里直接插入路径。
谢谢，
德文德拉
在我们的程序中，我们从某些目标表中加载来自不同表的数据。在我们的一个程序中，我们在一个表中插入数据
• 是分区表
拥有一个唯一的索引
·对一个或多个列没有null约束
当前，在将数据插入最终表之前，我们不会删除违反NULL/唯一约束的记录。当我们尝试插入将违反NULL约束或唯一约束的记录时，我们会在错误日志表中获得一些重复项。
例如。如果我们有2条违反NULL约束的记录和一条违反唯一约束的记录，那么错误日志表应该有3条记录。但是这里我们有4或5条记录。
Oracle版本详细信息-
Oracle数据库11g企业版版本11.2.0.3.0-64位生产PL/SQL版本11.2.0.3.0-生产核心11.2.0.3.0生产
以下是创建表以复制此问题的示例查询
/* Sample table & index creation -- START */
drop table tmp_table purge;
create table tmp_table
(
CREATE_DT date
,REC_SEQ_NBR number( 12 )
,REC_TYP_CD varchar2( 3 byte )
,EMP_NBR number( 6 )
,DEPT_NBR number( 8 )
);
-- create final table with partitions to insert data into it
drop table tmp_final_table purge;
create table tmp_final_table
(
CREATE_DT date not null
,REC_SEQ_NBR number( 12 ) not null
,REC_TYP_CD varchar2( 3 byte ) not null
,EMP_NBR number( 6 )
,DEPT_NBR number( 8 )
)
nocompress
result_cache (mode default)
pctused 0
pctfree 5
initrans 1
maxtrans 255
storage( buffer_pool default flash_cache default cell_flash_cache default )
partition by range
(create_dt)
(
partition
PM201603
values less than
(to_date( ' 2016-03-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
,partition
PM201606
values less than
(to_date( ' 2016-06-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
,partition
PM201609
values less than
(to_date( ' 2016-09-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
) )
nocache
noparallel
monitoring;
-- create a primary key
create unique index tmp_final_table_pk
on dkharwandikar.tmp_final_table( create_dt, rec_seq_nbr )
logging
tablespace ALL_DATA_4M_01_I
pctfree 10
initrans 2
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
noparallel;
;
/* Sample table & index creation -- END */
/* PLSQL block to insert data in the final table - START */
begin
insert /*+ parallel(6) */
into tmp_final_table( create_dt
,REC_SEQ_NBR
,REC_TYP_CD
,EMP_NBR
,DEPT_NBR
)
select create_dt
,REC_SEQ_NBR
,REC_TYP_CD
,emp_nbr
,dept_nbr
from tmp_table
log errors into err$_tmp_final_table ( 'INSERT' )
reject limit unlimited;
commit;
end;
/* PLSQL block to insert data in the final table - END */
Below are the different scenarios and the results (Please truncate/ drop & create the tables before you test each of the
-- Case 1- Few records violating UNIQUE constraint and few records violating NULL constraint
-- Result- For each record violating NULL constraint, we can see 2 records in the error log table.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some records with REC_TYP_CD value as NULL. This is having NULL constraint on final table
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 11, null, 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, null, 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 12, null, 3, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
COMMIT;
--
-- Case 2- No record violating NULL constraint; few records with UNIQUE constraint violation
-- Result- For first record causing duplicate, we can see 2 records in the error log table. So, in below example, we should get 3 records in error log but we get 4 records.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
COMMIT;
--
-- Case 3- Try to insert duplicate records by running insert query twice.
-- Result- For first run, it will insert all the data & in 2nd run it will insert all records in error log. In error log you can see one additional record is present.
-- Use below data set to test this scenario
-- In below example, in 2nd run, we should get 10 records in error log. But we get 11 records in error log.
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
COMMIT;
--
-- Case 4- Few records violating NULL constraint; no records with UNIQUE constraint violation
-- Result- For each record violating NULL constraint we can see 1 records in the error log table.
-- No Issue
--
-- Case 4- Use any of the scenario (case 1, 2 or 3) data setup with CURSOR FORALL-BULK COLLECT- INSERT
-- Result- For each record violating constraint we can see 1 records in the error log table.
-- No Issue
-- code to try this scenario
declare
type t_create_dt is table of tmp_final_table.create_dt%type
index by binary_integer;
v_create_dt t_create_dt;
type t_rec_seq_nbr is table of tmp_final_table.rec_seq_nbr%type
index by binary_integer;
v_rec_seq_nbr t_rec_seq_nbr;
type t_rec_typ_cd is table of tmp_final_table.rec_typ_cd%type
index by binary_integer;
v_rec_typ_cd t_rec_typ_cd;
type t_emp_nbr is table of tmp_final_table.emp_nbr%type
index by binary_integer;
v_emp_nbr t_emp_nbr;
type t_dept_nbr is table of tmp_final_table.dept_nbr%type
index by binary_integer;
v_dept_nbr t_dept_nbr;
type t_cmd_cur is ref cursor;
cmd_cur t_cmd_cur;
v_sql_stmt varchar2 ( 2500 ) := null;
n_commit_cnt number := 2;
n_tot_rec_load_cnt number := 0;
begin
v_sql_stmt := ' select create_dt
,rec_seq_nbr
,rec_typ_cd
,emp_nbr
,dept_nbr
from tmp_table ';
open cmd_cur for v_sql_stmt;
loop
fetch cmd_cur
bulk collect into v_create_dt
,v_rec_seq_nbr
,v_rec_typ_cd
,v_emp_nbr
,v_dept_nbr
limit n_commit_cnt;
if cmd_cur%rowcount > n_tot_rec_load_cnt
then
forall j in v_create_dt.first .. v_create_dt.last
insert
into tmp_final_table ( create_dt
,rec_seq_nbr
,rec_typ_cd
,emp_nbr
,dept_nbr
)
values (
v_create_dt ( j )
,v_rec_seq_nbr ( j )
,v_rec_typ_cd ( j )
,v_emp_nbr ( j )
,v_dept_nbr ( j ) )
log errors into err$_tmp_final_table ( 'INSERT' )
reject limit unlimited;
n_tot_rec_load_cnt := n_tot_rec_load_cnt + sql%rowcount;
end if;
commit;
exit when cmd_cur%notfound;
end loop;
close cmd_cur;
commit;
end;
我知道我们可以解决tmp_table本身的这些问题，并删除导致问题的记录。但是我想知道这里出了什么问题？只有当我们在分区表上使用它时，才会发生这种情况。如果我们从最终表中删除分区，问题将得到解决。
我们在许多程序中遵循类似的逻辑和表/索引设置，我想避免修改这些程序。是预期的行为吗？
在一个oracle论坛中，我看到错误表中的唯一约束对于直接路径插入的工作方式不同。我相信我不是在这里直接插入路径。
谢谢，
德文德拉
专家解答
我不确定我是否在遵循您的要求-但这是我的脚本/输出以进行比较
当然，在进行唯一检查时，请不要忘记对dml错误日志记录的文档限制。
“DML错误日志记录的限制
以下情况导致语句失败并回滚，而无需调用错误日志记录功能:
违反了延迟约束。
引发唯一约束或索引违规的任何直接路径插入或合并操作。”
http://docs.oracle.com/database/122/SQLRF/INSERT.htm#SQLRF01604
SQL>
SQL>
SQL> drop table tmp_table purge;
Table dropped.
SQL> create table tmp_table
2 (
3 CREATE_DT date
4 ,REC_SEQ_NBR number( 12 )
5 ,REC_TYP_CD varchar2( 3 byte )
6 ,EMP_NBR number( 6 )
7 ,DEPT_NBR number( 8 )
8 );
Table created.
SQL>
SQL> drop table tmp_final_table purge;
Table dropped.
SQL> create table tmp_final_table
2 (
3 CREATE_DT date not null
4 ,REC_SEQ_NBR number( 12 ) not null
5 ,REC_TYP_CD varchar2( 3 byte ) not null
6 ,EMP_NBR number( 6 )
7 ,DEPT_NBR number( 8 )
8 )
9 nocompress
10 result_cache (mode default)
11 pctused 0
12 pctfree 5
13 initrans 1
14 maxtrans 255
15 storage( buffer_pool default flash_cache default cell_flash_cache default )
16 partition by range
17 (create_dt)
18 (
19 partition
20 PM201603
21 values less than
22 (to_date( ' 2016-03-01 00:00:00'
23 ,'SYYYY-MM-DD HH24:MI:SS'
24 ,'NLS_CALENDAR=GREGORIAN'
25 ))
26 ,partition
27 PM201606
28 values less than
29 (to_date( ' 2016-06-01 00:00:00'
30 ,'SYYYY-MM-DD HH24:MI:SS'
31 ,'NLS_CALENDAR=GREGORIAN'
32 ))
33 ,partition
34 PM201609
35 values less than
36 (to_date( ' 2016-09-01 00:00:00'
37 ,'SYYYY-MM-DD HH24:MI:SS'
38 ,'NLS_CALENDAR=GREGORIAN'
39 ))
40 )
41 nocache
42 noparallel
43 monitoring;
Table created.
SQL>
SQL> create unique index tmp_final_table_pk
2 on tmp_final_table( create_dt, rec_seq_nbr )
3 ;
Index created.
SQL>
SQL> --exec dbms_errlog.create_error_log('tmp_final_table');
SQL>
SQL> truncate table tmp_table;
Table truncated.
SQL> truncate table tmp_final_table;
Table truncated.
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 11, null, 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, null, 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 12, null, 3, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> COMMIT;
Commit complete.
SQL> truncate table err$_tmp_final_table ;
Table truncated.
SQL>
SQL> insert /*+ append */
2 into tmp_final_table( create_dt
3 ,REC_SEQ_NBR
4 ,REC_TYP_CD
5 ,EMP_NBR
6 ,DEPT_NBR
7 )
8 select create_dt
9 ,REC_SEQ_NBR
10 ,REC_TYP_CD
11 ,emp_nbr
12 ,dept_nbr
13 from tmp_table
14 log errors into err$_tmp_final_table ( 'INSERT' )
15 reject limit unlimited;
insert /*+ append */
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select * from err$_tmp_final_table ;
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------------------------------------------------------------------
ORA_ERR_ROWID$
----------------------------------------------------------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
----------------------------------------------------------------------------------------------------------------------------------
CREATE_DT
----------------------------------------------------------------------------------------------------------------------------------
REC_SEQ_NBR
----------------------------------------------------------------------------------------------------------------------------------
REC_TYP_CD
----------------------------------------------------------------------------------------------------------------------------------
EMP_NBR
----------------------------------------------------------------------------------------------------------------------------------
DEPT_NBR
----------------------------------------------------------------------------------------------------------------------------------
1400
ORA-01400: cannot insert NULL into ("MCDONAC"."TMP_FINAL_TABLE"."REC_TYP_CD")
I
INSERT
19-APR-16
11
1
10
1400
ORA-01400: cannot insert NULL into ("MCDONAC"."TMP_FINAL_TABLE"."REC_TYP_CD")
I
INSERT
29-MAR-16
2
2
10
1400
ORA-01400: cannot insert NULL into ("MCDONAC"."TMP_FINAL_TABLE"."REC_TYP_CD")
I
INSERT
13-DEC-15
12
3
10
3 rows selected.
SQL>
SQL>
SQL>
SQL> --
SQL> -- Case 2- No record violating NULL constraint; few records with UNIQUE constraint violation
SQL> -- Result- For first record causing duplicate, we can see 2 records in the error log table. So, in below example, we should get 3 records in error log but we get 4 records.
SQL> -- Use below data set to test this scenario
SQL>
SQL> truncate table tmp_table;
Table truncated.
SQL>
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
1 row created.
SQL> -- insert some duplicates
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
1 row created.
SQL> COMMIT;
Commit complete.
SQL> truncate table err$_tmp_final_table ;
Table truncated.
SQL>
SQL> insert /*+ append */
2 into tmp_final_table( create_dt
3 ,REC_SEQ_NBR
4 ,REC_TYP_CD
5 ,EMP_NBR
6 ,DEPT_NBR
7 )
8 select create_dt
9 ,REC_SEQ_NBR
10 ,REC_TYP_CD
11 ,emp_nbr
12 ,dept_nbr
13 from tmp_table
14 log errors into err$_tmp_final_table ( 'INSERT' )
15 reject limit unlimited;
insert /*+ append */
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
SQL> commit;
Commit complete.
SQL>
SQL> select * from err$_tmp_final_table ;
no rows selected
SQL>
SQL> --
SQL> -- Case 3- Try to insert duplicate records by running insert query twice.
SQL> -- Result- For first run, it will insert all the data & in 2nd run it will insert all records in error log. In error log you can see one additional record is present.
SQL> -- Use below data set to test this scenario
SQL> -- In below example, in 2nd run, we should get 10 records in error log. But we get 11 records in error log.
SQL>
SQL> truncate table tmp_table;
Table truncated.
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
1 row created.
SQL> COMMIT;
Commit complete.
SQL> truncate table err$_tmp_final_table ;
Table truncated.
SQL> insert /*+ append */
2 into tmp_final_table( create_dt
3 ,REC_SEQ_NBR
4 ,REC_TYP_CD
5 ,EMP_NBR
6 ,DEPT_NBR
7 )
8 select create_dt
9 ,REC_SEQ_NBR
10 ,REC_TYP_CD
11 ,emp_nbr
12 ,dept_nbr
13 from tmp_table
14 log errors into err$_tmp_final_table ( 'INSERT' )
15 reject limit unlimited;
10 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> insert /*+ append */
2 into tmp_final_table( create_dt
3 ,REC_SEQ_NBR
4 ,REC_TYP_CD
5 ,EMP_NBR
6 ,DEPT_NBR
7 )
8 select create_dt
9 ,REC_SEQ_NBR
10 ,REC_TYP_CD
11 ,emp_nbr
12 ,dept_nbr
13 from tmp_table
14 log errors into err$_tmp_final_table ( 'INSERT' )
15 reject limit unlimited;
insert /*+ append */
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
SQL> commit;
Commit complete.
SQL>
SQL> select * from err$_tmp_final_table ;
no rows selected
SQL>
SQL> --
SQL> -- Case 4- Few records violating NULL constraint; no records with UNIQUE constraint violation
SQL> -- Result- For each record violating NULL constraint we can see 1 records in the error log table.
SQL> -- No Issue
SQL> --
SQL> -- Case 4- Use any of the scenario (case 1, 2 or 3) data setup with CURSOR FORALL-BULK COLLECT- INSERT
SQL> -- Result- For each record violating constraint we can see 1 records in the error log table.
SQL> -- No Issue
SQL> -- code to try this scenario
SQL>
SQL> truncate table tmp_table;
Table truncated.
SQL>
SQL> insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
1 row created.
SQL> insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> declare
2 type t_create_dt is table of tmp_final_table.create_dt%type
3 index by binary_integer;
4 v_create_dt t_create_dt;
5 type t_rec_seq_nbr is table of tmp_final_table.rec_seq_nbr%type
6 index by binary_integer;
7 v_rec_seq_nbr t_rec_seq_nbr;
8 type t_rec_typ_cd is table of tmp_final_table.rec_typ_cd%type
9 index by binary_integer;
10 v_rec_typ_cd t_rec_typ_cd;
11 type t_emp_nbr is table of tmp_final_table.emp_nbr%type
12 index by binary_integer;
13 v_emp_nbr t_emp_nbr;
14 type t_dept_nbr is table of tmp_final_table.dept_nbr%type
15 index by binary_integer;
16 v_dept_nbr t_dept_nbr;
17 type t_cmd_cur is ref cursor;
18 cmd_cur t_cmd_cur;
19 v_sql_stmt varchar2 ( 2500 ) := null;
20 n_commit_cnt number := 2;
21 n_tot_rec_load_cnt number := 0;
22 begin
23 v_sql_stmt := ' select create_dt
24 ,rec_seq_nbr
25 ,rec_typ_cd
26 ,emp_nbr
27 ,dept_nbr
28 from tmp_table ';
29 open cmd_cur for v_sql_stmt;
30 loop
31 fetch cmd_cur
32 bulk collect into v_create_dt
33 ,v_rec_seq_nbr
34 ,v_rec_typ_cd
35 ,v_emp_nbr
36 ,v_dept_nbr
37 limit n_commit_cnt;
38 if cmd_cur%rowcount > n_tot_rec_load_cnt
39 then
40 forall j in v_create_dt.first .. v_create_dt.last
41 insert
42 into tmp_final_table ( create_dt
43 ,rec_seq_nbr
44 ,rec_typ_cd
45 ,emp_nbr
46 ,dept_nbr
47 )
48 values (
49 v_create_dt ( j )
50 ,v_rec_seq_nbr ( j )
51 ,v_rec_typ_cd ( j )
52 ,v_emp_nbr ( j )
53 ,v_dept_nbr ( j ) )
54 log errors into err$_tmp_final_table ( 'INSERT' )
55 reject limit unlimited;
56
57 n_tot_rec_load_cnt := n_tot_rec_load_cnt + sql%rowcount;
58 end if;
59 commit;
60 exit when cmd_cur%notfound;
61 end loop;
62 close cmd_cur;
63 commit;
64 end;
65 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from err$_tmp_final_table ;
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------------------------------------------------------------------
ORA_ERR_ROWID$
----------------------------------------------------------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
----------------------------------------------------------------------------------------------------------------------------------
CREATE_DT
----------------------------------------------------------------------------------------------------------------------------------
REC_SEQ_NBR
----------------------------------------------------------------------------------------------------------------------------------
REC_TYP_CD
----------------------------------------------------------------------------------------------------------------------------------
EMP_NBR
----------------------------------------------------------------------------------------------------------------------------------
DEPT_NBR
----------------------------------------------------------------------------------------------------------------------------------
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
19-APR-16
1
AA
1
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
29-MAR-16
2
AA
2
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
13-DEC-15
3
AB
3
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
10-JAN-15
4
AA
4
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
09-JUN-14
5
AC
5
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
15-MAY-16
6
AA
6
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
18-FEB-15
7
DH
7
10
1
ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------------------------------------------------------------------------------
ORA_ERR_ROWID$
----------------------------------------------------------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
----------------------------------------------------------------------------------------------------------------------------------
CREATE_DT
----------------------------------------------------------------------------------------------------------------------------------
REC_SEQ_NBR
----------------------------------------------------------------------------------------------------------------------------------
REC_TYP_CD
----------------------------------------------------------------------------------------------------------------------------------
EMP_NBR
----------------------------------------------------------------------------------------------------------------------------------
DEPT_NBR
----------------------------------------------------------------------------------------------------------------------------------
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
12-FEB-11
8
XX
8
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
10-MAR-16
9
AB
9
10
1
ORA-00001: unique constraint (MCDONAC.TMP_FINAL_TABLE_PK) violated
I
INSERT
23-DEC-15
10
AA
10
10
10 rows selected.
SQL>
SQL>
当然，在进行唯一检查时，请不要忘记对dml错误日志记录的文档限制。
“DML错误日志记录的限制
以下情况导致语句失败并回滚，而无需调用错误日志记录功能:
违反了延迟约束。
引发唯一约束或索引违规的任何直接路径插入或合并操作。”
http://docs.oracle.com/database/122/SQLRF/INSERT.htm#SQLRF01604
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
