Skip to content

Error Handling

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

Error_SYS

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.

Procedure raise_application_error

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.

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

Example

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.

Context variables

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.