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.
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;
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.
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:
Adding a sequence will allow us to order the message in the order they occurred
By using the PL/SQL-directives $$PLSQL_UNIT and $$PLSQL_LINE, we know where the log was created.
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.
To reproduce the error, it can be helpful to know which parameters where used when the error occurred.
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.
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”
“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″
“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.
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.