Hi Tom,

Just curious to know whats the benefit of using the multi table insert here. I tried the multi table inserts as well as standard inserts with the same results in the err$_t table.

scott@ORA11GLI> insert ALL
  2  into t ( x, y )
  3  values ( a, translate_field( b ) )
  4  log errors reject limit unlimited
  5  select rownum a, mod(rownum,10)+1 b
  6    from all_objects;

48520 rows created.

scott@ORA11GLI> select count(*) from err$_t;

  COUNT(*)
----------
      5391

scott@ORA11GLI> truncate table err$_t;

Table truncated.

scott@ORA11GLI> truncate table t;

Table truncated.

scott@ORA11GLI> insert into t ( x, y )
  2  select rownum, translate_field(mod(rownum,10)+1)
  3    from all_objects
  4  log errors reject limit unlimited ;

48520 rows created.

scott@ORA11GLI> select count(*) from err$_t;

  COUNT(*)
----------
      5391

Regards
Vinay

Forgot to mention, I tested this on Oracle 11g:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Regards
Vinay

ops$tkyte%ORA10GR2> select count(*) from err$_t;

  COUNT(*)
----------
         0

ops$tkyte%ORA10GR2> insert into t
  2  select rownum, translate_field( mod(rownum,10)+1 )
  3    from all_users
  4   log errors reject limit unlimited;

53 rows created.

ops$tkyte%ORA10GR2> select count(*) from err$_t;

  COUNT(*)
----------
         6

Thank you for your repsonse. I copied and pasted your example, and it worked for me too, so I was pretty confused at first.

I didn't realize it at the time that I posted the original question, but it turns out the having DISTINCT in the select statement is what is causing the problem. I need the DISTINCT to keep from getting duplicate rows from my query that does a join over 4 or 5 tables (although I'm guessing there may be a better way to do the join that doesn't require DISTINCT).

Does it make sense to you that adding DISTINCT to the select statement would change the behavior of log errors?

  SQL>  insert into t
  2  select distinct rownum, translate_field( mod(rownum,10)+1 )
  3  from all_users
  4* log errors reject limit unlimited
select distinct rownum, translate_field( mod(rownum,10)+1 )
                        *
ERROR at line 2:
ORA-20001: Unrecognized number passed in: 10
ORA-06512: at "NXEM.TRANSLATE_FIELD", line 10

Thanks again.

But isn't

SQL> insert into t ( x, y )
  2  select rownum, translate_field(mod(rownum,10)+1)
  3    from all_objects
  4  log errors reject limit unlimited ;

a tad weak in general (without rownum), since one cannot guarantee that translate_field() is calculated at row insert time - since the kernel could decide, say, to push translate_field() inside the select statement - or, say, materialize the result set before inserting "in bulk" [or, say again, partially materialize up to a block worth of rows, which would look like a nice optimization latch-wise ] ?

Actually, the OP already experienced something like this when S/He used "translate_field(col2)" and, probably, translate_field() was pushed "next to the source table old_table" - passing a dummy rownum "translate_field(col2, rownum)" could prevent the kernel from pushing, but not materializing.

Or is it documented somewhere that this is not the case for "log errors" statements ?

In constrast,

SQL> insert ALL
  2  into t ( x, y )
  3  values ( a, translate_field( b ) )
  4  log errors reject limit unlimited
  5  select rownum a, mod(rownum,10)+1 b
  6    from all_objects;

seems to convey much better the coder's intention to calculate translate_field() for each row, just before inserting - even if it would be nice to know whether the kernel is documented to be bound to honor this intention (and it is not free e.g. to rewrite the latter statement as the former one).

For the example that was provided, instead of using the function to do the translation / BR check, wouldn't it be cleaner to do the field translation via a case statement and keep the field validation as a check constraint?

CREATE TABLE T
(
X INTEGER NULL,
Y VARCHAR2(5 BYTE) NULL
);


