Wednesday, April 06, 2011

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>

No comments: