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:
SELECT ...
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN ...
-- Which SELECT statement caused the error?
END;
DECLARE----- WTF EXCERPT END -----
stmt INTEGER := 1; -- designates 1st SELECT statement
BEGIN
SELECT ...
stmt := 2; -- designates 2nd SELECT statement
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO errors VALUES ('Error in statement ' || stmt);
END;
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:
declareOutput:
x number;
begin
x := 1 / 0;
dbms_output.put_line(x);
exception
when others then
dbms_output.put_line(SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
And it took them only 10 versions to implement.
You may be interested in this: the I-Hate-Oracle Club ;-)
ReplyDeletehttp://forums.thedailywtf.com/forums/17.aspx
Count me in :)
ReplyDeleteLet me get this straight - the documentation example does not do what you want, so you think the technology is deficient?
ReplyDeleteFor the record, Ada (of which PL/SQL is a dialect) is all about modularity. It is unnatural to assume global scope the way you would in PHP. A real life programmer would say something like:
DECLARE
foo EXCEPTION;
bar EXCEPTION;
BEGIN
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN RAISE bar;
END;
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN RAISE foo;
END;
EXCEPTION
WHEN foo THEN ...
WHEN bar THEN ...
WHEN OTHERS THEN RAISE;
END;
...or even better, encapsulate each scope unit in a different function.
Ryuo,
ReplyDeleteproblem is that when you've got a large code base along with features like Oracle AQ and your PL/SQL code is running asynchronously triggered by jobs (in other words, hard to simulate the processes by hand) AND you cannot break the routine with RAISE, but just log errors for future diagnostics, it's a pain in the ass to debug. That's it.
Oh, now I understand. The Oracle "insert into errors" feature is not good for much of anything, so what most people do is build a custom logging subsystem. Basically, that consists of:
ReplyDelete1. a table to store the messages
2. a message constructor (e.g., capture the SQLCODE)
3. A logging function with PRAGMA AUTONOMOUS_TRANSACTION (to log in a separate process in case the main transaction fails).
That's a fair amount of custom code, but every systems needs something like that, and it is easy to extend into a major subsystem should you want to.
Indeed oracle has full tracing capabilities, try this:
ReplyDeletehttp://www.unix.com.ua/orelly/oracle/guide8i/ch07_02.htm.
still not an option for normal production operation, but with that you can easily debug complex asyinchronous procedures.
have fun
not sure why to "hate" RDBMS for this. Just use PLOG and that, pretty much, gets everything you need.
ReplyDeleteIf the question is - that pl.sql should be improved - well, no doubts. Like pretty much everything else.
Peace.
What I find amusing is the combination of the subtitle of the blog: "A humble blog about life and software development"
ReplyDeleteAnd the not-so-humble, no wait..., the damn-not-humble-at-all startup line of the post: "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."
If you think you're smart and wise enough to make that kind of judgement about PL/SQL and PHP you're neither smart nor wise.
Having said that, thank's for sharing the final part of the post. That definitely is worth reading and useful.
Every time I read this post I have fun, specially reading the readers' comments. When I had first seen huge applications developed using Oracle, I wondered how one could choose it instead of other "featureful" programming languages and plataforms (specially the open source ones, that I like most).
ReplyDeleteBut now, I face Oracle's deficiencies in a neutral way. Instead of keep complaining about the language (which is funny sometimes), I prefer to challenge myself to learn and master the platform. And I use its weakness to challenge myself to overcome them.
Learning is fun ;)
Indeed, the JAVA call stack is much preferrable!!!!
ReplyDelete