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>

SET SCAN OFF and SET SCAN ON

This was tested on Oracle 11.2. It's not a good idea to use an ampersand in a PL/SQL comment. Oracle assumes it is the start of a substitution variable and prompts you to input a value:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /
Enter value for how:
old   3: -- Ampersands & how they cause problems in PL/SQL.
new   3: -- Ampersands  they cause problems in PL/SQL.

Procedure created.

SQL>

... and the comment entry is not stored correctly in the database:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands  they cause problems in PL/SQL.
null;
end;

SQL>

One way to deal with this is to get your developers to use the word and instead:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands and how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

Then the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands and how they cause problems in PL/SQL.
null;
end;

SQL>

If that is not possible, you can do set scan off before compiling the PL/SQL then Oracle does not treat the ampersand as the start of a substition variable. Before you do this, you need to be sure that your PL/SQL does not contain any genuine substitution variables:

SQL> set scan off
SQL> show scan
scan OFF
SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

... and again the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands & how they cause problems in PL/SQL.
null;
end;

SQL>

It's probably best to set scan on afterwards in case any SQL which follows includes a real substitution variable:

SQL> set scan on
SQL> show scan
scan ON
SQL>

Comments on Materialized Views

In Oracle 9.2.0.7.0 you had to put comments on materialized views as follows:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create materialized view andrews_mv

  2  refresh with rowid

  3  as select * from andrews_table

  4  /

 

Materialized view created.

 

SQL> comment on table andrews_mv is

  2  'My first materialized view'

  3  /

 

Comment created.

 

SQL>

 

This no longer works in Oracle 11.2.0.1.0. You have to do it like this instead:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create materialized view andrews_mv

  2  refresh with rowid

  3  as select * from andrews_table

  4  /

 

Materialized view created.

 

SQL> comment on table andrews_mv is

  2  'My first materialized view'

  3  /

comment on table andrews_mv is

                 *

ERROR at line 1:

ORA-12098: cannot comment on the materialized view

 

SQL> comment on materialized view andrews_mv is

  2  'My first materialized view'

  3  /

 

Comment created.

 

SQL>

Adding a Comment to a Column Description

This simple example, tested on an Oracle 9 database, shows how to add a comment to a column description. First create a table:

 

SQL> create table blah (col1 varchar2(1))

  2  /

 

Table created.

 

SQL>

 

And check that there are no comments on it:

 

SQL> select column_name, comments

  2  from user_col_comments

  3  where table_name = 'BLAH'

  4  /

 

COLUMN_NAME          COMMENTS

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

COL1

 

SQL>

 

Then add a comment to column col1 restrospectively and query user_col_comments again:

 

SQL> comment on column blah.col1 is 'Column 1'

  2  /

 

Comment created.

 

SQL> select column_name, comments

  2  from user_col_comments

  3  where table_name = 'BLAH'

  4  /

 

COLUMN_NAME          COMMENTS

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

COL1                 Column 1

 

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>