oracle-databaseoracle12csubquery-factoring

oracle 12c using subquery factoring clause with plsql declaration


I'm a big fan of the subquery factoring clause.

WITH t1 as (select 1 as id from dual)
select * from t1;

and oracle 12c now includes a PL/SQL declaration section in the WITH clause

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1

but I can't seem to get them to work together is it possible?

WITH t1 as (select 1 as id from dual)
WITH  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1;

Solution

  • Please refer to the syntax:
    https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702

    enter image description here

    plsql_declarations
    enter image description here

    subquery_factoring_clause enter image description here

    As you see, the syntax is:

    WITH [ plsql_declarations ] [ subquery_factoring_clause ]
    

    This means that PL/SQL must go first, then a rest of SQL query, in this way:

    WITH 
      FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
      BEGIN
        RETURN p_id;
      END;
    t1 as (select 1 as id from dual)
    SELECT with_function(id)
    FROM   t1
    WHERE  rownum = 1;