Most programmers don’t like exceptions, but you should consider them as your closest friends. They are the ones that honestly say what is wrong with your program. We cannot foresee all possible problematic events, and even the best programmers write bugs.

Exceptions

There are three kinds of exceptions

  • Internally defined: A system error, defined by Oracle, that occurs.
  • Predefined: The most common internally defined exceptions that are given predefined names.
  • User defined: A logical error which you define and raise yourself

System errors could occur from improper coding, like the “ORA-01001: Invalid cursor”, which you should try to fix as soon as possible in your code. And the “TOO_MANY_ROWS”-error might give you clues about bad data quality. To resolve these bugs, it is important to know where, when and why it happened.

But system errors could also occur from hardware failures, like the “ORA-12541: TNS: no listener”, when an ftp-server might be unreachable over the network. In that case, all you can do, and should do, is provide proper error handling and transaction management, and give as detailed information as possible about this situation to the people that need to know.

These system-errors always have an error number assigned, so you can easily identify the error. The 22 predefined exceptions also have a name assigned, which allows for easier, and more readable exception handling. For the other, non-predefined, system-errors, a name can be linked by using the pragma “EXCEPTION_INIT”.

DECLARE
  network_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(network_error, -12541);
BEGIN
  ...
EXCEPTION
  WHEN too_many_rows THEN
    ...
  WHEN network_error THEN
    ...
END;

User defined errors we will raise ourselves. They can be given a number and a name.

To raise a user defined error with a chosen number and error message, we call the procedure “RAISE_APPLICATION_ERROR”. Oracle allows us the create error numbers in the range of -20000 to -20999. This allows us to create 1000 unique error codes for our logical errors throughout our application. Just like we did for system errors, we can name our user defined errors by using the pragma “EXCEPTION_INIT”.

BEGIN
  RAISE_APPLICATION_ERROR(-20000,’Logical error occured’);
END;

If we do not care about the error code and error message, and we will foresee an exception block to directly handle the error, we could also raise errors by the keyword “RAISE”, followed by the exception name. If we do not handle the error, the error ORA-65000, “Unhandled user exception” is propagated.

DECLARE
  logical_error EXCEPTION;
BEGIN
  RAISE logical_error;
EXCEPTION
  WHEN logical_error THEN
    ...
END;

Besides user defined errors, we could also raise one of the predefined errors. Although it is recommended to use proper a description for your errors, instead of recycling error codes.
In an exception block, the keyword “RAISE” could also be used without an exception name, which can be useful to add logging or to execute clean-up code, before propagating the error.

BEGIN
  RAISE no_data_found;
EXCEPTION
  WHEN no_data_found THEN
    ...
    RAISE;
END;

Error Propagation

When an error occurs, further execution of the execution block is halted, and an appropriate exception handler is searched. If no handler is found in the current block, the error is propagated to the calling block. As a side note, errors that occur in the declaration section are also handled in the calling block.

BEGIN
  BEGIN
    RAISE no_data_found;
  END;
EXCEPTION
  WHEN no_data_found THEN
    ...
END;

Transactions stay pending when errors are raised, it is our own responsibility to rollback or commit, although most frameworks will rollback an unhandled exception themselves.

Handling errors

Errors will occur, and when they do, it is important that we know about them, and get as much details from them as possible. When troubleshooting we need the “what”, “where”, “when” and “why”.

For this reason, I add following block to every public function or procedure:

PROCEDURE test(p_param1 IN NUMBER
              ,p_param2 IN VARCHAR2)

IS
BEGIN
  ...
EXCEPTION
  WHEN OTHERS THEN
    log_error($$PLSQL_UNIT,$$PLSQL_LINE,p_param1,p_param2);
    RAISE;
END;

The “log_error”-procedure defined as autonomous transaction, writing the information we need for troubleshooting to a table. Simplified, it looks like this:

PROCEDURE log_error(p_object_name IN log_messages.object_name%TYPE
                   ,p_line         IN log_messages.line%TYPE
                   ,p_attribute1   IN log_messages.attribute1%TYPE   DEFAULT NULL
                   ,p_attribute2   IN log_messages.attribute2%TYPE   DEFAULT NULL
                   ,p_attribute3   IN log_messages.attribute3%TYPE   DEFAULT NULL
                   ,p_attribute4   IN log_messages.attribute4%TYPE   DEFAULT NULL)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log_messages(seq
                          ,object_name
                          ,line
                          ,error_stack
                          ,error_backtrace
                          ,call_stack
                          ,attribute1
                          ,attribute2
                          ,attribute3
                          ,attribute4
                          ,log_dt
                          ,log_user)
                   VALUES(seq.nextval
                          ,p_object_name
                          ,p_line
                          ,dbms_utility.format_error_stack
                          ,dbms_utility.format_error_backtrace
                          ,dbms_utility.format_call_stack
                          ,p_attribute1
                          ,p_attribute2
                          ,p_attribute3
                          ,p_attribute4
                          ,SYSDATE
                          ,USER);
  COMMIT;
END log_error;

Going over the different parameters:

Seq:

Adding a sequence will allow us to order the message in the order they occurred

Object_name/Line:

By using the PL/SQL-directives $$PLSQL_UNIT and $$PLSQL_LINE, we know where the log was created.

Error_stack/Error_backtrace/Call_stack:

In the dbms_utilty package, we find three functions that give us valuable information about the error that was raised. The error stack gives us an overview of all the errors that were raised, giving more information than “SQLCODE” and “SQLERRM”. The error stack gives us the exact line number where the error occurred. The call stack will give us information about which code called the procedure or function raising the error. Later on, I will explain this in more detail.

Attribute1-4:

To reproduce the error, it can be helpful to know which parameters where used when the error occurred.

Log_dt/Log_user:

Further helpful information are the “who” and “when”. Depending on the technology used, you might want to use your own logic to retrieve the application user instead of the Oracle user.

Dbms_utilty example

Let’s take a look at what these functions produce by executing following block of code:

DECLARE
  PROCEDURE proc1
  IS
  BEGIN
    RAISE NO_DATA_FOUND;
  END;

  PROCEDURE proc2
  IS
  BEGIN
    proc1;
  END;

  PROCEDURE proc3
  IS
  BEGIN
    proc2;
  EXCEPTION
    WHEN OTHERS THEN
      log_error($$PLSQL_UNIT,$$PLSQL_LINE);
      RAISE_APPLICATION_ERROR(-20001,'Unhandled exception occured.',TRUE);
    END;

  PROCEDURE proc4
  IS
  BEGIN
    proc3;
  END;

BEGIN
  proc4;
EXCEPTION
  WHEN OTHERS THEN
    log_error($$PLSQL_UNIT,$$PLSQL_LINE);
  RAISE;
END;

As you can see, I’ve created four procedures, calling each other. Proc1 raises an error, which is caught in proc3, logged, and followed by the raise of a different error. There is a second log written in the anonymous block, so we end up with two records.

The two call stacks are

“ORA-01403: no data found”

And

“ORA-20001: Unhandled exception occured.
ORA-06512: at line 21
ORA-01403: no data found”

As you can see in the code of proc3, I have added a third parameter to the “RAISE_APPLICATION_ERROR”-procedure, telling it to keep the error stack. Our first message tells us a “no data found”-error occurred, our second message tells us we had two errors, first the ORA-01403, followed by the user-defined ORA-20001. The ORA-06512 is merely telling us the line number.

The two backtraces are:

“ORA-06512: at line 5
ORA-06512: at line 11
ORA-06512: at line 17″

And

“ORA-06512: at line 21
ORA-06512: at line 27
ORA-06512: at line 30″

The first back trace tells us that the error occurred at line 5, after proc1 was called at line 11, after proc2 was called on line 17. It points us to the error. If we look at the second backtrace, it points us to line 21, were we find the call to “RAISE_APPLICATION_ERROR”. Notice how it loses the information of the original error on line 5, so it is vital to store the back trace whenever we catch an exception.

Finally, looking at the first call stack, we find following information:

“—– PL/SQL Call Stack —–
object      line  object
handle    number  name
1CA24A88         1  anonymous block
1C9CDCC0        10  procedure SCOTT.LOG_ERROR
1CA121EC        20  anonymous block
1CA121EC        27  anonymous block
1CA121EC        30  anonymous block”

It points us to the original call. The actual log is written in the procedure “log_error”, which was called in proc3 at line 20. Proc3 was called by proc4 at line 27, and proc4 was called at line 30. Where the back trace tells us everything that happened between the error and the logging, the call stack tells us everything that happened before the error.

Conclusion

Troubleshooting errors can be difficult, especially if you don’t know what was going on. But by preparing yourself for these situations, and making sure you have all the information you need, you will be able to pinpoint the problem much faster.

I tried to highlight the most important aspect of error handling in my opinion. If you feel something is missing, please share your knowledge by leaving a comment.

Tags: