When an error message is logged into the errorlog table, there is often a need to know the entire call stack, to debug the exact location of the error. This becomes more crucial when the error occurs in a multi-procedure transaction. The tracking can be done by calling a push method on a user-defined stack at the beginning of the procedure and a pop on the stack at the completion of successful execution of the procedure. However, the coding becomes cumbersome when there are lots of procedures/functions/methods and if you forget to push or pop from a stack, you can get lot of inaccurate results.
In Oracle8 onwards, we can use the package “built-in” to retrieve the call stack. This eases the developer’s job, as only in the error logging routine do they have to access the call stack.
The package built-in to be used is this:
DBMS_UITLITY.FORMAT_CALL_STACKThis built-in returns a formatted string of maximum length of 2,000 characters containing the entire call stack with the address, the procedure name, and the line number of the error. You can write a parsing routine on the stack to get only the procedure name and the exact line number of error. Use it in the error logging procedure to debug an error efficiently.
