when others then null, redux
It keeps happening.
Over and over it keeps happening.
Here is case 124,215,412,523 for your review.
The user writes me:
... I have a situation where I have started receiving the 'Cursor is Closed' SQLException from a stored procedure, say, SP_A, suddenly for last 5-6 weeks, since 2nd week of March. This happens only intermittently. If I take the same parameters and execute the sp again after a few minutes after getting the error, it does not generate the error and returns expected results back.
...
Additional details as requested:
what ora-xxxx error are you getting
Unfortunately, it does not generate an ORA-???? error. I know that many a times, the 'Cursor is Closed' error is a coding issue, but in this case, that is not the case. I cannot create this error at will, I have not been able to create the error in our development or user acceptance environment. Even in production, I cannot recreate it with same parameters that have generated the error.
...
The reason I think this could be oracle memory issue is that we very recently moved from 9i to 10g RAC. This application - java code and stored procedures - have been working for about 5 to 6 years now. Even the errors of Cursor is closed we receive are not consistent i.e. The parameters that generate the error now will work with exact same code a few minutes later.
...
Ok, here they are - they say "there is no ora-xxxx error, Oracle is just closing cursors on us - no fair. We know this cannot be a bug in our code, this is a bug in Oracle.
The story ALWAYS goes that way. I just finally gave up... closed the question. They followed up:
What was found that SP_A had exception clause of WHEN OTHERS that was masking the actual error.
When we removed that error, this is the error we see
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SP_A"
ORA-06508: PL/SQL: could not find program unit being called: "SP_A"
ORA-06512: at "SP_B", line 317
ORA-06512: at line 1
This is what has been happening intermittently ( we just did not know, because the way the sp was
coded and the java code interacted with returned parameters, it manifested into Cursor Closed Error
).
and now they want "insight" into why this is happening....
Remember everyone, everyone remember, keep in mind:
When others not followed by RAISE or RAISE_APPLICATION_ERROR is almost certainly, with 99.999999999% degree of accuracy, a bug in your developed code. Just say "no" to when others not followed by raise or raise_application_error!
http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html
posted by Thomas Kyte at 7:43 PM
36 Comments:
While I strongly agree with your main point, I don't think your claim that in 99.999999999% of cases this is a bug.
There may be certain situations where it is used by design.
Say, for example, that you have a function that parses a string in some fashion. Now, if the string parsing fails, for whatever reason, you might want to return a NULL value to the caller, instead of re-raising the exception.
Something like this:
begin
...
exception
when others then
debug_pkg.print(sqlerrm);
l_returnvalue := null;
end;
return l_returnvalue;
Anonymous,
I am sure Tom will answer this more effectively but the example you gave might be alomst certainly the case of poor coding/design. Your statement if the string parsing fails, for whatever reason, is vague. isn't it ? I guess it all depends upon what you define as "failure" here. If it is a business validation (and you want to raise an exception for it), you should (ideally) declare user-defined exception and trap that specific exception in exception handler.
WHEN OTHERS should almost always be used for any errors that you do not expect to occur (e.g technical failures like object does not exist). I don't think it is a good idea to "ignore" any "unexpected" errors.
@anonymous
the reason I used 99.99999999999999% is because there are RARE cases where you might use when others - not followed by null - is OK.
Your example however, is DEFINITELY NOT ONE OF THEM.
If the string parsing can fail sometimes without any reason....
then the string parsing can ALWAYS fail (by definition - any error returns null, everything can return NULL and that'd be OK)
so, just make the function be
return null;
and you are done.
If you have routines like you describe, your code has a bug (regardless of what you think, it has a bug - why bother even calling it...)
> The reason I think this could be oracle memory issue is that we very recently moved from 9i to 10g RAC.
I find this all the time - something stops working, or fails intermittently and its always the 'compilers' fault, or the firewalls or something, but never the application!
Either that or people come up with crazy ways to fix things and fix it with a lucky side effect - scenarios that remind me of the old 'the database is always faster on a Monday when its raining come to mind' - people get it to work somehow, but have no real idea why - sometimes its slow, but if we gather stats on table x it always fixes it, so it must be a stats problem.
Is it just me, or does there seem to be a serious missing skill in most people that is to take a complex system, isolate the problem, create a reproducible test case that provides evidence that a suggestion is probably the root cause.
I see it again and again and almost every time the people without the test case try 4 or 5 things before they get it right (with 4 or 5 outages as they cannot reproduce on test).
This week I just solved two such cases - If we need something, its some sort of course that teaches people how to debug problems and craft test cases so that they can communicate the problem to people like you or Oracle support for help!
I thought of you when I read this article about a tragic "when others then null;" situation.
And by tragic, I mean very, very expensive.
Not long ago a developer at my company wanted to create a stored procedure with WHEN OTHERS THEN NULL. I explained to him why it was bad and asked that he remove it. He did.
Thanks Tom.
so, please, what does
"reject limit unlimited"
mean when defining an external table ?
it means
"when others then null"
correct ?
(
for example
"....The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data...."
on http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2448
)
Error is probably logged (when a badfile was specified), but no exception is raised.
As I said for example on
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:677074900346092274#679544300346683770
, I think
when others then null
has no sense.
So, when we want
when others then null
to be removed
we also must have the need to be
reject limit
be removed
I must say that I very much like you reasoning - if it can fail sometimes, there is no need to do it at all. I have recently used it in a discussion over a user requirement.
We're working on a system where users have to prepare data and send them somewhere in time on a daily basis. The requirement was to send the data just before the deadline automatically if users for whatever reason don't do it themselves, regardless of whether the data are correct.
I advised to fire the employees responsible for this process and just send the data automatically every day.
(Note: the reasoning isn't all that silly - wrong data are rejected by the receiving side, so it is more or less the same as sending no data at all. By sending whatever is in the application at the deadline it might happen that the outcome would be better than sending nothing just by chance. However, it made me wonder how the internal processes at the customer's site are set up.)
I don't think it is necessary to dismiss the string parsing example as bad practice. I have found it very useful for checking date formatting - I definitely don't care, what was the exact reason (from about eighteen different possibilities) for failure, all that's needed is the fact that conversion was not successful. And this can be easily achieved by enclosing the conversion function into a single-line block and return an error code whenever an exception occure.
Tom, we had enough of your blogs on Oracle internals.. Why not revert to your old style of blogging general topics as well...
Tom said: "If you have routines like you describe, your code has a bug (regardless of what you think, it has a bug - why bother even calling it...)"
Obviously, this hypothetical string parsing function must be called from somewhere (why bother to make it otherwise? :-), and the calling routine would know what to do if the function returned a NULL value -- raise an exception, or just continue with some other processing.
It's not a bug, it's by design, and therefore it does not introduce any subtle/strange/weird behavior since the calling routine knows what it can be expected to return (either a string or a null value).
I am always advising to follow a WHEN OTHER EXCEPTION by a RAISE. However, I have one case where it seems for me that the WHEN OTHER EXCEPTION can not be followed by a RAISE. I hope this is the 0.000000001 case which transgress the rule.
When we are doing an initial load of 500.000 client accounts and we want to do not stop the process of loading all accounts if one error occurs during the treatment of the initial load.
So we make our process restartable (by using a flag_treated ‘Y’ or ‘N’) and we Loop on all not treated accounts doing this within the loop
BEGIN
LOOP
BEGIN
savepoint foo;
/*... do plsql code here; */
Update treated account
set fag_treated = 'Y';
EXCEPTION
WHEN OTHER THEN
Rollback to savepoint foo;
Update treated account
Set fag_treated = 'N',
Error_code = SQLERRM;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHER THEN
RAISE;
END;
Please let me know if you still believe that also this exceptional case represents a bug
Mohamed Houri
@M. Rogel
reject limited unlimited is not any more like "when others then null" than DML error logging is, then save exceptions is
You are getting a BAD file of rejects.
You are getting a log file full of error messages.
You are NOT IGNORING THE ERROR, you are NOT MAKING THE ERRORS DISAPPEAR, you are rather saying "I expect some of the data to be dirty, load what you can - log the errors over here and we'll process them"
I do not see any logical comparison to be made between the bug that is "when others then null (or just no raise)" and reject limit unlimited.
@anonymous:
I don't think it is necessary to dismiss the string parsing example as bad practice. I have found it very useful for checking date formatting - I definitely don't care, what was the exact reason (from about eighteen different possibilities) for failure, all that's needed is the fact that conversion was not successful. And this can be easily achieved by enclosing the conversion function into a single-line block and return an error code whenever an exception occure.
Oh, so
a) you NEVER code a bug, you write DEFECT FREE CODE, you never make a mistake
b) you NEVER encounter unexpected errors that cause a date that SHOULD PARSE to fail (eg: out of memory, internal error, nls settings making it mess up *sometimes*)
If the above two things are true - sure, you'd be right
However, you do not write perfect code. You do encounter other issues outside of your control and the invoker of your routine NEEDS TO KNOW.
And why you would want to code:
convert_date( input, format, output)
if ( input is not null and output is null )
then
there was an error
else
continue onward
end if;
as opposed to
convert_date( input, format, output)
continue onward
and if you want to SKIP ERRORS (which would make me reject your code out of hand, you would have to fix it, it would not pass any sort of peer review, we would laugh at it) you would
begin
convert_date( input,format, output )
exception when others then null;
end;
making it CLEAR that in this case - at this point - any and all errors are okey dokey with you.
Forgetting for the moment that the call might have been:
convert_date( '01-jan-2008',
'dd-mon-yyyy', output );
which SHOULD WORK, but failed - why did it fail? I don't know, you won't TELL ME. You presume that nothing exceptional will ever happen (all pun intended there)
Catching a when others in a parse routine is a bug. It is a bug in your developed code. It might not bite you for months, maybe years, but it'll get someone, sometime - it is a matter of time.
Be lazy, but don't call it "by design", it is "by lack of design"
@Anonymous
Tom, we had enough of your blogs on Oracle internals.. Why not revert to your old style of blogging general topics as well...
excuse me?
If you call this stuff internals - well, whatever.
I've been writing about that which ticks me off.
You can always unsubscribe - I'm always surprised when someone posts "I don't like what you write anymore" - there is such a simple fix for that - and it doesn't involve posting a single thing on a single site...
@Mohamed Houri
The only problem I might have with that is - what happens if the data *should* have been entered but something 'exceptional' caused it to not succeed. This one is probably OK with the exception of this:
EXCEPTION
WHEN OTHER THEN
RAISE;
never do that - just do this instead:
[this space intentionally left blank]
all you did there was HIDE the actual line number the error happened on!
Tom,
I've thought about this for years. And I do agree with the 99.999999999% comment. However, I do believe I finally have a real-world example that fits within that 0.000000001%.
In a nutshell, in APEX 3.1 I added the functionality to employ browser-caching headers for static files sent from a PL/SQL procedure. In previous versions of APEX, if you requested an uploaded image, we always delivered the content to the browser - every time. In APEX 3.1, though, using Etag and Last-Modified HTTP headers, we'll send an HTTP status 304 if nothing has changed - much more efficient.
Unfortunately, if for any reason an error occurred when comparing the computed dates for Last-Modified, the procedure failed and the end user received no response (e.g., a broken image).
No exception block. Shame on me - I should know better. But I never could have anticipated this one specific exception. It's ultimately an underlying database bug (day of week in server-generated If-Modified-Since header is off by one day, but only if using embedded PL/SQL gateway and only if database is created in certain territories - 6913430). Even if I had some reasonable exception processing, I believe I still would have been smoked by this underlying bug that I did not anticipate.
So in my fix for APEX 3.1.1, I have an exception block around all of the Etag and If-Modified-Since logic. And it's with "when others then null". I don't care what exception is encountered when comparing these HTTP headers - if they fail for any reason, always, always, always attempt to deliver the requested content instead of delivering nothing.
Have I found the elusive 0.000000001%?
Joel
@Joel,
and when a new release of the infrastructure comes out that entirely breaks your logic - causing you to never send a 304 again - ever - what then? You'll never know.
If you had always had the exception block, would:
... (day of week in server-generated If-Modified-Since header is off by one day, but only if using embedded PL/SQL gateway and only if database is created in certain territories - 6913430) ....
that bug ever have been filed and get fixed?
Could you not have worked around that bug - that specific bug, looking for those specific conditions in an exception block?
"You'll never know."
In this case, where the end impact would have been performance rather than function, if you don't know you've got a problem, then you probably don't.
And if the bug had never got filed it would be because no one had a problem with the way it was working. Conversely, if someone did experience unacceptable performance, then it still could have been found and fixed.
Given the particular circumstance (ie that an exception in that code can be worked-around by a slower, but otherwise identical mechanism), I support Joel on this, though I'd generally prefer a 'log it and workaround' approach rather than 'ignore it and workaround'.
@Gary,
but if know one knows something is broken - as the when others then null here would have it - then no one knows to file the bug. What if - from the get go - there was a when others then null. This exception - something that should not happen - would never have been detected. Ever.
Further, you say "than an exception in code ... otherwise IDENTICAL mechanism..."
But - there is a when others then null - instead of catching THE problem, they are ignoring any and ALL problems.
That is, they did not work around this one issue (that would be my approach), they said "we care not what the issue is, we'll ignore it from here on in". If they worked around this one problem - we'd not be having this discussion because there would not have been a when others null...
"This exception...would never have been detected. Ever."
Maybe. If it was never detected, it is not significant enough to be noticed, so who cares. If some-one had noticed that "This application is slower in THIS environment than THAT environment." the investigation would have shown files being re-requested, rather than served from the browser cache. Deeper investigation would show that this was due to a parse failure of the HTTP "If-Modified-Since" line, which could then be remedied. But in the meantime Apex still works, just slower.
On the other hand, if JUST that exception had been coded in, then APEX would have broken at the next hurdle, for example the Netscape specific extension to the HTTP standard "If-Modified-Since" line described in this post.
http://forums.oracle.com/forums/thread.jspa?threadID=630471&start=0&tstart=0
The fix appears to be around line 270 in WPG_DOCLOAD, if you have Apex 3.1.1 installed.
@Gary
...
Maybe. If it was never detected, it is not significant enough to be noticed, so who cares.
....
oh, that is not the way it works:
customer: this doesn't seem to work
any support: what error are you getting?
customer: no error, just doesn't seem to work
any support: oh, it is supposed to work that way, it will cache when it can and won't when it cannot
customer: but it should be caching
any support: are you getting an error?
customer: no
any support: then, how can you say that - if there was an error, they we could see your point. In Far East Elbonia - it might cache differently than in California - differences in timezones and other conditions you know. When the sunspots align in the proper way - this works very well
customer: it is not working
any support: what error are you getting?
customer: go to top and start over
when others then null in this context is hiding a bug. Period.
We'll have to agree to disagree on that one then. But if, instead of a WHEN OTHERS, Jooal had listed the dozen or more exceptions that Oracle can raise from a PL/SQL TO_DATE() conversion, would you still class it as a bug. That is, do you object to a NULL operation in any exception handler, or just in WHEN OTHERS ?
@Gary,
I've said before - you catch exceptions when they are not.... exceptional. When you are expecting them. When they are a known condition.
If this caught the very exception it throws (there are not a dozen, there is one) and had a nice big comment that said "pending resolution of bug #5614154321 this exception can be ignored if this is true <.......>" and then it would check the condition and if true - "null;" else "raise;" (because we did not expect it)
That, that would workaround this one issue - this one issue we now expect might happen under certain circumstances. And it might not even do "null;" it might fix up the date - making the cache WORK again (a true workaround).
And when bug #whatever were resolved..... it goes away.
"When you are expecting them. "
That's the difference. I expect that, given the variety of browsers, versions, OSs, database versions/edition/patchsets/settings, there will always be the potential for dates to be passed in that cannot be parsed (for any number of reasons). And that, where the date cannot be parsed, it is appropriate to bypass the cache.
Given that the code has to cater for dates in several formats, I'm not convinced about your 'one exception' agrument. I'd be interested to see how you'd actually code the processing we are discussing:
BEGIN
mod_date := to_date(mod_since, 'Dy, DD Mon YYYY HH24:MI:SS "GMT"');
EXCEPTION
WHEN OTHERS THEN
BEGIN
mod_date := to_date(mod_since, 'Day, DD-Mon-YY HH24:MI:SS "GMT"');
EXCEPTION
WHEN OTHERS THEN
BEGIN
mod_date := to_date(mod_since, 'Day Mon DD HH24:MI:SS YYYY');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
END;
@Gary,
I don't know that we have enough information to code the entire procedure (I haven't studied the date formats myself)
but given the description:
... It's ultimately an underlying database bug (day of week in server-generated If-Modified-Since header is off by one day, but only if using embedded PL/SQL gateway and only if database is created in certain territories - 6913430). ...
it does sound like it would be "a detectable situation"
We can go back and forth on this forever.
My experience has been constantly:
when others not followed by raise is almost certainly a bug, I see it AT LEAST once a day and usually many times more. I saw it on three separate occasions today - and when detailing what happens to the ACID properties when we have them - there was "surprise" (many people have no idea what they've just really done!).
Given the history I have with them, It would take a lot to get me to say "oh whatever, go for it"
A lot.
This might be one, we'd need to see if there wasn't a more elegant way - I'd sort of want:
when others
then
log into a special table that this happened, do it in such a way that we keep a count of how many times we had the exception and the date of the last occurrence. Goal would be that when we can reach the mother ship, this stuff would get uploaded into a repository. Tie this into the EM framework perhaps and nag the DBA that we need to touch base with the folks back in development;
end;
hows that - that would satisfy both of our goals IN THIS PARTICULAR case of a date gone awry, and that doesn't stand a chance of corrupting any data.
There is no question that if I had originally used a 'when others then null', the database bug 6913430 would never have been found for a long time. So now it's a detectable situation, but in the meantime, I've released software that broke a customer's applications.
With all this said, though, I believe that I have not found the 0.000000001% situation where "when others then null" should have been used. I think the pseudo code should have been something like:
....
when others then
if unknown exception, insert into a log table somewhere
end;
send image back to browser
This way, I would be handling the exception, I would be recording somewhere that an unknown exception happened (with the hope that this log entry would eventually get reported back to me), and I still deliver the requested file back to the client - not breaking their application.
Joel
The only time I've done something like this was in procedure that set the action and long op information in a process. I didn't want ANY errors that occur there to affect the application or stop processing, and I didn't have any good reason to handle the potential errors.
Aside from that one rare condition, I've never used WHEN OTHERS without a raise of some sort under it.
Tom,
This seems to have evolved into a rant against "when others then null," which I would agree with in production code. However, on the original point, I use "when others" without a subsequent raise all the time in the outermost levels of modplsql code to catch any error, display it in html, and log it. Enabling modplsql debug mode is not allowed, so doing a raise of any kind results in a 404 error which is useless to me and the user in fixing a problem.
John
Completely agree with you Tom. So it completely makes me go nuts when I work on Oracle Retail code to find that there is not ONE single procedure there and EVERYTHING is a function returning a boolean with TRUE/FALSE meaning this went well/Not well...
It even makes my task of convincing others how to write code the right way. They just reply to me: "Look, this is ORACLE code! It isn't like that! Who do you think you are? Tom?!" ehehe
Shame really...
Hi Tom
I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.
I cannot modify the procedure as its on production.
I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ?
please provide guidance on how to check the cursor stats at runtime. I want to check upto which record the cursor has been fetched and how many are still remaining.
I have cursor name.
Is there some dynamic view to check cursor stats at runtime ??
thanks.
@Ankit v
a couple of comments for you....
first - when you write "I cannot modify the procedure", that is technically inaccurate. What you must write in the future is "we have decided, we have made a conscious - purposeful decision to NOT modify the procedure". The reason for this change is that OF COURSE you can modify the procedure, you have chosen not to.
if this procedure is doing a series of full scans as it should be (if you are getting 1.5 million records by an index, you are doing it wrong), you can use v$session_longops to see what it is doing.
if you are in 11g, you can use the real time monitoring in sql monitory.
you should have used calls to dbms_application_info set session longops in the code (that would involve changing it) - that would be best.
and lastly, when you write "I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.", you should know that method is horrifically slow. If you do something really fast a million times, it takes a long long long time (hours in fact!). That should have just be a JOIN - JUST JOIN. Databases where BORN TO JOIN, it is what they do best. You missed a big opportunity to make this go much faster. JUST JOIN.
Hi Tom
I apologize for not putting the question upright.
Thing is that, I was told to refresh a test database D1 from another database D2 via stored procedures, which access data from D2 via db link into staging tables in D1 and after manipulation, the data in staging tables of D1 is put into final tables of D1. All the procedures were developed by someone else and i am currently supporting application.
Now, in one such procedure, which is already in place (and it might not have been developed to handle such a large data) , a query fills a cursor :
CURSOR cur1 IS
select a,b,c,d
from STG1
where d = '2278' ;
Now, based upon this cursor, a loop is fired which inserts into a final table F1 via this code :
FOR process_all IN cur1 LOOP
insert into FINAL1
select * from TAB1
where col1 =cur1.a and
col2 = cur1.b and
col3 = cur1.c and
col4 = cur1.d;
END LOOP;
This cur1 has fetched around 15,000,00 records. I checked this by running the query.
The above loop is ongoing since last 3 days and i can see the given insert statement in v$sqlarea also.
Its not a long running operation as the query is not visible in v$session_longops. Moreover, indexes are present on TAB1.
Everybody understands that, Ok, the data is too much, so activity will take time, but problem is that how much more time it will take ? That I need to tell this estimate.
I think that I can tell so based upon how many more cursor records are remaining to be processed.
That's my problem. Please advice.
Thanks.
@ankit v
that is horrible coding - whenever I see
for x in cur loop
do another cursor based on X;
end loop
I know for a fact the developer has just done it plain wrong.
that TAB1 has indexes makes it EVEN WORSE. Do you really want to do 15,000,000 fast things - or 1 moderately fast thing?
I know what I would choose.
I have no advice for you short of YOU using dbms_application_info to instrument the code. There is no single "long running" operation here, just millions and millions of short things (which add up really fast, think about it, do the math...)
I advise a rewrite of this to be a single sql statement.
Hi Tom
In a pl/sql procedure code, I created a normal table (create table)using dynamic sql. Then I used that table in procedure for further processing. But while compiling, it gave error that table does not exist. I can understand that he table is not present in DB, so the error came. But at the same time I need to create a table dynamically, use it and drop it. Does it mean that I need to make every query referencing that table as dynamic ??
Thanks.
@ankit
you would have to use dynamic sql all of the time in that procedure.
But - and this is important - you are doing it wrong. Oracle is not sql server.
You should create a global temporary table ONCE (it will be there in the dictionary) and then use static sql to access it.
See http://asktom.oracle.com/pls/asktom/f?p=100:11:2172713263672218::::P11_QUESTION_ID:48812348054 for details
POST A COMMENT
<< Home