Most PL/SQL program units execute SQL statements. Most of those SQL statements are selects – query DML (data manipulation language). But a good portion of those SQL statements are non-query DML: updates, inserts, deletes and merges.
The execution of non-query DML (I’ll refer to them simply as “DML” for the rest of this article, since that’s usually what database developers think of when they see that acronym) can cause all sorts of SQL errors to be returned to the PL/SQL engine (and then “converted” into PL/SQL exceptions).
So it’s really important to handle DML errors well. Here are some guidelines to consider following:
- Identify in advance specific errors that could be raised and then write separate WHEN clauses for them (rather than, or in addition to, when others)
- When you log the error make sure to save any bind values that might have contributed to the error
- Rely on a reusable error logging procedure (see the open source Logger utility as either a model or for use in your application)
create or replace procedure full_of_dml ( p_order in number, p_lines in lines_t ) is l_msg_error varchar2(1000); e_dml exception; begin begin update my_orders set status = 2 where order_id = p_order_id; exception when others then l_msg_error := 'Error changing status in my_orders'; raise e_dml; end; for indx in 1..p_lines.count loop begin insert into my_line_items ( order_id, line_num, line_info) values ( p_order, indx, p_lines(indx) ); exception when others then l_msg_error := 'Unable to insert line for order'; raise e_dml; end; end loop; exception when e_dml then rollback; raise_application_error(-20000, l_msg_error); when others then rollback; raise_application_error (-20000, 'unknown error after ' || l_msg_error); end;
Before I go all negative on this code, I’d like to point out a few things I like about this procedure:
- A user-defined exception: you don’t see these all that often. It’s good to know you can do this, and it’s good to use them – properly. In this case, it’s unnecessary.
- Separate handler for DML errors
- The developer was paying attention. They knew they might have errors. They wanted to be sure that as soon as one DML statement failed, they would not do the others. They also wanted to know which DML statement failed.
Overall grade: A for effort and intention. But as for the implementation…maybe something more like a C.
Here’s what I do not like so much about this code:
- That user-defined exception and nested blocks are just “noise”. They don’t actually add any value and make the program harder to read.
- You don’t need a hand-coded “just in time” message string to tell you which DML statement failed.
- None of the error information is logged for later diagnosis.
- The information passed back via raise_application_error leaves a lot to be desired, most importantly the values of the parameters and local variables.
Let’s rewrite that procedure.
create or replace procedure full_of_dml ( p_order in number, p_lines in lines_t ) is l_scope logger_logs.scope%type := 'full_of_dml'; l_params logger.tab_param; begin logger.append_param(l_params, 'p_order', p_order); update my_orders set status = 2 where order_id = p_order_id; for indx in 1..p_lines.count loop logger.append_param ( l_params, 'p_lines ' || indx, p_lines(indx)); insert into my_line_items ( order_id, line_num, line_info ) values ( p_order, indx, p_lines(indx) ); end loop; exception when others then rollback; logger.log_error('Unhandled exception', l_scope, l_params); end;
Everything I need and nothing I don’t need!
Time to Explore
I wrote an article for Oracle Magazine some years ago that touches on this topic. Here’s the link:
(I apologize in advance for the very poor formatting of code – not that it’s my fault. You can be sure I didn’t write it like that. Look at all that UPPER CASE.)