Advanced Oracle PL/SQL Programming with Packages

3.6. Building a Structured Function

Consider the problem of a function that does not execute a RETURN. The whole point of a function is to return a value. Not only should it return a value when everything goes right, it should even return a value when the function fails and raises an exception (NULL being the usual candidate under these circumstances).

In the twice function all my RETURN statements are nested inside IF clauses. So an invalid entry by the user means that all those RETURNs are ignored. There are lots of ways to fix this specific problem. You could include an ELSE statement. You could make sure that the action was valid at the start of the function (we'll look at that in a moment). The best all-around solution, however, is to always construct your functions with the following templated structure:

 1    FUNCTION twice RETURN VARCHAR2
 2    IS
 3       v_retval VARCHAR2(100) := 'null';
 4    BEGIN
 5
 6       RETURN v_retval;
 7
 8    EXCEPTION
 9       WHEN OTHERS
10       THEN
11          RETURN NULL;
12    END twice;

In this template I declare a local variable (the return value or v_retval) with the same datatype as the function itself. I then always make the last line of the function a RETURN of the v_retval variable's value. In addition, my exception returns NULL if any kind of exception is raised. You will never get a -6503 error with this template—and it is easier to debug than functions with RETURN statements scattered throughout the body of the program.

A version of twice that follows the template is shown in ...