{{announcement.body}}
{{announcement.title}}

A New Use for DML Error Logging

DZone 's Guide to

A New Use for DML Error Logging

In this article, we discuss a new use for DML error logging with an Oracle database to better handle SQL debugging.

· Database Zone ·
Free Resource

DML error logging is a feature of Oracle that has been around for many years now. It's great for capturing errors during a large load without losing all of the rows that are successfully loaded.

But here is a possible new use case for DML error logging, even if you are not doing large scale loads. Let me describe the problem first, and then show how DML error logging might be a solution.

I’ll create a table with a constraint on its column:

SQL
 




xxxxxxxxxx
1


 
1
create table t1 (val number not null); 
2
-- Table created. 



One of the nice things that came into Oracle (I think) way back in version 8.0 was more detailed information when you violate those constraints. So, when I try to insert a null into that table.

SQL
 




xxxxxxxxxx
1


 
1
insert into t1 values (null); 
2
insert into t1 values (null) 
3
 
          
4
-- * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL") 



Not only am I told that I got an ORA-1400, I am also told the column (VAL) that caused the error. Even if this INSERT is performed from a PL/SQL routine, I still get that information:

SQL
 




xxxxxxxxxx
1


 
1
exec insert into t1 values (null); 
2
 
          
3
BEGIN insert into t1 values (null); 
4
END; 
5
 
          
6
-- ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL") 
7
-- ORA-06512: at line 1 



That might not seem particularly beneficial in this instance, but consider a more true to life application, which might have tables with dozens of columns, or even hundreds of columns. Knowing which column was in error is a handy piece of information to have when debugging.

You may also like: Oracle Quick SQL Platform.

That all seems just sweet, until I throw a little bulk binding into the mix. Here’s a package that will bulk bind an insert into the T1 table.

SQL
 




x


1
create or replace package bulk_ins as 
2
type t1_tt is table of t1%rowtype; 
3
procedure bulk_insert; 
4
end bulk_ins; 
5
-- Package created. 
6
 
          
7
create or replace package body bulk_ins as 
8
procedure bulk_insert is 
9
l_data t1_tt:=t1_tt(); 
10
begin 
11
l_data.extend(2); 
12
l_data(1).val:=999; 
13
l_data(2).val:=null; 
14
 
          
15
begin 
16
forall i in 1..l_data.count save exceptions 
17
insert into t1 values l_data(i); 
18
exception 
19
when others then 
20
for i in 1..sql%bulk_exceptions.count loop 
21
dbms_output.put_line( sqlerrm( -sql%bulk_exceptions(i).error_code ) ); 
22
end loop; 
23
end forall_loop_with_save_except; 
24
commit; 
25
end bulk_insert; 
26
end bulk_ins; 
27
-- Package body created. 



Even without running the code, you can see on line 6 and 7 that we are seeding an array with a null value in one of the entries, so table T1 is not going to like that upon insertion! Luckily though, we will be using the SAVE EXCEPTIONS extension to capture any errors and report on them to the calling environment. Here is what happens


SQL
 




xxxxxxxxxx
1


 
1
set serverout on exec bulk_ins.bulk_insert; 
2
 
          
3
-- ORA-01400: cannot insert NULL into () PL/SQL procedure successfully completed. 



The SAVE EXCEPTIONS has captured the error, but notice that the vital column name has been lost. Because SAVE EXCEPTIONS captures the error code, but the time we convert that to standard error message text, the precision has been lost.

Here is where DML error handling can step in and improve things. I’ll recode the package body to use DML error logging and dispense with the SAVE EXCEPTIONS

SQL
 




x


 
1
-- exec DBMS_ERRLOG.create_error_log(dml_table_name=>'T1') PL/SQL procedure successfully completed. 
2
 
          
3
create or replace package body bulk_ins as
4
procedure bulk_insert is
5
l_data t1_tt:=t1_tt(); 
6
begin 
7
l_data.extend(2); 
8
l_data(1).val:=999; 
9
l_data(2).val:=null; 
10
 
          
11
forall i in 1..l_data.count 
12
insert into t1 values l_data(i) LOG ERRORS REJECT LIMIT UNLIMITED; 
13
 
          
14
commit; 
15
end bulk_insert; 
16
end bulk_ins; 
17
 
          
18
-- Package body created. 
19
 
          
20
exec bulk_ins.bulk_insert; 
21
-- PL/SQL procedure successfully completed.
22
 
          
23
elect * from err$_t1 
24
--@pr ============================== ORA_ERR_NUMBER$ : 1400 ORA_ERR_MESG$ : ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL") ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : VAL : 



Notice that the error message now contains the complete information as it did from the standard SQL insert statement. That’s pretty cool. I’m not saying that you should rush out and replace all of your SAVE EXCEPTIONS code with DML error logging. You can see from the example, that there are overheads in the management of this. 

You need to have the error logging table defined in advance, you would need to manage the rows from multiple sessions, and, obviously, every load must be suffixed with a query to the error logging table once to see if any errors occurred. But if you really really need that column name, using DML error logging might be the way forward for you.


Further Reading

Topics:
errors and solutions, oracle, plsql

Published at DZone with permission of Connor McDonald , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}