Re: there is an error in comments

From: George Rypysc <>
Date: Fri, 25 Dec 2009 02:22:22 -0500
Message-ID: <op.u5hf3kpucl5azy_at_t2042>

On Wed, 23 Dec 2009 18:58:07 -0500, hpuxrac <> wrote:

> On Dec 23, 7:35 am, Daneel Yaitskov <> wrote:
> snip
> My guess is that you do not use PLSQL much ... as long as we are
> offering wild guesses.
> The line in question will work quite well as a comment in a real
> procedure. When feeding it in a line at a time thru sqlplus well not
> so much.
> Perhaps one might actually test something out before offering broad
> based opinions?
> Just a thought ...

Bingo hpuxrac - the issue is with SQL*Plus, not the PL/SQL language: "...SQL*Plus expects no text after a statement terminator and is unable to process the command." -- from SQL*Plus User's Guide and Reference, Release 10.2, Section: 'Notes on Placing Comments'

Daneel could also try moving the semi-colon to the end of the line to work around this quirk in SQL*Plus:

call echo('dddddd') -- comment;

...that's a bit odd, and inconsistent with PL/SQL comments, but runs fine in SQL*Plus. I believe it works because scripts must always end with a ';' or '/' (followed by a new line) if you run them in SQL*Plus or else they don't "finish". I bet Daneel ran the script, saw that a 'SQL>' prompt appeared with no error, and so assumed the entire script finished running. It didn't, because typing a '/' or ';' at the 'SQL>' prompt after executing the script reveals the error:

SQL> get script.sql

   1 set serveroutput on;
   2 create or replace procedure echo(msg varchar ) as    3 begin
   4 dbms_output.put_line (msg);
   5 end;
   6 /
   7 call echo('dddddd'); -- comment
   8* -- the end of script
   9 . <=== Manually typed a '.' here to get back to SQL> prompt. SQL> _at_script.sql

Procedure created.

SQL> <=== Here it appears the entire script, including the 'call' ran. It didn't. (If your 'script.sql' file ends with only one blank line you may get a ' 3' instead of a 'SQL>' prompt). Typing 'show errors' here will return 'No errors.' because the 'call' line has not run yet! You need to type ';' or '/' so SQL*Plus passes the 'call' line to the database:

SQL> /
call echo('dddddd'); -- comment


ERROR at line 1:
ORA-00911: invalid character

...the error is revealed!
If you replace 'call' with 'exec', you get the error ddf mentioned above, instead of a silent, incomplete execution of the script. Seems like 'exec' is the better choice.

George Received on Fri Dec 25 2009 - 01:22:22 CST

Original text of this message