ETL and Oracle error log        June 17, 2007

SQL> create table realsoft (F1 number(1) primary key);

Table created.


1 begin
2 dbms_errlog.create_error_log('REALSOFT','ERROR_LOG_REALSOFT') ;
3* end;
SQL> /


PL/SQL procedure successfully completed.


The statement above associates the REALSOFT table with an ERROR log table. The ERROR log table name is given above as the second parameter to the create_error_log procedure. The statement above also creates the ERROR_LOG_REALSOFT log table



SQL> desc error_log_realsoft
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)
F1 VARCHAR2(4000)

Note: the procedure created the error table with 5 column starting with ORA and needed for reporting the errors and a field called F1 which is the REALSOFT fields.

Before running some example, please note the f1 in number(1), maning that it can hold 0,1,..,9
The following example will try to insert values from 1 .. 11. Therefore, 10 and 11 are values larger the the capacity of F1





1 begin
2 for i in 1 .. 11 loop
3 insert into realsoft values (i) log errors into
4 ERROR_LOG_REALSOFT REJECT LIMIT UNLIMITED;
5 end loop;
6* end;
SQL> /

PL/SQL procedure successfully completed.


SQL> commit;

Commit complete.

The procedure is completed successfully????
How in the world did this happend?
One would expect the following error

begin
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 3

And would expect the statement to rollback due to the error.

Let us see if there exists record in REALSOFT table

SQL> select * from realsoft;

F1
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

The statement was executed and rows inserted into REALSOFT table; the two records that violate the column width are rejected WITHOUT rolling back the entire statement. At the same time the rejected rows are inserted into the ERROR_LOG_REALSOFT table



F1 ORA_ERR_MESG$
---------- ------------------------------
10 ORA-01438: value larger than s
pecified precision allowed for
this column

11 ORA-01438: value larger than s
pecified precision allowed for
this column


The second example, i am going to test primary key violation.
I will delete a few records and run the previous loop again

SQL> DELETE FROM REALSOFT WHERE F1 >5
2 ;


When the loop is run again , it should violate the first 5 records because the values from 1 .. 5 already exists as shown in the following output

SQL> r
1* SELECT * FROM REALSOFT

F1
--
1
2
3
4
5

SQL>
1 begin
2 for i in 1 .. 11 loop
3 insert into realsoft values (i)
4 log errors into ERROR_LOG_REALSOFT REJECT LIMIT UNLIMITED;
5 end loop;
6* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Again, scripts above violates two conditions, namely, the primary key and the column width specifications


SQL> SELECT * FROM REALSOFT;

F1
--
1
2
3
4
5
6
7
8
9

9 rows selected.

Again, the non-violating rows are inserted into the table and the violating should be in the ERROR_LOG


SQL> r
1* SELECT F1, ORA_eRR_MESG$ FROM ERROR_LOG_REALSOFT

F1 ORA_ERR_MESG$
---- --------------------------------------------------------------------------
10 ORA-01438: value larger than specified precision allowed for this column
11 ORA-01438: value larger than specified precision allowed for this column
1 ORA-00001: unique constraint (SCOTT.SYS_C006960) violated
2 ORA-00001: unique constraint (SCOTT.SYS_C006960) violated
3 ORA-00001: unique constraint (SCOTT.SYS_C006960) violated
4 ORA-00001: unique constraint (SCOTT.SYS_C006960) violated
5 ORA-00001: unique constraint (SCOTT.SYS_C006960) violated
10 ORA-01438: value larger than specified precision allowed for this column
11 ORA-01438: value larger than specified precision allowed for this column

9 rows selected.

the Error_log_realsoft table shows rows rejected by the loop script (third row till the 9th)

Note: You can create the log table manually if you which as you create any ordinary table, however, you must include the following mandatory field

ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$

Ammar Sajdi

Amman - jordan
www.e-ammar.com