Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

dbms_errlog Tips

Oracle Database Tips by Donald BurlesonJuly 23, 2015

When doing a batch insert you may receive data from a host of external locations.  While it's nice to assume that the data has been scrubbed and validated, there is always a chance that you will have invalid numeric and character data.  The problem is how to deal with large volumes of errors and that is what the dbms_errlog package does for us.
 
This dbms_errlog package allows DML operations to continue working properly despite errors that might occur during the procedure.
 
To do this, dbms_errlog creates a table called an ?error log? table. Any records not processed by the DML operation due to errors will be inserted into this table allowing any problems in the operation to be analyzed and fixed later on.
 
When doing massive DML operations, problems like these may arise:

  • Data values that are too large for the column (e.g. inserting 40 characters into a varchar2(20).
  • Partition mapping errors happen (No partition exists)
  • Errors during triggers execution occur (mutating table error)
  • Constraint violations (check, unique, referential and NOT NULL constraints) occur
  • Type conversion errors (numeric with alpha characters, invalid dates) happen

For these cases, the dbms_errlog package can be used to create a table that will store  details about all DML operations that present errors.
 
The following script demonstrates its use as seen in the package. DML errors of operation are simulated, then they are inserted in the log table that was created for the package under analysis.

Note: This script will create a DBA user with a weak password what is not recommended for any production environment.

create user
   pkg
identified by
   pkg#123
default tablespace
   users
temporary tablespace
   temp;
 
grant dba to pkg;

Next, a test table is created. Just for fun, we decided to name it tb_dbms_errlog as that is the name of the package that is being studied.  Finally, our test table is also given a primary key.

--Create a test table
create table tb_dbms_errlog
as
select
   *
from
   dba_objects
where
   rownum < 1;
 
alter table
   tb_dbms_errlog
   add constraint      
      pk_obj_id10 primary key (object_id);

After executing this DDL, our error log table is created using the package dbms_errlog and rows are inserted to simulate constraint errors.
 
To create an error log table, we specify both the name and location where it should be created as well as the name of the table it is intended to deal with, i.e. the table whose DML will be logged into it.

--Create an error log table

exec dbms_errlog.create_error_log(
   dml_table_name        => 'tb_dbms_errlog',
   err_log_table_name    => 'tb_log',
   err_log_table_owner   => 'pkg',  
   err_log_table_space   => 'users');

Now, we insert rows into the tb_dbms_errlog table, logging any errors. Also specify an optional tag that can be used to identify errors more easily, and an unlimited reject limit to ensure the operation succeeds no matter how many records present errors.

select
   count(*)
from
   tb_dbms_errlog;

 
  COUNT(*)
----------
         0

insert into
   tb_dbms_errlog
select
   *
from
   dba_objects
      log errors into
         tb_log('tag_27042009')
      reject limit unlimited;

 
49742 rows created.

commit;

 
Commit complete.
 
select
   count(*)
from
   tb_dbms_errlog;

 
  COUNT(*)
----------    
     49742

Next, we select data from the tb_log table and confirm that no errors exist.
 
select
   count(*)
from
   tb_log;

 
 
COUNT(*)
----------
         0
 
Next, we delete some rows from the test table. This will be needed in the next step to simulate some records failing because of the primary key constraint while the others are successfully inserted.
 

delete
from
   tb_dbms_errlog
where
   object_id between 2354 and 4598;


2228 rows deleted.
 
commit;
 
Commit complete.


Now, let's insert all the rows again using the log errors into table_name syntax. As some rows exist with the same object_id, some errors will be generated.
 
insert into
   tb_dbms_errlog
select
   *
from
   dba_objects
      log errors into
         tb_log('tag_27042009_1')
      reject limit unlimited;

 
2228 rows created.
 
commit;
 
Commit complete.

 
Now it is possible to check errors generated by the insert command in our error log table. In this example, the ROWNUM clause was used to return less than 10 rows.
 
col "err msg"  for a60
col "err type" for a1
col "err tag"  for a15
col "obj id"   for a10


select
   t.ora_err_mesg$  "err msg",
   t.ora_err_optyp$ "err type",
   t.ora_err_tag$   "err tag",
   t.object_id    "obj id"
from
   tb_log t
where
   rownum < 10;



Err Msg                                                  E Err Tag      Obj ID
-------------------------------------------------------  - ------------- -----
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  176
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  177
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  178
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  179
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  180
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  181
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  182
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  184
ORA-00001: unique constraint (pkg.pk_obj_id10) violated  I tag_27042009_1  185

 
As seen in this insert operation, all the valid records have been inserted into the table and any records breaking the constraint have been automatically sent to the log table, saving both time and resources on large DML operations.

=================================================

DML error logging is one of the major innovations in the release 10g R2 to trap the error prone data into an error log table for future analysis. This logging process is very much effective during the bulk SQL operations. This is similar to the SAVE EXCEPTIONS in the FORALL construct in PL/SQL which is introduced in the release 9i.
 
To start with this logging process, an error log table has to be created either automatically by using the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package with necessary parameters in place or manually by using the traditional DDL method of creating tables.
 
The automatic method of creating the error log table creates five default columns prefixed with ORA_ERR and the rest of the columns are added from the source table which are to be logged. The procedure accepts the first mandatory parameter with the source table name within quotes and the optional second parameter with the target error table name. If the second parameter is not provided, by default a table is created with its name prefixed with ERR$_ followed by the first 25 characters of the source table name.
 
In the below example, an error log table with a user suggested name is created for the EMPLOYEES table.
 
BEGIN 
dbms_errlog.create_error_log(dml_table_name=>'EMPLOYEES', err_log_table_name=>'EMPLOYEES_ERR_TAB');
END;
/
 
The manual method of creating the error log table provides more control over the source columns which are to be added in the target table whereas the automatic method duplicates all the supported columns unconditionally. Other than the source columns which are chosen by the user in the manual method, there are 5 mandatory columns which are to be added to the target table in any order and those are shown below,
 
CREATE TABLE err$_log_table
  (
    ora_err_number$ NUMBER, -- This column stores the Oracle error number.
 
    ora_err_mesg$ VARCHAR2(2000), -- This column stores the Oracle error                                                                                            message.
 
    ora_err_rowid$ rowid, -- This column stores the row ID of the affected    column in case of update and delete else it stays Null.
 
    ora_err_optyp$ VARCHAR2(2), -- This column stores the type of operation. I for insert, U for update and D for delete.
 
    ora_err_tag$   VARCHAR2(2000) - This column stores the optional tag value supplied in the logging clause.
  );
 
The next step after the error log table is created is the logging of the errors. The prototype of the logging clause is shown below,
 
LOG ERRORS [INTO ] [Tag_statement] [reject limit ];
 
·         The INTO clause is not mandatory if the table is created automatically using the inbuilt procedure or if the name is of the format ERR$_||SUBSTR(SOURCE_TABLE_NAME, 1, 25).
 
·         The TAG_STATEMENT is a simple text which the user passes optionally for better readability of the error.
 
·         The REJECT LIMIT clause is technically not mandatory. If it is not mentioned, by default the reject limit is 0 which fails the SQL even if one error occurs, similar to the traditional SQL operation.
 
·         The limit can be an integer or UNLIMITED. This parameter defines the limit of errors by which the SQL statement should fail. If specified UNLIMITED, the SQL script never fails allowing unlimited errors to be logged.
 
In the below example, there are 3 SELECT queries with employee information which are combined together using the UNION set operator. The first SELECT query fails due to NULL email column value and the third SELECT query fails due to NULL hire date column value as these columns are not null in the EMPLOYEES table. The second SELECT query is the only compatible record for insertion into the EMPLOYEES table.
 
INSERT INTO employees
SELECT 207 employee_id,
  'Bruce' first_name,
  'Wayne' last_name,
  NULL email,
  '515.123.7181' phone_number,
  '13-June-2011' hire_date,
  'SH_CLERK' job_id,
  15000 salary,
  NULL commission_pct,
  101 manager_id,
  110 department_id
FROM dual
UNION
SELECT 208 employee_id,
  'Tony' first_name,
  'Stark' last_name,
  'TSTARK' email,
  '650.507.9922' phone_number,
  '13-June-2011' hire_date,
  'MK_MAN' job_id,
  25000 salary,
  NULL commission_pct,
  100 manager_id,
  20 department_id
FROM dual
UNION
SELECT 209 employee_id,
  'Clark' first_name,
  'Kent' last_name,
  'CKENT' email,
  '650.507.9923' phone_number,
  NULL hire_date,
  'AD_ASST' job_id,
  10000 salary,
  NULL commission_pct,
  101 manager_id,
  10 department_id
FROM dual log errors
INTO employees_err_tab reject limit unlimited;
 
1 rows inserted.
 
After the above INSERT script is executed, the script output states that 1 record has been inserted and the other 2 records are logged in the error table.
 
By issuing the below SELECT on the error log table, the failed rows can be verified, fixed and re-inserted into the database.
 
SELECT ora_err_number$,
  ora_err_mesg$,
  ora_err_rowid$,
  ora_err_optyp$,
  ora_err_tag$,
  employee_id,
  first_name,
  last_name
FROM employees_err_tab;

Errors Handled by DML Error Logging Table
1.       Constraint violations like NOT NULL, UNIQUE, CHECK and REFERENTIAL.
 
2.       Too large column values.
 
3.       Trigger execution failures.
 
4.       Type conversion errors.
 
5.       Partition mapping errors.
 
6.       "Unable to get a stable set of rows" error in MERGE.
Restrictions on DML Error Logging Table
1.       Errors and constraint violations from Long, LOB, Object, Nested table columns cannot be handled.
 
2.       Deferred constraints cannot be handled.
 
3.       The direct path insert, merge or an update raising a unique constraint or an index violation cannot be handled.

 

 
 
 
Inside the DBMS Packages

The DBMS packages form the foundation of Oracle DBA functionality.  Now, Paulo Portugal writes a landmark book Advanced Oracle DBMS Packages:  The Definitive Reference

This is a must-have book complete with a code depot of working examples for all of the major DBMS packages.  Order directly from Rampant and save 30%. 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster