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:

https://blogs.oracle.com/oraclemagazine/on-exceptions-and-rules

(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.)