DBMS ERRORS

To use DBMS error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

The error logging table is designed to hold the errorred rows during the DML operation. The error table may be created in any of the two ways:

Manually via the regular CREATE TABLE command. This table must follow certain guidelines as described in the next section "Error Logging Table Format".

Create : 
	SQL> create table tesdb (
	id           number(10)    not null,
	code         varchar2(10),
	description  varchar2(50),
	constraint tesdb_pk primary key (id));
	Table created.

	SQL> insert into tesdb
	select level,
	to_char(level),
	'Description for ' || to_char(level)
	from   dual
	connect by level <= 100000;
	100000 rows created.

	SQL> commit;
	Commit complete.

	SQL> update tesdb set code = null where id in (1000, 10000);
	2 rows updated.

	SQL> commit;
	Commit complete.

	SQL> exec dbms_stats.gather_table_stats(user, 'tesdb', cascade => true);
	PL/SQL procedure successfully completed.

	create table test (
	id           number(10)    not null,
	code         varchar2(10)  not null,
	description  varchar2(50),
	constraint tesdb1_pk primary key (id));
	Table created.

	SQL> create table test_child ( id       number,
	test_id  number,
	constraint child_pk primary key (id),
	constraint test_child_tes_fk foreign key (test_id) references test(id));
	Table created.

Once the basic tables are in place we can create a table to hold the DML error logs for the TEST. A log table must be created for every base table that requires the DML error logging functionality. This can be done manually or with the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package, as shown below.

	SQL> begin
	  dbms_errlog.create_error_log (dml_table_name => 'test');
	end;
	/
	PL/SQL procedure successfully completed.

The structure of the log table includes maximum length and datatype independent versions of all available columns from the base table, as seen below.

	SQL>  desc err$_test
	 Name                              Null?    Type
	 --------------------------------- -------- --------------
	 ORA_ERR_NUMBER$				NUMBER
	 ORA_ERR_MESG$					VARCHAR2(2000)
	 ORA_ERR_ROWID$					ROW ID
	 ORA_ERR_OPTYP$					VARCHAR2(2)
	 ORA_ERR_TAG$					VARCHAR2(2000)
	 ID						VARCHAR2(4000)
	 CODE						VARCHAR2(4000)
	 DESCRIPTION					VARCHAR2(4000)

When we built the sample schema we noted that the CODE column is optional in the tesdb table, but mandatory in TEST table. When we populated the table we set the code to NULL for two of the rows. If we try to copy the data from the Testdb table to the TEST table we get the following result.

	SQL> insert into test select  *  from   tesdb;
	select *
		   *
	ERROR at line 2:
	ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")

	The failure causes the whole insert to roll back, regardless of how many rows were
	inserted successfully. Adding the DML error logging clause allows us to complete
	the insert of the valid rows.

	SQL> insert into test select  *  from   tesdb;
	log errors into err$_test ('INSERT') reject limit unlimited;
	99998 rows created.

	The rows that failed during the insert are stored in the ERR$_TEST table,
	along with the reason for the failure.

	SQL> select ora_err_number$, ora_err_mesg$
	from   err$_test where  ora_err_tag$ = 'INSERT';

	ORA_ERR_NUMBER		$ORA_ERR_MESG$
	-----------------------------------------------------------------------------
		1400		ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")
		1400		ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")

	The following code attempts to update the CODE column for 10 rows, setting it
	to itself for 8 rows and to the value NULL for 2 rows.

	SQL> update test set    code = decode(id, 9, null, 10, null, code)
	where  id between 1 and 10;

	set    code = decode(id, 9, null, 10, null, code)
		   *
	ERROR at line 2:
	ORA-01407: cannot update ("SYS"."TEST"."CODE") to NULL


	Adding the DML error logging clause allows us to complete
	the update of the valid rows.

	SQL> update test
	set    code = decode(id, 9, null, 10, null, code)
	where  id between 1 and 10

	log errors into err$_test('UPDATE') reject limit unlimited;
	8 rows updated.

	SQL> select ora_err_number$, ora_err_mesg$
	from   err$_test where  ora_err_tag$ = 'UPDATE';


	The rows that failed during the update are stored in the ERR$_TEST table,
	along with the reason for the failure.

	ORA_ERR_NUMBER	$ORA_ERR_MESG$
		1407		ORA-01407: cannot update ("SYS"."TEST"."CODE") to NULL
		1407		ORA-01407: cannot update ("SYS"."TEST"."CODE") to NULL

The following code deletes some of the rows from the TEST table, then attempts to merge the data from the tesdb table into the TEST table.

	delete from test
	where  id > 50000;

	merge into test a
		using tesdb b
		on (a.id = b.id)
	  when matched then
		update set a.code        = b.code,
				   a.description = b.description
	  when not matched then
		insert (id, code, description)
		values (b.id, b.code, b.description);

	ERROR at line 9:
	ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")

	Adding the DML error logging clause allows the merge operation
	to complete.

	SQL> merge into test a
		using tesdb b
		on (a.id = b.id)
	  when matched then
		update set a.code     = b.code,
				   a.description = b.description
	  when not matched then
		insert (id, code, description)
		values (b.id, b.code, b.description)

	  log errors into err$_test ('MERGE') reject limit unlimited;
	99998 rows merged.

	SQL> select ora_err_number$, ora_err_mesg$
	from   err$_test
	where  ora_err_tag$ = 'MERGE';

	ORA_ERR_NUMBER$              ORA_ERR_MESG$
	1400   ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")
	1400    ORA-01400: cannot insert NULL into ("SYS"."TEST"."CODE")

	SQL> insert into test_child (id, test_id) values (1, 100);
	1 row created.

	SQL> insert into test_child (id, test_id) values (2, 101);
	1 row created.

	SQL >delete from test;

	delete from test
	*
	ERROR at line 1:

	ORA-02292: integrity constraint (SYS.TEST_CHILD_TEST_FK) violated
	- child record found

	delete from test

	log errors into err$_test ('DELETE') reject limit unlimited;
	 The rows that failed during the delete operation are stored in the
	 ERR$_TEST table, along with the reason for the failure.

	SQL> select ora_err_number$, ora_err_mesg$
	from   err$_test
	where  ora_err_tag$ = 'DELETE';

	ORA_ERR_NUMBER$         ORA_ERR_MESG$
	--------------		--------------
	2292		ORA-02292: integrity constraint (SYS.TEST_CHILD_TEST_FK)
			violated - child record found

	2292		ORA-02292: integrity constraint (SYS.TEST_CHILD_TEST_FK)
			violated - child record found