CODEWITHSUNDEEP
oracle-databaseoracle12csubquery-factoring
Bryan Dellinger
Bryan Dellinger

Reputation: 5304

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;

Upvotes: 1

Views: 1015

Answers (1)

krokodilko
krokodilko

Reputation: 36127

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;

Upvotes: 3

Related Questions