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
Appendix entry: PRAGMA UDF
Related: DETERMINISTIC WITH FUNCTION RESULT_CACHE

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

Putting the pragma in the package spec — it belongs in the body's function.
Expecting a change in results — none; it is purely a compilation mode. If results differ, something else changed.
Pre-12c: PLS-00103 — the pragma does not exist; use DETERMINISTIC and inline SQL.
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.
Adding the pragma and stopping there. The order of fixes is: inline SQL → join → DETERMINISTIC + PRAGMA UDF → RESULT_CACHE for lookups.