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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: