PL SQL - Return SQLCODE as OUT parameter is accepted?

Question

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

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 2

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.