Hi Tom,

thanks for your explanation but I have some further questions:

create table t(col1 number);

create unique index t_ix on t(col1);

insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(4);

commit;

begin
 dbms_errlog.create_error_log('T');
end;
/

Now I update a single row:

update t set col1=2 where col1=1 log errors into err$_t reject limit unlimited;

update t set col1=2 where col1=1 log errors into err$_t reject limit unlimited
*
ERROR at line 1:
ORA-00001: unique constraint (DIMITRI.T_IX) violated


select * from err$_t;

As you said, the error was not logged.
Now I update the whole table:

update t set col1=1 log errors into err$_t reject limit unlimited;

update t set col1=1 log errors into err$_t reject limit unlimited
*
ERROR at line 1:
ORA-00001: unique constraint (DIMITRI.T_IX) violated

select * from err$_t;

ORA_ERR_NUMBER$  ORA_ERR_MESG$                                         ORA_ERR_ROWID$     ORA_ERR_OPTYP$ COL1  
---------------- ----------------------------------------------------- ------------------ -------------- ----- 
1               ORA-00001: unique constraint (DIMITRI.T_IX) violated    AAADPqAAEAAAB/wAC U              1     
1               ORA-00001: unique constraint (DIMITRI.T_IX) violated    AAADPqAAEAAAB/wAD U              1  

Suddenly some errors are logged.

Also when I select the rowids from T I cannot find the values from ORA_ERR_ROWID$:

select rowid,col1 from t;

AAAM+oAAEAAAAH/AAA          1
AAAM+oAAEAAAAH/AAB          2
AAAM+oAAEAAAAH/AAC          3
AAAM+oAAEAAAAH/AAD          4

When I added another 4 rows into T oracle logged 6 rows, when T had 12 rows in it oracle logged 10 rows and so on.
Now what's the "secret" behind this behavior and what rowid was written into the ORA_ERR_ROWID$ column?
Is it the entry of the unique index or something like that?

Regards
Wolfgang

Ok. Thanks for clarification Tom

Regards Wolfgang

Hi Tom,
 I'm unable to understand this behavior :

SQL> create table emp nologging as select * from scott.emp;

Table created.

SQL> alter table emp
  2  add constraint pk_empno primary key(empno);

Table altered.

SQL> exec dbms_errlog.create_error_log('EMP');

PL/SQL procedure successfully completed.

SQL> desc err$_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 EMPNO                                              VARCHAR2(4000)
 ENAME                                              VARCHAR2(4000)
 JOB                                                VARCHAR2(4000)
 MGR                                                VARCHAR2(4000)
 HIREDATE                                           VARCHAR2(4000)
 SAL                                                VARCHAR2(4000)
 COMM                                               VARCHAR2(4000)
 DEPTNO                                             VARCHAR2(4000)

SQL> insert /*+ append */ into emp
  2  select * from scott.emp
  3  log errors reject limit unlimited;
insert /*+ append */ into emp
*
ERROR at line 1:
ORA-00001: unique constraint (SG365Y.PK_EMPNO) violated

SQL>  insert  into emp
  2  select * from scott.emp
  3* log errors reject limit unlimited
SQL> /

0 rows created.

SQL> select count(*) from err$_emp;

  COUNT(*)
----------
        14

So why does the error logging fail when the append hint is used while it works without it? It does not support direct path inserts? 

Thanks again

Tom, the link above is broken, can you fix it. I am interested into the reason for the "append" hint not working.

To access the link above, trim the bit after '.htm':

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm

Hi Tom,

I am facing problem while using LOG ERRORS clause in MERGE & INSERT statements. In my first Merge statement MigKey is not being recorded in error log table while it is there in both error log as well as in source table.

As a way-around of above I am logging MigKey value in Tab column. For Merge it is working fine but for Insert it is giving compilation error. So I am finding the scope of tables in Merge and Insert are inconsistent inside LOG ERRORS clause.

Please correct or help more on this problem.

Thanks and regards,

-- Script Output --
SQL> drop table stab;

Table dropped.

SQL>
SQL> drop table dtab;

Table dropped.

SQL>
SQL> create table stab ( id number (2), name varchar2(20), migkey number (3) );

Table created.

SQL>
SQL> create table dtab ( id number (2), name varchar2(10) );

Table created.

SQL>
SQL> alter table dtab add primary key (id);

Table altered.

SQL>
SQL> insert into dtab values (1,'Ram');

1 row created.

SQL>
SQL> insert into dtab values (2,'Sita');

1 row created.

SQL>
SQL> insert into stab values (1,'New name of Ram',501); <----- Name value is more than 10 char *******
1 row created.

SQL>
SQL> drop table errlogtab;

Table dropped.

SQL>
SQL> begin
2 dbms_errlog.create_error_log ('stab','errlogtab');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from dtab;

ID NAME
---------- ----------
1 Ram
2 Sita

SQL>
SQL> select * from stab;

ID NAME MIGKEY
---------- -------------------- ----------
1 New name of Ram 501

SQL>
SQL> merge into dtab d
2 using stab s
3 on (s.id=d.id)
4 when matched then
5 update set d.name=s.name
6 when not matched then
7 insert (id,name) values (s.id,s.name)
8 log errors into errlogtab('Error') reject limit unlimited; <------ FIRST MERGE

0 rows merged.

SQL>
SQL> select ora_err_number$, substr(ora_err_mesg$,1,10) as ora_err_mesg$, substr(ora_err_tag$,1,10) as ora_err_tag$
2 ,substr(id,1,5) as id ,substr(name,1,30) as name,substr(migkey,1,5) as migkey
3 from errlogtab;

ORA_ERR_NUMBER$ ORA_ERR_ME ORA_ERR_TA ID NAME MIGKE
--------------- ---------- ---------- ----- ------------------------------ -----
12899 ORA-12899: Error 1 New name of Ram

SQL>
SQL> merge into dtab d
2 using stab s
3 on (s.id=d.id)
4 when matched then
5 update set d.name=s.name
6 when not matched then
7 insert (id,name) values (s.id,s.name)
8 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited; <----- SECOND MERGE

0 rows merged.

SQL>
SQL> select ora_err_number$, substr(ora_err_mesg$,1,10) as ora_err_mesg$, substr(ora_err_tag$,1,10) as ora_err_tag$
2 ,substr(id,1,5) as id ,substr(name,1,30) as name,substr(migkey,1,5) as migkey
3 from errlogtab;

ORA_ERR_NUMBER$ ORA_ERR_ME ORA_ERR_TA ID NAME MIGKE
--------------- ---------- ---------- ----- ------------------------------ -----
12899 ORA-12899: Error 1 New name of Ram
12899 ORA-12899: MigKey=501 1 New name of Ram

SQL>
SQL> insert into dtab (id,name)
2 select id,name from stab s
3 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited;
log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited
*
ERROR at line 3:
ORA-00904: "S"."MIGKEY": invalid identifier


SQL>

1 row created.

SQL>
SQL> drop table errlogtab;

Table dropped.

SQL>
SQL> begin
2 dbms_errlog.create_error_log ('stab','errlogtab');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from dtab;

ID NAME
---------- ----------
1 Ram
2 Sita

SQL>
SQL> select * from stab;

ID NAME MIGKEY
---------- -------------------- ----------
1 New name of Ram 501

SQL>
SQL> merge into dtab d
2 using stab s
3 on (s.id=d.id)
4 when matched then
5 update set d.name=s.name
6 when not matched then
7 insert (id,name) values (s.id,s.name)
8 log errors into errlogtab('Error') reject limit unlimited; <----------- FIRST MERGE

0 rows merged.

