In 12C release 1 you can define a PL/SQL function, procedure in the WITH clause of a subquery and use in ordinary SQL statements.

WITH
 PROCEDURE <NAME_PROCEDURE> 
 BEGIN
 ... 
 END;

 FUNCTION <NAME_FUNCTION>
 BEGIN
   ...
 END;
SELECT <NAME_FUNCTION>
FROM <TABLE>;
/

Test data

Let’s prepare some test data

create table test_tbl
(id number,
id1 number);

begin
  for i in 1..10
  loop
    insert into test_tbl values(1, i);
  end loop;
  commit;
end;
/

select * from test_tbl;

        ID        ID1
---------- ----------
         1          1 
         1          2 
         1          3 
         1          4 
         1          5

Examples

Simple case. I declared only one new function add_fnc

WITH
  function add_fnc(p_id number) return number
  is
  begin
    return p_id + 1; 
  end;
select add_fnc(id1) from test_tbl;
/

ADD_FNC(ID1)
------------
       2
       3
       4
       5
       6

Function WITH has got priority over functions declared on schema level

create or replace function add_fnc(p_id number) 
return number
is
begin
  return p_id - 1;
end;
/

FUNCTION ADD_FNC compiled

WITH
  function add_fnc(p_id number) return number
  is
  begin
    return p_id + 1; 
  end;
select add_fnc(id1) from test_tbl;
/  2    3    4    5    6    7    8  

ADD_FNC(ID1)
------------
       2
       3
       4
       5
       6

You can define many function procedures using WITH

WITH
  procedure add_prc(p_id number, p_out_id out number)
  is
  begin
    p_out_id := p_id + 1;
  end;

  function add_fnc(p_id number)
  return number
  is
    v_id number;
  begin
    add_prc(p_id, v_id);

    return v_id + 1; 
  end;
select add_fnc(id1) from test_tbl;
/

ADD_FNC(ID1)
------------
       3
       4
       5
       6
       7

/ and ;

To run WITH FUNCTION, PROCEDURE / is always required in the end of statement in other case sqlplus waits for more text to be entered.

WITH
  function add_fnc(p_id number) return number
  is
  begin
    return p_id + 1; 
  end;
select add_fnc(id1) from test_tbl;  2    3    4    5    6    7  
  8             <- enter nothing happens
  9             <- enter nothing happens
 10             <- enter nothing happens
 11             <- enter nothing happens
 12             <- enter nothing happens
 13  /          <- / enter finally runs

ADD_FNC(ID1)
------------
       2
       3
       4
       5
       6

PL/SQL and dynamic code

WITH function procedure can’t be used directly in PL/SQL

begin
  for i in ( with
               function add_fnc(p_id number) return number
               is
               begin
                 return p_id + 1; 
               end;
             select add_fnc(id1) ok from test_tbl )
  loop
    dbms_output.put_line(i.ok);
  end loop;
end;
/

function add_fnc(p_id number) return number
                        *
ERROR at line 3:
ORA-06550: line 3, column 25:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 33:
PLS-00103: Encountered the symbol ";" when 
expecting one of the following:
loop

dynamic code helps

set serveroutput on
declare
  v_cur sys_refcursor;
  v_id  number;
begin
  open v_cur for 
  'with
     function add_fnc(p_id number) return number
     is
     begin
       return p_id + 1; 
     end;
   select add_fnc(id1) from test_tbl';

  loop
    fetch v_cur into v_id;
    exit when v_cur%notfound;

    dbms_output.put_line(v_id);
  end loop;

  close v_cur;
end;
/

anonymous block completed
2
3
4
5
6

WITH_PLSQL hint

If WITH function procedure is not first declaration before the top level query but in subquery then your query can fail. To avoid the problem you must use hint WITH_PLSQL. NOTE – It’s not performance hint.

select * from 
( with
    function add_fnc(p_id number) return number
    is
    begin
      return p_id + 1; 
    end;
  select add_fnc(id1) from test_tbl );
/
  2    3    4    5    6    7    8    9  ( with
  *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

to fix it use WITH_PLSQL hint

select /*+ WITH_PLSQL */ * from 
( with
    function add_fnc(p_id number) return number
    is
    begin
      return p_id + 1; 
    end;
  select add_fnc(id1) from test_tbl );
/

ADD_FNC(ID1)
------------
       2
       3
       4
       5
       6

another example for UPDATE

SQL> update test_tbl
set id = (with
            function add_fnc(p_id number) return number
            is
            begin

              return p_id + 1; 
            end;
          select add_fnc(id) from dual);
/
  2    3    4    5    6    7    8    9   10  set id = (WITH
          *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

update /*+ WITH_PLSQL */ test_tbl
set id = (with
            function add_fnc(p_id number) return number
            is
            begin

              return p_id + 1; 
            end;
          select add_fnc(id) from dual);
/  

5 rows updated.

Have a fun 🙂

Tomasz