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.
Field 1 | Field 2
To calculate percentage between field1 and field 2
[sourcecode language="sql"] SELECT ((FIELD1/FIELD2) *100) as Percentage from TableA; [/sourcecode]
Now since the value of FIELD2 contains zero and any number divided by zero is infinity. So this will throw the exception:
[sourcecode language="sql"] ERROR at line 1: ORA-01476: divisor is equal to zero [/sourcecode]
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
[sourcecode language="sql"] DECODE(FIELD2,0,0,((FIELD1/FIELD2)*100)) [/sourcecode]
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
[sourcecode language="sql"] EXCEPTION WHEN ZERO_DIVIDE THEN :field2_var := 0.00001; END; [/sourcecode]
Or alternately you can replace the output of the divide by zero equation with a zero return value.
[sourcecode language="sql"] EXCEPTION WHEN ZERO_DIVIDE THEN return 0; END; [/sourcecode]