Downloaded 13 times
Uploaded byLarry Nung
PL/SQL & SQL CODING GUIDELINES – Part 7
This document discusses guidelines for handling exceptions in PL/SQL and SQL code. It provides 57 guidelines organized under the headings of "Exception Handling" and "Reference". The guidelines recommend against directly handling unnamed exceptions by error number, assigning predefined exception names to user-defined exceptions, overuse of the WHEN OTHERS clause without specific handlers, and directly using Oracle's predefined exceptions. The document emphasizes exception handling best practices like declaring custom exceptions, avoiding hardcoded error messages, and ensuring all exceptions are properly handled.
PL/SQL & SQL CODING GUIDELINES – Part 7
- 1. PL/SQL & SQL CODINGGUIDELINES – PART 7 Larry Nung
- 2.
- 3.
- 4. 51. NEVER HANDLEUNNAMED EXCEPTIONS USING THE ERROR NUMBER.
- 5. BAD BEGIN ... EXCEPTION WHEN OTHERS THEN IF SQLCODE= -2291 THEN ... END IF; END;
- 6. GOOD DECLARE e_parent_missing EXCEPTION; PRAGMA EXCEPTION_INIT(e_parent_missing,- 2291); ... BEGIN ... EXCEPTION WHENe_parent_missing THEN ... END;
- 7. 52. NEVER ASSIGNPREDEFINED EXCEPTION NAMES TO USER DEFINED EXCEPTIONS.
- 8.
- 9. GOOD DECLARE empty_value EXCEPTION; ... BEGIN ... EXCEPTION WHEN empty_value THEN sys.dbms_output.put_line(co_empty_value); WHENno_data_found THEN sys.dbms_output.put_line(co_no_data_found); END;
- 10. 53. AVOID USEOF WHEN OTHERS CLAUSE IN AN EXCEPTION SECTION WITHOUT ANY OTHER SPECIFIC HANDLERS.
- 11. BAD EXCEPTION WHEN OTHERS THEN IF SQLCODE= -1 THEN update_instead (...); ELSE err.log; RAISE; END IF;
- 12. GOOD EXCEPTION WHEN DUP_VAL_ON_INDEX THEN update_instead (...); WHENOTHERS THEN err.log; RAISE;
- 13. 54. AVOID USEOF EXCEPTION_INIT PRAGMA FOR A -20,NNN ERROR
- 14. BAD CREATE OR REPLACEPROCEDURE check_hiredate (date_in IN DATE) IS BEGIN IF date_in < ADD_MONTHS (SYSDATE, -1 * 12 * 18) THEN RAISE_APPLICATION_ERROR ( -20734, 'Employee must be 18 years old.'); END IF; END check_hiredate;
- 15. GOOD CREATE OR REPLACEPROCEDURE check_hiredate (date_in IN DATE) IS BEGIN IF emp_rules.emp_too_young (date_in) THEN err.raise (errnums.emp_too_young); END IF; END check_hiredate;
- 16. 55. AVOID USEOF THE RAISE_APPLICATION_ERROR BUILT-IN PROCEDURE WITH A HARD-CODED - 20,NNN ERROR NUMBER OR HARD-CODED MESSAGE.
- 17.
- 18.
- 19. 56. AVOID UNHANDLEDEXCEPTIONS
- 20. BAD CREATE OR REPLACEPACKAGE BODY department_api IS FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE IS l_department_name departments.department_name%TYPE; BEGIN SELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id; RETURN l_department_name; END name_by_id; END department_api;
- 21. GOOD CREATE OR REPLACEPACKAGE BODY department_api IS FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE IS l_department_name departments.department_name%TYPE; BEGIN SELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id; RETURN l_department_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RAISE; END name_by_id; END department_api;
- 22. 57. AVOID USINGORACLE’S PREDEFINED EXCEPTIONS
- 23.
- 24.
- 25.
- 26. REFERENCE Trivadis PL/SQL& SQL Coding Guidelines Version 2.0 http://www.trivadis.com/sites/default/files/downloads/PL SQL_and_SQL_Coding_Guidelines_2_0_HiRes.pdf 26
- 27.
- 28.