ORA-01476 Divisor equal to 0

# Thread: ORA-01476 Divisor equal to 0

1. Junior Member
Join Date
Oct 2011
Posts
4

## ORA-01476 Divisor equal to 0

Hi

I am working on an application that allows a user to create a KPI formula, and the app dynamically creates a view for this formula.
However as we started using it we have found that we are encountering the ORA-10476 (divisor is equal to zero) often.
I have read about options of using decode or case to prevent this but it doesn't cover all our options.
As a result of the fact that the user can create any equation he wants , and it can be as complicated as they want (
2 examples:
A/(B/C-D/E) - If C or E or (B/C-D/E) are 0 the whole select will fall
A/(B-C/(D-F)) - if (D-F) or ((B-C/(D-F)) is 0 then again the select will fall.

I have seen that in MS SQL they have an option of arithabort which allows the database to return the rows that don't fall under the "divisor by 0".

Does anyone have an idea of how to solve this?
either by setting something similar to arithabort, or maybe someone has a procedure to check an equation and return the arithmetical steps in order that athey are performed?

Thanks
Liran

2. Just test the divisor and, yes - decode() is a great option.

3. Junior Member
Join Date
Oct 2011
Posts
4
Because ita a system that creates the quay dynamically I have no way ahead of tine of knowing what the divisor is.
If you have an expression that can help find the divisor in an equation than that would help.

4. Originally Posted by liranke
Because ita a system that creates the quay dynamically I have no way ahead of tine of knowing what the divisor is.
If you have an expression that can help find the divisor in an equation than that would help.
This can be handled via EXCEPTIONS, like:

begin
exception
when ZERO_DIVIDE
then (raise error or do whatever you have to do when divisor = zero)
end;

5. Junior Member
Join Date
Oct 2011
Posts
4
Hi

The problem with using exceptions is that if I want to perform an insert from select on the result set , the exception causes the whole set to fall. I am trying to find a way of inserting the rows that dont have a divisor of 0.

6. Not true if you encapsulate ONLY your calculations in a begin/end block - if you do that you can do whatever you want with the zero-divisor rows and still keep inserting the good ones.

7. Can you use a case statement?

Code:
```CASE ("formula for computing divisor")
WHEN 0 then null
ELSE "formula for computing numerator"/"formula for computing divisor"
END thisColumMayBeNull,```

8. Junior Member
Join Date
Oct 2011
Posts
4
Hi

No I can't use case as I don't know what the divisor is as it is created automatically in the pl SQL code with the whole equation being inserted to the procedure when run.
Some times the equation could look like:

Counter1/counter2+counter3
Here the divisor is counter2

Or it could be:

Counter1/(counter2 + counter3)
here the divisor is (counter2 + counter3)

And it could be :

Counter1/counter2/counter3
Here both counter2 and counter3 are divisors and then case won't work.

Liran

9. Create a function that accepts a varchar2 and returns a number. Then do that "Math" in that function and if it works return the results otherwise either return 0 or something else that lets people know the calculation was invalid. You would need to convert variables into actual values without doing the math.

You might also create a type to over ride the number type and in the type body have error handling for the divide by zero error. That type would be the data type used in the actual tables. I have never tried this, but it might work.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•