ALTER TABLE T ADD (
CONSTRAINT TEST_BR
CHECK (x between 1 and 9));

exec dbms_errlog.create_error_log( 'T' );

insert /*+ append */into t ( x, y )
select mod(rownum,10)+1,
case
when mod(rownum,10)+1 between 1 and 5
then 'A'
when mod(rownum,10)+1 between 6 and 9
then 'B'
else 'C'
end as b
from all_objects
log errors reject limit unlimited;

I'm not sure how sophisticated the check constriants can be before they start slowing down the insert statement, or how sophisticated you need them to be to support you Business Logic, but at least this way you write less code, and can hopefully do a direct path load.

Hello Tom;

In the test case below, errors are logged when parallel dml is disabled, but when parallel dml is enabled, the execution breaks. Any thing I am missing here?

Thanks

create table table_01
as
select t1.OBJECT_ID, t1.OBJECT_NAME
from all_objects t1
where 1 = 2;

create table table_02
as
select t1.OBJECT_ID, t1.OBJECT_NAME
from all_objects t1
where rownum < 11;

-- The below stament will cause invalid number error.
alter table table_01 modify (object_name number);

begin DBMS_ERRLOG.CREATE_ERROR_LOG('table_01'); end;

insert /*+ APPEND PARALLEL(p) */ into table_01 p
select /*+ PARALLEL(t1) */  t1.OBJECT_ID, t1.OBJECT_NAME
from table_02 t1
log errors into err$_table_01('aaa') REJECT LIMIT UNLIMITED;

-- 0 rows created.

select * from err$_table_01 ;

-- 10 rows selected.

truncate table err$_table_01 ;

-- Table truncated.

alter session enable parallel dml;

-- Session altered.

-- Here comes the error. Should have only logged to error table. 
-- Instead puts some log on table and interrupts the operation.

insert /*+ APPEND PARALLEL(p) */ into table_01 p
select /*+ PARALLEL(t1) */  t1.OBJECT_ID, t1.OBJECT_NAME
from table_02 t1
log errors into err$_table_01('aaa') REJECT LIMIT UNLIMITED;

ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-01722: invalid number

And the underlying data itself is sometimes wrong when error logging is used with updates - no error is thrown though

So we are using only error logging with inserts, not updates

Hi Tom,

Is there any way of capturing all the check constraint violations at once? I was hoping DBMS_ERRLOG would do it, but it doesn't.

For example, in the below SQL, I'm deliberately violating two constraints. Is there a way to report both of them at once, or am I doomed to iterations of find the first violation, fixing, then running again to see if any more check constraints violated?...

10gR2: SCOTT > !cat test.sql
create table error_test
( WAVE_NUM NUMBER DEFAULT 1,
REG_NUM NUMBER,
FISH NUMBER CONSTRAINT FISH_CHK
CHECK ( FISH IN ( 1,2,3,4,5 )),
RED_MEAT NUMBER CONSTRAINT RED_MEAT_CHK
CHECK ( RED_MEAT IN ( 1,2,3,4,5 )),
CHICKEN NUMBER CONSTRAINT CHICKEN_1_CHK
CHECK ( CHICKEN IN ( 1,2,3,4,5 ))
);

begin
dbms_errlog.create_error_log('ERROR_TEST','ERR$_ERROR_TEST') ;
end;
/

insert into error_test
( reg_num, fish, red_meat, chicken )
values
( 1,88,61,1 )
log errors into ERR$_ERROR_TEST reject limit unlimited;

commit;

10gR2: SCOTT > @test

Table created.


PL/SQL procedure successfully completed.


0 rows created.


Commit complete.


ORA_ERR_NUMBER$ ORA_ERR_MESG$ REG_NUM
--------------- ---------------------------------------------------------------------- ----------
2290 ORA-02290: check constraint (SCOTT.FISH_CHK) violated 1

1 row selected.

... but what I'd really like is:

