Skip to main content
← PL/SQL Topics

Advanced & Performance

Explain PRAGMA UDF for functions called from SQL

PRAGMA UDF

ExplanationOracle Fusion / EBS · Technical · PL/SQLHigh confidenceGenerated

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.

How it works
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

GroundingGenerated

Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.

Have a follow-up, or a different question?

Continue in Ask Oracle AI