How to define a function and a query in the same with block?

March 31, 2022

I can create a subquery in a with block.

WITH b AS (SELECT 2 FROM DUAL)
SELECT *
  FROM b;

I can create a function in a with block

WITH 
FUNCTION a (a IN INTEGER)
    RETURN INTEGER
IS
BEGIN
    RETURN a + 1;
END;
SELECT a (COLUMN_VALUE) FROM sys.ODCINumberList (1);

But I haven’t succeeded to define a function and a query in the same with block.

WITH 
 b AS (SELECT 2 FROM DUAL);
FUNCTION a (a IN INTEGER)
    RETURN INTEGER
IS
BEGIN
    RETURN a + 1;
END;
SELECT a(COLUMN_VALUE) FROM sys.ODCINum

[Error] Execution (13: 8): ORA-00904: "A": invalid identifier

(A is the name of function)

I have to use "," instead of ";" and other things likewise but to no avail

I know that I can define a function directly in a pl/sql package, but the function I want to create will be used only in one query. Thefore it’s not worth doing that

>Solution :

In a WITH clause, the PL/SQL declarations need to come before the sub-query factoring clauses.

Therefore the function declaration needs to come first.

Also, a query can only return a single result set; therefore you need to combine the output using UNION or UNION ALL:

WITH FUNCTION a (a IN INTEGER)
    RETURN INTEGER
IS
BEGIN
    RETURN a + 1;
END;
b (value) AS (
 SELECT 1 FROM DUAL
)
SELECT value FROM b
UNION ALL
SELECT a(COLUMN_VALUE) FROM sys.ODCINUMBERLIST(2);

Which outputs: