Error Handling in PL/SQL

When PL/SQL detects an error, normal execution stops and an exception is raised.  This exception can be captured and processed within the block by the exception handler if it is present.  If the block does not contain an exception handler section, the exception propagates outward to each successive block until a suitable exception handler is found or the exception is presented to the client application.

Oracle provides many predefined exceptions for common error conditions, like NO_DATA_FOUND when a SELECT ... INTO statement returns no rows.  The following example shows how exceptions are trapped using the appropriate exception handler.  It also shows how to return the username associated with a specific user_id value.

SET SERVEROUTPUT ON
DECLARE
  l_user_id   all_users.username%TYPE := 0;
  l_username  all_users.username%TYPE;
BEGIN
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  user_id = l_user_id; 

  DBMS_OUTPUT.put_line('l_username=' || l_username);
END;
/
l_username=SYS

PL/SQL procedure successfully completed.

SQL>

That works fine for user_id values that exist, but look what happens when one is used that does not exist.

SET SERVEROUTPUT ON
DECLARE
  l_user_id   all_users.username%TYPE := 999999;
  l_username  all_users.username%TYPE;
BEGIN
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  user_id = l_user_id; 

  DBMS_OUTPUT.put_line('l_username=' || l_username);
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

SQL>

This is not a very user friendly message, so this error can be trapped and something else produced that is more meaningful to the users.

SET SERVEROUTPUT ON
DECLARE
  l_user_id   all_users.username%TYPE := 999999;
  l_username  all_users.username%TYPE;
BEGIN
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  user_id = l_user_id; 

  DBMS_OUTPUT.put_line('l_username=' || l_username);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('No users have a user_id=' || l_user_id);
END;
/

No users have a user_id=999999

PL/SQL procedure successfully completed.

It is possible to declare your own exceptions for application specific errors, or associate them with Oracle "ORA-" messages that are executed using the RAISE statement.  The example below builds on the previous example using a user defined exception to signal an application specific error.

SET SERVEROUTPUT ON
DECLARE
  l_user_id   all_users.username%TYPE := 0;
  l_username  all_users.username%TYPE; 

  ex_forbidden_users  EXCEPTION;
BEGIN
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  user_id = l_user_id; 

  -- Signal an error is the SYS or SYSTEM users are queried.
  IF l_username IN ('SYS', 'SYSTEM') THEN
    RAISE ex_forbidden_users;
  END IF; 

  DBMS_OUTPUT.put_line('l_username=' || l_username);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('No users have a user_id=' || l_user_id);
  WHEN ex_forbidden_users THEN
    DBMS_OUTPUT.put_line('Don''t mess with the ' || l_username || ' user, it is forbidden!');
END;
/

Don't mess with the SYS user, it is forbidden!

PL/SQL procedure successfully completed.

The code still handles users that do not exist, but now it also raises an exception if the user returned is either SYS or SYSTEM.

My Ideal Environment

At this point I think it is worth spending a little time describing what I believe makes an ideal environment for application development.  It may not be to everyone’s liking, but I have always found it to be the most secure and flexible approach thus far.

I believe the use of PL/SQL Application Program Interfaces (APIs) should be compulsory.  Ideally, client application developers should have no access to tables for views.  If they require some information, an API should be written to provide it for them.  This has a number of beneficial effects, including:

• It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers, code is simply added into the API.
• It prevents people who do not understand SQL from writing inefficient queries. All SQL should be written by PL/SQL developers or DBAs, thus reducing the likelihood of bad queries.
• The underlying structure of the database is hidden from the client application developers, so structural changes can be made without client applications being changed.
• The API implementation can be altered and tuned without affecting the client application layer,  thus reducing the need for redeployments of applications.
• Security and auditing mechanisms can be implemented and maintained at the database level, with little or no impact on the client application layer.
• The same APIs are available to all applications that access the database, resulting in reduced duplication of effort.

This may sound a little extreme, but this approach has paid dividends for me again and again.  I will elaborate on these points to explain why this approach is so successful.

My first point related to the use of triggers.  It seems that every company I have worked for has at one time or another used triggers to patch a “hole” or implement some business functionality in their application, and every time this occurs, my heart sinks. 

Invariably these triggers get disabled by accident and bits of functionality go AWOL, or people forget they exist and recode some of their functionality elsewhere in the application.  It is far easier to wrap the table level processing in an API that includes all necessary functionality, thereby removing the need for table triggers entirely.

My next point relates to the quality of SQL in applications.  Many client application developers have to be able to work with several database engines, and as a result are not always highly proficient at coding against Oracle databases.   Added to that, some development architectures such as J2EE positively discourage developers from working directly with the database.  

Most people would not ask an inexperienced person to fix their car; likewise it is not wise to ask one to write SQL.  Abstracting the SQL in an API leaves client application developers to do what they do best, while PL/SQL programmers should be left to do what they do best and write the most efficient SQL and PL/SQL possible.

During the lifetime of an application, many changes can occur in the physical implementation of the database.  It is nice to think that the design will be perfected before application development starts, but in reality this seldom seems to be the case.  The use of APIs abstracts the developers from the physical implementation of the database, allowing change without impacting on the application.

In the same way, it is not possible to foresee all possible performance problems during the coding phase of an application.  Many times developers will write and test code with unrealistic data, only to find the code that was working perfectly in a development environment works badly in a production environment.  If the data manipulation layer is coded as an API, it can be tuned without recoding sections of the application after all the implementation has changed, not the interface.

It is a sad fact that auditing and security are often only brought into focus after something bad has happened.  Having the ability to revise and refine these features is a massive bonus.  If this means you have to refactor your whole application, you are going to have problems.  If on the other hand it can be revised in your API layer you are on to a winner.

A problem I see time and time again is that companies invest heavily in coding their business logic into a middle tier layer on an application server.  They then want to perform data loads either directly into the data base, or via a tool that will not link to their middle tier application.  As a result they have to recode sections of their business logic into PL/SQL or some other client language. 

Remember, it is not just the duplication of effort during the coding, but also the subsequent maintenance.  Since every language worth using can speak to Oracle via OCI, JDBC or ODBC, it makes sense to keep the logic in the database and let every application or data load use the same programming investment.

Of course, total control of the development environment may not be available, but it is worth keeping these points in mind.