Oracle Illustrated

Feb 21 2010   7:17AM GMT

Migrating from 9i to 11g – DML Error logging

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

When we are bulk processing records – especially when we need to perform DML operations based on global temporary tables it becomes really difficult to trap the error – unless we use bulk exception handling. Error handling itself becomes a separate coding by itself !!

Create table emp1(empno number, still_employed varchar2(20))

create unique index emp1_idx on emp1 (empno)

Oracle 9i

forall j in emp_tab.first .. emp_tab.last save exceptions
INSERT INTO

VALUES (emp_tab(j));

exception when bulk_errors then
for j in 1 .. sql%bulk_exceptions.Count
loop
dbms_output.put_line ( ‘Error -‘ ||
To_Char(sql%bulk_exceptions(j).error_index) || ‘: ‘ ||
Sqlerrm(SQL%bulk_exceptions(j).error_code) );
end loop;

Oracle 11g
exec dbms_errlog.CREATE_ERROR_LOG (‘EMP1′,’ERR_EMP1’)

INSERT

insert into emp1 values (1, ‘Y’);
insert into emp1 values (2, ‘N’);
insert into emp1 values (3, ‘Y’);

insert into emp1 SELECT rownum, ‘N’ from dual connect by level <= 20
log errors into err_emp1
reject limit 50
17 rows created.

insert into emp1 SELECT rownum, ‘N’ from dual connect by level <= 20
log errors into err_emp1
reject limit 10
SQL> insert into emp1 SELECT rownum, ‘N’ from dual connect by level <= 20
2 log errors into err_emp1
3 reject limit 10;
insert into emp1 SELECT rownum, ‘N’ from dual connect by level <= 20
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.EMP1_IDX) violated

SELECT * FROM ERR_EMP1

Output-

1 “ORA-00001: unique constraint (SYSTEM.EMP1_IDX) violated
” I 1 N

UPDATE

SQL> desc emp1
Name Null? Type
—————————————– ——– ——————-

EMPNO NUMBER
STILL_EMPLOYED VARCHAR2(1)

UPDATE emp1
SET still_employed = decode(still_employed,’Y’, ‘YES’, ‘N’) where
empno in (1,6)
log errors into err_emp1
reject limit 2
1 row updated.

UPDATE emp1
SET still_employed = ‘YES’ where
empno between 1 and 20
log errors into err_emp1
reject limit 2
ERROR at line 2:
ORA-12899: value too large for column “SYSTEM”.”EMP1″.”STILL_EMPLOYED” (actual: 3, maximum: 1)

DELETE

DLETE emp1
where
empno between 1 and 20
log errors into err_emp1
reject limit 2
(Mainly useful for Referential integrity)

Also possible with Merge

The coding effort becomes really really less !!

As per Oracle documentation –

Error Logging Restrictions and Caveats-

– Oracle Database logs the following errors during DML operations:
– Column values that are too large
– Constraint violations (NOT NULL, unique, referential, and check constraints)
– Errors raised during trigger execution
– Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
– Partition mapping errors
– Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: