How to handle divide by zero in Oracle function

Question

I'm getting a divide by zero error when I use this function. It compiles fine. Since I'm not using a select statement I'm assuming NULLIF isn't going to work. What can I use to avoid the divide by zero issue?

    OPEN GET_DATA;
    fetch GET_DATA into v_gpa_hsch,v_gpa_hsch_scale,v_gpa_scale,v_gpa,
     v_gpa_recalc,v_gpa_hsch_recalc;
    IF GET_DATA%NOTFOUND
    THEN
    IT_final_final_calc :=0;

    ELSE
            IF  v_gpa_hsch_scale < v_gpa_scale
            THEN IL_higher_scale_calc := v_gpa_scale;
            ELSE IL_higher_scale_calc := v_gpa_hsch_scale;
            END IF;

            IF v_gpa_hsch < v_gpa
            THEN IM_gpa_calc := v_gpa;
            ELSE IM_gpa_calc := v_gpa_hsch;
            END IF;

            IF IM_gpa_calc > IL_higher_scale_calc
            THEN IN_highest_gpa_calc := IL_higher_scale_calc;
            ELSE IN_highest_gpa_calc := IM_gpa_calc;
            END IF;

            IO_factor_calc := (IL_higher_scale_calc/4);

            --I PRESUME THE FOLLOWING LINE IS THE ISSUE:
            IP_rec_calc := +(IN_highest_gpa_calc/IO_factor_calc);

I only posted part of the code needed. Really, the only line that needs changing is IP_rec_calc := +(IN_highest_gpa_calc/IO_factor_calc). IO_factor_calc can be a zero. — doc, Jan 29 '19 at 16:24

score 5 · Accepted Answer · answered Jan 29 '19 at 16:30

You may use these two options depending on your requirement,

use a CASE

IP_rec_calc := CASE IO_factor_calc WHEN 0 then 0 --whatever you want 
                    ELSE +(IN_highest_gpa_calc/IO_factor_calc) 
                    END;

or a zero_divide exception handler

EXCEPTION
 WHEN zero_divide then
    dbms_output.put_line('Division by Zero happened .');
IP_rec_calc := 0; --whatever you want

score 4 · Answer 2 · answered Jan 29 '19 at 16:46

You don't need a query in order to work with NULLIF, you can use it in PL/SQL as well:

IP_rec_calc := IN_highest_gpa_calc / NULLIF(IO_factor_calc, 0);

This is even a very typical way to deal with this situation. The result of a division by zero is undefined, so we make it null.

score 2 · Answer 3 · answered Jan 29 '19 at 16:40

You are using a programming language. As such it features conditions of course:

IF IO_factor_calc = 0 THEN
  IP_rec_calc := 0;
ELSE
  IP_rec_calc := IN_highest_gpa_calc / IO_factor_calc;
END IF;

You can put more code in the IF and ELSE block in case you want to do some logging etc.

Tony Andrews · Answer 4 · 2019-01-29T16:32:44.080

1

What result do you want when IO_factor_calc = 0? You could do e.g.

IP_rec_calc := case when IO_factor_calc=0 then 0
                    else +(IN_highest_gpa_calc/IO_factor_calc) 
                    end;

edited Jan 29 '19 at 16:32

answered Jan 29 '19 at 16:27

Tony Andrews

129,880
21
220
259

If IO_factor_calc = 0 then I want IP_rec_calc to be zero. IP_rec_calc := case when IO_factor_calc=0 then IP_rec_calc = 0 else +(IN_highest_gpa_calc/IO_factor_calc) end; – doc Jan 29 '19 at 16:30

score 1 · Answer 5 · answered Jan 29 '19 at 17:22

1

I am used to this one:

IP_rec_calc := DECODE(IO_factor_calc, 0,NULL, IN_highest_gpa_calc / IO_factor_calc);

answered Jan 29 '19 at 17:22

Wernfried Domscheit

54,457
9
76
110

How to handle divide by zero in Oracle function

5 Answers5