Categories
Blogroll sql sqlplus

take care of minus !

Imagine this script (10gR2) :
set echo on
select BINARY_DOUBLE_INFINITY -
BINARY_DOUBLE_INFINITY from DUAL;

Run it and you would will get an expected result!

SQL> select BINARY_DOUBLE_INFINITY -
> BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY
----------------------
Inf

The issue in sqlplus is that – at the end of line means “query continues next line”. The correct answer of Inf-Inf is Nan.

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
Nan

By Laurent Schneider

Oracle Certified Master

2 replies on “take care of minus !”

Thanks to recall us that point, but the problem is not limited with BINARY_DOUBLE_INFINITY usage :
SQL> select 1 –
> 1 from dual;
select 1 1 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
…where 0 would be expected.

All the best !

Leave a Reply

Your email address will not be published.