## Saturday, March 01, 2014

### 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
----------

```