Ouch, that hurts
This one just came across the wire and I groaned out loud. I really wish "WHEN OTHERS" would simply raise "illegal instruction" when executed.
I see selecting sequence.nextval from dual - totally not necessary and an utter waste of CPU cycles.
I see an append with a values clause. insert /*+ append */ ... values works just as well as insert /*+ dont_understand_how_this_works */ ... values does. (insert append works with SELECT only, never never values)
I see a when others then NULL. That is the worst thing a programmer could ever do. Basically you are saying "it does not matter if this code executes or not". Well guess what - if it does not matter DO NOT EXECUTE IT ever!
I see a nologging attempt that is as effective as using the identifier I_truly_dont_get_it. Nologging is an attribute of a segment that can be used for certain bulk operations. It is not a modifier on any DML clause.
I'm suspect of an autonomous transaction with a when others null even more than usual here, since it neither COMMITS nor ROLLSBACK - in this case, not an issue but given the use of the when others, likely a problem else where in this system.
All I can say is ouch, ouch, OUCH.
And - I hate "when others", it should be removed given that it is never used properly in real life.
posted by Thomas Kyte at 9:35 PM
51 Comments:
I would avoid also to call a column "TIMESTAMP" or "SOURCE" in more or less actual Oracle version.
Maxim
Also, look at the preocedure name. log_error. Where does it log???
in the errorlogging table? like it says in the code
I'm not sure I understand the problem with the WHEN OTHERS THEN NULL. This appears to be code that automatically logs errors. One can derive from that it is part of a process that is not continously manually monitored, where one would want to try to get some record of what happened, but wouldn't want to just blow a whole huge process off just because there is a transitory problem.
I could see someone doing that with some huge bunch of overnight processes that have some problem like, say, running out of shared pool, where the root cause of the problem is caused by some other procedure than the one that is erring, and the instrumentation wasn't built into the large number of procedures involved.
In other words, tacking on last-gasp code to try to figure out what got bonked.
Hi Tom,
May be I haven't thought this through, but wouldn't this be a good use of a WHEN OTHERS clause:
If an exception has been thrown that was not expected, and cannot be properly handled, but the program is not run interactively, then would this not be reasonable -
EXCEPTION
...
WHEN OTHERS THEN
log_where_the_exception_was_raised;
RAISE;
END;
Obviously that wouldn't be appropriate in this example, since it actually is a logging procedure, but doing it this way could preserve useful information about why and how the program failed, and then RAISE the exception to be handled in the normal way.
Have I missed something?
... I'm not sure I understand the problem with the WHEN OTHERS THEN NULL. ...
Ok, riddle me this batman - if you cannot log the error, you will just let it silently be ignored, not ever reported any where. For how many months would you be willing too put with the fact that your error logging tablespace is filled and you haven't been capturing errors?
This is a horrible use of when others, it is followed by null. I categorically state here and now:
when other then null;
is a bug in the developed code. period.
If you can stand a transitory "error" (whatever that means), you can certainly stand a permanent one.
No, this is a bad use of a when others.
As for the next comment, a when others - follwed by RAISE - is perfectly legitimate. However, it is so rarely used or implemented that I'd rather burden the small percentage of people that know how to use this so as to not permit the huge percentage of people that abuse it from doing so.
I'd also suspect (given a propensity to use WHEN OTHERS) that there are other procedures in the application which do a
PROCEDURE whatever IS
BEGIN
inserts/updates/whatever
EXCEPTION
WHEN OTHERS THEN
log_error('whatever',sqlerrm);
RAISE;
END;
If that bit of code has the raise, it is not as big an issue. Maybe the error log is just there to store the 'real' error for support so a simple, pretty, error (eg "Contact Support") is given to the user.
In regard to James' comment "Obviously that wouldn't be appropriate in this example, since it actually is a logging procedure", I've actually seen an error logging procedure that, if it fails, tries to log an error using itself.....
Hi,
i love when others as the default execption handlers in my procedures and functions.
if the default handler is NULLED then this is dirty programming style.
PL/SQL has a lot implemented from ADA. And ADA seems to be a very very strict language. So why not implement a PL/SQL compiler flag PL_STRICT_EXCEPTIONS which does not allow NULLED exception handlers.
Karl
if you can put when others null in a routine, routine might as well not exist because you cannot rely on it ever being called.
As for loving when others as the default - if you ask me, the ONLY EXCEPTIONS YOU CATCH are those you expect (and hence they are not exceptions)
You might EXPECT no_data_found, and you handle it by defaulting the into variables appropriately.
You might EXPECT dup_val_on_index and do an update instead of an insert.
You do not EXPECT "out of space". You cannot ignore out of space, you cannot (typically, 99.99999% of the time) fix out of space, therefore the only logical things to do are:
a) maybe catch and log it, but I wouldn't do that here
b) let it propagate to the top level, preferably for me to the CLIENT (and remember plsql can be the client - APEX for example) and log it there and display something to the end user.
If you do B) we know the error's presence was signaled.
when others then null just says "ignore me". Just delete that procedure, it is as effective.
Tom,
Would you go to the effort of something like ...
DECLARE
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
BEGIN
execute immediate 'drop table my_table';
EXCEPTION
WHEN table_does_not_exist THEN
NULL; -- do not handle the error
END;
/
... to ...
DECLARE
BEGIN
execute immediate 'drop table my_table';
EXCEPTION
WHEN OTHERS THEN
NULL; -- do not handle the error
END;
/
... then? I'm thinking of cases where there is one obvious error that could be raised that you would want to ignore?
if the "table does not exist" is an expected condition, by all means catch that condition and move on.
Things like permission denied, database is read only, drop trigger says "no, not on tuesday", whatever - they are not expected, you can not deal with them, you don't know what to do about them - you have to bail out.
there are more reasons than one that you cannot drop a table and if you cannot drop the table, the remainder of your program is sort of "in an unknown state" as well.
You hit an exception, an exception you don't know what to do with.
Unfortunately I can find nothing to disagree with so far then. The "drop table/view/whatever" procedure and classic "is this a number?" function always appear with WHEN OTHERS THEN NULL and I've not been happy with that -- seems lazy, really.
Is there something more contraversial we talk about? :D
This comment has been removed by a blog administrator.
No performance difference between WHEN OTHERS, using predefined exceptions, and using user-associated named excpetions, I reckon.
http://oraclesponge.wordpress.com/2006/08/21/plsql-exception-handling-performance/
David Aldridge made up:
Is there something more contraversial we talk about? :D
How about the D-11 recall?
- Mark
Oh I'm D49 -- that's outside of my sphere of interests :D
I thought you seemed very falcon like :)
Ok, riddle me this batman
If I'm not mistaken (and I could be), you say this a lot.....whats the story (morning glory)?
Tom, I agree with you 100%. My origins stem from an ancient mainframe database where it was incumbent upon the programmer to check the returned DB status code. Too often I have seen inexpert programmers skip the exception handling code to meet deadlines or get a reputation as a fast coder. I've come in behind them, seen the lack of exception handling, put some in, then get chewed out by management for "breaking" the code when an exception was caught. When I began using PL/SQL some years ago, I saw the same phenomenon. In fact, some of the worst code I have ever seen was Oracle Financials AP code - no exception handling at all! It would have been funny to watch all those executives run around like chickens with thier heads cut off when a multi-million dollar AP ACH broke one evening, except that I was the programmer and they all looked at me and asked "what are you going to do about this?" If there had been some decent exception handling I am sure I would have had a better idea what the real problem was. Just let me say that exception handling done right will tell you exactly where your program encountered an unrecoverable error and what the conditions were. A little time writing that extra exception code can save you hours and hours of grief and stress in the middle of a dark and stormy night. Also, if you are going to log exceptions to the database you need to realize sometimes you can't. When this time comes, the utl_file package would be a handy thing to know about.
Ok, Tom how about that?
create or replace function get_tomorrow_date return date is
begin
dbms_lock.sleep(86400);
return sysdate;
end;
/
tsmileham said
A little time writing that extra exception code can save you hours and hours of grief and stress in the middle of a dark and stormy night.
though oftentimes the programmer doesn't save their time, but that of the sysadmin/dba/customer, this tends not to weigh so heavily on them, and is the one good reason I can think of for allowing programmers into production as support people.
The sense of exception handling is for me :
a) Even an expected or unexpected error occurs my program should go on with a defined error handling strategy.
b) find the root cause for the error or better : identify the program unit where the error occurs.
For both reason i will always define when others. It can be a blessing or the opposite - this depends how proper you use it.
Regards
Karl
Testing causes bugs.
Karl,
short of
"when I received an exception I was not anticipating, I log it and RE-RAISE it so that every level above knows that all hope is gone of doing the job we need to do correctly"
what is your idea of proper error handling.
Because I have no problem with that approach, I have a problem with "when others THEN NULL;" - and because that is how most people use it (really, they do not re-raise the exception, the error is silently ignored), it is a major bug.
wheh others
that is not followed by raise;
is almost certainly a bug in the developed code.
period.
NEVER swallow the error. with null;
handle expect possible errors
when no_data_found
when too_many_rows
.....
when others then
Log it....
raise;
I have another 'lovely' piece of code. This was actually the first procedure listed to be run in an Introduction to PL/SQL (10g) manual that I was asked to check for problems.... I got to page 45 and decided not to even bother looking at the next 100 odd pages. Anyway, here's the code:
DECLARE
BEGIN
FOR i in 1..1000 LOOP
INSERT INTO employee (ssn, lname, fname, dno, salary)
VALUES (90000000000+i, 'Doe','John',1,30000+i);
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
I say again. THIS WAS IN A COURSEWARE MANUAL.
Niall said....
tsmileham said
A little time writing that extra exception code can save you hours and hours of grief and stress in the middle of a dark and stormy night.
though oftentimes the programmer doesn't save their time, but that of the sysadmin/dba/customer, this tends not to weigh so heavily on them, and is the one good reason I can think of for allowing programmers into production as support people.
Alas, all too true. Once auditors required that I not be allowed in the production database. That lasted about a week. I digress, however. My main point is there are too many dilettante programmers out there who cut corners to make unrealistic production deadlines. My contention is if you are the consummate programmer it isn't that much more difficult to put in the exception handling code. Of course you will then become the victim of "silent unrewarded productivity."
That was hilarious, QUADRO... Candidate for "most evil function" I've ever seen...
I like this
Hi Tom,
we have to maintain a programm developed by a marketleader in Germany. When we reviewed the code we made a 'best of'. One of them is
IF v_after_createpipe AND v_before_removepipe THEN
DECLARE
BEGIN
--v_return_code := DBMS_PIPE.REMOVE_PIPE(c_pipe_name);
NULL; --no remove, othwerwis PUBLIC PIPE possible!!!
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
Regards
Marcus
Tom,
Surely the fact that the error logging procedure has a WHEN OTHERS THEN NULL doesn't necessarily mean the errors are being "ignored" in the case when the error logging procedure goes into it's own exception handler, since the logging procedure is an autonomous transaction?
You'd have to know how it was being called...
For example:
CREATE OR REPLACE PROCEDURE some_error_logging
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
raise too_many_rows;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
NULL; -- do nothing
END;
CREATE OR REPLACE PROCEDURE DODGY_PROCEDURE IS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN no_data_found THEN
SOME_ERROR_LOGGING;
RAISE;
END;
SQL>EXEC DODGY_PROCEDURE
BEGIN DODGY_PROCEDURE; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SOME_SCHEMA.DODGY_PROCEDURE", line 7
ORA-06512: at line 1
Hey, has anyone seen my can of worms?
Seriously, who took it....oh no, its been opened....
You know, this is kind of scary. Its like a group of boys in a school yard. One kid makes a dumb comment and the pack jumps right on him.
I understand (or think I do), Tom's point of view that this type of issue doesn't make 'logical' sense. It's (very) possible this was coded by someone very inexperienced. But as some have pointed out, even Oracle has used this type of behaviour before, as well, shown examples of times when this is required to handle some sort of 'unexpected' error.
I see a lot of posts of "I've seen this, man what stupid developers". Did anyone help the developer? Train/educate them on why this is bad?
I truly *feel* bad for whomever wrote this code, they are probably feeling really stupid right now. Its possible they'll try to get out of coding and become a manager or something.
Imagine, getting chastised by many on the internet as well as Tom Kyte himself. Ouch.
I agree with Tom's advice on "WHEN OTHERS", but sometimes, time is of the essence and you have to quickly put together a piece of code (bad or not). This appears to have been created to possibly log some error they were getting as part of a debug.
So not ONLY is this person having debug issues on a different piece of code, they are also getting hammered in the internet community.
It's no wonder developers and dba's don't get along (which is odd to me because their jobs overlap and are so similar). Each has no empathy for another.
Hi Tom,
You are my main mentor when it comes to Oracle and IT issues... but I think this is a case where "Homer nodded".
I hope you never get your wish and do away with the WHEN OTHERS exception!
You said it is never used properly. I must heartily disagree, Tom.
This exception is the ONLY WAY to trap AND LOG unexpected exceptions. All one has to do to do things properly is to make the last line of the WHEN OTHERS block a RAISE (or RAISE_APPLICATION_ERROR and include SQLERRM).
Best regards, Robert.
"insert /*+ append */"
You should blame Oracle docs too. Even in 10g R2 reading the manual it looks to be the "magical" fast=true switch to speed up inserts. Manuals do not explain properly when it will work properly. I understood it only reading asktom and your books.
> It's no wonder developers and dba's don't get along
I don't see what this has to do with DBAs.
Don't feel bad for the developer. Developers like that don't read blogs.
I have always wondered why people want to use WHEN OTHERS to capture bugs. Even when a RAISE is placed in the exception handler the debugging is made much more troublesome. The worst are those who give a message from this handler such as: "An error has occurred, contact support".
In the JAVA world developers do not have this nasty habbit, however, that has the other side-effect of users being confronted with screensfull of Java error-stacks.
Regarding the WHEN OTHERS THEN NULL I think this is a fundamental discussion, because that is the only one that I sometimes use, but only for functions not for procedures! I have e.g. a function that should return some description. It has only been designed for displaying and does not affect any functionality. It does a SELECT INTO, and I do not want an error when no row is selected, too many rows are selected, or even the table that I am selecting from does not exist. I do not want to make code for 3 exceptions, I am lazy so make a WHEN OTHERS THEN NULL.
... It does a SELECT INTO, and I do not want an error when no row is selected, too many rows are selected, or even the table that I am selecting from does not exist. ....
forgetting that there are other errors that can happen - value error, truncation, whatever (you must be using dynamic sql).
Don't be lazy, being lazy here is as bad, if not worse, then when others null; I could say it is worse because you KNOW better.
what happens when you get no data found? You leave your variables in an unknown state, same with too many rows - your into variables - garbage.
convinced that
when others then null
is evil,
I just wrote
create or replace procedure my_null is
begin
my_null;
exception when others then
my_null;
end my_null;
/
and replaced every occurrency of
"when others then null"
into
"when others then my_null"
Tom Said: "what happens when you get no data found? You leave your variables in an unknown state, same with too many rows - your into variables - garbage. "
I seriously don't agree with you on this one.
When the WHEN OTHERS fires in the logging procedure, the value of any variables is irrelevant since all local variables go out of scope when the procedure ends.
Since there is no package state being altered in the logging procedure it doesn't matter.
You can't know whether the silent failure to log is relevant because we have no information about when and how this procedure is called.
The WHEN OTHERS is only there in this case to stop the logging procedure failing, that doesn't necessarily mean that it has been called from a WHEN OTHERS, as in the example posted earlier.
Roll me in oil and set me on fire if you like, but I wager this one of the few valid uses of WHEN OTHERS.
Personally I love to have applications log into a database table that I can query later for clues when things go wrong. But I wouldn't ever want my clue providing procedure to crash my application simply because it experienced some problem I didn't anticipate.
While I'm at it. I'd like a PL/SQL exception handler enhancement to be able to do this:
WHEN no_data_found
OR too_many_rows
OR some_dodgy_data THEN
...
Also wouldn't a FINALLY be nice?
BEGIN
/* some_code */
EXCEPTION
WHEN x_y_z THEN
/* handle exception */
WHEN no_data_found THEN
/* do something slightly different */
FINALLY
/* always do this before returning */
END;
I seriously don't agree with you on this one.
did you read it in context?
We are talking about when other then NULL <<<<========
We are talking about people IGNORING errors, pretending they did not happen.
We are talking about a serious bug in the DEVELOPED CODE.
WHEN OTHERS NOT FOLLOWED BY RAISE IS ALMOST CERTAINLY ASSURED TO BE A HEINOUS BUG IN THE DEVELOPED APPLICATION
I'll scream that from the top of the highest mountain over and over.
There are valid uses of WHEN OTHERS, they are the ones followed by RAISE to re-raise the exception.
As I've stated over and over again here - that almost never happens - the raise part.
For whatever reason developers feel OBLIGED to not return errors to applications, to hide them (they haven't be taught correctly yet)
I say "I'd rather remove when others from the language simply because it is totally abused and misused more often than it is put to good use. It is and has been and likely will continue to be the cause of MANY bugs in MANY applications. I get chills ever time I see "when others then null;" - that is the most frequent use of it."
Tom,
Oracle's (fairly) new type of ANYDATA cannot be "inspected" unless you call the ANYDATA type methods, such as getObject. Unfortunately if the passed in (SELF) data is not of the type you are checking, Oracle's api throws an error. I cannot change Oracle's api, therefore if I have a piece of data of completely unknown data type and we're trying to find out in order to do something with the data, then we have to trap the api call with WHEN OTHERS THEN NULL. In this snippet of code we are checking for up to a 4 different data types: varchar2, number, date, and object. Depending on what type it is, we would "do" something with it. However we don't want to throw an exception if it fails the oracle api check (GETxxx).
[pre]
MEMBER PROCEDURE print IS
l_data ANYDATA;
v_varchar2 VARCHAR2(32767);
v_number NUMBER;
v_date DATE;
v_list ot_tab_fruit;
BEGIN
FOR i IN 1 .. SELF.how_many LOOP
-- This selects a column of ANYDATA
l_data := SELF.get_at(i);
BEGIN
IF l_data.GETVARCHAR2(v_varchar2) = DBMS_TYPES.SUCCESS THEN
DBMS_OUTPUT.PUT_LINE('Node '||i||' is VARCHAR2 value= '||v_varchar2);
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
IF l_data.GETNUMBER(v_number) = DBMS_TYPES.SUCCESS THEN
DBMS_OUTPUT.PUT_LINE('Node '||i||' is NUMBER value= '||v_number);
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
IF l_data.GETDATE(v_date) = DBMS_TYPES.SUCCESS THEN
DBMS_OUTPUT.PUT_LINE('Node '||i||' is DATE value= '
||TO_CHAR(v_date,'MM/DD/YYYY HH24:MI:SS'));
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
IF l_data.GETOBJECT(v_list) = DBMS_TYPES.SUCCESS THEN
DBMS_OUTPUT.PUT_LINE('Node '||i||' is OBJECT');
v_list.print();
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END print;
[/pre]
--Kate
Kate -
You do NOT need "when others" (there are specific exceptions raised, when others is the "lazy way")
That and you can certainly do something akin to:
http://www.oracle.com/technology/oramag/oracle/02-jul/o42asktom_l4.html
where you CASE off of the getTypeName
Tom,
You are correct sir. I was not aware of the ANYDATA method getTypeName. Now the only reason I could muster up for WHEN OTHERS THEN NULL has been yanked. Didn't mean for your blog to become "asktom-ish", but thank you anyway! --Kate
HeadStart framework (from Oracle Consulting) provides the following pattern:
in every procedure, after all WHEN specific_exception... put
WHEN OTHERS THEN qms$errors.unhandled_exception('procedure_name')...
IN "unhandled_exception", the first thing is checking if this is a exception-wrapper-#9999. If not, information is logged and original exception is wrapped into HeadStart exception-wrapper-#9999, and re-raised. If it is #9999 -- just re-raise.
This way exception is logged only once and moved over any number of layers till some layer that deside on transaction.
============================
Tom,
Your advice on listing every expected exception is undisputable, but only in LOW LEVEL code dealing with selects, inserts right here/right now. Coding BUSINESS LOGIC procedures, one can use numerous calls to another blocks, often written by other groups. A coder does not know all variety of possible exceptions from 3-rd party modules, so cover all holes is stupit in this case. Here, WHEN OTHERS is absolutely apropriate, of cource followed by (i) log/report/ (ii) rollback+stop or (iii) rollback this transaction and continue next transaction (batch with commit on every TX).
to last poster
I could not DISAGREE WITH YOU MORE.
do you see they say "logged AND THEN re-raised"
AND THEN re-raised
AND THEN re-raised
when others
followed by null
is almost certainly A BUG IN THE DEVELOPED CODE
(I scream loudly from the top of mountains over and over...)
entirely unexpected errors must just be re-raised, period.
Tom,
Please, save your voice for your excellent presentations -- nobody agrgue with '''when others the null'' is bad'.
I agrgue that in complex systems with a lot of integrated legacy, 3-rd party, and in-house every-day changing code, "when others then log,re-raise,re-raise...rollback,continue other TXs" has its rights to exist.
After all, I agree, by-itself "when others" is an indication that a coder is not in control of situation. Yes, I do not trust some legacy code, and not assigned to correct it.
Have a nice week,
Alex V. (Montreal)
I has found a link on this thread in Jul-2007 Oracle Magazine.
Good thread.
I am totally agree with BAD habit of placing 'EXCEPTION WHEN OTHERS THEN NULL'.
Speaking about re-raising itself...
A re-raising for the purpose of substitution of a native oracle error output by some "user friendly" message and/or error logging can cause misstatement of an original location of the exception.
PL/SQL changes the context of the exception when you call RAISE. From this moment it is, basically, not the original exception, but a new exception that is associated with a location where RAISE has been called from.
People have to be careful about it.
For example, we have a code where an exception can happen in several places (and this is a pretty much common case).
DECLARE A NUMBER; B NUMBER; C NUMBER;
function DIV(A number, B number) return NUMBER as
begin
return A/B;
end;
BEGIN
IF mod(dbms_random.random(),2)>0
THEN
A := 2;
B := 0;
C := DIV(A,B);
ELSE
A := 0;
B := 2;
C := DIV(B,A);
END IF;
END;
/
We have here a function DIV that can be randomly called from two places.
A native error output, without using EXCEPTION clause will return following stack (in 10g)
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4
ORA-06512: at line 15
or the last line can be
ORA-06512: at line 11
depending where DIV has been called from.
This is a result that can provide a sensible information to a developer and can help to fix the problem.
Now let's put an exception handler here. Our goal is to log an error and still provide user with exception message.
For convenience our "logging" procedure will be dbms_output.
DECLARE A NUMBER; B NUMBER; C NUMBER;
function DIV(A number, B number) return NUMBER as
begin
return A/B;
end;
BEGIN
IF mod(dbms_random.random(),2)>0
THEN
A := 2;
B := 0;
C := DIV(A,B);
ELSE
A := 0;
B := 2;
C := DIV(B,A);
END IF;
EXCEPTION
WHEN OTHERS THEN
--kind of logging
dbms_output.put_line('I''ve got exception:' || sqlerrm||chr(10)||'What was a line of code it has been happen?');
RAISE;
END;
/
It will return
I've got exception:ORA-01476: divisor is equal to zero
What was a line of code it has been happen?
DECLARE A NUMBER; B NUMBER; C NUMBER;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 21
See? A location of exception is "at line 21" now! The line where RAISE has been called from.
How does one know where the exception actually has happen?
No way until 10g, unfortunately.
In Oracle 10g a wonderful function has been finally added - DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.
DECLARE A NUMBER; B NUMBER; C NUMBER;
function DIV(A number, B number) return NUMBER as
begin
return A/B;
end;
BEGIN
IF mod(dbms_random.random(),2)>0
THEN
A := 2;
B := 0;
C := DIV(A,B);
ELSE
A := 0;
B := 2;
C := DIV(B,A);
END IF;
EXCEPTION
WHEN OTHERS THEN
--kind of logging
dbms_output.put_line('I''ve got exception:' || sqlerrm||chr(10)||'What was a line of code it has been happen?');
dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||SYS.DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;
END;
/
It returns
I've got exception:ORA-01476: divisor is equal to zero
What was a line of code it has been happen?
FORMAT_ERROR_BACKTRACE:ORA-06512: at line 4
ORA-06512: at line 15
DECLARE A NUMBER; B NUMBER; C NUMBER;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 22
It is much better!
However you have to migrate on 10g to have it.
You also can change not user friendly RAISE to friendly
raise_application_error(-20002,'User friendly Division by 0 message');
When others then NULL;
Will it not help to trap any errors and return success.
I am processing 200 tables and one of the table is not available.
I want to move to next next table without bothering about errors.
- xx
@at the brave anonymous
well, if you want to "process" 200+ tables and "not bother someone" if one of them fails - you can simply code:
begin
return;
end;
think about it - it is THE SAME. Well, actually begin null; end; IS BETTER because it is predicable and reliable.
If a table is allowed to fail
and a table is allowed to fail SILENTLY
then said table NEVER NEEDS BE PROCESSED (apparently, don't argue with me on this - you are wrong if you try) if it is allowed to fail SOMETIMES without "bothering anyone" it is by definition allowed to fail EVERYTIME - so stop processing it - it is a waste of your time, my time, their time)
so, just stop processing it, it doesn't matter if it works or not so why bother in the first place?!?!?!?!?!
think about that for a while. Please.
You seen ?\RDBMS\ADMIN\EXECOCM.SQL in 11g? Also in CATOCM.SQL.
#Rem stop the job
BEGIN
BEGIN
-- call to stop the job
ORACLE_OCM.MGMT_CONFIG.stop_job;
EXCEPTION
WHEN OTHERS THEN
-- ignore any exception
null;
END;
END;
/
This kind of obscured a failure, leading to a jsks.c error and caused catastrophic failure of upgrades until we got to the bottom of it. Confused the heck out of MOS for a while.
Cheers,
John T
POST A COMMENT
<< Home