How to Use DETERMINISTIC Functions in PL/SQL

A function is deterministic when it always returns the same result for the same arguments: converting kilometers to miles is, looking up today's exchange rate is not. Declaring a function DETERMINISTIC tells Oracle so. Oracle may then avoid repeated calls with the same arguments, and it is required before the function can be used in a function-based index or a virtual column.

Code for This Guide

The main examples are in the examples/subprograms folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] function name (params) return type
  deterministic
is
begin
  ...
end;

Oracle does not check the claim. A function declared DETERMINISTIC that reads tables or SYSDATE can return stale results from an index or a cache, so declare it only for pure calculations.

A Deterministic Function in a Query

KM_TO_MILES is a pure calculation, declared DETERMINISTIC. The example also creates AIRPORT_CITY, a result-cache function covered separately.

Example:

create or replace function km_to_miles (p_km number) return number
  deterministic
is
begin
  return round(p_km / 1.609344);
end;
/
create or replace function airport_city (p_code char) return varchar2
  result_cache
is
  v_city airports.city%type;
begin
  select city into v_city from airports where airport_code = p_code;
  return v_city;
end;
/
select origin, airport_city(origin) as city, distance_km, km_to_miles(distance_km) as miles
from   routes
where  route_id <= 3;

Output:

Function KM_TO_MILES compiled

Function AIRPORT_CITY compiled

ORIGIN    CITY         DISTANCE_KM    MILES
_________ _________ ______________ ________
DXB       Dubai               5497     3416
LHR       London              5497     3416
DXB       Dubai               5239     3255

Required for Function-Based Indexes

An index on a function's result needs the function to be declared DETERMINISTIC; otherwise the index is refused.

Example:

create or replace function km_to_miles2 (p_km number) return number deterministic is
begin
  return round(p_km / 1.609344);
end;
/
create or replace function km_to_miles_nd (p_km number) return number is
begin
  return round(p_km / 1.609344);
end;
/
-- a function-based index needs a DETERMINISTIC function
create index routes_miles_ix on routes (km_to_miles2(distance_km));
create index routes_miles_nd_ix on routes (km_to_miles_nd(distance_km));

Output:

Function KM_TO_MILES2 compiled

Function KM_TO_MILES_ND compiled

Index ROUTES_MILES_IX created.

Error starting at line : 13
In command -
create index routes_miles_nd_ix on routes (km_to_miles_nd(distance_km))
Error report -
ORA-30553: The function is not deterministic.

The index on the deterministic version is created; the one on the identical but undeclared function fails with ORA-30553. The example drops its functions and index afterward.

Things to Know

• Within one query, Oracle may reuse a deterministic function's result for repeated arguments, but it does not guarantee it; use RESULT_CACHE to cache across calls and sessions.
• Virtual columns that call PL/SQL functions also require DETERMINISTIC.
• If the function's logic changes, rebuild indexes that use it, since stored values were computed with the old logic.

Related Guides

• How to Create Function-Based Indexes in Oracle
• How to Create Virtual Columns in Oracle

Conclusion

DETERMINISTIC declares that a function's result depends only on its arguments. Use it for pure calculations, where it lets Oracle skip repeated calls and allows the function in function-based indexes and virtual columns, and never for functions that read changing data.