Handle Divided By Zero in Oracle

Problem

Ratio is very useful variable. For example, the ratio between credit card account balance and credit limit is a good measurement of the default risk. However, when calculating ratio, it the denominator is zero, Oracle raises an error,ORA-01476: divisor is equal to zero, and stops the query. This is inconvenient as shown below.

SQL> select * from tbl_account;

CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT
----------- ------------ ------------
          1         2000         5000
          2            0            0
          3         6000        12000

SQL> select a.*, ACCT_BALANCE/CREDIT_LIMIT r from tbl_account a;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected

Solution

One solution to calculating ratio or division is to build a function that returns NULL when the denominator is zero and normal value when it is not zero. The script below builds such a function div(num1, num2) that calculates num1/num2. It will return NULL when num2 is zero and continue the execution.

create or replace FUNCTION div (
   num1 number,
   num2 number
)
   RETURN NUMBER IS
   v_number   NUMBER;
BEGIN
   BEGIN
      v_number:= num1/num2 ;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;
   END;

   RETURN v_number;
END div;
/

After we run the above script to build function div(), we can use it to replace division "/".

SQL> select a.*, div(ACCT_BALANCE,CREDIT_LIMIT) r from tbl_account a;

CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT          R
----------- ------------ ------------ ----------
          1         2000         5000         .4
          2            0            0
          3         6000        12000         .5

The following are other outputs of using div()function.

SQL> select div(0,0) r from dual;

         R
----------


SQL> select div(NULL,0) r from dual;

         R
----------


SQL> select div(NULL,NULL) r from dual;

         R
----------