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 :

  • By using the REMARK command
  • By using double-hyphen characters
  • By delimiting the comment by /* and */

Each method works a bit differently from the others. You will probably find yourself gravitating toward the /* . . . */ and -- delimiters. I find typing REMARK to be a bit cumbersome.

8.7.1 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 be abbreviated to REM as the following example shows:

REMARK This is a comment.

REM This is a comment too.

SQL*Plus doesn't 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.

8.7.2 The /* and */ Delimiters

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


This is the second line of a comment.

This is the third line.


You can use /* and */ to add comments to SQL statements or to PL/SQL blocks. When you do this, such comments are not recognized by SQL*Plus, but rather by the database engine. Such comments may appear anywhere within a SQL statement:


 FROM employee

 WHERE /* employees are current */

 SYSDATE BETWEEN employee_hire_date 

 AND nvl(employee_termination_date,SYSDATE);

SQL*Plus objects to comments that appear following the beginning of a SQL*Plus command:


DESCRIBE /* Is this a comment? */ employee

SP2-0565: Illegal identifier.

When commenting SQL*Plus commands, be sure to comment entire commands. Don't add trailing comments to a command, and don't add comments in the middle of a command.

8.7.3 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.


 FROM employee

 WHERE -- employees are current

 SYSDATE BETWEEN employee_hire_date 

 AND NVL(employee_termination_date,SYSDATE);

Don't use double hyphens to place comments at the end of a SQL*Plus command. For example, the following command fails:


DESCRIBE employee --Is this a comment?

Usage: DESCRIBE [schema.]object[@db_link]

As with /* . . . */ , the double hyphen may be used to embed comments within SQL statements and PL/SQL blocks. The only difference is that double hyphen comments cannot span lines. Within SQL and PL/SQL, you may use -- to place comments at the end of a statement.

8.7.4 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:

--In this comment, &var is not a substition variable.

However, if you enter a similar comment as part of a SQL statement, SQL*Plus will see &var as a substitution variable:




FROM employee


--Now, &var is treated as a substitution variable.


WHERE employee_termination_date IS NULL;

Enter value for var:

The reason for this seemingly inconsistent behavior is that SQL*Plus doesn't parse your SQL statements; instead, SQL statements are sent to the database engine. As soon as SQL*Plus sees that you have begun to type in a SQL statement or a PL/SQL block, 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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: