by 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.