Unable to put a WITH FUNCTION clause in a BEGIN/END block

Question

Why doesn't the below code compile:

DECLARE
c number;
BEGIN
WITH
FUNCTION calculate(i IN NUMBER) RETURN NUMBER
AS
r number;
BEGIN
  r := i*i;
  RETURN r;
END;
select calculate(1) INTO c from dual;
END;

giving the following error:

Error report -
*ORA-06550: line 5, column 10:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

whereas:

WITH
FUNCTION calculate(i IN NUMBER) RETURN NUMBER
AS
r number;
BEGIN
  r := i*i;
  RETURN r;
END;
select calculate(1) from dual;

compiles?

Oracle version information

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production

Answer 1

This construct just doesn't seem to be supported in PL/SQL yet. Presumably it will be added in a future release.

In the meantime it's unpleasant but you could use dynamic SQL, which continues to run your working statement in a SQL context where it is understood:

DECLARE
  c number;
BEGIN
  EXECUTE IMMEDIATE '
WITH
FUNCTION calculate(i IN NUMBER) RETURN NUMBER
AS
  r number;
BEGIN
  r := i*i;
  RETURN r;
END;
select calculate(2) from dual'
  INTO c;
  DBMS_OUTPUT.PUT_LINE(c);
END;
/

4

The documentation for select into doesn't show that the with clause is supported in PL/SQL even for subquery blocks, but that does work even in earlier releases. So it doesn't refer to the new PL/SQL declaration syntax either. Based on experiments in Oracle's Live SQL platform, which is running 12.2.0.1, it isn't supported in 12cR2 either.