Is it best practice for functions to return after raising an error in PL/SQL

Question

I have a bunch of functions in packages that first check the validity of the work being requested and throw exceptions if not.

Example:

package body foo as
  function implode (
    i_foo_id number
  ) return implode_id as
    not_implodable exception;
    implode_id number;
  begin
    if not is_implodable(i_foo_id) then
      raise not_implodable;
    end if;
    //Implode logic here.
    return implode_id;
  exception
    when not_implodable then
      raise_application_error(-20005, 'Imploding is not possible on this foo.');
  end implode;
end foo;

I have turned on warning reporting and get something like this when I compile.

Warning(67,3): PLW-05005: subprogram IMPLODE returns without value at line 14.

If I put a return statement after the raise_application_error then the warning goes away. Since raising the error exits the call stack, is there any good reason to return null anyways?

Answer 1

No. It is not a best practice for functions to return after raising and error in PL/SQL.

It is possible and sometimes even recommended to add code just to shut up the compiler, like:

exception
  when fooex then
    raise_application_error(-20100, 'invalid number');
    return null; -- silence PLW-05005
end;

Be sure to document such a peculiar code !

Generally I don't like a code that is just there to satisfy compiler warning, but there is always exceptions to this rule. In this particular case I'd say the problem is more the compiler than the code. In my opinion code like below is perfectly valid and compiler should not complain about it. In fact I think compiler should warn if a block has statements after raise as it's effectively a dead-code (unreachable code).

Let's consider the following function:

$ cat foo.sql 
create or replace function foo(p_in in number)
return number is
  fooex exception;
begin
  if p_in < 1 then
    raise fooex;
  end if;

  return p_in;
exception
  when fooex then
    raise_application_error(-20100, 'invalid number');
end;
/
show errors

Running this in Oracle 11g XE:

-- By default the warnings are disabled
SQL> @foo

Function created.

No errors.

Usually I want all warnings:

SQL> alter session set plsql_warnings = 'ENABLE:ALL';

Session altered.

SQL> @foo

SP2-0806: Function created with compilation warnings

Errors for FUNCTION FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05005: subprogram FOO returns without value at line 13
1/1      PLW-05018: unit FOO omitted optional AUTHID clause; default
         value DEFINER used

After reviewing the code I found out the warnings above are incorrect, so I disable them for this compilation unit only:

SQL> alter session set plsql_warnings = 'ENABLE:ALL, DISABLE:(5005,5018)';

Session altered.

SQL> @foo

Function created.

No errors.
SQL>

Now all is good and great.

My recommendation is to turn on all warnings by default and then turn off wrong positives per compilation unit (alter session set plsql_warnings = 'ENABLE:ALL, DISABLE:(5005,5018)';). If possible: alter system plsql_warnings = 'ENABLE:ALL'; but in practice this might be a bit too harsh ...

And oh, it is a recommended practice to turn on all compiler error checks. We as a programmers need all the help the compilers can give to us ! Unfortunately with Oracle PL/SQL compiler this needs to be compromised.