CODEWITHSUNDEEP
oracle-databaseplsqlsqlexceptionsql-function
user6261443
user6261443

Reputation:

SQL: exception handling in a function

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?

Upvotes: 1

Views: 1021

Answers (2)

William Robertson
William Robertson

Reputation: 15991

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

Upvotes: 1

pahariayogi
pahariayogi

Reputation: 1163

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

Upvotes: 2

Related Questions