ORA_ERR_NUMBER$ ORA_ERR_MESG$ REG_NUM
--------------- ---------------------------------------------------------------------- ----------
2290 ORA-02290: check constraint (SCOTT.FISH_CHK) violated 1
2290 ORA-02290: check constraint (SCOTT.RED_MEAT_CHK) violated 1

2 rows selected.

ops$tkyte%ORA10GR2> create table error_test
  2  (   WAVE_NUM                  NUMBER     DEFAULT 1,
  3      REG_NUM                   NUMBER,
  4      FISH                      NUMBER     CONSTRAINT  FISH_CHK
  5                                               CHECK ( FISH                    IN ( 1,2,3,4,5 )) deferrable,
  6      RED_MEAT                  NUMBER     CONSTRAINT  RED_MEAT_CHK
  7                                               CHECK ( RED_MEAT                IN ( 1,2,3,4,5 )) deferrable,
  8      CHICKEN                   NUMBER     CONSTRAINT  CHICKEN_1_CHK
  9                                               CHECK ( CHICKEN                 IN ( 1,2,3,4,5 ))  deferrable
 10  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into error_test
  2  ( reg_num, fish, red_meat, chicken )
  3  values
  4    ( 1,88,61,1 )
  5  /
insert into error_test
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.RED_MEAT_CHK) violated


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set constraints all deferred;

Constraint set.

ops$tkyte%ORA10GR2> insert into error_test
  2  ( reg_num, fish, red_meat, chicken )
  3  values
  4    ( 1,88,61,1 )
  5  /

1 row created.

ops$tkyte%ORA10GR2> set constraint FISH_CHK immediate;
set constraint FISH_CHK immediate
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.FISH_CHK) violated


ops$tkyte%ORA10GR2> set constraint red_meat_chk immediate;
set constraint red_meat_chk immediate
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.RED_MEAT_CHK) violated


ops$tkyte%ORA10GR2> set constraint chicken_1_chk immediate;

Constraint set.

Hi Tom,

Thanks for your response. I agree that setting deferrable constraints would be a painful way of doing it.

I will either write a stored procedure or create a view to show the integrity violations. Unfortunately, both will require some coding, and will be rather ugly.

Another forum thread has some interesting feedback on complex constraints which I will also consider, although I think I'd run into the same issue with multiple check constraints not being reported if I used a Materilized view.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:21389386132607

I look forward to the day when Oracle release new functionality around check constraints.

Can't we use a multi-table insert?

INSERT FIRST 
WHEN (CHECK1 NOT IN (x,y,z) AND 
CHECK2 NOT IN (1,2,3) ) THEN INTO table1
(...) ELSE ....

It could be clunky with a lot of criteria, but it would allow you to check multiple constraints simultaneously without going row by row.

Any drawbacks that make this approach unworkable from a performance (or other) perspective?

 Scott@IRADSDB> DESC T;
  Name                                                                    Null?    Type
  ----------------------------------------------------------------------- -------- ---------
  X                                                                       NOT NULL NUMBER

 Scott@IRADSDB> CREATE OR REPLACE PACKAGE PKG1 AS
   2    PROCEDURE CREATE_ERROR_LOG;
   3    PROCEDURE DROP_ERROR;
   4  END;
   5  /

 Package created.

 Scott@IRADSDB> CREATE OR REPLACE PACKAGE BODY PKG1 AS
   2    PROCEDURE  CREATE_ERROR_LOG AS
   3    BEGIN
   4      dbms_errlog.create_error_log(dml_table_name=>'T');    <<<========
   5    END CREATE_ERROR_LOG;  
   6    
   7    PROCEDURE DROP_ERROR AS
   8    BEGIN
   9      EXECUTE IMMEDIATE ' DROP TABLE ERR$_T PURGE ';
  10    END;  
  11  END;  
  12  /

 Package body created.

 Scott@IRADSDB> exec pkg1.CREATE_ERROR_LOG;
 BEGIN pkg1.CREATE_ERROR_LOG; END;

 *
 ERROR at line 1:
 ORA-01031: insufficient privileges
 ORA-06512: at "SYS.DBMS_ERRLOG", line 258
 ORA-06512: at "SCOTT.PKG1", line 4
 ORA-06512: at line 1

 Scott@IRADSDB> begin
   2  dbms_errlog.create_error_log(dml_table_name=>'T');     <<<=========
   3  end;
   4  /

 PL/SQL procedure successfully completed.

 Scott@IRADSDB> desc err$_T;
  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)
  X                                                                                VARCHAR2(4000)

Tom,
The user SCOTT has the DBA privilege Granted to it.
While calling procedure DBMS_ERRLOG.CREATE_ERROR_LOG from Anonymous Blocks it is executed sucessfully.
But while Calling the procedure DBMS_ERRLOG.CREATE_ERROR_LOG from a package PKG1 i am getting the error ORA-01031: insufficient privileges.
please help me how to resolve this also why i am getting this error when invoking from packages.

Curious timing! I was just helping one of our developers with this issue yesterday, and wrote this:
http://it.toolbox.com/blogs/data-ruminations/ora01031-disappearing-privilege-disorder-33707

rajesh@IRADSDB> CREATE TABLE t_iot
  2    (
  3      X NUMBER PRIMARY KEY
  4    )
  5    organization INDEX;

Table created.

rajesh@IRADSDB> INSERT INTO t_iot
  2  SELECT level
  3  FROM dual
  4  CONNECT BY level <=5
  5  /

5 rows created.

rajesh@IRADSDB> commit;

Commit complete.

rajesh@IRADSDB> exec dbms_errlog.create_error_log(dml_table_name=>'T_IOT');

PL/SQL procedure successfully completed.

rajesh@IRADSDB> INSERT INTO t_iot
  2  SELECT level
  3  FROM dual
  4  CONNECT BY level <=5
  5  LOG ERRORS INTO ERR$_T_IOT REJECT LIMIT UNLIMITED;
INSERT INTO t_iot
*
ERROR at line 1:
ORA-00001: unique constraint (RAJESH.SYS_IOT_TOP_111191) violated

Tom,
Does IOT's doesnot support DML Error logging feature in 10g (Version Oracle 10gR2)? i verified the product docs. but no restrictions.
Can you plese help me.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2080505

ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ops$tkyte%ORA10GR2> create table t ( x number primary key ) organization index;

Table created.

ops$tkyte%ORA10GR2> insert into t select level from dual connect by level <= 5;

5 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec dbms_errlog.create_error_log( dml_table_name => 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> insert into t select level from dual connect by level <= 5
  2  log errors reject limit unlimited;

0 rows created.

Tom,

Here is my results. I think the problem is with 10.2.0.1 version. Please correct me if i am wrong.

rajesh@IRADSDB> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

rajesh@IRADSDB> create table t ( x number primary key ) organization index;

Table created.

rajesh@IRADSDB> insert into t select level from dual connect by level <= 5;

5 rows created.

rajesh@IRADSDB> commit;

Commit complete.

rajesh@IRADSDB> exec dbms_errlog.create_error_log( dml_table_name => 'T' );

PL/SQL procedure successfully completed.

rajesh@IRADSDB> insert into t select level from dual connect by level <= 5
  2  log errors reject limit unlimited;
insert into t select level from dual connect by level <= 5
*
ERROR at line 1:
ORA-00001: unique constraint (RAJESH.SYS_IOT_TOP_111216) violated

scott@10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

scott@10G> create table t ( x number primary key ) organization index;

Table created.

scott@10G> insert into t select level from dual connect by level <= 5;

5 rows created.

scott@10G> commit;

Commit complete.

scott@10G> exec dbms_errlog.create_error_log( dml_table_name => 'T' );

PL/SQL procedure successfully completed.

scott@10G> insert into t select level from dual connect by level <= 5
  2  log errors reject limit unlimited;
insert into t select level from dual connect by level <= 5
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_IOT_TOP_62719) violated

