# 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](../course-plan.md) · Appendix entry: [WITH FUNCTION](../appendix.md#with-function) · Related: [PRAGMA UDF](pragma-udf.md), [DETERMINISTIC](deterministic.md), [OPEN FOR](open-for.md)

---

## 1. What It Is and When

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.

## 2. Syntax

```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.

## 3. Examples on the Course Schema

### 3a. One-off calculation in a report

```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;
/
```

### 3b. Procedural parsing the SQL way would not read well

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;
/
```

### 3c. Inside INSERT ... SELECT — the WITH_PLSQL hint

```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`.

### 3d. From PL/SQL — dynamic SQL only

```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](pragma-udf.md).

### 3e. With a procedure and a CTE

```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);
/
```

## 4. What Breaks at Scale

- 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.

## 5. Pitfalls

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 FUNCTION`s — 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.

## 6. Related

- [PRAGMA UDF](pragma-udf.md) — the same calling convention for a permanent function.
- [DETERMINISTIC](deterministic.md) — fewer calls; combine when applicable.
- [OPEN FOR](open-for.md) — running it from PL/SQL.