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

It's helpful to include the error you get from the first version. But select into isn't quite the same as select. It's possible the with function ... syntax hasn't made it into the PL/SQL version yet; although as the syntax diagrams don't even show the CTE syntax it's hard to tell. Are you using 12cR1 or 12cR2 - it might have changed between releases? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2017-01-09T16:17:40

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.

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

1 Answers