How can a function within a with block use a table defined in this same with block

Question

WITH
    FUNCTION f (a in integer)
        RETURN INTEGER
    IS
        ret integer;
    BEGIN
        SELECT COUNT (*) into ret
        FROM B;
        RETURN ret +a;
    END;
B(b1) as (select 1 from dual),
select f(3) from dual;

Oracle say :

[Error] Execution (6: 1): ORA-06552: PL/SQL: ORA-00942: table or view does not exist

dbfiddle (https://dbfiddle.uk/?rdbms=oracle_21&fiddle=ff90cec6ed25724bad9c215f0173607a)say :

ORA-00903: invalid table name

I think that Oracle doesn't know the table B because, she is defined after f is defined. But I can't change the order of what is defined in a with block. The functions must be defined before the table.

Does somebody has a solution to use in a function a table which is defined in the same with block?

I can't declare the B table inside the function, because I intend to create others function that use the table B. I don't want to defined B more than 2 times.

Answer 1

Declare B inside the function:

WITH FUNCTION f (a in integer)
  RETURN INTEGER
IS
  ret integer;
BEGIN
  WITH B(b1) AS (
    SELECT 1 FROM DUAL
  )
  SELECT COUNT (*) into ret
  FROM B;
  RETURN ret +a;
END;
SELECT f(3)
FROM   DUAL;

Or, do the counting outside the function:

WITH FUNCTION f (
  a   IN PLS_INTEGER,
  cnt IN PLS_INTEGER
)
  RETURN INTEGER
IS
BEGIN
  RETURN cnt +a;
END;
B (b1) AS (
  SELECT 1 FROM DUAL
)
SELECT f(3, (SELECT COUNT(*) FROM b)) AS value
FROM   DUAL;

Which both output:

VALUE
4

---

However

If you want to COUNT inside the function and reference a subquery factoring clause that is defined later then you are out of luck as you need to have the table defined before the function.

The syntax diagram states:

select::=

subquery::=

query_block::=

with_clause::=

subquery_factoring_clause::=

Although there is a clear chain in the syntax diagram SELECT, SUBQUERY, QUERY_BLOCK, WITH_CLAUSE, SUBQUERY_FACTORING_CLAUSE and back to SUBQUERY if, in practice you try to do something like nesting WITH clauses:

WITH B (b1) AS (
  SELECT 1 FROM DUAL
),
C (value) AS (
  WITH FUNCTION f (a in integer)
    RETURN INTEGER
  IS
    ret integer;
  BEGIN
    SELECT COUNT (*) into ret
    FROM B;

    RETURN ret +a;
  END f;
  SELECT f(3)
  FROM   DUAL 
)
SELECT value
FROM   c;

You will get the error:

ORA-32034: unsupported use of WITH clause

db<>fiddle here