#Database #Development #PL/SQL

The Power of Exception Handling

01 Aug

by alukaszewski

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:

DECLARE
-- .. declare section for variables, cursors, exceptions, ...
BEGIN
-- .. program block in which exception can be raised
EXCEPTION
  -- .. exception catch block
  WHEN [exception_name_1] THEN
  -- .. exception handling
  ;
  WHEN [exception_name_2] THEN
  -- .. exception handling
  ;
  WHEN OTHERS THEN
  -- .. catches whatever comes
END;

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:

DECLARE
-- .. declare section for variables, cursors, exceptions, ...
BEGIN
  -- .. program block in which exception can be raised
  -- .. nested begin end block.
  BEGIN
  -- .. call an function/package/procedure which could raise an exception
  EXCEPTION
    WHEN [named_exception_1]
    -- .. handle the exception
    WHEN OTHERS THEN
    -- .. handle all or maybe undefined exception in this block.
  END;
  
EXCEPTION
  -- .. exception catch block
  WHEN [exception_name_1] THEN
  -- .. exception handling
  ;
  WHEN [exception_name_2] THEN
  -- .. exception handling
  ;
  WHEN OTHERS THEN
  -- .. catches whatever comes
END;

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.

`SQLERRM`and `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.

SET SERVEROUTPUT ON;
declare
  l_a_number NUMBER;
BEGIN
  -- .. since 'ABC' is not a valid number the query will run into an
  -- INVALID_NUMBER exception.
  select to_number('ABC')
  INTO l_a_number
  from dual;
  
EXCEPTION
  WHEN invalid_number THEN
    dbms_output.put_line('We have got an INVALID_NUMBER  exception: '
        || sqlerrm || ' (' || SQLCODE || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

And the output:

We have got an INVALID_NUMBER exception: ORA-01722: invalid number (-1722)
ORA-06512: at line 6

Example TOO_MANY_ROWS

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

SET SERVEROUTPUT ON;
DECLARE
  l_owner dba_objects.owner%type;
BEGIN
  -- .. the local variable l_owner is not able to handle a set of several
  -- values. The query will run into a TOO_MANY_ROWS exception.
  SELECT owner
  INTO l_owner
  FROM dba_objects;
  
EXCEPTION
  WHEN too_many_rows THEN
    dbms_output.put_line('We have got an TOO_MANY_ROWS exception: ' ||
      sqlerrm || ' (' || SQLCODE || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

And the ouput:

We have got an TOO_MANY_ROWS exception: ORA-01422: exact fetch returns more than requested number of rows (-1422)
ORA-06512: at line 6

Example NO_DATA_FOUND

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

SET SERVEROUTPUT ON;
DECLARE
  l_owner dba_objects.owner%type;
BEGIN
  -- .. There is no owner 'ABCDE1234' and therefore the variable l_owner
  -- cannot be set. The query will run into a NO_DATA_FOUND exception.
  SELECT owner
  INTO l_owner
  FROM dba_objects
  WHERE owner = 'ABCDE1234';
  
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('We have got an NO_DATA_FOUND exception: ' || sqlerrm || ' (' || SQLCODE || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

And the output:

We have got an NO_DATA_FOUND exception: ORA-01403: no data found (100)
ORA-06512: at line 6

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.

SET SERVEROUTPUT ON;
DECLARE
  -- .. exception will be thrown if the object cannot be found.
  
  object_not_available EXCEPTION;
  l_name dba_objects.object_name%type;
  
  /**
  * Private function we can use to find a dba_object.
  *
  * @param p_object_name The object idenfied by its name.
  * @return The object owner (only for demonstration).
  * @throws object_not_available Throws when an object cannot be identified by
  *   the p_object_name.
  */
  FUNCTION pvt_find_object(
      p_object_name IN dba_objects.object_name%type)
    RETURN dba_objects.object_name%type
  AS
    x_return dba_objects.owner%type;
  BEGIN
    SELECT owner
    INTO x_return
    FROM dba_objects
    WHERE object_name = p_object_name;
    RETURN x_return;
  EXCEPTION
      -- .. we wrap the system-defined user exception in our user-defined ones.
    WHEN no_data_found THEN
      raise object_not_available;
  END;
  
BEGIN
  -- .. we call the private finder function. The object 'ABCD1234' does not
  -- exists. This will lead to an object_not_available exception.
  l_name := pvt_find_object('ABCD1234');
EXCEPTION
  -- .. we catch our user-defined exception here!
  WHEN object_not_available THEN
    dbms_output.put_line('We have got an object_not_available  exception: '
      || SQLERRM || ' (' || SQLCODE || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

Output:

We have got an object_not_available exception: User-Defined Exception (1)
ORA-06512: at line 31
ORA-06512: at line 38

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:

SET SERVEROUTPUT ON;
DECLARE
  -- .. exception will be thrown if the object cannot be found.
  object_not_available EXCEPTION;
  PRAGMA EXCEPTION_INIT(object_not_available, -20101);
  
  l_name dba_objects.object_name%type;
  
  /**
  * Private function we can use to find a dba_object.
  *
  * @param p_object_name The object idenfied by its name.
  * @return The object name (only for demonstration).
  * @throws object_not_available Throws when an object cannot be identified by
  *   the p_object_name.
  */
  FUNCTION pvt_find_object(
      p_object_name IN dba_objects.object_name%type)
    RETURN dba_objects.object_name%type
  AS
    x_return dba_objects.owner%type;
  BEGIN
    SELECT owner
    INTO x_return
    FROM dba_objects
    WHERE object_name = p_object_name;
    
    RETURN x_return;
  EXCEPTION
      -- .. we wrap the system-defined user exception in our user-defined ones.
    WHEN no_data_found THEN
      -- raise object_not_available;
      -- .. we use the raise_application_error build-in procedure
      -- to give more information to the caller.
      raise_application_error(-20101, 'No dba object could be identified by the p_object_name = '
        || p_object_name);
  END;
  
BEGIN
  -- .. we call the private finder function. The object 'ABCD1234' does not
  -- exists. This will lead to an object_not_available exception.
  l_name := pvt_find_object('ABCD1234');
EXCEPTION
    -- .. we catch our user-defined exception here!
  WHEN object_not_available THEN
    dbms_output.put_line('We have got an object_not_available exception:  ' 
      || sqlerrm || ' (' || sqlcode || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

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

We have got an object_not_available exception: ORA-20101:
ORA-06512: at line 32
ORA-01403: no data found (-20101)
ORA-06512: at line 32
ORA-06512: at line 39

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.

SET SERVEROUTPUT ON;
DECLARE
  -- .. exception will be thrown if the object cannot be found.
  
  object_not_available EXCEPTION;
  PRAGMA EXCEPTION_INIT(object_not_available, -20101);
  
  l_name dba_objects.object_name%type;
  /**
  * Private function we can use to find a dba_object.
  *
  * @param p_object_name The object idenfied by its name.
  * @return The object name (only for demonstration).
  * @throws object_not_available Throws when an object cannot be identified by
  *   the p_object_name.
  */
  FUNCTION pvt_find_object(
      p_object_name IN dba_objects.object_name%type)
    RETURN dba_objects.object_name%type
  AS
    x_return dba_objects.owner%type;
  BEGIN
    SELECT owner
    INTO x_return
    FROM dba_objects
    WHERE object_name = p_object_name;
    
    RETURN x_return;
  EXCEPTION
      -- .. we wrap the system-defined user exception in our user-defined ones.
    WHEN no_data_found THEN
      -- raise object_not_available;
      -- .. we use the raise_application_error build-in procedure
      -- to give more information to the caller.
      raise_application_error(-20101,
      'No dba object could be identified by the p_object_name = ' ||
      p_object_name);
  END;

BEGIN
  -- .. we call the private finder function. The object 'ABCD1234' does not
  -- exists. This will lead to an object_not_available exception.
  l_name := pvt_find_object('ABCD1234');
EXCEPTION
    -- .. we catch our user-defined exception here!
  WHEN object_not_available THEN
    dbms_output.put_line('We have got an object_not_available exception:  ' 
      || sqlerrm || ' (' || sqlcode || ')');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

And the final output:

We have got an object_not_available exception:  ORA-20101: No dba object could be identified by the p_object_name = ABCD1234 (-20101)
ORA-06512: at line 33
ORA-06512: at line 40