DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-01476 Divisor equal to 0

  1. #1
    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. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Just test the divisor and, yes - decode() is a great option.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    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. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by liranke View Post
    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
    (your calculation here)
    exception
    when ZERO_DIVIDE
    then (raise error or do whatever you have to do when divisor = zero)
    end;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    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. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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. #8
    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. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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
  •  


Click Here to Expand Forum to Full Width