Avoid hard-coded exposure of error handling (Oracle)


Link to this posting

Postby Ursego » 01 Aug 2019, 08:01

From the book "PL/SQL Best Practices":

Avoid hard-coded exposure of error handling by using standard, declarative procedures

The best way to achieve consistent, high-quality error handling throughout your application is to offer a set of predefined procedures that do the basic plumbing of error handling: record the error information if desired, propagate the exception, and so on.

It's crucial then to make certain that development team members always and only use these procedures in their WHEN clauses.

Example

Here's the kind of code you should never write inside an exception handler:

Code: Select all
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      v_msg := 'No company for id ' || TO_CHAR (v_id);
      v_err := SQLCODE;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES (v_err, v_msg, v_prog, SYSDATE, USER);
   WHEN OTHERS THEN
      v_err := SQLCODE;
      v_msg := SQLERRM;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES (v_err, v_msg, v_prog, SYSDATE, USER);
      RAISE;

There are several problems with this code:
* Exposure of logging method. What if you change the structure of the table, or decide to write to a file instead? Every handler has to change.
* Hard-coded program names. This information is available from the built-in function DBMS_UTILITY.FORMAT_CALL_STACK.

A better approach is to rely on predefined handlers. Here's a rewrite of the same exception section:

Code: Select all
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      err.handle('No company for id ' || TO_CHAR (v_id), log => TRUE, reraise => FALSE);
   WHEN OTHERS THEN
      err.handle(log => TRUE, reraise => TRUE, send_email_to_dev_team => TRUE);

Benefits

* All developers handle errors in the same way, achieving consistency in logging and also in user presentation of error feedback.
* Enhancements or changes in logging standards can be easily (almost instantly) implemented.

Challenges

Well, you need to implement the generic package, but the predefined procedure gives you a functional starting point for that. Developers must be trained in using the package, and then they must use it. Use code walkthroughs and/or automated code analysis to ensure that programmers are following the standard.
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker