In SqlDeveloper I’m trying to execute the following multi-line SQL EXECUTE with comments,
execute MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/,
null/*Param4*/, etc.
On the first newline it flags the error
execute MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/, ; END;
ORA-06550: line 1, column 131:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted st
and on every subsequent newline after that
Error report -
Unknown Command
There is no ; anywhere on these newlines. How I do run a multi-line formatted SQL without it complaining about newlines or comments?
>Solution :
It isn’t the comments. If you look at your actual error again you won’t be seeing what you showed:
execute MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/, ; END;
ORA-06550: line 1, column 131:
...
It will actually show:
BEGIN MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/, ; END;
ORA-06550: line 1, column 131:
...
This is because execute is just a convenience wrapper for an anonymous block, and it only looks at the current line of your script. So it effectively runs:
BEGIN
MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/, ;
END;
/
It is adding the BEGIN, the trailing semicolon after the first line of your statement, and END;. And that is what is shown in the error message. The rest of your multiline statement is treated as independent commands, which is why they are reported as ‘unknown’.
You can either manually convert your execute to an explicit BEGIN/END; block, or force your whole call onto a single line, or you can use the line continuation character:
You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing Return. If you wish, you can type a space before typing the hyphen.
So you can add a hyphen at the end of each line, which will make SQL Developer (the docs are for SQL*Plus, but still apply) include all of those lines when it converts the execute to an anonymous block:
execute MYPKG.MYSP (null/*Param1*/, null/*Param2*/, 123/*Param3*/, -
null/*Param4*/, etc.
Personally I only use exec for quick ad hoc things, and think it’s clearer and simpler to use normal, full, anonymous blocks most of the time. It also makes it easier to run your code in another client if you ever need to.