28Apr
By: Steven Feuerstein On: April 28, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

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)

Moving past the generalities, I’d like to show you some DML-handling code I ran into recently, and suggest a rewrite.

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


Feuertips

Every Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
Speaking of rewards, Steven finishes each show with a quick quiz to see if you were listening. Participation, of course, is optional!
One lucky quiz answerer will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity: The Sheldrick Wildlife Trust, The Center for Biological Diversity, and Earthwatch.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us Wednesdays at 11!
Share this:
Share

Leave reply:

Your email address will not be published. Required fields are marked *