Oracle PL/SQL is definitely the worst programming language I've ever encountered. Some time ago I thought that PHP was the worst, but well, things change.
I've been searching for the source of a weird CLOB related bug in a big pile of PL/SQL
sh.. mess for a couple of days till I got fed up and decided to find a way to get the stack trace or at least the last line of code where the error was triggered from. Would you believe that before Oracle 10g there was no normal way to get the trace? Here's some Daily WTF material from the official PL/SQL User's Guide and Reference.
----- WTF EXCERPT START -----
Using Locator Variables to Identify Exception Locations
Using one exception handler for a sequence of statements can mask the statement that caused an error:
BEGINNormally, this is not a problem. But, if the need arises, you can use a locator variable to track statement execution, as follows:
WHEN NO_DATA_FOUND THEN ...
-- Which SELECT statement caused the error?
DECLARE----- WTF EXCERPT END -----
stmt INTEGER := 1; -- designates 1st SELECT statement
stmt := 2; -- designates 2nd SELECT statement
WHEN NO_DATA_FOUND THEN
INSERT INTO errors VALUES ('Error in statement ' || stmt);
Yes, they even have a name for this. Locator Variables. Damn. I can't decide whether to laugh or to cry...
On a good note, since Oracle 10g you can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to get a string representation of stack trace with procedure names and code line numbers. They still forgot to add this into the "Handling PL/SQL Errors" section of their manual...
So, here's how you get the stack trace:
x := 1 / 0;
when others then
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
And it took them only 10 versions to implement.