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.
Soft and hard errors¶
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.
- Soft errors: Soft errors are often described as application errors that can be corrected within the application code. In most cases these kinds of errors result in an error message to the end user. The message is closely connected to the application and should be a translated text that includes business terms, well known by the end user.
- Hard errors: Hard errors are often described as exceptional errors that are unforeseen by the application. These errors are of technical character and are often presented in a server dependent language. For Foundation1 these kinds of errors are typically Oracle errors. Some examples are given below, illustrating the difference between the two error types.
Examples of soft errors:
- Given financial account does not exist
- Too many default roles for an employee
- An unpaid customer invoice cannot be removed
Examples of hard errors
- ORA-01400: mandatory (NOT NULL) column is missing or NULL
- ORA-00001: unique constraint (SYS4MS.MS_ROLE_PK) violated
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.
Business Logic 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.
- Predefined texts - This means that there are interfaces in Error_SYS that have predefined error texts to be used when raising the error to the database session. For non-protected interfaces it is always possible to override them with a user defined message instead.
- User defined texts - This means that there are interfaces in Error_SYS that requirean error text to be defined in a parameter.
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.
|Appl_Access_||-20106||Predefined and Protected|
|Security_Checkpoint_||-20140||Predefined and Protected|
|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.
Trapping IFS framework exceptions¶
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.
Trapping Oracle 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.