Home > oracle > Error logging table in 10g

Error logging table in 10g

Many times if you issue a CTAS (Create Table As Select) you get this error:

ORA-01858 A non-numeric character was found where a numeric was expected

If you want to investigate which records are regected, in Oracle 10g you can create an error logging table:

begin
   DBMS_ERRLOG.create_error_log (dml_table_name => 'test');
end;
/

By default the error log table is created in the current user’s schema, and it’s named ERR$_test. The structure of the log table includes maximum length and datatype of all available columns from the base table, as seen below, plus some admin columns (ORA_ERR_%).

SQL> DESC err$_test
 Name                              Null?    Type
 --------------------------------- -------- --------------
 ORA_ERR_NUMBER$                            NUMBER
 ORA_ERR_MESG$                              VARCHAR2(2000)
 ORA_ERR_ROWID$                             ROWID
 ORA_ERR_OPTYP$                             VARCHAR2(2)
 <the other columns are all of the test columns>

Nowyou can log the DML sentences like this:

INSERT INTO test SELECT * FROM test_old
LOG ERRORS INTO err$_test ('INSERT') REJECT LIMIT UNLIMITED;
SELECT * FROM err_$test;
Advertisements
Categories: oracle Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: