Deep Dive — WITH FUNCTION

A PL/SQL function that lives inside one query: no schema object, no grants, no invalidation — and compiled the fast way.

Used in
Chapter 3 — Functions Inside SQL
Appendix entry
WITH FUNCTION
Related
PRAGMA UDF DETERMINISTIC OPEN FOR

12c+. The WITH clause can declare PL/SQL functions (and procedures) that exist only for the duration of that statement. They are compiled with the PRAGMA UDF calling convention automatically, so they are as cheap to call as a UDF-compiled schema function.

Use it for: ad-hoc analysis and one-off reports that need a bit of procedural logic; scripts that must not leave objects behind; testing a function's logic before creating it. Do not use it for shared business logic — that belongs in a package.

SQL 
WITH
  FUNCTION <name> (<params>) RETURN <type> [DETERMINISTIC] IS
    ...
  BEGIN
    ...
  END;
  [PROCEDURE <name> (...) IS ... END;]
  [<cte_name> AS (SELECT ...)]          -- ordinary subquery factoring may follow
SELECT ... FROM ... ;
/
  • Only in a top-level SELECT. In INSERT ... SELECT, CREATE TABLE AS SELECT, or a subquery, the outer statement needs the /*+ WITH_PLSQL */ hint.
  • In SQL*Plus / SQLcl the statement must be terminated with / on its own line (the ; inside the function body would otherwise end the statement).
  • Not allowed in static SQL inside PL/SQL (PLS-00103) — from PL/SQL, run it through OPEN FOR or EXECUTE IMMEDIATE.
  • Functions declared this way may not be referenced from another statement or from a view/MV definition.

SQL 
WITH
  FUNCTION vat (p NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN ROUND(p * 0.17, 2);
  END;
SELECT order_id, amount, vat(amount) AS vat_amount
FROM   orders
WHERE  order_date >= DATE '2026-01-01' AND customer_id = :id;
/

Status codes stored as 'NEW:2026-01-15:web' in a legacy column:

SQL 
WITH
  FUNCTION part (p_str VARCHAR2, p_n PLS_INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
  BEGIN
    RETURN REGEXP_SUBSTR(p_str, '[^:]+', 1, p_n);
  END;
SELECT order_id,
       part(legacy_status, 1) AS status,
       TO_DATE(part(legacy_status, 2), 'YYYY-MM-DD') AS status_date,
       part(legacy_status, 3) AS channel
FROM   orders_legacy
WHERE  ROWNUM <= 1000;
/

SQL 
INSERT /*+ WITH_PLSQL */ INTO order_vat (order_id, vat_amount)
WITH
  FUNCTION vat (p NUMBER) RETURN NUMBER IS
  BEGIN RETURN ROUND(p * 0.17, 2); END;
SELECT order_id, vat(amount) FROM orders WHERE order_date >= DATE '2026-01-01';
/

Without the hint: ORA-32034: unsupported use of WITH clause.

SQL 
DECLARE
  c SYS_REFCURSOR;
  TYPE t_amts IS TABLE OF NUMBER;
  l_vat t_amts;
BEGIN
  OPEN c FOR q'[
    WITH FUNCTION vat (p NUMBER) RETURN NUMBER IS BEGIN RETURN ROUND(p * 0.17, 2); END;
    SELECT vat(amount) FROM orders WHERE customer_id = :1
  ]' USING :id;
  FETCH c BULK COLLECT INTO l_vat LIMIT 1000;
  CLOSE c;
END;
/

If you find yourself doing this, the function should probably be a real object with PRAGMA UDF — see PRAGMA UDF.

SQL 
WITH
  PROCEDURE note (p VARCHAR2) IS
  BEGIN DBMS_OUTPUT.PUT_LINE(p); END;
  FUNCTION bucket (p NUMBER) RETURN VARCHAR2 IS
  BEGIN
    note('bucket called');                       -- side effects run per row: demo only
    RETURN CASE WHEN p < 100 THEN 'S' WHEN p < 1000 THEN 'M' ELSE 'L' END;
  END;
  recent AS (SELECT amount FROM orders WHERE order_date >= DATE '2026-01-01')
SELECT bucket(amount) AS b, COUNT(*)
FROM   recent
GROUP  BY bucket(amount);
/

  • Same per-row call cost as a PRAGMA UDF function: 40M rows = 40M calls. Fine for a report; not for the nightly batch's hot path — inline SQL there.
  • The function is recompiled every time the statement is hard-parsed. Negligible for a report; a reason not to use it in high-frequency OLTP statements with varying literals.
  • No RESULT_CACHE on inline functions.

  1. Missing / terminator in SQL*Plus — the client ends the statement at the first ; inside the function body: ORA-00933/ORA-06550.
  2. ORA-32034 — used in a subquery or DML without /*+ WITH_PLSQL */.
  3. PLS-00103 — used in static SQL inside PL/SQL. Dynamic SQL only.
  4. Some clients/drivers cannot parse it (older JDBC statement parsers choke on the ;). Test in the target client.
  5. Business logic copied into five reports as five WITH FUNCTIONs — the moment the rate changes, five places to fix. Shared logic goes in a package.
  6. Cannot be used to create a view (ORA-32034) — a view needs a schema-level function.