steven-dml-error-feedback.sql

-- Feedback based on https://www.insum.ca/feuertip-7-how-not-to-handle-dml-errors/

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
      -- MD: Don't do this it'll create a lot of entries in an array.
      -- MD: In this example not bad but will take up more memory if this is scattered in all your code
      -- logger.append_param (
      --    l_params, 'p_lines ' || indx, p_lines(indx));
      
      -- MD: Instead do this: Good for debugging but won't show up when an exception happens and
      -- MD: Logger is just set to log errors
      logger.log('indx: ' || indx, l_scope);
      logger.log('p_lines(indx): ' || p_lines(indx), l_scope);

      insert into my_line_items (
         order_id,
         line_num,
         line_info )
      values (
         p_order,
         indx,
         p_lines(indx)
      );
   end loop;
exception
   when others
   then
      -- MD: Side note: I usually don't put commits or rollbacks as it should be the calling app's determiniation as to what to do
      -- rollback;

      -- MD: at this point can do 2 things. Either add key metrics to be logged to l_params or just log them:
      logger.log_error('indx: ' || indx, l_scope);
      logger.log_error('p_lines(indx): ' || p_lines(indx), l_scope);

      -- MD: OR
      logger.append_param(l_params, 'indx', indx);
      logger.append_param(l_params, 'p_lines(indx)', p_lines(indx));


      logger.log_error('Unhandled exception', l_scope, l_params);

      -- MD: Based on logic / commend for rollback, should raise the error and let the calling app determine what to do
      raise;
end;