How to I add a constant into a with block?

Question

In a with block, I can define function and query, I would like to define a constant too that I can reuse after.

the following code works.

WITH
    FUNCTION a (a IN INTEGER)
        RETURN INTEGER
    IS
    BEGIN
        RETURN a + 1;
    END;
    b(v) AS (SELECT column_value FROM sys.ODCINUMBERLIST(1,2,3)) 

SELECT a (v) FROM  b;

but this doesn't work.

WITH
    c integer:=1;
    FUNCTION a (a IN INTEGER)
        RETURN INTEGER
    IS
    BEGIN
        RETURN a + 1;
    END;
    b(v) AS (SELECT column_value FROM sys.ODCINUMBERLIST(1,2,3)) 

SELECT a (v) FROM  b;

[Error] Execution (14: 20): ORA-00942: table or view does not exist

I know that I can create another query in the with block: select 1 c from dual

But it doesn't look pretty if I have a lot of constants.

Is there a way to add constant in a with block?

Answer 1

Add another subquery factoring clause with exactly one row and define all the constants in there and then CROSS JOIN it to your other queries:

WITH FUNCTION a (a IN INTEGER)
  RETURN INTEGER
IS
BEGIN
  RETURN a + 1;
END;
constants (c1, c2, c3, c4) AS (
  SELECT 1, 2, 3, 4 FROM DUAL
),
b(v) AS (
  SELECT column_value FROM sys.ODCINUMBERLIST(1,2,3)
) 
SELECT a(v), c.*
FROM   b
       CROSS JOIN constants c;

Or, you could declare a function for each constant:

WITH FUNCTION a (a IN INTEGER)
  RETURN INTEGER
IS
BEGIN
  RETURN a + 1;
END;
FUNCTION c1 RETURN INTEGER IS BEGIN RETURN 1; END;
FUNCTION c2 RETURN INTEGER IS BEGIN RETURN 2; END;
FUNCTION c3 RETURN INTEGER IS BEGIN RETURN 3; END;
FUNCTION c4 RETURN INTEGER IS BEGIN RETURN 4; END;
b(v) AS (
  SELECT column_value FROM sys.ODCINUMBERLIST(1,2,3)
) 
SELECT a(v), c1, c2, c3, c4
FROM   b;

(However, using functions may introduce a performance overhead with repeated context-switches between SQL and PL/SQL scopes. Personally, I would use the first option.)

Which both output:

A(V)	C1	C2	C3	C4
2	1	2	3	4
3	1	2	3	4
4	1	2	3	4

db<>fiddle here