Include multiple inline functions and multiple CTEs in a WITH clause in a single query

Oracle 18c:

What is the syntax for including multiple inline functions and multiple CTEs in a WITH clause in a single query?

Function #1:

function fucntion1(num in number) return number
is
begin
    return num + 1;
end;

Function #2:

function fucntion2(num in number) return number
is
begin
    return num + 2;
end;

CTE #1:

cte as (select 1 from dual)

CTE #2:

cte2 as (select 2 from dual)

As ever for a "what is the syntax for ..." questions, you should refer to the official documentation.

The SELECT syntax is

query_block ::=

with_clause ::=

plsql_declarations ::=

subquery_factoring_clause ::=

The PL/SQL function syntax

function_definition ::=

body ::=

Therefore:

• A PL/SQL function's body must be terminated with a ;.

  (Note: this is a PLSQL statement terminator and not a separator in the WITH clause between PL/SQL function declarations as there is no separator character following PL/SQL function declarations.)

• There is a , character between successive sub-query factoring clauses.

• The SELECT statement does not need a ; or / statement terminator but it may be allowed/required/forbidden by the client application you are using to denote the termination of the statement.

  For example:

  • You can only pass a single statement via an OJDBC statement and, for this client, the statement terminator is forbidden.
  • In SQL Developer, when you are running a single statement then the trailing statement terminator is allowed but is optional.
  • In SQL Developer, when you are running a script then statement terminators are required between statements.