Is it possible to have both a subquery and a function in a WITH clause? In Oracle 12c, we can include subqueries in the WITH clause:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;


We can also include functions 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


Is it possible to include both a subquery and a function in the WITH clause of a single query?



This is just a hypothetical question that came to mind. I don't have any real-world examples, but would be interested to see how this might work. I'm not really experienced enough to come up with an example that's useful.
Here is an example:

with 
  function f1 return number is begin return 1; end;
  t1 as (select * from dual connect by level &lt;= 3)
  select f1 from t1;
  /

        F1
----------
         1
         1
         1