Lookup functions are called over and over with the same arguments: the city of an airport code, the name of a currency, a configuration value. A RESULT_CACHE function keeps its results in the shared result cache, for all sessions, and returns them without running again, until the tables it reads change.
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 result_cache is begin ... end;
Oracle records which tables the function reads and invalidates the cached results automatically when any of them changes.
A Cached Lookup
AIRPORT_CITY reads the city of an airport code once per code; later calls with the same code come from the cache. The example also creates KM_TO_MILES, a deterministic function.
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
See the Cache
V$RESULT_CACHE_OBJECTS lists the cached results and their dependencies.
Example:
create or replace function airport_city2 (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 airport_city2('DXB') as dxb, airport_city2('SIN') as sin, airport_city2('DXB') as dxb_again;
select type, status, count(*) as entries
from v$result_cache_objects
where name like '%AIRPORT_CITY2%'
group by type, status
order by type;Output:
Function AIRPORT_CITY2 compiled DXB SIN DXB_AGAIN ________ ____________ ____________ Dubai Singapore Dubai TYPE STATUS ENTRIES _____________ ____________ __________ Dependency Published 2 Result Published 2
Three calls with two different codes produce two cached results, for DXB and SIN, and the dependencies Oracle tracks to invalidate them. The example drops its function afterward.
Things to Know
- The cache is shared by all sessions of the instance, so one session's lookups speed up everyone's.
- Best for functions called often with few distinct arguments on tables that rarely change; frequent changes invalidate the cache constantly.
- DBMS_RESULT_CACHE reports on and manages the cache, including flushing it.
Related Guides
Conclusion
RESULT_CACHE stores a function's results in a cache shared by all sessions and returns them on repeated calls, invalidating them when the underlying tables change. Use it for frequently called lookups on slowly changing data.
