We are facing some issue when we try to use DML error logging while inserting data in a partitioned table.
In our programs, we load the data from different tables in some target tables. In one of our program, we are inserting data in a table which
•Is a partitioned table
•Have one unique index
•Have not null constraint for one or more columns
Currently we are not removing records violating NULL/ UNIQUE constraint before inserting data in the final table. When, we try to insert records which will violate the NULL constraint or UNIQUE constraint we get some duplicates in the error log table.
Ex. if we have 2 records violating NULL constraint and one record violating UNIQUE constraint then the error log table should have 3 records. But here we get 4 or 5 records.
Oracle version details-
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production
Below are the sample queries to create the tables to replicate this issue
/* 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;
I know that we can take care of these issues the tmp_table itself and remove the records causing problem. But I want to know what is going wrong here? This happens only when we use it on a partitioned table. If we remove the partitions from the final table, the problem will be resolved.
We are following similar logic and table/ index setup in many programs and I want to avoid modifying those programs. Is it the expected behavior?
In one of the oracle forums I see that unique constraint in error table works differently for direct path inserts. I believe I am not doing a direct path insert here.
Thanks,
Devendra
I'm not sure I'm following what you are asking - but here's my script/output for comparison
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>
And of course, dont forget the documented restrictions on dml error logging when it comes to unique checks.
"Restrictions on DML Error Logging
The following conditions cause the statement to fail and roll back without invoking the error logging capability:
Violated deferred constraints.
Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation."
http://docs.oracle.com/database/122/SQLRF/INSERT.htm#SQLRF01604