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

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 other 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!
One participant will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity.
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 every other Wednesday at 11!
Share this:
Share

2 Comments:

    • Niels Hecker
    • May 26, 2021
    • Reply

    Hello Steven,
    as I mentioned in the episode is that I use is a little package:

    create or replace
    package E is
       EAbort  exception;
           E_ABORT  constant binary_integer := -20999;
           pragma exception_init( EAbort, -20999);
    
       EAssertion  exception;
           E_ASSERTION  constant binary_integer := -20998;
           pragma exception_init( EAssertion, -20998);
    
       -- raise EAbort exception
       procedure Abort;
       procedure Abort (pMsg    in varchar2,
                        pOnTop  in boolean default True);
       procedure Abort (pCond   in boolean,
                        pMsg    in varchar2 default null,
                        pOnTop  in boolean default True);
    
       -- raise EAssertion exception (always on top of existing error stack)
       procedure Assert (pTest  in boolean,
                         pMsg   in varchar2 default null);
       procedure AssertNumeric (pValue  in varchar2);
       procedure AssertNotNumeric (pValue  in varchar2);
       procedure AssertInteger (pValue  in varchar2);
    end E;
    /
    

    And yes, the EAssertion is for checking parameters for correct values.
    The EAbort I use if – for which reason ever – further computation is not neccessary or useful,, e.g. when accessing web-services or a LDAP server:

    declare
       vConn  Utl_TCP.Connection;
    begin
       begin
          vConn := utl_tcp.open_connection( ...);
       exception
          when Utl_TCP.Network_Error then
             -- log error, clean up if neccessary
             Abort();
       end;
    
       -- build the body of the mail
       -- set the receivers
       -- send the mail
    exception
       when E.EAbort then
          -- everything is already logged and cleaned up
          null;
       when others then
          -- log unexpected error, clean up if neccessary
          raise;
    end;
    

    The advantage is that you don’t need to have an exception handler for each of the different exceptions at the end but only one and that you can handle the different exceptions as close to their occurrence as it can be done and then quickly “jump” to the end of the execution.
    Kind regards, Niels

      • Steven Feuerstein
      • May 27, 2021
      • Reply

      I really like this, Niels. Very clean, very easy to understand!

Leave reply:

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