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?

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 — user330315
– user330315, Commented Oct 18, 2016 at 9:06
Your procedure raises an exception, then handles the error to print a message, but in this case you have no return value — Aleksej
– Aleksej, Commented Oct 18, 2016 at 9:06
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
– Boneist, Commented Oct 18, 2016 at 9:08

Aleksej · Accepted Answer · 2016-10-18 09:20:52Z

2

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

edited Oct 18, 2016 at 9:20

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

answered Oct 18, 2016 at 9:17

pahariayogi

1,1831 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aleksej Over a year ago

This only makes sense if the caller of the functions is ready to handle a -1 as an error; besides you should be sure that -1 can not be a valid return value

pahariayogi Over a year ago

It's just a point to the problem and to prove that it fixes the problem. One can always change the value returned value from '-1' to anything. The point is you cannot have a function without returning any value. Its rather best NOT to handle zero division exception if -1 or -x value is not acceptable.

William Robertson · Accepted Answer · 2016-10-18 13:37:45Z

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.)

Collectives™ on Stack Overflow

SQL: exception handling in a function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related