2

In Oracle 12c WITH FUNCTION was introduced. Simply:

WITH FUNCTION f_double(i IN INT) RETURN INT
AS
BEGIN
  RETURN i*2;
END;
SELECT f_double(t.c) 
FROM(SELECT 10 AS c FROM dual UNION SELECT 20 FROM dual) t;

-- output:
20
40

Now if I try to use it inside PL/SQL block:

DECLARE
  TYPE t_i IS TABLE OF INT;
   v_i t_i;
BEGIN
    WITH FUNCTION f_double(i IN INT) RETURN INT
    AS
    BEGIN
      RETURN i*2;
    END;
    SELECT f_double(t.c) 
    BULK COLLECT INTO v_i
    FROM(SELECT 10 AS c FROM dual UNION SELECT 20 FROM dual) t;

    FOR i IN v_i.FIRST..v_i.LAST LOOP
       DBMS_OUTPUT.put_line('v_i => ' ||  v_i(i));
    END LOOP;
END;

And it will end with error:

ORA-06550: line 5, column 19:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 5:
PLS-00103: Encountered the symbol "SELECT"

Of course I could use simple trick with dynamic SQL to make it work:

DECLARE
  TYPE t_i IS TABLE OF INT;
   v_i t_i;
BEGIN
    EXECUTE IMMEDIATE q'{
    WITH FUNCTION f_double(i IN INT) RETURN INT
    AS
    BEGIN
      RETURN i*2;
    END;
    SELECT f_double(t.c) 
    FROM(SELECT 10 AS c FROM dual UNION SELECT 20 FROM dual) t
    }' BULK COLLECT INTO v_i;

    FOR i IN v_i.FIRST..v_i.LAST LOOP
       DBMS_OUTPUT.put_line('v_i => ' ||  v_i(i));
    END LOOP;
END;

Output:

v_i => 20
v_i => 40

Now I wonder why the second example does not work (using common-table-expression WITH clause works as it should). Maybe I am missing something really obvious or it is limitation of WITH FUNCTION.

References to official documentation are welcomed.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Not official documentation, but here's a link to show the problem has been "documented" before. https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#plsql-support It may not be possible to find documentation for this; Oracle often does not document what DOESN'T work (if it wasn't intended to work in the first place). –  Oct 30 '17 at 16:45
  • 1
    @lad2025 .. the link in question says the feature isn't supported in PL/SQL. `There does not appear to be any PL/SQL support for this feature. Any attempt to use it results in compilation errors` – Vamsi Prabhala Oct 30 '17 at 16:46
  • @mathguy VamsiPrabhala Mystery solved :) – Lukasz Szozda Oct 30 '17 at 16:48
  • @VamsiPrabhala Pleae post it as answer so I could accept it :) – Lukasz Szozda Dec 28 '19 at 07:55

0 Answers0