New "WITH FUNCTION" clause not supported (12c)

[Philippe Malera]

Impossible to execute this code in a test window :

BEGIN

  WITH
    PROCEDURE with_procedure(p_id IN VARCHAR2) IS
    BEGIN
      dbms_output.put_line('p_id=' || p_id);
    END;

    FUNCTION with_function(p_id IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
      with_procedure(p_id);
      RETURN p_id;
    END;

  SELECT with_function(dummy)
    INTO :OUT
    FROM dual;

END;

I get this error :

ORA-06550: line 4, column 15:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 5:
PLS-00103: Encountered the symbol "FUNCTION"
ORA-06550: line 9, column 36:
PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following:

   (
ORA-06550: line 15, column 3:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   begin function pragma procedure
The symbol "begin" was substituted for "SELECT" to continue.

And also impossible to beautify this code.
Is this feature supported in Version 12.0.5.1828 ?

 

[Marco Kalter]

This seems to be an Oracle Server PL/SQL restriction. It does not work in SQL*Plus either.

 

[Nicolas D.]

SELECT WITH function or Procedure is not supported within a PL/SQL block, but only in pure SQL.

That's indeed a PL/SQL restriction.

 

[Philippe Malera]

You're right, sorry for not having seen that.
It is the first time I see an SQL query not supported by the PLSQL engine.