PL/SQL exception handling in function

Question

I am trying to generate DIVIDE_BY_ZERO exception in my oracle PL/SQL program. I using function but when I run my program I am getting error which is shown below. Can anyone tell me what is wrong in my program ?

code:

 CREATE OR REPLACE 
PACKAGE CALCULATOR AS 

  FUNCTION AddNumber(addend IN Number, Addend2 IN Number) RETURN NUMBER;
  FUNCTION DivNumber(divend IN Number, divend2 IN Number) RETURN NUMBER;

END CALCULATOR;
/

CREATE OR REPLACE
PACKAGE BODY CALCULATOR AS

  FUNCTION AddNumber(addend IN Number, Addend2 IN Number) RETURN NUMBER AS
  BEGIN
     return addend + addend2; 

  END AddNumber;

  FUNCTION DivNumber(divend IN Number, divend2 IN Number) RETURN NUMBER AS
  BEGIN
         return divend / divend2;
         EXCEPTION 
         WHEN ZERO_DIVIDE THEN
         NULL;    
  END DivNumber;

END CALCULATOR;
/

select calculator.AddNumber(3,4) from dual;
select calculator.DivNumber(12,0) from dual;

output:

Telling you what is wrong is easy. How to fix it is another matter; it depends on the "specification" (what you want the function to do when it encounters division by zero). What is wrong: In the case of division by zero, you enter the EXCEPTION block (execution does not reach the RETURN command), and in the EXCEPTION block you do not have a RETURN statement. A function is not permitted to have no RETURN statement. That's pretty much what the error message says. — user5683823
– user5683823, Commented Nov 16, 2017 at 1:16

aljassi · Accepted Answer · 2019-09-09 10:19:35Z

2

you can deal with exceptions like this

FUNCTION DivNumber(divend IN Number, divend2 IN Number) RETURN NUMBER AS
 e_ZERO_DIVIDE EXCEPTION;
 BEGIN
-- the condition
  IF divend2 = 0 THEN 
    Raise e_ZERO_DIVIDE; 
   END IF;
 return divend / divend2;
--Exception handling  
EXCEPTION 
WHEN e_ZERO_DIVIDE THEN
   dbms_output.put_line('Division by 0 or null');
   RETURN 1; -- or 0 or null
 WHEN OTHERS THEN
  dbms_output.put_line('ERROR: '||sqlerrm);
  RETURN 1; -- or 0 or null
 END DivNumber;

edited Sep 9, 2019 at 10:19

answered Nov 16, 2017 at 12:17

aljassi

2462 silver badges10 bronze badges

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

2 Comments

Riya Over a year ago

This program works but when user divide by 0 then message should also be displayed "Division by 0 or null". We have used output.put_line but message is not displaying ?

aljassi Over a year ago

u must set ur server output to on

Bob Jarvis - Слава Україні · Accepted Answer · 2017-11-16 01:29:00Z

0

IMO you should never write code which throws an avoidable exception. There may be times when you can't avoid raising an exception but in this case you can. I suggest that you rewrite your function as follows:

FUNCTION DivNumber(numerator IN Number, denominator IN Number) RETURN NUMBER AS
BEGIN
  IF NVL(denominator, 0) <> 0 THEN
     return numerator / denominator;
  ELSE
    RETURN NULL;
  END IF;
END DivNumber;

Best of luck.

answered Nov 16, 2017 at 1:29

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

6 Comments

Riya Over a year ago

Thank you for answering my question but when I run program I am getting this error : Error(6,10): PLS-00323: subprogram or cursor 'DIVNUMBER' is declared in a package specification and must be defined in the package body

user5683823 Over a year ago

Can you elaborate on why one "should never write code which throws an avoidable exception"? That doesn't make any sense to me. In this case, it makes perfect sense to want to alert the caller to the fact that division by zero was attempted. This is very different from one of the two input numbers (or both) being NULL. The function WOULD return NULL if one of the numbers was NULL. Would you not want to distinguish between that and division by zero? And if not, why not?

Riya Over a year ago

Though I added function in package body only.

Bob Jarvis - Слава Україні Over a year ago

@mathguy - in a routine which is intended to perform division it seems perfectly reasonable to me that the routine should test test its arguments to verify that they are valid and to take appropriate action. There are circumstances where you can't know ahead of time if the argument is valid or useful - for instance, when performing a singleton select which may throw a NO_DATA_FOUND exception - and in that case handling the exception is reasonable. But dividing by zero because the code didn't check for a non-zero denominator seems to me to be rather...sloppy.

APC Over a year ago

I agree with @mathguy and also with BobJarvis. There is a lot of merit in validating parameters and handling wrong values. The solution presented here implements "handling" by suppressing the ORA-01476 exception. In real life that could be undesirable: we might need to know when a zero is passed because that would indicate a data issue. It comes down to requirements, and to be fair the OP's requirement is to suppress the exception.

|

Collectives™ on Stack Overflow

PL/SQL exception handling in function

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related