# Deep Dive — PRAGMA UDF

> Tells the compiler "this function will be called from SQL" — and the SQL-to-PL/SQL switch gets several times cheaper.

Used in: [Chapter 3 — Functions Inside SQL](../course-plan.md) · Appendix entry: [PRAGMA UDF](../appendix.md#pragma-udf) · Related: [DETERMINISTIC](deterministic.md), [WITH FUNCTION](with-function.md), [RESULT_CACHE](result_cache.md)

---

## 1. What It Is and When

12c+. A function compiled with `PRAGMA UDF` uses a calling convention optimized for invocation from SQL: less state to save and restore on each context switch. Typical gain: 2–4x on the call overhead of a trivial function. It does nothing about the *work* inside the function.

Use it on any function that is called from SQL on many rows and cannot be replaced by inline SQL. It is the cheapest fix available — one line, no logic change — and the least powerful: `CASE`/join in SQL is still 10–100x faster than the best-compiled call.

## 2. Syntax

```sql
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  [DETERMINISTIC]
IS
  PRAGMA UDF;          -- in the declaration section, before any declarations or BEGIN
  ...
BEGIN
  ...
END;
```

- Works on standalone functions and on functions in package bodies (put the pragma in the body's function, not the spec).
- Combines freely with `DETERMINISTIC`, `RESULT_CACHE`, `PARALLEL_ENABLE`.
- A `WITH FUNCTION` in a query ([WITH FUNCTION](with-function.md)) is compiled as if it had `PRAGMA UDF`.

## 3. Examples on the Course Schema

### 3a. Before and after

```sql
-- plain
CREATE OR REPLACE FUNCTION vat_plain (p NUMBER) RETURN NUMBER IS
BEGIN RETURN ROUND(p * 0.17, 2); END;
/
-- UDF
CREATE OR REPLACE FUNCTION vat_udf (p NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN RETURN ROUND(p * 0.17, 2); END;
/
SET TIMING ON
SELECT SUM(vat_plain(amount)) FROM orders WHERE order_date >= DATE '2026-01-01';   -- ~40M rows
SELECT SUM(vat_udf(amount))   FROM orders WHERE order_date >= DATE '2026-01-01';
SELECT SUM(ROUND(amount * 0.17, 2)) FROM orders WHERE order_date >= DATE '2026-01-01';
```

Illustrative results on 40M rows:

| Variant | Time | Relative |
| --- | --- | --- |
| `vat_plain` | 68 s | 1x |
| `vat_udf` | 21 s | ~3x faster |
| inline `ROUND(amount * 0.17, 2)` | 4 s | ~17x faster |

The pragma removed two thirds of the overhead. Inline SQL removed all of it.

### 3b. In a package body

```sql
CREATE OR REPLACE PACKAGE pricing_fn AS
  FUNCTION vat (p NUMBER) RETURN NUMBER DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY pricing_fn AS
  FUNCTION vat (p NUMBER) RETURN NUMBER DETERMINISTIC IS
    PRAGMA UDF;
  BEGIN
    RETURN ROUND(p * 0.17, 2);
  END;
END;
/
```

Each function in the body carries its own pragma; there is no package-level switch.

### 3c. Where it does not help — SQL inside the function

```sql
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2 IS
  PRAGMA UDF;
  v customers.tier%TYPE;
BEGIN
  SELECT tier INTO v FROM customers WHERE customer_id = p_id;   -- this is 95% of the cost
  RETURN v;
END;
/
```

The pragma trims the call; the `SELECT` per row remains. On 40M rows you save a few seconds out of many minutes. This function should be a join.

### 3d. Called from PL/SQL instead of SQL

```sql
BEGIN
  FOR i IN 1 .. 10000000 LOOP
    x := vat_udf(i);          -- PL/SQL → PL/SQL: no benefit, possibly a little slower
  END LOOP;
END;
```

`PRAGMA UDF` optimizes the SQL entry path. From PL/SQL the call is already cheap; the UDF convention can add a small cost. If a function is heavily called from both sides, it usually does not matter — measure before worrying.

## 4. What Breaks at Scale

- The switch still happens per row. 500M rows × a "cheap" switch is still minutes of pure overhead. The pragma moves the function from "unacceptable" to "tolerable", not to "free".
- Functions that allocate (build strings, collections) per call are dominated by that work — the pragma is noise.
- No effect on `RESULT_CACHE` hits (the body is skipped anyway) except making the remaining call cheaper.

## 5. Pitfalls

1. Putting the pragma in the package spec — it belongs in the body's function.
2. Expecting a change in results — none; it is purely a compilation mode. If results differ, something else changed.
3. Pre-12c: `PLS-00103` — the pragma does not exist; use `DETERMINISTIC` and inline SQL.
4. Forgetting that the function is still a black box to the optimizer: no index use, no cardinality estimate, no partition pruning through it. A predicate `WHERE vat_udf(amount) > 100` is a full scan with a filter, pragma or not.
5. Adding the pragma and stopping there. The order of fixes is: inline SQL → join → `DETERMINISTIC` + `PRAGMA UDF` → `RESULT_CACHE` for lookups.

## 6. Related

- [DETERMINISTIC](deterministic.md) — fewer calls.
- [WITH FUNCTION](with-function.md) — the same optimization for an inline, query-local function.
- [RESULT_CACHE](result_cache.md) — skipping the body for repeated arguments.