5

I am calling a PL/SQL procedure like this:

execute util.verify(src_schema => '&username',
                    stab       => '&tab_name');

and I get these errors:

SQL> execute util.verify(src_schema => '&username',
BEGIN util.verify(src_schema => 'u1',; END;

                                     *
ERROR at line 1:
ORA-06550: line 1, column 57: 
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 string literal with character set specification>
<an alternatively


SQL>                   stab       => '&tab_name',
SP2-0734: unknown command beginning "stab      ..." - rest of line ignored.

Looks like I cannot just break the call in between at a ,. How can I write this call in multiple lines?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Moeb
  • 10,527
  • 31
  • 84
  • 110

3 Answers3

13

In SQLPlus, you put a dash at the end of lines that continues on the next line.

execute util.verify(src_schema => '&username', -
                    stab       => '&tab_name');

Update: Added link to documentation

EXECUTE, SQL*Plus® User's Guide and Reference

Ronnis
  • 12,593
  • 2
  • 32
  • 52
  • This is only for SQL*PLUS commands, correct? SQL and PL/SQL commands typed into SQL*PLUS automatically allow continuation to a new line. – Shannon Severance Dec 27 '10 at 19:12
  • @Shannon, I'm not sure how execute is classified. But sql/dml/ddl works with new lines (but not blank lines). – Ronnis Dec 27 '10 at 20:04
8

There is an alternative way, some thing like the below:

begin
    util.verify(src_schema => '&username',
                    stab       => '&tab_name');
end;
/
bernd_k
  • 11,558
  • 7
  • 45
  • 64
6

execute xxxx; (or exec xxxx;) is a short-cut for writing begin xxxx; end;

It only works for one liners. So if you have multiple lines, you need to explicitly use begin and end.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • That was mind blowing. Especially if you try to find description of behavior in extensive Oracle documentation... – gavenkoa Oct 24 '18 at 18:26