Unable to put WITH FUNCTION clause in BEGIN / END block

Question

Why the code below does not 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;

causing 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
06550. 00000 -  "line %s, column %s:\n%s"
Cause:    Usually a PL/SQL compilation error.
Action:*

then

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 does not seem to be supported in PL / SQL yet. Presumably, it will be added in a future version.

In the meantime, this is unpleasant, but you can use dynamic SQL, which continues to run your working statement in the context of SQL, where this 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

select into , with PL/SQL , . PL/SQL. Oracle Live SQL, 12.2.0.1, 12cR2.