Plsql Exception Management - Part 1
Achieving ideal error management
You should take care of the following points
1-Define your requirements clearly
2-Understand PL/SQL error management features and make full use of what PL/SQL has to offer
3-When will errors be raised, when handled?
Do you let errors go unhandled to the host, trap locally, or trap at the top-most level?
4-How should errors be raised and handled?
Will users do whatever they want or will there be standard approaches that everyone will follow?
5-Useful to conceptualize errors into three categories: Deliberate, unfortunate, unexpected
PL/SQL error management features
1- Defining exceptions
2- Raising exceptions
3- Handing exceptions
We will explain every topic individually
1-Defining exceptionsThe EXCEPTION is a limited type of data.
Has just two attributes: code and message.
You can RAISE and handle an exception, but it cannot be passed as an argument in a program.
pragma exception_init(error_name,-error_number);example :-
a-RAISE raises the specified exception by name.
RAISE; re-raises current exception. Callable only within the exception section. b-RAISE_APPLICATION_ERROR
Communicates an application specific error back to a non-PL/SQL host environment.
Error numbers restricted to the -20,999 - -20,000 range.
RAISE_APPLICATION_ERROR (num binary_integer, msg varchar2,
keeperrorstack boolean default FALSE);
RAISE_APPLICATION_ERROR (-20070, ‘Employee must be more than 18 years old.’);
3-Handing exceptionsa-The EXCEPTION section consolidates all error handling logic in a block.
b-We can use the following functions at exception section:
–SQLCODE --> get error number.
–SQLERRM--> get error message.
Returns exception in more details.
Returns the full stack of errors with line number information.number that raised the error.
This stack is available only if you the error is unhandled.
c-The DBMS_ERRLOG package
–Quick and easy logging of DML errors
d- We can use The AFTER SERVERERROR trigger to log any error have been raised at database.
Every developer should develop error package to use for exception management and he will have a lot feature
a-single procedure to raise exception.
b-single function to get error meaning from his error table.
c-He can logging exception at separate database table.
d-Single point of maintenance.
I am developing generic exception package now, I will publish it at part 2 ISA.
Mahmoud A. El-Sayed