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
CodePudding user response:
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:
VALUE 1 3
db<>fiddle here