Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Steve.

Asked: October 26, 2008 - 12:55 am UTC

Last updated: November 01, 2013 - 8:38 pm UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hello

I have a DBA that is claiming that by allowing a PL/SQL procedure to allow an unhandled exception (i.e. return the exception to the host program) somehow causes a problem. He is claiming that doing this "pushes the error to the database engine" and "causes core dumps".

His directive is that all PL/SQL procedures should contain a WHEN OTHERS exception block, the error should be handled within PL/SQL and an output parameters for the error code and the stack trace should be placed on all PL/SQL procedures. Therefore instead of the host program having a try/catch block for the "unhandled exception", it should check the output parameter to determine the success of the operation.

My main problem with this approach is that it consists of "swallowing" exceptions and allows the host program to ignore the error (either inadvertently or otherwise) and therefore produces an opportunity for bugs. This suggested approach seems to violate the error handling premise of not catching exceptions on any particular tier if you are not able to handle them. My other problem is this makes the application more complex and laborious to maintain.

I am open to the idea that this "unhandled exception" somehow creates overhead in Oracle, but I have been provided with no documentation backing this up and am unable to find any on my own.

Is there any reason to be concerned about allowing PL/SQL to propagate errors to the host program? If so, do you have any suggestions for a source of information on this topic?




and Tom said...

That is the most frightening story I've ever heard. Unbelievable and amazing.

The sheer "incorrectness" of your DBA is astounding.

You have ruined my day - seriously. Your DBA should not be allowed to advise anyone, especially on programming.


When 11g was under development, the PLSQL group asked me if there was anything I'd like to have added to PLSQL...

I requested that WHEN OTHERS be removed from the language. They would not comply :) however they did make it a WARNING - a warning that if you have a when others that does not raise the error - you almost certainly have a bug in the developed code.

ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8      PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR


that is how seriously messed up that concept is that your DBA proposes - it is such a bad idea the COMPILER YELLS at you "do not do it!!!!"

This is well known in all programming languages that throw exceptions. Developers have a very bad habit of catching any and all exceptions and hiding them. It is written about a lot:

http://gen5.info/q/2008/07/31/stop-catching-exceptions/
is a good example of what I'm talking about..

https://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html
Is one I wrote.

WHEN OTHERS - not followed by a raise or raise_application_error - is almost certainly a bug in the developed code. There is exactly one place it might be useful - and that would be the top level caller (as I mentioned), the top level invocation of the procedure block. For example, if you are coding in APEX, it might make sense to have the top level procedure have a when others - that logs the error and returns to the end user a nice looking error page (in this case, the top level client is PLSQL).

If it were a java application, then the plsql should contain NO when others not followed by a raise or raise_application_error, the top level in this case is the java application and it has a definite need to know there was an error that could not be handled.

Does your DBA know that by putting when others then <NO raise/raise application error> - the coder would break the Atomic part of the ACID principles - the stored procedure will allow some of it's work - what it did up to the exception - but not the bits that were skipped to remain in the database - not be transparently rolled back?

that is, the DBA is breaking the database, changing it's functionality - or at least proposing to do so. Their claim that an unhandled exception would cause core dumps on the server is ludicrous

The reason for this new feature and what it looks like to the developer follows:

ops$tkyte%ORA11GR1> create table t( x varchar2(4000) );

Table created.

ops$tkyte%ORA11GR1> create or replace
2 procedure maintain_t
3 ( p_str in varchar2 )
4 as
5 begin
6 insert into t
7 ( x ) values
8 ( p_str );
9 exception
10 when others
11 then
12 -- call some log_error() routine
13 null;
14 end;
15 /

Procedure created.

There we have a procedure, it uses when others to capture the error into a logging table and continues on.

ops$tkyte%ORA11GR1> exec maintain_t( rpad( 'x', 4001, 'x' ) );

PL/SQL procedure successfully completed.

Someone invokes it, it sure appears to work right? Everything is good... or is it?

ops$tkyte%ORA11GR1> select * from t;

no rows selected

Hmm, that table is empty, that is not good - what happened?

ops$tkyte%ORA11GR1> alter procedure maintain_t compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /

SP2-0805: Procedure altered with compilation warnings

ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8 PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR

Ahh, it was the dreaded when others not followed by a raise or raise application error. And it is reported as a warning - just like java reports bad programming practices and C does and C++ and Ada and so on. The developer is free to ignore these warnings, at their own peril of course.



Here is an excerpt from Expert Oracle Database Architecture that shows what I mean when I say your DBA's proposal "breaks the database"

<quote>
Procedure-Level Atomicity

