ORA-01476: divisor is equal to zero

This error occurs when an expression is divided by zero. It is considered a logical error in Oracle. In mathematics, division by zero is division where the divisor (denominator) is zero. Such a division can be formally expressed as a/0 where a is the dividend (numerator). Whether this expression can be assigned a well-defined value depends upon the mathematical setting. In ordinary (real number) arithmetic, the expression has no meaning, as there is no number which, multiplied by 0, gives a (a≠0), and so division by zero is undefined. Since any number multiplied by zero is zero, the expression 0/0 has no defined value and is called an indeterminate form.

In computing, a program error may lead to an attempt to divide a number by zero. Depending on the programming environment and the type of number (e.g. floating point, integer) being divided by zero, it may: generate positive or negative infinity by the IEEE 754 floating point standard, generate an exception, generate an error message, cause the program to terminate, or result in a special not-a-number value.

Let us illustrate this with the help of an example.Lets assume a SELECT statement where you are doing a calculation based on the value inside a column. A simple example would be percentage calculation. Percentage is a fraction of 2 numbers multiplied by 100.

TableA

Field 1 | Field 2
_____________________

20 0
30 50
40 60

To calculate percentage between field1 and field 2

SELECT ((FIELD1/FIELD2) *100) as Percentage from TableA;

Now since the value of FIELD2 contains zero and any number divided by zero is infinity. So this will throw the exception:

ERROR at line 1:
ORA-01476: divisor is equal to zero

So how do you solve this. There are many ways to handle this error in Oracle.

1. The first and foremost way is to enforce the business logic and try to ensure that the field doesn’t contain a 0 in the first place.

2. Use the DECODE function

DECODE(FIELD2,0,0,((FIELD1/FIELD2)*100))

This will return 0 in case the divisor is set to 0

3.User ZERO_DIVIDE to handle a zero divisor error
In PL/SQL you can trap the error using ZERO_DIVIDE option. The best way to do it is replace the zero with a very small value like 0.00001

EXCEPTION
WHEN ZERO_DIVIDE THEN
:field2_var := 0.00001;
END;

Or alternately you can replace the output of the divide by zero equation with a zero return value.

EXCEPTION
WHEN ZERO_DIVIDE THEN
return 0;
END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s