This article presents how result cache can be used together with PL/SQL.

it’s important to read following article

SQL Result Cache 11g

Let’s prepare simple function that is using result cache functionality

create or replace function sleep_fnc(id number)
return number
result_cache
is
begin
  dbms_lock.sleep(5);

  return 5;
end;
/

FUNCTION SLEEP_FNC compiled

First call of the function takes 5 seconds and result of the function is cached

select id, type, status, name from v$result_cache_objects;
no rows selected

declare
  v_id number;
begin
  v_id := sleep_fnc(1);
end;
/
anonymous block completed
Elapsed: 00:00:05.030

select id, type, status, name from v$result_cache_objects;
no rows selected

ID TYPE       STATUS    NAME                                       
-- ---------- --------- -------------------------------------------
 0 Dependency Published TOMASZ.SLEEP_FNC                           
 1 Result     Published "TOMASZ"."SLEEP_FNC"::8."SLEEP_FNC"#fac892c

So next time you call the function it returns immediately result from result cache

declare
  v_id number;
begin
  v_id := sleep_fnc(1);
end;
/
anonymous block completed
Elapsed: 00:00:00.001

The same effect you can see if you use this function in different context

select sleep_fnc(1) from dual;
      SLEEP
----------
         5 

Elapsed: 00:00:00.004

Another example will show how result cache works for a function which is dependendent on a table.

--clear result cache
begin
  SYS.dbms_result_cache.flush;
end;
/
anonymous block completed

create table test_tbl
(id number);
table TEST_TBL created.

begin
  for i in 1..10
  loop
    insert into test_tbl values(i);
  end loop;
  commit;
end;
/
anonymous block completed

select count(*) from test_tbl;
  COUNT(*)
----------
        10

create or replace function count_fnc
return number
result_cache
is
  v_count number;
begin
  dbms_lock.sleep(5);
  select count(*) into v_count from test_tbl;

  return v_count;
end;
/
FUNCTION COUNT_FNC compile

First call of the function count_fnc takes about 5 sec another call returns data from result cache

 set timing on
select count_fnc from dual;
 COUNT_FNC
----------
        10 
Elapsed: 00:00:05.003

select count_fnc from dual;
  COUNT_FNC
----------
        10 
Elapsed: 00:00:00.001

We can see cached result and dependency between the function count_fnc and table test_tbl.

select id, type, status, name 
from v$result_cache_objects;

ID TYPE       STATUS    NAME                                       
-- ---------- --------- -------------------------------------------
 2 Dependency Published TOMASZ.TEST_TBL                            
 0 Dependency Published TOMASZ.COUNT_FNC                           
 1 Result     Published "TOMASZ"."COUNT_FNC"::8."COUNT_FNC"#9689ba4#1

select * from v$result_cache_dependency;
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          2      79875 
         1          0      79873

As long as data is not modified in the table test_tbl result cache is valid and can be resused. However if we modifiy data in the table it invalidates result cache so next call of function count_fnc is again slow.

insert into test_tbl values(1);
commit;

select id, type, status, name 
from v$result_cache_objects;

ID TYPE       STATUS    NAME                                       
-- ---------- --------- -------------------------------------------
 2 Dependency Published TOMASZ.TEST_TBL                            
 0 Dependency Published TOMASZ.COUNT_FNC                           
 1 Result     Invalid   "TOMASZ"."COUNT_FNC"::8."COUNT_FNC"#9689ba4#1

set timing on
select count_fnc from dual;
 COUNT_FNC
----------
        11 

Elapsed: 00:00:05.002

Have a fun 🙂

Tomasz