Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Tuesday, October 09, 2007

Comments in SQL*Plus

This Warning from Eddie Awad remind me of problem with comments I've had some time ago. I've prepared an script that was intended to run in sqlplus. Then I've extensively tested it, and as a last step, I've commented it. And ... did not test again. When this script was run on production caused errors. The problem was in the way that sqlplus interpret comments.
Example:
SQL> select * from dual;

D
-
X
runs fine, while this:
SQL> select * from dual; -- some comments
stops at second line waiting for input:
SQL> select * from dual; -- some comment
  2 
It was really unpleasant experience.

Cheers, Paweł

8 comments:

Unknown said...

In case you didn't know, the issue is the first - is treated as a continuation. So, plus thinks there's more coming on the next line.

Paweł Barut said...

Yes, I know this.
But it was a big surprise that adding comment at end of line caused this behavior.

Eddie Awad said...

The same thing happens with the slash and asterisk style comment:

SQL> select * from dual; /* some comment */
2

Anonymous said...

Hi,

This way it's not problematic:

SQL> select * from dual -- check this
2 /

IR

Matthias R. said...

The comment must be on a line for itself or it must be a part of the command, so ending before the ';'.

Works:
select * from dual --cccc;
select * from dual -- cccc;
select * from dual /*cccc*/;
select * from dual /* cccc*/;
--cccc
-- cccc
/* cccc*/

Works also on multipe lines:
select --cccc
* -- cccc
from /*cccc*/
dual /* cccc*/;

Does not work:
select * from dual; --cccc
select * from dual; -- cccc
select * from dual; /*cccc*/
select * from dual; /* cccc*/
/*cccc*/

Paweł Barut said...

Hi Matthias,

Yes, it works, however it is not consistant with PL/SQL:

SQL> var v char
SQL> begin
2 select * into :v from dual --ccc;
3 end;
4 /
end;
*
ERROR at line 3:
ORA-06550: line 3, column 4:
PLS-00103: Encountered the symbol "end-of-file"

While this works:
SQL> var v char
SQL> begin
2 select * into :v from dual; --ccc
3 end;
4 /

PL/SQL procedure successfully completed.

/Paweł

Inventory Management Software said...

Thanks for sharing your post and it was superb .I would like to hear more from you in future too.

Anonymous said...

2.Do not put comments after statement terminators (period, semicolon or slash). For example, if you enter:

SELECT 'Y' FROM DUAL; -- TESTING
You get the following error:

SELECT 'Y' FROM DUAL; -- TESTING
*
ERROR at line 1:
ORA-00911: invalid character

 

Copyright © Paweł Barut
Printing from DOS to USB Printer