5

I just read this question, and a solution states that:

The fact that you don't know you got the NO_DATA_FOUND exception suggests that you have made one of the biggest errors PL/SQL developers ever make:

EXCEPTION
    -- Never do this in real code!!!
   WHEN OTHERS THEN NULL;
END;

Could you explain me what is the error in this statement and what would you do to avoid doing that...

Community
  • 1
  • 1
Romain Linsolas
  • 79,475
  • 49
  • 202
  • 273

3 Answers3

14

The problem is, that you are catching all exceptions, and then ignoring them. You'll never know when something went wrong.

Matthew Watson
  • 14,083
  • 9
  • 62
  • 82
  • You mean that the bad practice here is not by using "When Others Then Null", but *only* using it, i.e. whitout catching any other exception before? – Romain Linsolas Sep 17 '09 at 12:13
  • 1
    No, its bad to ever use it. at the VERY least, your when others clause should log the exception somewhere. but most likely you should log and raise the exception – Matthew Watson Sep 17 '09 at 12:40
  • 4
    @Matthew: I disagree. There are a number of perfectly valid scenarios where you would want to catch all exceptions and ignore them. Whether you log them or not is up to you. I do agree that 9 times out of ten you'd want to do some kind of logging but if I'm doing some kind of best-efforts code in a frequently used, low level API then I'd probably skip the overheads of logging. – darreljnz Sep 17 '09 at 19:55
  • 3
    @darreljnz: and then one day you'll be scratching your head over why something is failing, and have go adding debug code, or run through the debugger to find the place where its failing. There may be very limited cases, I would suspect its more like 999/1000 than 9/10 though. – Matthew Watson Sep 18 '09 at 11:26
  • 13
    Bottom line: if you don't care if the process fails, you don't therefore care if the process succeeds - so why waste resources doing it in the first place - delete the code entirely. – Jeffrey Kemp Sep 23 '09 at 11:49
1

There's nothing wrong with this snippet of code if you don't want the pl/sql block's exception to propagate any further for example. If you do it on purpose, it's not bad code or a mistake. That's the catch all in pl/sql. And there might be situations in code where you have nested BEGIN/EXCEPTION/END blocks and one might not want the transaction to fail just if a particular cross section of code fails. You can't state it's bad coding if you do it intentionally for whatever reason/requirement. 

BEGIN

  --something important here

  --something even more important here

  BEGIN
    --something secondary goes here but not important enough to stop the process or
    --log a message about it either
    --maybe send an informative email to the support group or 
    --insert a log message when debugging the process or
    --the list could go on and on here
  EXCEPTION
    --I don't care if this block fails, absorbing all errors regardless of type
    WHEN OTHERS THEN NULL;
  END;

  -- something super important here, must happen

EXCEPTION
  WHEN NO_DATA_FOUND THEN 
    -- do something useful for this exception
  WHEN OTHERS THEN
    -- do something by default if we don't expect this error
END;
mike-james-greco
  • 56
  • 3
  • 3
    In practice it is very rare to not care about any possible exception. 99.9% of the `when others then null` code is when someone wants to ignore one specific error but is too lazy to catch it properly. – Jon Heller Aug 16 '14 at 22:32
-2

It is ALWAYS bad coding. And you can say that it is bad coding if you do it on purpose. In fact, it is horrible code if you do it on purpose because it demonstrats how little you understand about all of the errors you are completely ignoring.

http://stevenfeuersteinonplsql.blogspot.com/2017/02/now-not-to-handle-exceptions.html

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1155066278457

dci_dave
  • 65
  • 2