ir@IR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

ir@IR>      CREATE TABLE ADT_IOT
  2        (
  3          CK      VARCHAR2(1000) CONSTRAINT adt_dx_iot PRIMARY KEY,
  4          EK     NUMBER(10),
  5          DK    NUMBER(10),
  6          HD      VARCHAR2(10),
  7          CT      DATE
  8        ) ORGANIZATION INDEX OVERFLOW TABLESPACE TS_AUDIT;

ir@IR> EXEC dbms_errlog.create_error_log(dml_table_name=>'ADT_IOT',err_log_table_space=>'TS_AUDIT');

ir@IR>  INSERT INTO ADT_IOT
  2        (
  3         CK    ,
  4         EK    ,
  5         DK    ,
  6         HD    ,
  7         CT
  8        )
  9        
 10         SELECT
 11         NVL2(ENC.HD,ENC.HD||'_',ENC.HD)           ||
 12         NVL2(ENC.DK,ds.source_cd||'_',ENC.DK) ||
 13         NVL2(ENC.LK,lb.LD||'_','NA_')           ||
 14         NVL2(ENC.MN,ENC.MN||'_','NA_')         ||
 15         NVL2(ENC.MD,ENC.MD||'_','NA_')       ||
 16         ENC.ER                 ||'_'||
 17         dcd.dd                  AS CK      ,
 18         ENC.EK,
 19         aud.DK ,
 20         ENC.HD,
 21         SYSDATE
 22        FROM T1 AUD       ,
 23          T2 ENC        ,
 24          T3  ds      ,
 25          T4 lb     ,
 26          T5 dcd
 27        WHERE ENC.EK = AUD.EK
 28        AND   ENC.MAX_DT BETWEEN TO_DATE('01/01/2009','MM/DD/YYYY') AND TO_DATE('12/31/2009','MM/DD/YYYY')
 29        AND   ENC.DK      = ds.SK (+)
 30        AND   ENC.LK       = lb.LOB_KEY(+)
 31        AND   dcd.diag_cd_key      = aud.DK
 32        LOG ERRORS  REJECT LIMIT UNLIMITED;
  INSERT INTO ADT_IOT
*
ERROR at line 1:
ORA-38906: insert into DML Error Logging table "ERR$_ADT_IOT" failed
ORA-00001: unique constraint (IRADS.ADT_DX_IOT) violated

Tom,
please correct me what i am doing wrong. Why DML Error logging in table "ERR$_ADT_IOT" failed ?

