PRAGMA UDF
PRAGMA UDF compiles a standalone function for efficient use from SQL, reducing the value-passing overhead.
How it works
When a stored function is called from a SQL statement, the datatypes are marshalled between the SQL and PL/SQL engines. PRAGMA UDF, placed as the first line of a standalone function body, tells the compiler this function is primarily called from SQL and to generate a form that passes values more cheaply — similar in effect to defining it with WITH FUNCTION, but reusable and stored. It can be slightly slower when the same function is called from PL/SQL, so use it for SQL-facing helpers.
CREATE OR REPLACE FUNCTION net_amount(p_gross NUMBER, p_tax_pct NUMBER) RETURN NUMBER IS PRAGMA UDF; BEGIN RETURN ROUND(p_gross / (1 + p_tax_pct / 100), 2); END; / SELECT invoice_id, net_amount(gross_amount, 8.25) AS net FROM ap_invoices_all WHERE rownum <= 20;
Related questions
Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.