Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    58

    Lightbulb Unanswered: ORA-01476: divisor is equal to zero

    I have a stored procedure and inside I do

    UPDATE answer SET
    value = (select (number_system/v_total_no_system) * 100 from
    answer)
    where question_id = 50;

    I got ORA-01476: divisor is equal to zero error.

    The reason is number_system = 0 in that record.

    Why when I manually do this below it doesn't return error?

    SQL> select (0/2)*100 from dual;

    (0/2)*100
    ----------
    0

    How can I resolve this error in stored procedure?

    Thanks in advance!

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Are you sure your v_totao_no_system is not 0?
    It is.

    See if that's what you meant

    UPDATE answer SET
    value = (select (number_system/v_total_no_system) * 100 from
    answer
    where question_id = 50)
    where question_id = 50;
    Last edited by Nocopy; 05-05-04 at 18:41.
    My way or the highway. Yeah

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I think it's your v_total_no_system
    PHP Code:
    select (2/0)*100 from dual
             
    *
    ERROR at line 1:
    ORA-01476divisor is equal to zero 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4

    Mathematics essentials: dividing

    When dividing two numbers, one should know their names. They are not just number1 and number2 or something like that, but dividendand divisor. Number you get as a result is called a quotient.
    Code:
    dividend
    --------- = quotient
    divisor
    As you probably know (and, if not, use a calculator), when a divisor equals zero (0), the result (quotient) is indefinite.

    You can do something about it, however, using the decode function:
    PHP Code:
    UPDATE answer SET
      value 
    =
      (
    SELECT (number_system /
               
    DECODE(v_total_no_system01e-99,
                                            
    v_total_no_system                     
                     
    )
              ) * 
    100
       FROM answer
      
    )
    WHERE question_id 50
    Doing so, 1E-99 (which stands for 0.00...001 - a decimal number which has 1 on 99th place behind a decimal sign) is something like 0 and the quotient will be VERY large number (unless dividend is, of course, similar to divisor), but it will not lead you to ORA-01476 error.

    I guess answer.value is a NUMBER and will not be able to hold such a huge value. Therefore, substitute 1E-99 with 1E99 and the result will then be not something_large, but 0.

    Or, ensure that data used as divisors are not equal to 0 (check constraint or something like that).

    Phew!

  5. #5
    Join Date
    Apr 2004
    Posts
    58
    Thanks for the suggestions!

Posting Permissions

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