A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911: 

SQL> @test1

SQL> set echo on

SQL> select 'Comment->' from dual; /*Andrew was here*/

  2  select 'More SQL' from dual;

select 'Comment->' from dual; /*Andrew was here*/

                            *

ERROR at line 1:

ORA-00911: invalid character

 

SQL>

To get the code to work, you need to include the comment before the semi-colon:

SQL> @test2

SQL> set echo on

SQL> select 'Comment->' from dual /*Andrew was here*/;

 

'COMMENT-

---------

Comment->

 

SQL> select 'More SQL' from dual;

 

'MORESQL

--------

More SQL

 

SQL>

However, if you try this in SQL Developer, both options work (as usual, click on the images to enlarge them and bring them into focus):

 

 

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>