ORA-01476: divisor is equal to zero

pinecone · #1 (**permalink**) 05-05-04, 17:34

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!

Nocopy · #2 (**permalink**) 05-05-04, 17:39

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;

The_Duck · #3 (**permalink**) 05-05-04, 17:39

I think it's your v_total_no_system

PHP Code:


		
			
select (2/0)*100 from dual

         *

ERROR at line 1:

ORA-01476: divisor is equal to zero

Littlefoot · #4 (**permalink**) 05-06-04, 02:46

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_system, 0, 1e-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!

pinecone · #5 (**permalink**) 05-06-04, 12:09

Thanks for the suggestions!