I am a big fan of comments (see also CDD Manifesto a bit further down). Comments are simple ways to add information to objects/code without changing code.
Comments that work in SQL or PLSQL might behave differently in SQL*Plus. This post is to create awareness about such traps.
The basics
comments in SQL and PLSQL
Comments in SQL and PLSQL come in two flavors. The one line comment and the multi line comment.
-- this is a one line comment.
The double hyphen “–” starts the one line comment. It ends at the end of the line.
/* this is a mutli-line comment */
A slash “/” followed by an asterisks “*” starts a multi line comment. And it is ended in the opposite way.
Comments do not stack.
-- this /* will not work */
This also will not work.
/* line 1
/* line 2
/* line 3
line 4 */
line 5 */
line 6 */
The multi-line comment ends after the first time “*/” is encountered. So line 5 and 6 are not commented, instead line 5 will raise a syntax error.
In SQL a very special form of a comment is a hint. A hint tries to give the SQL optimizer additional instructions. A + indicates that this comment is a hint.
select /*+warp_speed(5) */ from big_table;
A hint could be compared to an annotation in other languages. The comment syntax is used to add some special meaning to the surrounding code. This post is not about such annotations.
comments in SQL*Plus
In general we run SQL statements, PLSQL blocks and SQL*plus commands in a SQL*Plus script. Typical examples are SELECT statements, CREATE OR REPLACE PACKAGE commands and SET commands..
The normal SQL and PLSQL comments do function in SQL*plus as well. Additionally we have a REM(ARK) command to add comments.
SQL> REM this is a SQL*plus comment
We can also use PROMPT to have a kind of echo inside a SQL*Plus script. Although it is not a real comment, it is used in a similar way.
SQL> PROMPT *** heading *** *** heading ***
Surprising differences
SQL*Plus interprets comments slightly differently than SQL or PL/SQL. Here are some examples. Try to guess what happens before reading the result. The examples do work in SQL Developer, but behave differently in SQL*Plus.
Example 1
generate some DDL commands…
select 'drop '||object_type||';'
from user_objects
--where object_type = 'VIEW';
where object_type = 'TRIGGER';
Result 1 (real object names changed)
... many similar rows ... 'DROP'||OBJECT_TYPE||''||OBJECT_NAME||';' drop SYNONYM PLSQL_PROFILER_DATA; drop SYNONYM PLSQL_PROFILER_RUNNUMBER; drop SYNONYM PLSQL_PROFILER_RUNS; drop SYNONYM PLSQL_PROFILER_UNITS; drop INDEX ABC_UK01; drop PACKAGE MYPK; drop PACKAGE BODY MYPK; drop TABLE MYTAB; drop DATABASE LINK DISTANT.WORLD.LOCAL; drop DATABASE LINK REMOTE.WORK.LOCAL; 2243 rows selected. SQL> SP2-0734: unknown command beginning "where obje…" - rest of line ignored. SQL>
2242 rows selected? Yes there were some triggers, but not that many. When SQL*plus finds a semicolon at the end of a line, it interprets this as the end of the command. Even if that semicolon is commented out. In this example the statement produced a drop command for each object in the schema. But the intention was to only drop all triggers.
Example 2
Add plsql doc information…
create or replace function convertNum(p1 in varchar2) return number
is
/***************************
@usage: converts a string into a number.
If conversion is not possible, 0 is returned.
@author: Sven
@param : p1 : input string
@return : converted number
****************************/
begin
return to_number(p1 default '0' on conversion error);
end convertNum;
/
Result 2
SQL>
create or replace function convertNum(p1 in varchar2) return number
is
/***************************
@usage: converts a string into a number.
SP2-0310: unable to open file "usage:.sql"
If conversion is not possible, 0 is returned.
@author: Sven
SP2-0310: unable to open file "author:.sql"
@param : p1 : input string
SP2-0310: unable to open file "param.sql"
@return : converted number
SP2-0310: unable to open file "return.sql"
****************************/
begin
return to_number(p1 default '0' on conversion error);
end convertNum;
9 /
Function created.
SQL>
An @ at the start of a line is always interpreted as a call to a script even if it is inside a multi line comment. This lowers the usefulness of the “generate DB doc” functionality in SQL Developer a lot. Putting additional blanks before the @ will not change this behaviour.
Example 3
comment out optional params…
SQL> set serveroutput on -- size unlimited
Result 3
SP2-0158: unknown SET option "--"
This means the SET option is ignored. There is an error message, but it is a SQL*Plus (SP2) error message. This would not stop a script that has when WHENEVER SQLERROR EXIT set at the beginning.
Example 4
comment a statement…
select sysdate from dual; -- print current day
Result 4
SQL> select sysdate from dual; -- print current day 2Do you notice the 2 at the beginning? The statement is not terminated. The buffer waits for the next line and the next command probably finishes the buffer and runs into a syntax error.
Warning
Personally I think that those examples are bugs. But it is documented behaviour, so Oracle probably disagrees. There is an extra section in the SQL*plus documentation, dedicated to comments in scripts where these and more problems are described.
Remember
If you intend to run some plsql code or a SQL statement from a SQL plus script, then
- do not have semicolons in a comment, especially not at the end of the line
- do not use @ in the comment, especially not at the beginning of a line
- do not use — inside
SELECTs (hints seem to work) - avoid to use — or /* */ to write sql*plus comments, instead use
REMARKorPROMPT
More side effects
Comments near the beginning of a command can confuse sql*plus.
SQL> create /* testproc */ procedure test 2 is 3 begin null; Warning: Procedure created with compilation errors. SQL> end; SP2-0042: unknown command "end" - rest of line ignored. SQL> /
Blank lines in comments can break the code. This can be suppressed by using SET SQLBLANKLINES ON.
SQL> SET SQLBLANKLINES ON; SQL> select sysdate from dual 2 /* this is a multi 3 line 4 5 comment */ 6 ; SYSDATE 25-AUG-20 SQL> SET SQLBLANKLINES OFF; SQL> select sysdate from dual 2 /* this is a multi 3 line 4 SQL> comment */ 2 ; comment */ * ERROR at line 1: ORA-00969: missing ON keyword SQL>
An ampersand “&” in comments is tried to be substituted by a value of a variable. This can be suppressed using SET DEFINE OFF. Not a big deal unless we do not want to suppress it.
SQL> select sysdate from dual 2 -- current day at marks&spencers 3 ; Enter value for spencers: old 2: -- current day at marks&spencers new 2: -- current day at marks SYSDATE 25-AUG-20
A comment inside a PROMPT is not a comment, but will be printed to the screen.
SQL> prompt /* comment or not comment that is the question */ /* comment or not comment that is the question */
If you want a “;” to be printed to a spool file using PROMPT, use two “;;”.
SQL> prompt test;; test;
Additional thoughts
Good developers can get a solid overview over unknown code, just by reading the comments.
Set your syntax highlighting in such a way that makes it easy to read comments. Unfortunately Oracle SQL Developer some time ago switched from green comments to grey comments. My recommendation is to change this setting.
Comment Driven Development (CDD) Manifesto
Comments are good!
Every code needs comments!
Comment first, code later!
Comments state code intention.
Comments describe why a technical decision was made
or not made.
Comments can give examples.
The world needs more comments!
svenweller 