Simple demo of DBMS_ERRLOG

This is a simple demo of DBMS_ERRLOG. This is also based on one of my posting in OTN.

SQL> 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

SQL> /* My first table with some duplicate records */
SQL> CREATE TABLE test_dbms_error_log1(col1 INTEGER);

Table created.

SQL> INSERT INTO test_dbms_error_log1 VALUES (&col1);
Enter value for col1: 1
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (1)

1 row created.

SQL> /
Enter value for col1: 2
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (2)

1 row created.

SQL> /
Enter value for col1: 3
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (3)

1 row created.

SQL> /
Enter value for col1: 4
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (4)

1 row created.

SQL> /
Enter value for col1: 4
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (4)

1 row created.

SQL> /
Enter value for col1: 1
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (1)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM test_dbms_error_log1;

      COL1
----------
         1
         2
         3
         4
         4
         1

6 rows selected.

SQL> /* My second table with primary key. I will insert into test_dbms_error_log2
DOC>   from test_dbms_error_log1.
DOC>   Because of duplicate rows into test_dbms_error_log1, Some rows will get rejected. */
SQL> CREATE TABLE test_dbms_error_log2(col1 INTEGER primary key);

Table created.
SQL> BEGIN
  2   DBMS_ERRLOG.create_error_log(dml_table_name =>'TEST_DBMS_ERROR_LOG2');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> /* The above statement will create a table named err$_TEST_DBMS_ERROR_LOG2 */
SQL>
SQL> /* Now my insert statement */
SQL> INSERT INTO TEST_DBMS_ERROR_LOG2 SELECT * FROM TEST_DBMS_ERROR_LOG1
  2  LOG ERRORS REJECT LIMIT UNLIMITED;

4 rows created.

SQL> SELECT * FROM test_dbms_error_log2;

      COL1
----------
         1
         2
         3
         4

SQL> /* So, two rows got rejected. */
SQL> 

SQL> desc err$_TEST_DBMS_ERROR_LOG2
 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)
 COL1                                               VARCHAR2(4000)

SQL> SELECT ora_err_number$,SUBSTR(ora_err_mesg$,1,50) err_msg
  2         ,ora_err_rowid$ rwid, col1 what_is_rejected
  3  FROM err$_test_dbms_error_log2;

ORA_ERR_NUMBER$ ERR_MSG
--------------- --------------------------------------------------
RWID
--------------------------------------------------------------------------------
WHAT_IS_REJECTED
--------------------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.SYS_C005514) v

4

              1 ORA-00001: unique constraint (SCOTT.SYS_C005514) v

1

ORA_ERR_NUMBER$ ERR_MSG
--------------- --------------------------------------------------
RWID
--------------------------------------------------------------------------------
WHAT_IS_REJECTED
--------------------------------------------------------------------------------