PL SQL - Return SQLCODE as OUT parameter is accepted ?

Question

Hi,

I have a procedure that returns an OUT parameter.

procedure foo (in_v IN INTEGER, out_v OUT integer)
BEGIN
...
EXCEPTION
  WHEN OTHERS THEN
    --sh*t happend
    out_v := SQLCODE;
END

That parameter will be 0 if everything goes OK, and <> 0 if something ugly happened.

Now, if sh*t happens along the way, an exception will be thrown.

Is it ok to assing the SQLCODE value to the OUT parameter ? Or is this consideres a code smell, and I will be expelled from the programming community ?

Thanks in advance.

Answer 1

Whether it's OK or not depends on what you're trying to achieve with it, I suppose. What problem are you trying to solve, or what requirement are you trying to meet?

the usual behaviour with errors is to gracefully handle the ones that you expect might happen during normal functioning and to allow those that you do not expect to be raised, so this does look odd.

Answer 2

If there is no additional handling of the error, I would probably advise against it. This approach just makes it necessary for each caller to examine the value of the out parameter anyway. And if the caller forgets it, a serious problem may pass unnoticed at first and create a hard to debug problem elsewhere.

If you simply don't catch OTHERS here, you ensure that the caller has to explicitly catch it, which is a lot cleaner an easier to debug.

Answer 3

Its ok, but I don't recommend it. By doing it this way you're forcing the calling code to do non-standard error handling. This is fine if you are the only person ever calling the code and you remember to check the error code. However, if you're coding in a large system with multiple programmers I think you should be kind to your fellow programmers and follow the standard way of exception handling supported by the language.

If you do decide to go down that route, also pass back SQLERRM as without the error text you only have the error code to go by. After years of catching "-20001" for the hundreds of application errors in 3rd party software the SQLERRM is often important.

Answer 4

This coding style is not a good idea for a number of reasons.

First, it is bad practice to mix errors and exceptions, even worse practice to mix return values and exceptions. Exceptions are meant to track exceptional situations - things completely unexpected by normal programming such as out of memory issues, conversion problems and so on that you normally would not want to write handlers at every call site for but need to deal with at some level.

The second worrying aspect of the coding style is that your code is effectively saying when an exception is encountered, the code will signal to its caller that something bad has happened but will happily throw away ALL exception information except for the SQLCODE.