ORA-00911

I saw this error today and decided to investigate it. My 1996 SQL*Plus User’s Guide says:

Note: You cannot enter a comment on the same line on which you enter a semicolon.

I tried this out on SQL*Plus version 10.2.0.3.0. I ran a file containing SQL like this:

select sysdate-2 /* Two days ago */

from dual;

select sysdate-1

from dual; /* Yesterday */

select /* Today */

sysdate from dual;

select sysdate+1

from /* Tomorrow */

dual;

... and the SQL for SYSDATE-1 was ignored:

SQL> select sysdate-2 /* Two days ago */

  2  from dual;

SYSDATE-2

---------

07-AUG-12

SQL>

SQL> select sysdate-1

  2  from dual; /* Yesterday */

  3

SQL> select /* Today */

  2  sysdate from dual;

SYSDATE

---------

09-AUG-12

SQL>

SQL> select sysdate+1

  2  from /* Tomorrow */

  3  dual;

SYSDATE+1

---------

10-AUG-12

SQL>

Then I ran another file containing the same SQL with the blank lines removed:

select sysdate-2 /* Two days ago */

from dual;

select sysdate-1

from dual; /* Yesterday */

select /* Today */

sysdate from dual;

select sysdate+1

from /* Tomorrow */

dual;

... and the SQL for SYSDATE-1 caused an ORA-00911:

SQL> select sysdate-2 /* Two days ago */

  2  from dual;

SYSDATE-2

---------

07-AUG-12

SQL> select sysdate-1

  2  from dual; /* Yesterday */

  3  select /* Today */

  4  sysdate from dual;

from dual; /* Yesterday */

         *

ERROR at line 2:

ORA-00911: invalid character

SQL> select sysdate+1

  2  from /* Tomorrow */

  3  dual;

SYSDATE+1

---------

10-AUG-12

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>