Why do people do this?
Let me ask all of you something.
WHY DO PEOPLE DO THIS, WHY IS THIS DONE, WHAT IS THE LOGIC, THE POINT, THE THOUGHT, THE REASONING:
6 WHEN OTHERS THEN
7 RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:'
||SQLERRM);
why??? I don't get it. Is it because
a) you don't want to know what line really caused the error?
b) you get paid by the number of lines of code you write?
c) you want to spend lots of time looking up the actual error code the you just lost?
Why is it that everyone seems to feel "I must catch all exceptions". I cannot understand this, I do not see the point, I only see this doing HARM, never any good. Why take a perfectly good error code/message and totally destroy it?
This goes to people that turn exceptions into "return codes", masking the error, why???
posted by Thomas Kyte at 7:22 AM
63 Comments:
I couldn't agree more...
[SYSADM@PSDB]> create table hx_a (no integer);
Table created.
[SYSADM@PSDB]> create unique index hx_a_idx on hx_a(no);
Index created.
my way....
[SYSADM@PSDB]> edit
Wrote file afiedt.buf
1 begin
2 insert into hx_a values(1);
3 insert into hx_a values(1);
4 exception
5 when others then
6 raise_application_error(-20001,'I caught it!!!:'||sqlerrm);
7* end;
[SYSADM@PSDB]> /
begin
*
ERROR at line 1:
ORA-20001: I caught it!!!:ORA-00001: unique constraint (SYSADM.HX_A_IDX)
violated
ORA-06512: at line 6
tom's way...
[SYSADM@PSDB]> edit
Wrote file afiedt.buf
1 begin
2 insert into hx_a values(1);
3 insert into hx_a values(1);
4* end;
[SYSADM@PSDB]> /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (SYSADM.HX_A_IDX) violated
ORA-06512: at line 3
My way cant find for a million year which line actually caused the error...
Learning process its never ending...
People learn from mistakes....
My experience has been people do this to "provide the user with a more easily understood message".
Frankly, I personally would rather give the users some simple instructions, like "when you get an error message, please write the ENTIRE MESSAGE DOWN and call the help desk BEFORE YOU CLICK ON ANYTHING!!".
Bill S.
...My experience has been people do this to "provide the user with a more easily understood message"...
Bill, I don't want to believe that someone presents "unique constraint violated" or any other message straight from DB to end users...
Most of such errors have some transactional/business meaning and others are bugs that should go straight to error report or to be sent directly by the program to the bug database.
A case of 'almost right'?
I agree it can be useful to give extra information of what has happened and where in the code it did. If you add the extra parameter after your own error message with a value of true as in
raise_application_error(-20001, 'my own error message', true)
You get the best of both worlds, your own message and the error that caused it in the first place.
From 'anonymous' comments it seems there must be some way of setting the default behaviour to true, but I have no idea where that would be. In any environment I have worked I always needed to turn it on explicitly.
Tom,
We've got code like this coming out of our ears. And people just keep doing it! I've just forwarded the link to this blog entry to our entire development staff.
Why? Well, sometimes people just don't think, and also people just don't test.
So, without thinking they write the code, and then without testing they don't realise the short-comings on their implementation.
It would be useful for a standard implementation of one of the error handling packages that are around to be published - I think you have one of these outlined on AskTom. There is also a similar implementation by Steve Feuerstein and possibly another in Mastering Oracle PL/SQL.
Along these same lines, here are a couple of interview 'quiz' questions from which I have received interesting answers from candidates who think they know what they know... Lest you think I made these up, they are taken from real production code.
Ouch!
1)
BEGIN proc_a
BEGIN
For x in (...) LOOP
< do stuff here >
END LOOP;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO err_tbl('my_app.proc_a', SQLCODE, SQLERRM, SYSDATE);
COMMIT;
END;
COMMIT;
END proc_a;
/
Q. What is likely wrong with the above anonymous block?
2)
BEGIN proc_a
< do some stuff here >
EXCEPTION
WHEN OTHERS THEN
P_OUT_VAL := 1;
P_OUT_MSG := SQLERRM;
RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM,TRUE);
dbms_output.put_line('Error: '||SQLERRM);
END proc_a;
/
Q. when (under what conditions) will the dbms_output... statement execute?
Could not agree more.
Also, I'd like to make the note that this happens not only in PL/SQL but also in Java, C++ etc. code.
I believe the concept of exception handling is not easily understood - probably because there is heated debate of checked vs. unchecked exceptions (Java) and what level should throw which exceptions (only from the same module or other modules as well) etc.
Simple answer: because they can't program.
Longer answer: Because they learned to program in the Java School and work for a company who try to shield everything technical from the end user.
me? Cynical?
Tom, first of all :
BEGIN
FOR i IN 1 .. 1000000 LOOP
DBMS_OUTPUT.PUT_LINE('THANK YOU !');
END LOOP;
END;
/
My stabs at "why" :
1) Some may think (as I once did) that if you use an exception handler you have to handle every exception yourself, so WHEN OTHERS THEN RAISE is trying to mimic the default behavior?
2) People don't realize the original line number is lost?
3) There is this universal folly of trying to hide "technical" error codes behind some "user-friendly" jargon. On the contrary, it is vitally important that the real error code and message (and the line number) be brought to the attention of the programmer immediately. The user doesn't care whether he understands the error: he just wants the bug fixed right away.
If you put a baby in a soundproof room, will he stop crying?
If you cover his bottom with perfume so you can't smell him, will that keep you from having to change his diaper? No, he'll just have to cry longer until you figure out what's wrong.
And if you don't put the RAISE after the WHEN OTHERS, it's like a soundproof room without a diaper: when you do go in there you'll have a much bigger mess to clean up!
Because they heard people say that using
When Others then
null;
is wrong and this is a better way... and it is (slightly).
I thing the worst thing developer may do is
when others then
raise_application_error(-20001,'Error on server !!!');
We got it from our developers :)
Absolutely agree. This is my #1 annoyance about most PL/SQL code out there.
I believe this is an old programming 'wisdom' where people were told their code must handle errors correctly and took this to mean that exceptions are nasty things to be 'handled' at the earliest possible moment, even if the code can't usefully deal with the exception raised at that juncture. And it's even in the 11g docs here (first bullet point):
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/errors.htm#BCFEBJBF
I should point out, that if you wanted to log the error and re-raise is, prior to 10g (dbms_utility.format_error_backtrace) is was impossible to get the full exception information in PL/SQL. As I posted on asktom 4 years ago!
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4684561825338#14139201027846
Still AFAIK, PL/SQL is not able to re-raise the same exception in a useful way, which makes it impossible to avoid destorying the full exception stack, if your code requires a 'finally' block (ie. to close open resources).
In Java (note the 'Caused by' in the exception):
public class TestException {
private static void doSomething() throws Exception {
throw new Exception("Some sort of exception.");
}
public static void main(String[] args) {
try {
doSomething();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
//cleanUp();
}
}
}
Exception in thread "main" java.lang.RuntimeException: java.lang.Exception: Some sort of exception.
at TestException.main(TestException.java:9)
Caused by: java.lang.Exception: Some sort of exception.
at TestException.doSomething(TestException.java:3)
at TestException.main(TestException.java:7)
In PL/SQL (Exception stack information lost):
1 begin
2 -- DoSomething;
3 -- CleanUp;
4 null;
5 raise_application_error(-20001, 'Some sort of exception');
6 exception
7 when others then
8 -- CleanUp;
9 raise;
10* end;
SQL> /
begin
*
ERROR at line 1:
ORA-20001: Some sort of exception
ORA-06512: at line 9
I have read books by Stephen Feuerstein, and written a lot of PL/SQL code. There seems to be a few small cases where WHEN OTHERS is ok. I have included one such example. We get dates from a feeder system that are sometimes malformed, so I have to check to see if the date is valid, then I can add it. This is the backend for an Open LDAP database.
FUNCTION is_valid_zulu_date (p_date_in IN VARCHAR2)
RETURN BOOLEAN
AS
v_mydate DATE;
BEGIN
v_mydate:= TO_DATE(REPLACE(p_date_in, 'Z', ''), 'YYYYMMDDHH24MISS');
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN RETURN FALSE;
END is_valid_zulu_date;
Unfortunately when I look at how the function is used, I realize that I am sending back the wrong code for this error. I need to find the code for a bad date.
I guess I could change the code to find out specifically what cause the error, but then I would need more codes to tell the front end what caused a record to be rejected.
IF ldap_functions.is_valid_zulu_date(newval)
THEN
UPDATE cmuAccount
SET billingFromDate=ldap_functions.convert_zulu_to_number(newval)
WHERE id = keyval;
RC := cons.c_LDAP_SUCCESS;
ELSE
RC := cons.c_LDAP_CONSTRAINT_VIOLATION;
END IF;
Firstly, I've had places where some idiot has made up standards that every PL/SQL procedure/function must include an exception handler. Dumb as standard.
Secondly, the only situation I've found where a WHEN OTHERS is useful is around an attempt to TO_DATE() a string, because there's an assortment of different error codes that could be raised depending on exactly what is wrong. So I think there can be a case for
BEGIN
v_date := to_date(v_val,v_format_mask);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Value '||v_val||' does not conform to date format '||v_format_mask);
END;
But that exception handler should cover ONLY that one assignment statement. And it's better if you have a standard error number to cover an invalid date, rather than a generic 20001.
I was just looking at some of our vendor code, I didn't think it was possible to have anything worse than
when other then null;
However, they managed to make it slighty more dubious:
-- some code
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
NULL;
END; -- exception
END crappy_proc;
-Alexander
I see a lot of code where there's a huge procedure that does a ton of stuff, and at the very end you get
exception
...
when others then
rollback;
raise;
end;
And the idea is, you can't rely on the web app to rollback the transaction if an error occurred, so you have to rollback this way. problem is, you can't find out what line number the actual error occurred any more.
Does anyone know a good way to locate the actual line number (short of scattering statement locator variables everywhere and referencing them in a raise_application_error message)?
I think you are talking to me, so I thought I would answer. Yes, I know - dbms_utility.format_error_backtrace gives the line number and all that. We aren't on 10g yet though. I like knowing the line number and I've been thinking about it.
I like to put cursor loops inside anonymous blocks. If it fails, I want to know which record it was working on when it failed. That way, I can re-create the error fast instead of waiting 2 hours or whatever for it to get to that record. When I do that, I raise an exception.
I like to output all of the input variables of the procedure before raising the error, so I know how to re-create the error.
I've read that you like to have the outer calling program just do dbms_utility.format_error_backtrace. I like it and I'm thinking about it. I think I would miss not knowing which record it was working on and the input variables though.
Since my code is instrumented, it's fairly easy to turn instrumentation on, recreate the problem and know where it failed.
I'm open to suggestions.
Mike
Sorry - I meant to say anonymous blocks inside of cursor loops. The exception handler outputs which record it was working on before raising the exception.
Mike
Hello Tom...hey, this is my first post of a comment to your site, so first of all thanks for all your great insights.
Now, as to the question of WHEN OTHERS...I think there is a very good reason to include a WHEN OTHERS in many PL/SQL programs/blocks:
The information that Oracle provides when an error occurs is very limited and often of little use. The problem is that when an error occurs, we need to know WHY (application context) more than we need to know WHAT (error code and generic message).
If you do not trap the exception in the block in which the error occurred, you often lose the opportunity to log application-specific info (local variables).
Of course in a WHEN OTHERS or just about any other handler, you want to make certain to propagate that exception OUT after logging to the error cannot be ignored.
So I recommend the following:
** Trap exceptions as close to the point of the error being raised as possible (same block).
** Do NOT use RAISE_APPLICATION_ERROR. It is a poor mechanism for logging and communicating error information.
** ALWAYS call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to get the line number on which the error was raised (10g only, unfortunately).
** Build or obtain a well-designed component (usually PL/SQL package) to handle all logging and raising requirements.
I have built such a utility, the Quest Error Manager, available as freeware from www.ToadWorld.com/SF. I use this same software in Quest Code Tester, a commercial Quest product.
I look forward to your thoughts.
Regards, SF
SF,
I disagree with your first point, but I think might be a matter of taste.
I think catching exceptions as close as possible to the point they can occur is pointless clutter. You should instead let them filter up to the top level of your code, where they can be 'handled' before returning to the client. (Unless you really can deal with the exception in a useful way, but that's rare.) Presumably you have a lot less of these exit points than points where exceptions can occur - and of course, can exceptions can be thrown from literally everywhere.
If you really think it's worth knowing the values of local variables, you should be using logging anyway.
Anonymous (Tue Jan 29, 06:24:00 PM EST),
You can't, and that's the point of my post @ Tue Jan 29, 09:56:00 AM EST, where I tried to highlight the difference between PL/SQL and Java.
The PL/SQL RAISE; is, in my opinion, defective.
From Anonymous : "...the idea is, you can't rely on the web app to rollback the transaction if an error occurred, so you have to rollback this way. "
No, Oracle does it all for you. When the statement (in this case an anonymous PL/SQL block) fails, Oracle will rollback all the work performed by that statement.
In the following example, while the first INSERT succeeds, it is rolled back when the exception is raised to the client.
create table t (val varchar2(1));
begin
insert into t (val) values ('A');
insert into t (val) values ('AA');
end;
/
select * from t;
Tom,
Your favorit site for sql injection
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
tells to avoide giving the exact error message to user which lets him know what the data base objects we posses.
Whats your take on that...
tells to avoide giving the exact error message to user which lets him know what the data base objects we posses.
Whats your take on that...
I'm not saying "show this to the end user"
I'm saying "let this error propagate all of the way back to the CLIENT APPLICATION, that piece of code that will
a) find out - DOH, there is an error
b) log it
c) tell user "so sorry, try again"
No one is saying you have to show this to the user, we are saying "let errors be what they ARE - errors, you catch ONLY that which you can handle (and hence they are not errors, you were in fact expecting them to be possible outcomes, you anticipated them)"
I also see a lot of code where people dutifully log SQLCODE. I can never understand why, and it's never used anywhere. Even worse, they concatenate it with SQLERRM so you get the error code twice in a garbled message that is less readable than the default one.
Re. learning from mistakes:
I have to say that I did not realise that the "WHEN OTHERS" could be left out, to default to the error stack:
...
exception
when DUP_VAL_ON_INDEX then
< handle this error as required >
when OTHERS then
raise;
end;
Now I realise that the code can and should be:
...
exception
when DUP_VAL_ON_INDEX then
< handle this error as required >
end;
I consider myself corrected, and promise never to do it again!
If anyone went looking for Steven's Quest Error Manager and couldn't find it, it was under the Pet Projects link here:
http://www.toadworld.com/Education/StevenFeuersteinsPLSQLObsession/MyPetProjectsandContributions/QuestErrorManagerQEM/tabid/210/Default.aspx
Tom,
Most programmers are RBO's. They have a list of rules in their heads and follow them just as religiously as the Oracle RBO did.
I plan to start cataloging these rules... sometime...
The rule that you are referring to is:
"Professional programmers write robust code. Robust code always traps errors."
When their compilers run, they interpret that as every code chunk must end with EXCEPTION.
Sometimes their RBO's get corrupted and they start to believe that FTS = Bad, INDEX SCAN = Good, or rebuild your index for better performance. Those corrupt RBOs spread these rules like memes.
Perhaps you could use your reading base to tease out the rules of the developer RBO? An online brainstorming?
Norton hasn't made an anti-viral-meme app for the brains of people.
Its not just pl/sql where this occurs, you can do the same thing in the front end. The nastiest example I ever saw was a vb application that would trap errors sent back from the database, display a nice text message withthe full error message the offending sql and parameter vaules then give the user the option to continue or cancel, if the user continued it would simply ignore the error and carry on, some interesting results from that when it came to reporting and checking data integrity.
Well now I disagree with Tom as well...
You can't let the error propagate up to the client application, because - especially in the days of multi-tiered applications and 'software as a service' - you can't trust the client to do the right thing with it.
If you rely on the client to log the error correctly, you have just created a dependency between your code and any client that may use it.
Better to keep your database code as a self-contained entity. Hide the implementation details by logging the real exception and throw a new generic 'Error occured' exception or one of a minimal number of well-documented exceptions.
malcom said...
if everyone took that approach, the database would just return
ora-0001 something went wrong, don't worry, we notified support
and nothing else. You have to have some responsibility - at some level.
At most, I would allow the TOP LEVEL invocation of a procedure be changed from:
begin p( inputs...); end;
to
begin
p( inputs...);
exception
when others then
log_error(including the backtrace/errorstack );
RAISE;
end;
But that is the ONLY place it could be, should be, might make sense to be - at the very very top level
and you must include the raise, the error MUST propagate out of the database, back to the client - so that the transactional atomicity that is promised by the database - is there. (you break statement level atomicity if you do not propagate all of the way back).
The client will get the ora error, the ora error message, but the wrong line number and module, but you have logged that somewhere so that is probably OK.
But the error MUST propagate all of the way out - else you change the way the database behaves transactionally
Yep - your 'At most... ' is basically what I've been trying to say on this thread. (Thanks for the opportunity by the way.)
And also why it's annoying that RAISE clears the exception stack, instead of pushing a new line number onto it.
Malcome - Regarding "it's annoying that RAISE clears the exception stack, instead of pushing a new line number onto it."
Exactly right. I don't think people would complain so much about raising the exception if PL/SQL pushed a new line number onto the exception stack.
Mike
Holy @#%, I found this problem today and then saw your blog. It took me 20 min to diagnose the issue because of this sloppy coding!
quoting malcolm:
"If you rely on the client to log the error correctly, you have just created a dependency between your code and any client that may use it."
But the client has to log (not to mention manage) "generic" exceptions coming from the network (eg. "database unreachable") or the client driver anyway - so once you have written the client code properly, you can handle any "generic" PL/SQL exception "for free". No need for "when others".
Well, network errors and such are different - they don't originate in the DB code. If the error originates in the the DB, it should be logged in the DB.
If I were performing a post-mortem on an error which originated in a particular component of an application, the component that errored is the first place I'd look.
Maybe if you only had one client accessing your database, but that's extremely rare. These days you're likely to have a PHP website, a reporting program, etc.
And if you believe the SOA hype, your DB code could be exposed as webservice and have hundreds of clients using it.
Yes, but you need the client context to diagnose most errors.
Say you have an SP insert_new_order that fails with a PK violation on the order table (or an equivalent ORA-20001 "cannot proceed, order duplicated"); everything is fine as far as the SP is concerned, the problem is in the client, that, say, submitted the order twice due to a client application bug.
So most errors are detected by the db, but are caused by the client(s) - hence they are better logged in the client log.
Also because malfunctions are usually reported by customers (that e.g. phone the Customer Care), so issues are typically investigated starting from the outside of the onion.
Oh, for batch jobs - the "customer" is the main procedure of the batch (say the "load_daily_data" for a DWH night job), but the same reasonings hold.
I think, the main reason for pure programming PL/SQL-Exceptions is the poor way they are supported.
number I.
when will Oracle finally implement a "finally"
(we all wait for it since ages, we all know it *sometimes* will come, we all know it sometimes has to come we all wished it for christmas since we started PL/SQL programming many years ago)
number II.
beside that, java still has many better exception/error-handling-features
than PL/SQL (and I'm not talking about oo here!)
java for example distinguishes between exceptions and errors - small but subtle difference, isn't it ?
"a reasonable application should not try to catch an error" says javadoc
"a reasonable application might want to catch an exception" says javadoc
nice, no need to discuss what should be caught by the client app and what shouldn't
I completely agree with Sokrates. PL/SQL should have a finally section. The PL/SQL team has, of course, many things on their to-do list. We, the PL/SQL development community, need to be much more proactive at communicating our needs and priorities to Oracle.
With the support of Bryn Llewellyn, the PL/SQL Product Manager, I have put together the "I Love PL/SQL And..." website through which you can "vote" on your priorities with an email to Bryn.
"Add a finally section to PL/SQL" is one of the items you can vote on. Check out:
http://www.iloveplsqland.net
thank you Steven
just signed the campaign
"Add a FINALLY clause to PL/SQL blocks"
on the "I Love PL/SQL And..." website.
Sokrates, absolutely agree, Java does exceptions better. It has the distinction between errors and exceptions. I agree, Alberto, with what you write for exceptions, but not errors.
For errors aka unchecked exceptions aka runtime exceptions, e.g. out of memory, tablespace full, null pointer:
This is your code's problem; so log at the top-most level; raise generic exception for client.
For exceptions aka checked exceptions aka garbage in garbage out, e.g. duplicate order, order not exists
Not your code's problem; maybe log it, maybe not; if you choose to log, re-raise.
Steven,
Please could you add the 'RAISE-clear-exception-stack' peeve to your list (and thanks for facilitating this). It would be better if it added the line of the RAISE to the exception stack instead of clearing it. (Like how Java has 'Caused by:', see my comment Tue Jan 29, 09:47:00 AM EST.)
If they're worried about some 'clever' (ie. not clever at all) code that relies on RAISE working exactly as it does, perhaps they could add a new keyword like RERAISE.
Malcolm.
Malcolm, that would be nice - I mean having Oracle make a distinction between Errors and Exceptions. I'd like to have both propagated to the client (that has to be informed why its request failed - "Error, ok, I'll notify the Operations department, not my business" - "Exception, ouch, let's investigate more"), but to have Errors logged somewhere in a server trace file (some are logged already).
But I would make a distinction between the two types only for logging purposes - my preference is to send them to the client in any case. It's near to impossible to say whether a PK violation on the ORDER table is a client error or an SP bug, so better tell the client and let the client investigate and troubleshoot. Also because, in my experience, in most cases, it's a client problem :)
My pet peeve is websites with tiny fonts that can't be changed.
This sort of "Trap everything - always!" demon made a guest appearance in my DBA life just yesterday. A developer was getting an utterly meaningless "-3" error message from an App. and had been struggling to work out what the underlying problem was. It was, of course, a trapped error message. The real error? Primary Key violation. Simple, meaningful, lovely. Had it fixed in a jiffy. But it meant re-running the failing code to get the real message popping-out of the App!
I suspect that this sort of guff is perpetrated out of "received wisdom" - only it ain't wise!
Just wondering why SF or TK wrote
something like that
BEGIN
--some PL/SQL-Code
EXCEPTION
WHEN OTHERS TEHN
log_error(incl. backtrace/errStack)
RAISE;
END;
I'm not an expert in PL/SQL but one of the first thing I told my guys in the new company is to log the error where it occours. Before the only raise the error formatted like TKs example.
We log the errors now with pragma autonous transaction in a bug-table.
So at every time we can look at the error and have the exact line number by hand.
Of course we log not in every exception block, just there where it is useful (don't ask me when it's really useful and when not ;) )
Thx to that to SF, he gave me the ideas at a workshop in Munich in March last year.
Tom,
Wouldn't the exception stack be cleared by using the top level PL/SQL block you mentioned ? Or do we need to have the exception handling in the client only if we need to get the line number etc.
@last "i won't use a name" person...
begin
p( inputs...);
exception
when others then
log_error(including the backtrace/errorstack );
RAISE;
end;
including the backtrace/errorstack, which in current releases (software written this century) includes the original offending line number.
but yes, if you catch it in plsql (because you don't "trust" the client, which means you don't believe your coders know how to handle an error from anything), then - the original error line number must be manually preserved by you.
A comment to the second part of the original posting, addressing the idea of turning errors into return codes.
Not all languages, nor the way SQL is embedded into them, handle the concept of an exeception, and especially the difference between an exception and an error, gracefully.
CAVEAT: My (perhaps dated) experience here is in DB2 and COBOL, but from what I have seen, the Oracle/Cobol interface is rather similar.
Calling, say, a PL/SQL routine from COBOL (using Pro*Cobol) and handling exceptions using the WHENEVER SQLERROR GOTO/PERFORM construct clutters up the structure of your code horribly. It is really only useful as an emergency halt-everyting last way out.
If I put my (business)transaction logic into a PL/SQL procedure on the server and call this from COBOL, I would rather use return codes to handle exceptions in the normal flow of code, and reserve WHENEVER SQLERROR for errors.
There was a remark somewhere on this thread about "code written this century". Well, the reality in many places is still dealing with code that is old enough to legally drive a car or buy a beer in even the most conservative states of the US :-)
Best
Bo Brunsgaard,
ex COBOL programmer
@Bo -
but cobol doesn't have exceptions, so the ONLY way for cobol to handle this is...
via a return code.
which Oracle does nicely already.
with a nice error message.
There is no need EVER for the plsql to catch the exception (that it cannot deal with, it is an unexpected error of some sort) and take the MEANINGFUL error number (the ora-xxxxx number your cobol gets as a return code) and error message with the offending line of plsql code and turn that into say "-1 'something bad happened'"
Cobol
a) gets notified of an error
b) sqlcode is set
c) error messgae is available
and you choose how to deal with that (either exec sql whenever or just inspect the sqlcode - the return code - yourself)
If plsql you call HIDES the error, makes it so that it is not actually apparent there was an error OR it mucks with the ora-xxxx message - you lose, you either have a really bad bug or you lose information, useful information.
Nothing good comes of it.
Hello Tom,
I'm maintaining an application where every procedure ends with an WHEN OTHERS logging arguments and error messages in a DB table. This ends up with every error written many times as it goes up the calling hierarchy, each time getting longer because the arguments and messages of every level are added.
To get out of this we found this solution:
New code has (as a matter of principle) no errror handling. If we have e.g. a business rule violation we take the appropriate errorcode from our named exceptions (PRAGMA EXCEPTION_INIT) and raise it with RAISE_APPLICATION_ERROR( named_error_code, argumentstring).
Now every existing WHEN OTHERS just looks if the SQLCODE is from a named exception and then just reraises it. Loosing line number is no problem, as the argumentstring contains the necessary information.
At the top level before the client we write into our logging table and give the error to the client. With the SQLCODE the client can look into our error message table and knows which message to show to the user.
Older parts of the programm are treated as before. This way I hope we found the best solution for our existing code.
Regards
Marcus
Is this a bug or a feature?
SQL> create or replace function scott.a1 return varchar2
2 is
3 l_par varchar2(1);
4 begin
5 select dummy into l_par from dual where 1=2;
6 return l_par;
7 end;
8 /
Function created.
SQL> select scott.a1 from dual;
A1
-------------
Exception is not raised.
But...
SQL> create or replace function scott.a1 return varchar2
2 is
3 l_par varchar2(1);
4 begin
5 select dummy into l_par from dual where 1=2;
6 return l_par;
7 exception
8 when others then raise_application_error(-20000,sqlerrm);
9 end;
10 /
Function created.
SQL> select scott.a1 from dual;
select scott.a1 from dual
*
ERROR at line 1:
ORA-20000: ORA-01403: no data found
ORA-06512: at "SCOTT.A1", line 8
@Yap
It is a "feature", or rather "a side effect"
Think about this - what does a CLIENT do when it runs a query and receives "no data found"?
when it runs A QUERY and receives no data found, it *stops*.
In this one case, it is a side effect of calling plsql from sql and having the plsql raise an error that the client interprets as "not an error"
malcolm said:
If they're worried about some 'clever' (ie. not clever at all) code that relies on RAISE working exactly as it does, perhaps they could add a new keyword like RERAISE.
Wouldn't we then also need FOLD?
(It's possible I've been playing too much online poker)
Steven Feuerstein said....
i dont think you need a FINALLY block steven...
the following code
BEGIN
... my code
EXCEPTION
WHEN NO_DATA_FOUND THEN
... handle NDF
WHEN OTHERS THEN
... log error
FINALLY
... clean up
END;
can be achived in pl/sql as such like this
BEGIN
BEGIN
... my code
EXCEPTION
WHEN NO_DATA_FOUND THEN
... handle NDF
WHEN OTHERS THEN
... log error
END;
... clean up
END;
do write your comments on this.
Karthick, if you are content to perform such an unnatural to get "finally" behavior, you are most welcome to it!
:-)
But I am sure you would agree that it would be much, much better for Oracle to provide this natively in the language....
SF
Karthick,
Nope, that doesn't really work...
Since it's utterly mandatory to raise an exception, even if you choose to 'handle' them (ignoring philosophical disagreement over why for the moment), your code should be:
BEGIN
BEGIN
... my code
EXCEPTION
WHEN NO_DATA_FOUND THEN
... handle NDF
RAISE;
WHEN OTHERS THEN
... log error
RAISE;
END;
... clean up
END;
... and then your clean up code is never executed.
Definition of a 'finally' block is that it is *always* executed if the 'try' block is. (Even if there's an exception, even if there's an exception in the exception handler.)
So you have to do this:
BEGIN
... my code
... clean up
EXCEPTION
WHEN NO_DATA_FOUND THEN
... handle NDF
... clean up
RAISE;
WHEN OTHERS THEN
... log error
... clean up
RAISE;
END;
So the effect of NOT having FINALLY in PL/SQL is that you have to repeat the clean up code.
Malcolm.
On taking a second look into this made me realize the code repetition. Mainly when we need to reset some settings when the program ends. I was able to see in some of our code where they have done that both in the exception section and in the main code section. As malcolm rightly pointed out its a double job. So I am on your side steven :-)
FINALLY would be a great idea
just a little comment: when you use "log_error" technique, just make sure that that you will NEVER EVER encounter a "disk is full" error, that can become very, very ugly, as log_error procedure will itself trigger an error...
Personally, I plan to review my app error handling politic, so it depends on the context. (by example, if a customer is created by the customer service, a webshop, or a bought list of addresses, an unexpected error will have a different effect...).
I tend to use WHEN OTHERS and then write the exception to syslog, then raise. This does mask the line number of the actual exception to the client, but gives me a complete history of exceptions in my syslog files.
I've never had an error. Wow, I guess I should count my blessings.
One thought about "to avoide giving the exact error message to user".
You may use special list of errors' text that may show to user.
@anonymous
I asked why people do this:
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:'
||SQLERRM);
they are clearly not "mapping the error message"
and I don't see how someone could take a when others (catchall of all errors) and return a sensible error message.
besides, it is the job of the user interface to present to the end user the error message that is relevant in their context.
My firm, unshakeable belief is the client application that initiated the request has the right and the need to know what happened. THEY deal with the user and what the interface should present - but they need to know.
POST A COMMENT
<< Home