What is the correct syntax to break a PL/SQL procedure call in multiple lines?

Question

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?

Narva

Answer 1

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

Larcher

This is only for SQLPLUS commands, correct? SQL and PL/SQL commands typed into SQLPLUS automatically allow continuation to a new line. – Tyeshatyg

@Shannon, I'm not sure how execute is classified. But sql/dml/ddl works with new lines (but not blank lines). – Larcher

Answer 2

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

begin
    util.verify(src_schema => '&username',
                    stab       => '&tab_name');
end;
/

Ciscaucasia

More elegant solution!! – Tiphanie

Answer 3

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.

Femininity

That was mind blowing. Especially if you try to find description of behavior in extensive Oracle documentation... – Bcd