Last week I showed you a cool tool available since 10.2.0.1, that not every DBA is aware of. I was not aware of it either.
The package I am talking about is DBMS_ERRLOG, and the LOG ERRORS clause of a DML statement.
Today, I will show you an example on how to work with DBMS_ERRLOG package and the LOG ERRORS clause. It is really easy!
First, let’s get the environment ready! I’ll create two tables a source and a target table, and insert some data.
Setup script:
|
|
--source table
create table source_emp
( emp_id number primary key,
last_name varchar2(30),
first_name varchar2(30) );
insert into source_emp
values (1,'Robete','Diana');
insert into source_emp
values (2,'','Diana');
insert into source_emp
values (3,'Smith','John');
commit;
select count(*) from source_emp;
COUNT(*)
----------
3
--target table
create table target_emp
( emp_id number primary key,
last_name varchar2(30) not null,
first_name varchar2(30) not null );
insert into target_emp
select * from source_emp;
ERROR at line 2:
ORA-01400: cannot insert NULL into ("SYS"."TARGET_EMP"."LAST_NAME")
select count(*) from target_emp;
COUNT(*)
----------
0
|
End setup script.
For our example, the source table is source_emp, and the target table is target_emp.
The structures of the two tables are identical, with the only difference that the last_name and first_name columns have a not null constraint in the target_emp table.
The source_emp table has some data that will not comply with the not null constraint. This setup was done on purpose.
In this example, we are only dealing with 3 rows, so spotting the problematic row is easy.
However when dealing with millions of rows and lots of columns, spotting the problematic rows could be challenging.
Instead of attempting to figure out which is the problematic rows, you can use the DBMS_ERRLOG package and the LOG ERRORS clause for the insert statement.
Last week I showed you the high level steps on how to use the DBMS_ERRLOG package. This week, I’ll show you the details.
1) Determine which table you plan to perform the DML statements on.
We are inserting rows into the target_emp table. The source table is source_emp.
2) Create the error logging table for target_emp table, using DBMS_ERRLOG package. This is the table chosen in step 1.
|
|
exec DBMS_ERRLOG.create_error_log(dml_table_name => 'target_emp');
|
The above statement will create a table with a similar structure and some additional columns, as the target_emp table. The name of this new table is err$_target_emp.
|
|
select table_name from dba_tables where table_name like '%TARGET_EMP%';
TABLE_NAME
-----------------------------
TARGET_EMP
ERR$_TARGET_EMP
desc err$_target_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)
EMP_ID VARCHAR2(4000)
LAST_NAME VARCHAR2(4000)
FIRST_NAME VARCHAR2(4000)
|
3) Run the insert statement into target_emp table, and add the LOG ERRORS clause to the end of the statement:
|
|
insert into target_emp
select * from source_emp
log errors into err$_target_emp ('INSERT') REJECT LIMIT UNLIMITED;
2 rows created.
commit;
|
Notice that only 2 rows were inserted into the table, instead of 3. That is because one row got rejected. The rejected row was inserted into the err$_target_emp table.
4) Verify the error logging table err$_target_emp, for rejected rows.
|
|
select ORA_ERR_MESG$, ORA_ERR_TAG$, EMP_ID, LAST_NAME, FIRST_NAME
from err$_target_emp;
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID LAST_NAME FIRST_NAME
-------------------- -------------- ----- ---------- ----------
ORA-01400: cannot in INSERT 2 Diana
sert NULL into ("SYS
"."TARGET_EMP"."LAST
_NAME")
|
This is it! Very simple, easy and clean!