SQL>
SQL> select ora_err_number$, substr(ora_err_mesg$,1,10) as ora_err_mesg$, substr(ora_err_tag$,1,10) as ora_err_tag$
2 ,substr(id,1,5) as id ,substr(name,1,30) as name,substr(migkey,1,5) as migkey
3 from errlogtab;

ORA_ERR_NUMBER$ ORA_ERR_ME ORA_ERR_TA ID NAME MIGKE
--------------- ---------- ---------- ----- ------------------------------ -----
12899 ORA-12899: Error 1 New name of Ram

SQL>
SQL> merge into dtab d
2 using stab s
3 on (s.id=d.id)
4 when matched then
5 update set d.name=s.name
6 when not matched then
7 insert (id,name) values (s.id,s.name)
8 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited; <--- SECOND MERGE

0 rows merged.

SQL>
SQL> select ora_err_number$, substr(ora_err_mesg$,1,10) as ora_err_mesg$, substr(ora_err_tag$,1,10) as ora_err_tag$
2 ,substr(id,1,5) as id ,substr(name,1,30) as name,substr(migkey,1,5) as migkey
3 from errlogtab;

ORA_ERR_NUMBER$ ORA_ERR_ME ORA_ERR_TA ID NAME MIGKE
--------------- ---------- ---------- ----- ------------------------------ -----
12899 ORA-12899: Error 1 New name of Ram
12899 ORA-12899: MigKey=501 1 New name of Ram

SQL>
SQL> insert into dtab (id,name)
2 select id,name from stab s
3 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited;
log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited
*
ERROR at line 3:
ORA-00904: "S"."MIGKEY": invalid identifier


SQL>

Sorry I am not able to post full contents in a single post. To summarise below MERGE's LOG ERRORS clause can see source table data but in INSERT it can't.

Thanks and regards,

SQL> merge into dtab d
2 using stab s
3 on (s.id=d.id)
4 when matched then
5 update set d.name=s.name
6 when not matched then
7 insert (id,name) values (s.id,s.name)
8 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited;

0 rows merged.

Error table data
ORA_ERR_NUMBER$ ORA_ERR_ME ORA_ERR_TA ID NAME MIGKE
--------------- ---------- ---------- ----- ------------------------------ -----
12899 ORA-12899: Error 1 New name of Ram
12899 ORA-12899: MigKey=501 1 New name of Ram



SQL> insert into dtab (id,name)
2 select id,name from stab s
3 log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited;
log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited
*
ERROR at line 3:
ORA-00904: "S"."MIGKEY": invalid identifier

ops$tkyte%ORA11GR1> merge into dtab d
  2  using    stab s
  3  on (s.id=d.id-0.1)
  4  when not matched then
  5   insert (id,name) values (s.id,s.name)
  6  log errors into errlogtab('MigKey='||to_char(s.migkey)) reject limit unlimited;

0 rows merged.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec print_table( 'select * from errlogTab' );
.ORA_ERR_NUMBER$              : 12899
.ORA_ERR_MESG$                : ORA-12899: value too large for column "OPS$TKYTE"."DTAB"."NAME" (actual: 15, maximum: 10)

.ORA_ERR_ROWID$               :
.ORA_ERR_OPTYP$               : I
.ORA_ERR_TAG$                 : MigKey=501
.ID                           : 1
.NAME                         : New name of Ram
.MIGKEY                       :
-----------------

PL/SQL procedure successfully completed.

Dear Tom,

Could you please suggest how to workaround this case:
I have a table with unique constraint, and two (multiple, actually) sessions insert into the table. The key values, which each session is trying to insert, can overlap across sessions. My goal is to make a session able to insert non-overlapping values.

Test case:

Session 1

create table t1 (f number unique);

insert into t1
select level l from dual connect by level <= 5;

Session 2

insert into t1
select level l from dual connect by level <= 7;

--Session 2 is waiting...

Session 1

commit;

Session 2

SQL> insert into t1
  2  select level l from dual connect by level <= 7;
