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:

SELECT * 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:

SQL> 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. SELECT * 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:

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

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