Oracle's Idea of Infinity

In version 10, Oracle introduced binary_double and binary_float datatypes. If you select 1c or 1e from dual, the number 1 is given an alias of C or E respectively:

SQL> select 1c from dual

  2  /

         C

----------

         1

SQL> select 1e from dual

  2  /

         E

----------

         1

SQL>

If you select 1d from dual, it is treated as a binary_double datatype:

SQL> select 1d from dual

  2  /

        1D

----------

  1.0E+000

SQL>

If you select 1f from dual, it is treated as a binary_float datatype:

SQL> select 1f from dual

  2  /

        1F

----------

  1.0E+000

SQL>

You cannot divide 1c or 1e by zero:

SQL> select 1c/0 from dual

  2  /

select 1c/0 from dual

         *
ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> select 1e/0 from dual

  2  /

select 1e/0 from dual

         *
ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL>

But 1d or 1f divided by zero is infinite. This is not what I learnt at school. In those days, any number divided by zero was undefined.

SQL> select 1d/0 from dual

  2  /

      1D/0

----------

       Inf

SQL> select 1f/0 from dual

  2  /

      1F/0

----------

       Inf

SQL>

Oracle also has the concept of negative infinity:

SQL> select -1d/0 from dual

  2  /

     -1D/0

----------

      -Inf

SQL> select -1f/0 from dual

  2  /

     -1F/0

----------

      -Inf

And you can even test if a value is infinite:

SQL> select 'Yes' as Yes from dual

  2  where 1d/0 is infinite

  3  /

YES

---

Yes

SQL> select 'Yes' as Yes from dual

  2  where -1f/0 is infinite

  3  /

YES

---

Yes

SQL>

Comments in SQL*Plus

(Tested on Oracle 9.)

You can comment out a whole SQL statement by typing rem at the start of the line:

SQL>

SQL> rem select sysdate today from dual;

SQL>

If the SQL statement goes over more than one line, you cannot use rem to comment out part of it:

SQL> select

  2  rem Tomorrow

  3  sysdate + 1 tomorrow from dual;

sysdate + 1 tomorrow from dual

*

ERROR at line 3:

ORA-00923: FROM keyword not found where expected

SQL>

You have to use two hyphens at the start of the line to do that:

SQL> select

  2  -- Tomorrow

  3  sysdate + 1 tomorrow from dual;

TOMORROW

---------

07-APR-11

SQL>

Alternatively you can use /* and */ in the same way:

SQL> select

  2  /* Yesterday */

  3  sysdate - 1 yesterday from dual;

YESTERDAY

---------

05-APR-11

SQL>

You can also use -- at the end of a line to turn the remaining text into a comment:

SQL> select count(*)

  2  from -- Comment

  3  dba_tables;

  COUNT(*)

----------

      3017

SQL>

But it does not behave consistently. In the next example the comment is treated as an alias instead:

SQL> select count(*) -- Comment

  2  from dba_tables;

COUNT(*)--COMMENT

-----------------

             3017

SQL>

The same goes for /* and */:

SQL> select count(*)

  2  from /* Comment */

  3  dba_indexes;

  COUNT(*)

----------

      2280

SQL> select count(*) /* Comment */

  2  from dba_indexes;

COUNT(*)/*COMMENT*/

-------------------

               2280

SQL>

Slashes in SQL*Plus and PL/SQL

I had a problem recently with a large package, which used to compile without errors on
Oracle 9. When I moved it to Oracle 10 and tried to compile it there, it produced several pages of SP2-0734 messages. The package had about a dozen statements something like this:

SELECT
NUMERICAL_EXPRESSION1
         /
NUMERICAL_EXPRESSION2
FROM
SOME_TABLE_OR_VIEW
ETC

Each statement was doing a division and the slash was on a line by itself several places from the start of that line. These statements were interpreted correctly under Oracle 9 but not by Oracle 10 nor 11. I did an experiment with an SQL statement doing a simple division and ran it on Oracle 9, 10 and 11. You can see the results on the screen print below (click to enlarge it):

In the top left hand corner, the Oracle 9 version works correctly.

In the bottom row, the Oracle 10 and 11 versions both fail as soon as they see the slash in column 8. I believe Oracle sees it as an instruction to run the SQL typed so far rather than a division symbol.

In the top right hand corner, the Oracle 10 version is rewritten with the slash at the end of
line 1 and it works correctly again.
  
I then went back to the package with the compilation errors, changed the dozen or so division statements in the same way and the compilation errors disappeared.

When I had some spare time I raised a Service Request with Oracle. They confirmed that it was bug 4219339 and also provided a different workaround, setting SQLPLUSCOMPATIBILITY to a value less than 9. That worked too, as you can see in the example below, which I ran on Oracle 10.2.0.1.0 on Linux:

SQL> select 1
  2         /
select 1
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> set sqlpluscompatibility 8.1.7
SQL> select 1
  2         /
  3         2
  4  from dual;

       1/2
----------
        .5

SQL>