Re: there is an error in comments
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 <johnbhurley_at_sbcglobal.net> wrote:
> On Dec 23, 7:35 am, Daneel Yaitskov <rtfm.rtfm.r..._at_gmail.com> 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' http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref997
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