Oracle SQL*Plus: The Definitive Guide

Commenting Your Scripts

If you write extensive scripts, you should write extensive comments. In fact, any time you write a script, no matter how short, consider including a few comments to explain the purpose of the script.

Comments may be placed in a script using any of the following three methods:

1. By using the REMARK command

2. By using double-hyphen characters

3. By delimiting the comment by /* and */

Each method works just a bit differently from the others. You will probably find yourself gravitating towards the /*...*/ and — delimiters. The REMARK command is cumbersome, and consequently isn’t used very often.

The REMARK Command

The REMARK command may be used to place comments in a SQL script. Any text on the same line following the REMARK command is considered a comment. The REMARK command may also be abbreviated to REM, as the following example shows:

REMARK This is a comment.
REM This is a comment too.

SQL*Plus does not look for substitution variables in the text following a REMARK command, so you are free to use ampersands, and any other characters you like, in your comments.

The /* and */ Delimiters

The /* and */ delimiters are familiar to many programmers, and may also be used to delimit comments in SQL*Plus. Comments created using this method may span multiple lines, for example:

/*
This is the second line of a comment.
This is the third line.
*/

You can also use /* and */ to add comments to SQL queries, for example:

SELECT * 
  FROM employee
 WHERE /* employees are current */
       SYSDATE BETWEEN employee_hire_date 
                   AND nvl(employee_termination_date,SYSDATE);

An interesting side effect of using /* and */ is that when you use them to mark off complete lines of comments in a script, those lines are echoed to the display. For example, if you place the following commands in a file:

/*
This script selects the current date from the 
DUAL table, and displays it for you to see.
*/
SELECT SYSDATE FROM DUAL;

then execute that file, your output will look like this:

SQL> @c:\jonathan\sql_plus_book\xe_ch_5\comtest
SQL> /*
DOC>This script selects the current date from the
DOC>DUAL table, and displays it for you to see.
DOC>*/
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
18-MAR-98

This echoing occurs regardless of whether the echo setting is currently on or off. It will only occur when the comment starts on a line by itself. Comments that you embed within a SQL query will not be displayed in this manner.

Tip

Note that comments may not be nested.

Double Hyphens (- -)

Double hyphens may be used to delimit comments in much the same manner as the REMARK command. Anything following the double hyphen is considered a comment. Here are some examples:

--Describe the employee table
DESCRIBE employee
--Select all currently employed people.
SELECT * 
  FROM employee
 WHERE -- employees are current
       SYSDATE BETWEEN employee_hire_date 
                   AND NVL(employee_termination_date,SYSDATE);

As with /*...*/, the double hyphen may be used to embed comments within SQL queries and PL/SQL blocks. The only difference is that double hyphen comments cannot span lines.

Substitution Within Comments

SQL*Plus doesn’t normally check comments for substitution variables, but the rules change when comments are embedded in a SQL query or a PL/SQL block. Thus, you can enter the following comment, and SQL*Plus won’t treat &VAR as a substitution variable:

SQL> --In this comment, &VAR is not a substition variable.

However, if you enter a similar comment as part of a SQL query, SQL*Plus will see &VAR as a substitution variable, for example:

SQL> SELECT * 
  2    FROM employee
  3         --Now, &VAR is treated as a substitution variable.
  4   WHERE employee_termination_date IS NULL;
Enter value for var:

The reason for this seemingly inconsistent behavior is that SQL*Plus does not parse your SQL statements; instead, they are sent to Oracle. As soon as SQL*Plus sees that you have begun to type in a SQL command, it stops parsing and accepts whatever text you enter into the buffer. Before the contents of the buffer are sent to Oracle, SQL*Plus must replace any substitution variables with their contents. In doing this, it simply scans the entire buffer, including any comments it contains.

Substitution is never an issue with the REMARK command, because REMARK is a SQL*Plus command and can never be used in a SQL query.