insert into t1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS_C00262572) violated

In this case I want Session2 to insert two rows (6,7) if Session1 commits its transaction, and all rows (1-7) if Session1 rollbacks...

What is better solution? - AQ (but this will be serialized!)?

- or my "handmade" solution:
I create a common table T2 (without constraints), into which all sessions will insert their data. A special dedicated job will constantly select distinct rows from T2, insert them into T1 and delete these rows from T2.

- or something else?

*** Taking into account possible huge workload (many sessions and each session has many rows to try to insert), could you please point out what I should consider more closely from performace perspective?

Thank you!

it works like this:
It's a graph creation mechanism - I have a table G which represents a graph. Each row is an edge with vertices. And each edge must be unique - I don't want to have duplicated edges.

There are source tables, where transactional data is stored:
T_SOURCE1 with rows
A
B
C

and T_SOURCE2 with rows
D
E
F

Based on some criteria, each session creates edges. Suppose, Session1 found that A and D fit the session's criteria, so it forms the edge A-D.
At the same time, Session2, having other (but similar) criteria, finds that A-D and B-E edges must be created.
Therefore, I want these edges to be in G:
A-D
B-E


BTW, I realised, that my "handmade" solution must be serialized as well... so, - AQ?

Well, it seems I found the solution:

A session, before inserting records from source tables, will SELECT FOR UPDATE these rows (so, candidate records in source tables will be locked). And then will INSERT WHERE source_table_rows NOT IN (select rows from G).
So easy and standard...

Therefore, it becomes:

-- this is existing source table
create table source_table 
as select level L from dual connect by level <= 10;

--Session 1

SELECT * 
  FROM source_table 
 WHERE L BETWEEN 1 AND 7
   FOR UPDATE;

INSERT INTO t1
select L from source_table where L between 1 and 7
 WHERE L NOT IN (SELECT f FROM t1);

--Session 2

-- we want to insert some rows, which might be ovelapping 
-- with another session. In this case we want only new (non-overlapping) rows
-- to be inserted
SELECT * 
  FROM source_table 
 WHERE L BETWEEN 4 AND 10
   FOR UPDATE;

--Session 2 is waiting...


--Session 1

commit;

--Session 2

-- now we can safely insert, and only new rows will appear
-- in target table
INSERT INTO t1
select L from source_table where L between 4 and 10
 WHERE L NOT IN (SELECT f FROM t1);

Done! :-)

Still a little bit of serialization, but only where necessary.

well... actually, LOG ERRORS works on inserts, which is exactly what I need... :-)
I missed the point that it doesn't work on Direct Path inserts, but it does for conventional inserts.

It might be that the solution for your problem is the hint
ignore_row_on_dupkey_index((id)) if you are in 11gR2


SQL> CREATE TABLE t (ID NUMBER PRIMARY KEY, text VARCHAR2(10));

Table created.

SQL> INSERT INTO t
  2     SELECT     ROWNUM, 'Test DML'
  3           FROM DUAL
  4     CONNECT BY LEVEL <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> INSERT  /
  2          ignore_row_on_dupkey_index((id))
  3         */
  4   INTO t
  5     SELECT ROWNUM, 'unique ig'
  6       FROM DUAL
  7   CONNECT BY LEVEL <= 13
  8  LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;

3 rows created.

Best Regards

Dear Tom,

Are you aware that DML error logging doesn't work correctly with after statement triggers?

It is possible that you will told me that this is a normal behaviour but i decided to show you the following example

sql> CREATE TABLE t (ID NUMBER PRIMARY KEY, text VARCHAR2(100));

Table created.

sql> BEGIN
  2    DBMS_ERRLOG.create_error_log (dml_table_name => 't');
  3  END;
  4  /

PL/SQL procedure successfully completed.

sql>> INSERT INTO t
  2     SELECT     ROWNUM, 'Test DML'
  3           FROM DUAL
  4     CONNECT BY LEVEL <= 10
  5     LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;

10 rows created.

sql> commit;

Commit complete.

sql> CREATE OR REPLACE TRIGGER t_trg
  2     AFTER INSERT
  3     ON t
  4  DECLARE
  5     my_exception   EXCEPTION;
  6  BEGIN
  7     NULL;
  8     RAISE my_exception;
  9  EXCEPTION
 10     WHEN my_exception
 11     THEN
 12        DBMS_OUTPUT.put_line ('In My_exception ');
 13        RAISE;
 14  END;
 15  /

Trigger created.

sql> set serveroutput on
sql> INSERT INTO t
  2     SELECT     11, 'Test DML after Statement Trigger'
  3           FROM DUAL
  4     LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;
In My_exception                                                                 
   LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED
                   *
ERROR at line 4:
ORA-06510: PL/SQL: unhandled user-defined exception 
ORA-06512: at "T_TRG", line 10 
ORA-04088: error during execution of trigger 'T_TRG' 


sql>    select count(1) from err$_t;

  COUNT(1)                                                                      
----------                                                                      
         0                                                                      

sql> rollback;

Rollback complete.

sql> CREATE OR REPLACE TRIGGER t_trg
  2     AFTER INSERT
  3     ON t
  4     FOR EACH ROW
  5  DECLARE
  6     my_exception   EXCEPTION;
  7  BEGIN
  8     NULL;
  9     RAISE my_exception;
 10  EXCEPTION
 11     WHEN my_exception
 12     THEN
 13        DBMS_OUTPUT.put_line ('In My_exception ');
 14        RAISE;
 15  END;
 16  /

Trigger created.

sql> INSERT INTO t
  2     SELECT     11, 'Test DML after Statement Trigger'
  3           FROM DUAL
  4     LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;
In My_exception                                                                 

0 rows created.
                                                                                

sql>> select count(1) from err$_t;

  COUNT(1)                                                                      
----------                                                                      
         1                                                           

We know that DML error logging works for ordinary inserts (Case #1 below), but not APPEND (direct-path) (Case #2 below), as is documented.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#sthref9460

Unless as my co-worker, Tom Harding, suggests -- we (a) add a trick column with an "ordinary" check constraint, and (b) tell Oracle which records to ignore. See Case #3:

SCRIPT:
drop table t purge;
drop table err$_t purge;
create table t ( pk number primary key, other number );
exec dbms_errlog.create_error_log('T');
-- CASE ONE : ordinary DML
insert into t
select 1, level as l from dual
connect by level < 10
log errors into err$_t reject limit unlimited;
-- CASE TWO : direct-path
truncate table t;
truncate table err$_t;
insert /*+ APPEND */ into t
select 1, level as l from dual
connect by level < 10
log errors into err$_t reject limit unlimited;
-- CASE THREE : TOM'S FAKE-OUT
truncate table t;
truncate table err$_t;
alter table t add r# integer constraint "ORA-00001" check(r#=1);
alter table err$_t add r# integer;
alter table t drop primary key;
alter table t add constraint t_pk primary key ( pk, r# );
insert /*+ APPEND */ into t
select pk
, other
, row_number() OVER (partition by pk
order by other /*anything*/) r#
from (
select 1 as pk, level as other from dual
connect by level < 10
)
log errors into err$_t reject limit unlimited;
select ORA_ERR_NUMBER$
, ORA_ERR_MESG$
, r#
from err$_t;


RESULTS:
CASE #1:

1 row created.

CASE #2:

ERROR at line 1:
ORA-00001: unique constraint (scott.SYS_C001718695) violated

CASE #3:

1 row created.

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------------
----------------------------------------------------
R#
----------
2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
2

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
3

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
4

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
5

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
6

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
7

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
8

2290
ORA-02290: check constraint (CIDWWRITE.ORA-00001) violated
9


8 rows selected.