The Power of Exception Handling

In this tutorial I would like to discuss the possibilities of exception handling in PL/SQL code. In my point of view many developers do not use the exception handling appropriately and overlook the importance of it. This leads to bad code and weakness in programming design. Before I start to illustrate some of my examples I am going to give you some brief information regarding, which types of exceptions Oracle defines in its standard and how the syntax for applying them in your code could look like.

Syntax

Generally the exception section will be embedded at the end of the defined BEGIN block within the code. Using the EXCEPTIONSstatement allows the developer to specify the exception handling routine for system or user-defined exceptions. Even Oracle also defines unnamed exceptions it seems for me a little bit wired to use this exceptions in your code. Undefined exceptions have only a code and exception message. Developers are able to catch (handle) this kind of exceptions only by using the OTHERS clause. I doubt that a developer is able to design an sophisticated handling using this type.

The most common syntax a developer will see looks as follows:

The program logic is not restricted to only one BEGIN Block. Whenever you call a program unit which can throw exceptions you are able to wrap this code in a new block. For instance:

Nested blocks

PL/SQL is able to handle nested BEGIN blocks. Define new blocks wherever an error handling needs to be applied or you need to define new local variables for a restricted scope.

Types of Exceptions

As already mentioned in the previous section Oracle provides a set of system-defined Exceptions (also known as predefined exceptions) . By wrapping these predefined exception with user-defined ones the developer is able to create a better application-based and subject-specific error handling.

System-defined exception have always an Oracle error code which can be returned by using the SQLCODE function. In addition to this error code an error message will also be passed by the exception and can be retrieved with the SQLERRM function.

Both parameters can be also set for user-defined exceptions using the EXCEPTION_INIT pragma.

SQLERRMand SQLCODE

Using SQLERRM and SQLCODE allows the user to get more information about the thrown exception. The developer is also able to define a code and a message for his own user-defined exceptions as wished.

System Named Exceptions

System-defined exceptions will be automatically raised by Oracle in cases a program violates Oracle’s system rules or any other system-inherent restriction.

Exception Description
ACCESS_INTO_NULL Initialization error. Occurs when you want to assign values to attributes with null references.
CASE_NOT_FOUND Typical CASE error. No WHEN is fulfilled and missing fallback ELSE.
COLLECTION_IS_NULL Problems with (maybe) uninitialized collection objects, e.g. varray or nested tables.
CURSOR_ALREADY_OPEN Cursor was already explicitly opened. Close the course and, if needed, reopen it again.
DUP_VAL_ON_INDEX Unique constraints by index violated.
INVALID_CURSOR Cursor operation cannot be applied. E.g. a closed cursor cannot return any values.
INVALID_NUMBER Type conversation problems. E.g. to_number('ABC') will raise this exception.
LOGIN_DENIED Login issues. Check your credentials.
NO_DATA_FOUND Typical exception will be raised in PL/SQL context. If a query will not return any values and therefore is not able to initiate a PL/SQL variable, the NO_DATA_FOUND exception will be thrown.
NOT_LOGGED_ON Database connection issues. Check your connection or session to the database.
PROGRAM_ERROR PL/SQL has got an internal problem (you could say there is an internal, unexpected exception).
SELF_IS_NULL Try to call a method of a not initiated instance (null pointer exception).
TOO_MANY_ROWS The SELECT INTO statement in your PL/SQL context returns more rows than expected. If several rows shall be loaded into a variable use a nested table.
VALUE_ERROR An arithmetic, conversation, truncation, or size-constraint error occurs.
ZERO_DIVIDE One of the most classic errors a developer knows.

Please, consider that there are still some more system-defined exceptions which I did not add to the list. In the Oracle Documentation you are able to get a complete overview.

The following you will find some code snippets for the system-defined exceptions.

Example INVALID_NUMBER

Will be thrown when there is a number conversation issue.

And the output:

Example TOO_MANY_ROWS

Will be thrown when too many values will be assigned to a variable than it can handle.

And the ouput:

Example NO_DATA_FOUND

Will be thrown when a variable when a value will be expected from the query but nothing will be returned.

And the output:

User-Defined Exceptions

One of the main benefits of user-defined exceptions is for me that you are able to design a sophisticated program design. Based on contextual business rules the developer is able to define error handling for his modules or APIs that mirror possible issues in business.

The API or program caller is able to decide what he wants to do in cases when an specific exception will be raised. Just propagating all exceptions to the user is in my point of view not always a behavior I expect from a system. It depends on the error and the situation.

In order to declare an user-defined exception you need to define it in the DECLARE block. The following code demonstrates how you could declare exception) a user-defined exception (or lets better say wrap a system-defined). You will find a function
pvt_find_object which returns the owner of the database object identified by its object_name. Instead of just handling theNO_DATA_FOUND exception the function translates this system-defined in my user-defined exception. The caller of the logic is able to handle the object_not_available exception and get a better idea of what could went wrong.

Output:

This output may look a little bit disillusioning because the database just says with calling SQLERRM that we have got an user-definedexception but not what exactly went wrong. We can overcome this weakness of certain information by not using the RAISE but the build-in RAISE_APPLICATION_ERRORprocedure which require two parameters: an error_number (of the exception) and an error_message.

As already written in the introduction you are able to use EXCEPTION_INIT pragma in order to bind an error code to an user-definedexception. This gives you in turn the possibility to bind an error message to the exception.

But, step by step: First we assign the user-defined exception an error code which can be requested by the SQLCODE function. I adapted the code:


When you execute the snippet you will get another output (look at the error code it changed):

Now you are able to set a more appropriated error messages for the object_not_available exception. You can use the assigned 20101 error code and an error messages you think fits best.

And the final output:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.