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.