Re: there is an error in comments
Date: Fri, 25 Dec 2009 02:22:22 -0500
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:
> 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);
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
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:
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