Search code examples
sqloraclewith-statementoracle18cinline-functions

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)
Solution

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

    The SELECT syntax is

    query_block ::=

    query_block syntax

    with_clause ::=

    with_clause syntax

    plsql_declarations ::=

    plsql_declarations

    subquery_factoring_clause ::=

    subquery_factoring_clause syntax

    The PL/SQL function syntax

    function_definition ::=

    function_definition syntax

    body ::=

    body syntax

    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.