SQL: exception handling in a function

Question

I have encountered a rather mysterious problem today. As I executed my SQL function f_interestrate()( which should raise a from me defined exception when one of the parameters is equal to 0 ) with the following parameters:

SELECT GENERAL_FUNCTIONS.F_INTERESTRATE(2500000, 0.10, 0) FROM dual;

Gave me the following error:

ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "NOAHBASE.GENERAL_FUNCTIONS", line 73
06503. 00000 - "PL/SQL: Function returned without value"
*Cause: A call to PL/SQL function completed, but no RETURN statement was executed.

*Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

But as you may see in the following code sample the function should instead raise the form me defined exception ex_invalid_devisor. Not forget to mention that this function is nested inside a package.

 FUNCTION f_interestrate(pn_principal NUMBER, pn_interest NUMBER, pn_years NUMBER) RETURN NUMBER IS 
      vn_interestrate NUMBER;
      ex_invalid_devisor EXCEPTION;
    BEGIN
      IF pn_principal = 0 OR 
         pn_interest = 0 OR 
         pn_years = 0 THEN 
          RAISE ex_invalid_devisor;
      ELSE
        vn_interestrate := ((pn_interest/pn_principal)-1)/pn_years;
        RETURN vn_interestrate;
      END IF;
    EXCEPTION
      WHEN ex_invalid_devisor THEN
        DBMS_OUTPUT.PUT_LINE('Devisor must be bigger then 0');
    END;

Am I doing anything wrong?

Answer 1

This is how you should handle ... I added 'return -1' in your code. HTH.

create or replace FUNCTION f_interestrate(pn_principal NUMBER, pn_interest NUMBER, pn_years NUMBER) RETURN NUMBER IS 
      vn_interestrate NUMBER;
      ex_invalid_devisor EXCEPTION;
    BEGIN
      IF pn_principal = 0 OR 
         pn_interest = 0 OR 
         pn_years = 0 THEN 
          RAISE ex_invalid_devisor;
      ELSE
        vn_interestrate := ((pn_interest/pn_principal)-1)/pn_years;
        RETURN vn_interestrate;
      END IF;
    EXCEPTION
      WHEN ex_invalid_devisor THEN
        DBMS_OUTPUT.PUT_LINE('Devisor must be bigger then 0');
        return -1;
    END;

SQL> select F_INTERESTRATE(2500000, 0.10, 0) FROM dual;

F_INTERESTRATE(2500000,0.10,0)
------------------------------
                            -1