SQL: exception handling in a function

1
votes

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?

2
oracleplsqlsqlexceptionsql-function
There is no return clause in case the exception occurs. You need to put, e.g. a return null into the catch block or re-raise the exception. Your current block silently swallows it - a_horse_with_no_name
Your procedure raises an exception, then handles the error to print a message, but in this case you have no return value - Aleksej
Yes, you're doing something wrong: your exception block doesn't do anything. In a function, it either needs to raise an error (and for a user defined error, you can use RAISE_APPLICATION_ERROR to output a specific error message) or return a value. Also, if you must use DBMS_OUTPUT it should only ever be for debugging purposes, IMHO; it has no place in production code. Personally, I'd go with some other method of debugging - if you're needing to log whether an action has occurred or not, why not log it to a table instead? - Boneist
fyi it's divisor, and greater than ;) - William Robertson

2 Answers

2
votes

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
1
votes

As PL/SQL already has a perfectly good zero_divide exception, I'd be tempted to just write the function as:

create or replace function f_interestrate
    ( pn_principal number
    , pn_interest  number
    , pn_years     number )
    return number
as
begin
    return ((pn_interest / pn_principal) - 1) / pn_years;
end;

then you'll get the default failure message:

SQL> select f_interestrate(2500000, 0.10, 0) from dual;
select f_interestrate(2500000, 0.10, 0) from dual
       *
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "XXX.F_INTERESTRATE", line 8

Or if you really need some customised handling,

create or replace function f_interestrate
    ( pn_principal number
    , pn_interest  number
    , pn_years     number )
    return number
as
begin
    return ((pn_interest / pn_principal) - 1) / pn_years;
exception
    when zero_divide then
        [[[ do something here ]]]
end;

I notice you also raise your custom ex_invalid_devisor exception when pn_interest is zero, even though this isn't used as a divisor, so perhaps there is some subtle logic I am missing here.

(Edit: thinking about it, if pn_interest is zero then maybe you just need to return pn_principal regardless.)