Thursday, August 09, 2012

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>

No comments: