One important aspect during development is how to handle errors. In PL/SQL it is easy to detect and process predefined and user-defined error conditions called "exceptions". When an error occurs, an exception is raised. That is, normal execution stops and control is transferred to the exception-handling part of the PL/SQL block or subprogram. To handle raised exceptions, a separate routine called "exception handler" is needed.
Database errors or exceptions are errors that are raised by the database. The most common of these errors are handled by the framework, but many others are raised as is to the client. Exceptions can be divided into two types of errors, namely soft errors or hard errors.
Examples of soft errors:
Examples of hard errors
The system service Error_SYS handles all type of errors that ends up with the error information being passed to the end-user. The translating of error messages will be handled in Error_SYS.
It is important to understand how error handling works when a call to the procedure raise_application_error is used. If an error is raised, then the procedure checks if there are any applicable exception-clauses within the procedure, and in that case, the exception code is executed. Otherwise the error is raised within the call stack.
If you do not want to catch a specific exception, Oracle enables you to catch all problems in the same exception block,
except those which are already handled. Then you write WHEN OTHERS
.
OTHERS should never be used without raising of an
exception. In PL/SQL, it is always possible to catch any SQL-error by using the pragma exception_init with the proper SQL-code. An example is
described in the section Trapping Oracle exceptions.
The application should not use raise_application_error directly, instead it should go through Error_SYS. One of the main reasons is to support the translation of error messages.
These types of messages are divided into two different parts. All of them will be treated exactly the same from Oracle or the client program.
Below is a list of used Oracle exception numbers in Error_SYS. The list and contents may be changed when the system services are changed. The documented ORA numbers should not be used as private numbers within the components.
Error method | ORA-no | Type |
---|---|---|
System_General | -20100 | User defined |
Appl_General | -20105 | User defined |
Appl_Access_ | -20106 | Predefined and Protected |
Record_General | -20110 | User defined |
Record_Not_Exist | -20111 | Predefined |
Record_Exist | -20112 | Predefined |
Record_Locked | -20113 | Predefined |
Record_Modified | -20114 | Predefined |
Record_Removed | -20115 | Predefined |
Record_Constraint | -20116 | Predefined |
Too_Many_Rows | -20117 | Predefined |
Item_General | -20120 | User defined |
Item_Insert | -20121 | Predefined |
Item_Update | -20122 | Predefined |
Item_Update_If_Null | -20123 | Predefined |
Item_Format | -20124 | Predefined |
Item_Not_Exist | -20125 | Predefined |
State_General | -20130 | User defined |
State_Not_Exist | -20131 | Predefined |
State_Event_Not_Handled | -20132 | Predefined |
Security_Checkpoint_ | -20140 | Predefined and Protected |
Component_Not_Exist | -20141 | Predefined |
Compile_Error_ | -20150 | Predefined and Protected |
Note: Whenever defining error messages in the calls to system service Error_SYS, then the tag should not exceed 40 characters.
By using the public exceptions defined in Error_SYS, it is possible to trap core framework exceptions. You should however avoid trapping any protected exceptions.
Below is an example on how to trap Oracle exceptions. Please note the
exception_init tag which is in the declaration part of the procedure.
When an exception occurs it will set three parameters in the context that will be sent to the client. These context parameter are called ERROR_CALL_STACK, ERROR_FORMATTED_KEY and ERROR_KEY_MESSAGE.
ERROR_CALL_STACK includes the full call stack from PL/SQL. This parameter can be used to understand why a specific exception occurs.
ERROR_FORMATTED_KEY a string that holds a formatted version of the primary key.
ERROR_KEY_MESSAGE an IFS Message holding the names and values for the primary key.