irads@IRADSDB> select dbms_metadata.get_ddl( 'TABLE', 'ERR$_ADT_IOT' ) from dual; 
  CREATE TABLE "IRADS"."ERR$_ADT_IOT" 
   ( "ORA_ERR_NUMBER$" NUMBER, 
  "ORA_ERR_MESG$" VARCHAR2(2000), 
  "ORA_ERR_ROWID$" UROWID (4000), 
  "ORA_ERR_OPTYP$" VARCHAR2(2), 
  "ORA_ERR_TAG$" VARCHAR2(2000), 
  "CK" VARCHAR2(4000), 
  "EK" VARCHAR2(4000), 
  "DK" VARCHAR2(4000), 
  "HD" VARCHAR2(4000), 
  "CT" VARCHAR2(4000)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_AUDIT";
 
irads@IRADSDB> SELECT * FROM user_dependencies
  2  WHERE referenced_name ='ERR$_ADT_IOT'
  3  AND type ='TRIGGER';

no rows selected

Tom,
Also the tablespace TS_AUDIT has more than 122 GB Freespace.

Hello,
I try to use errlog in a remote database and I get error.
It works fine in local database but failes in remote.

Here's my test case:
-- Local database
create table t(n number primary key);

Table created.

Elapsed: 00:00:00.32
exec dbms_errlog.create_error_log('t','t_errlog')

PL/SQL procedure successfully completed.

Insert the 1st row - works.

insert into t values(1)
log errors into t_errlog('test') reject limit 10;

1 row created.

Insert the same row - 0 rows inserted, error goes into errlog.

insert into t values(1)
log errors into t_errlog('test') reject limit 10;

0 rows created.

11:08:27 AP2> select ORA_ERR_NUMBER$,ORA_ERR_OPTYP$,ORA_ERR_TAG$,N,ORA_ERR_MESG$
11:08:52   2  from t_errlog
11:08:57   3  /

ORA_ERR_NUMBER$ OR ORA_ERR_TA N          ORA_ERR_MESG$ 
--------------- -- ---------- ---------- ------------------
              1 I  test       1          ORA-00001: unique constraint (VZWOLAP.SYS_C0028380) violated

1 row selected.

rollback;
Rollback complete.

-- Remote database
I can see errlog in remote database
desc vzwolap.t_errlog@tp_2_ap
 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)
 N                                VARCHAR2(4000)

I can manually insert into remote errlog:
insert into vzwolap.t_errlog@tp_2_ap values(1,'aaa',null,'I','test','xxxx');

1 row created.

When I try to insert the 1st row it works:

insert into t@tp_2_ap values(1)
log errors into t_errlog@tp_2_ap('test') reject limit 10;

1 row created.

When I try to insert the same row for the 2nd time, it fails:
insert into t@tp_2_ap values(1)
log errors into t_errlog@tp_2_ap('test') reject limit 10;

insert into t@tp_2_ap values(1)
*
ERROR at line 1:
ORA-00001: unique constraint
(VZWOLAP.SYS_C0028380) violated
ORA-02063: preceding line from TP_2_AP

Tom,

Is there a way to know if any rows were created into the ERR$_ table after an insert?

e.g.

insert into x (select .... from ...)
log errors reject limit unlimited
/

[ then check some return code here? ]

Thanks,

Robert.

ops$tkyte%ORA10GR2> create table t ( x int check (x>0) );

Table created.

ops$tkyte%ORA10GR2> exec dbms_errlog.create_error_log('T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create sequence error_log_seq;

Sequence created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> desc err$_t;
 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)
 X                                                 VARCHAR2(4000)

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure insert_rows( p_low in number, p_high in number )
  2  as
  3      l_tag varchar2(44);
  4  begin
  5      select error_log_seq.nextval
  6        into l_tag
  7        from dual;
  8
  9      insert into t (x)
 10      select p_low+level-1
 11        from dual
 12      connect by level <= (p_high-p_low+1)
 13      LOG ERRORS (l_tag)
 14      reject limit unlimited;
 15
 16      dbms_output.put_line( 'sql%rowcount = ' || sql%rowcount );
 17
 18      for x in (select count(*) cnt
 19                  from err$_t
 20                 where  ORA_ERR_TAG$ = l_tag )
 21      loop
 22          dbms_output.put_line( x.cnt || ' errors for tag = ' || l_tag );
 23      end loop;
 24  end;
 25  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec insert_rows( 1, 10 );
sql%rowcount = 10
0 errors for tag = 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec insert_rows( 0, 20 );
sql%rowcount = 20
1 errors for tag = 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec insert_rows( -100, 100 );
sql%rowcount = 100
101 errors for tag = 3

PL/SQL procedure successfully completed.

Got it Tom. Thanks!

Hi Tom,

Does the use of DBMS_ERRLOG in sql queries hamper the performance of the query,as per my little knowledge There must be some inbuilt triggers which are logging these errors for DBMS_ERRLOG.

Please let me know if its a good practice to use this to all DMLs in our Data Ware House environment.

Regards,
Snehasish Das