File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Dividing By zero Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Dividing By zero" Watch "Dividing By zero" New topic

Dividing By zero

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

I was doing a practice exam question and get across a question that included divide by zero scenario.

I tried this

select 1/0 from dual;

ERROR at line 1:
ORA-01476: divisor is equal to zero (I think for division operation it checks the divisor to make sure it is not a zero)

So why below query does not result in error.

select 1
from dual
where exists( select 1/0
from dual);


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

Interesting. I'd say that this is because the EXIST condition just looks whether the subquery returns any row, but doesn't evaluate any expressions in the SELECT clause. It doesn't have to - the existence of rows depends only on the WHERE clause, not on the expressions in the SELECT clause.

This particular case illustrates a general aspect of SQL, which is that you don't have any control over how many times and in which order individual expressions in your SQL statements will be evaluated. This has consequences especially when calling stored procedures from SQL statements, where the number of times they get called may be significant (unlike in pure SQL, where it doesn't matter).
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

I have created a table
create table idm(id number);
insert into idm values(1);
insert into idm values(2);
insert into idm values(3);

update idm
set id=1/0
where id=4;

This does not gives divide by zero error. (this is because here it is not trying to find out 1/0 as there is no row with id=4)
But if I try this

update idm
set id=1/0
where id=3; (this is because here it is trying to solve 1/0 as there is a row with id=3)

this results in divide by zero error
I agree. Here's the link:
subject: Dividing By zero
It's not a secret anymore!