It is interesting to note that Oracle considers PL/SQL anonymous blocks to be statements as well. Consider the following stored procedure:

ops$tkyte@ORA10G> create table t ( x int check (x > 0));
Table created.

ops$tkyte@ORA10G> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          insert into t values (-1 );
  6  end;
  7  /
Procedure created.
 
ops$tkyte@ORA10G> select * from t;
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0


So, we have a procedure we know will fail. The second INSERT will always fail in this case. Let¿s see what happens if we run that stored procedure:




ops$tkyte@ORA10G> begin
  2          p;
  3  end;
  4  /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C009598) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 2
 
 
ops$tkyte@ORA10G> select * from t;
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0


As you can see, Oracle treated the stored procedure call as an atomic statement. The client submitted a block of code, BEGIN P; END;, and Oracle wrapped a SAVEPOINT around it. Since P failed, Oracle restored the database back to the point right before it was called. Now, if we submit a slightly different block, we will get entirely different results:

ops$tkyte@ORA10G> begin
  2      p;
  3  exception
  4      when others then null;
  5  end;
  6  /
I fired and updated 1 rows
I fired and updated 1 rows
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t;
 
         X
----------
         1
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         1


Here, we ran a block of code that ignored any and all errors, and the difference in outcome here is huge. Whereas the first call to P effected no changes, here the first INSERT succeeds and the CNT column in T2 is incremented accordingly.

Note I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE to re-raise the exception to be a bug. It silently ignores the error and it changes the transaction semantics. Catching WHEN OTHERS and translating the exception into an old-fashioned return code changes the way the database is supposed to behave.


Oracle considered the ¿statement¿ to be the block that the client submitted. This statement succeeded by catching and ignoring the error itself, so the If error then rollback... didn¿t come into effect and Oracle did not roll back to the SAVEPOINT after execution. Hence, the partial work performed by P was preserved. The reason that this partial work was preserved in the first place is that we have statement-level atomicity within P: each statement in P is atomic. P becomes the client of Oracle when it submits its two INSERT statements. Each INSERT either entirely succeeds or fails. This is evidenced by the fact that we can see that the trigger on T fired twice and updated T2 twice, yet the count in T2 reflects only one UPDATE. The second INSERT executed in P had an implicit SAVEPOINT wrapped around it.

The difference between the two blocks of code is subtle, and something you must consider in your applications. Adding an exception handler to a block of PL/SQL code can radically change its behavior. A different way to code this¿one that restores the statement-level atomicity to the entire PL/SQL block¿is as follows:

ops$tkyte@ORA10G> begin
  2      savepoint sp;
  3      p;
  4  exception
  5      when others then
  6          rollback to sp;
  7  end;
  8  /
I fired and updated 1 rows
I fired and updated 1 rows
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t;
 
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0


Caution The preceding code represents an exceedingly bad practice. You should neither catch a WHEN OTHERS in general nor explicitly code what Oracle already provides as far as transaction semantics is concerned.


Here, by mimicking the work Oracle normally does for us with the SAVEPOINT, we are able to restore the original behavior while still catching and ¿ignoring¿ the error. I provide this example for illustration only¿this would be an exceeding bad coding practice.

</quote>



In short, loudly - explicitly and without backing down - refuse to do this, refuse.

It is a horrible coding practice

It does NOT do what your DBA claims (causes core dumps)

It WILL lead to countless hard to discover bugs


Just say NO



Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sadly you're not alone...

Simon Wenham, October 28, 2008 - 5:26 am UTC

I have recently started a new development project for a new client (I'm an independent contractor). I read through their coding standards and found a sentence stating that "There is also one non-specific exception handler called OTHERS which should always be defined"

They do go on to mention that this "should call a procedure called handle_unexpected_errors...this can become a default for exception handlers"

but this is the only mention of this procedure in the standards document and there is no guidance about what to put in the procedure to handle the exception (e.g. log the fact and anything else useful at the time and re-raise it).

What concerns me is that there will be a couple of permies working on the project who have no previous PL/SQL experience and they will blindly follow this guideline.
I shall be modifying the standards document.

Previously, I have found code on other projects with

WHEN OTHERS THEN
NULL;

They never fell over, ever. But not surprisingly there were problems with the data!
Tom Kyte
October 28, 2008 - 8:02 am UTC

their coding standard stinks.

The error should not be caught at the lowest level, it should be caught if at all at the HIGHEST level, logged and then reported to the end user.

Unhandled exceptions are by definition FATAL RUNTIME ERRORS.

Catching them does not make them all of a sudden "non-fatal", they are if possible even more fatal at that point, since they will lead to corrupt data (since you've completely broken the atomic bit of ACID)

Maybe show your client this page, or related pages.


When others
Triggers (on tables and instead of)
Autonomous transactions


three things, that had they not been invented, would completely change the world and reduce by orders and orders of magnitude the number of bugs in developed code - bugs that the developers don't even realize, will not be able to recognize, as bugs....

RE: number of bugs in developed code

Duke Ganote, October 28, 2008 - 3:07 pm UTC

"WHEN OTHERS THEN"? It's just bachelor housekeeping: sweep all the dirt and garbage under the rug -- and wonder why the place reeks, and the carpet's lumpy and hard to walk on.

Documentation bug?

rsergio, April 19, 2012 - 6:13 pm UTC

Tom,

In the PL/SQL Language Reference (Oracle documentation 11gR2), chapter 11 (PL/SQL Error Handling) there's a section called "Unhandled Exceptions" which states:

"If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram"

Any simple test shows this is not the case. So, either I am misunderstanding the statement or this is a documentation bug, right?
Tom Kyte
April 20, 2012 - 4:00 pm UTC

link to doc:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#i1889


It is sort of true - it is poorly worded to say the least. They were talking in the context of plsql


ops$tkyte%ORA11GR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3          x number;
  4  begin
  5          insert into t values ( 'hello world' );
  6          x := 1/0;
  7  end;
  8  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          p;
  3  exception
  4  when others
  5  then
  6          dbms_output.put_line( '----------------' );
  7          for x in ( select * from t )
  8          loop
  9                  dbms_output.put_line( x.x );
 10          end loop;
 11          dbms_output.put_line( '----------------' );
 12          RAISE;
 13  end;
 14  /
----------------
hello world
----------------
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 12


ops$tkyte%ORA11GR2> select * from t;

no rows selected



(and I hate their advice too, but that's another story)


as you can see in the above, the work of P was NOT rolled back while we were still in plsql - but it was in the clients perspective.


The client actually uses logic like this:
begin
   savepoint X;
   p;
exception
when others then 
  rollback to X;
  RAISE;
end;


to execute the code - the CLIENT rolls back - but the individual layers of plsql within the plsql do not.

the client always makes the decision whether to rollback or whatever.


I'll ask someone to look at that.



A reader, April 20, 2012 - 9:11 am UTC


A reader, April 20, 2012 - 12:51 pm UTC


Warning not showing up

A reader, April 23, 2012 - 9:38 am UTC 

In test below, why am I not getting the warning message "PLW-06009: procedure "SK_PRO" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR" as suggested by the discussion above under the Original Posting(the first answer)

SQL> CREATE OR REPLACE PROCEDURE SK_PRO AS
  2     L_NUM NUMBER;
  3  BEGIN
  4     L_NUM := 1/0;
  5  EXCEPTION
  6     WHEN OTHERS THEN
  7        NULL;
  8  END;
  9  /

Procedure created.

SQL> 
SQL> SHOW ERRORS PROCEDURE SK_PRO;
No errors.
SQL> 
SQL> SELECT *
  2  FROM V$VERSION;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production    
PL/SQL Release 11.2.0.2.0 - Production                                          
CORE 11.2.0.2.0 Production                                                      
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production               
NLSRTL Version 11.2.0.2.0 - Production

Tom Kyte
April 23, 2012 - 1:31 pm UTC

warnings are not enabled by default, did you enable them?

ops$tkyte%ORA11GR1> alter procedure maintain_t compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /

Anonymous PL/SQL block = "atomic statement": where is it documented?

Stew Ashton, December 06, 2012 - 4:18 am UTC


Recently, there was a lively discussion in an OTN forum on the question "Why does Tom Kyte say WHEN OTHERS without RAISE is a bug?"
https://forums.oracle.com/forums/thread.jspa?threadID=2470660
There was some debate about statement-level atomicity as it applied to PL/SQL. I tried to find something in the documentation about this, but failed. The Concepts Guides says "a SQL statement is an atomic unit of work" but doesn't say that an anonymous PL/SQL block is a "SQL statement".

Am I missing something? Is there anything in the documentation about atomicity in the specific context of PL/SQL?

By the way, I didn't find anything in the Support Knowledge Base either, but I'm a beginner there.
Tom Kyte
December 14, 2012 - 2:02 pm UTC

any "statement" is an atomic statement.


every "statement" is


plsql is just a statement, so is update, they are all just statements.


all statements in Oracle are atomic...

Found documentation on anonymous block rollback

Stew Ashton, December 06, 2012 - 4:57 am UTC


Of all places, the TimesTen documentation at
http://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm
"TimesTen PL/SQL differs from Oracle PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle will roll back to the beginning of the anonymous block. TimesTen will not roll back."

May I kindly disagree

Dick Goulet, May 03, 2013 - 1:08 pm UTC

I have a like for the when others exception handler. Especially since most pl/sql procedures at one time or another are run in batch modes that preclude anyone seeing the error. Not I absolutely dislike the "when others then null;" syntax, that one SHOULD raise an error, not a warning. Instead I prefer to have developers catch the error stack using dbms_utility.format_error_backtrace along with the sqlerrm and procedure name and dbname and sending it via utl_mail to a distribution list where the error will always get some attention. If you don't have a good distribution list to send it to, your local helpdesk is always a good choice. They enjoy annoying people.
Tom Kyte
May 06, 2013 - 7:10 pm UTC

specially since most
pl/sql procedures at one time or another are run in batch modes that preclude
anyone seeing the error.

huh??? really? Not in my experience. In my experience almost 100% of the time they are the code, they are the application, they are NOT written "in batch"

and even if they were - there must be some intelligent piece of code calling that batch (the only exception (all pun intended) would be dbms_job/scheduler).

There are two times I approve of when others NOT FOLLOWED BY A RAISE/RAISE_APPLICATION_ERROR.

a) the top level of a job submitted to dbms_job/scheduler. If it "fails", we retry it. So, you can 1) catch that, 2) log it, 3) email it, 4) pretend it "worked" knowing that you started some sort of workflow to get it fixed (eg: it is NOT IGNORED)

b) the top level of some application express calls - since PLSQL is the client language in this case. In order to gracefully return a nice error page (although apex does nicely 99% of the time itself without any help), you might need to catch all, log, alert someone and return an error page.


other than that - nope, exceptions should be re-raised to the client...

I'm pretty firm on this, have been and will be...


You can use when others correctly (error logging and such) but people ABUSE it and do stupid things like:


when others then
dbms_output.put_line( 'hey guys!!!! an error!!!! ' || sqlerrm );


that is why I tongue in cheek say "remove it from the language" - but love that they made when others NOT followed by raise/raise_appliation_error - a warning so we can find these bugs fast.

this, when others without raise, is in my experience the #1 cause of developer bugs - hands down.

same is true in java with try/catch blocks and every other language with exceptions. people "swallow" them - pretend they didn't happen and havoc results.



did DBA in original post mean exceptions in report builder?

Andrew, October 10, 2013 - 10:16 am UTC

Hello,
I sometimes maintain PL/SQL blocks in reports using reports builder 6i.

Something I observe is that any exception which is raised outside the report itself during actual report execution [eg formula columns] causes the entire process to die with signal 11.

I have no idea why this happens despite much searching - but the result is that we end up catching every exception within reports, writing something to log & not re-raising. I wonder if this is the kind of thing the DBA in original post meant?

Have you any idea why raising exceptions in reports may be causing the signal 11 / segfault errors? I really would love to know.

Thanks in advance.

PS I observe a number of WHEN OTHERS THEN NULL; statements from Oracle's code [Oracle Applications...] When I reject peer review of custom code for the same they try to tell me "well Oracle do it so why shouldn't I?" :(
Tom Kyte
October 10, 2013 - 1:42 pm UTC

because it is a bug in the oracle developed code as well - and please do file bugs against it.

I do when I see it.


ask your developers "so if you see someone jumping off of a bridge, would you do it because they did? or would you say 'that is stupid, they are going to kill themselves'"


Is it possible to amend your <quote> in your original response, to avoid confusion?

Mike from Brussels, October 22, 2013 - 1:26 pm UTC

Hi Tom,

In your original response to the question, you quote a section from "Expert Oracle Database Architecture". This is a little bit confusing as it is dealing with a 'different' table t to the one used in the example immediately preceding the quote, and this one evidently has a check constraint defined on it to disallow negative values.

Whilst plsql 'old hands' will find this fairly obvious, it could be confusing for beginners and it would be a shame to have this detract from the overall point of the response (which should be compulsory reading for all new plsql programmers!).

Any chance you could extend that quote to include the table creation statement with check constraint?

Thanks,
Mike.
Tom Kyte
November 01, 2013 - 8:38 pm UTC

done

Disable a single warning

Francois, June 19, 2014 - 10:01 am UTC

Hello,

I do like very much the warning with when others, but in our software there are a some procedures/functions where it doesn't end with a raise, this is by design.
For example the logging can fail, but if it is for traces or debug it shouldn't stop the main procedure. A few legacy functions returning an error code do the same.

I couldn't find anything like in Java where we can supress a warning for a single line of code, did i miss it?

Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.