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 :
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.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
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 (Definitive Guides)
Authors: Jonathan Gennick
Similar book on Amazon