According to Oracle documentation if a PL/SQL SP is returning a ref cursor then result cache is not supported. But there's a way to circumvent with somewhat beneficial results.
Below is a package written for the HR sample schema
create or replace package emps_pkg as
type detail is ref cursor;
function getempdetails (options in INTEGER, departname in VARCHAR2, jobtitle in VARCHAR2) return detail;
end;
/
create or replace package body emps_pkg as
function getempdetails(options in INTEGER,departname in VARCHAR2, jobtitle in VARCHAR2) return detail is
employees detail;
main_query VARCHAR2(2000) := 'select /*+ result_cache */ e.* from employees e,departments d,jobs j
where e.department_id = d.department_id and j.job_id = e.job_id ';
begin
if options = 1 then
main_query := main_query ||'and d.department_name=:1';
open employees for main_query using departname;
return employees;
else
main_query := main_query ||'and j.job_title=:1';
open employees for main_query using jobtitle;
return employees;
end IF ;
end;
end;
/
Package body contains some dynamic sql (there's no real logic behind this package only to demonstrate the use of result cache) which is constructed with the result_cache hint.
execute the package code and observe the plan
select emps_pkg.getempdetails(2,'Purchasing','Purchasing Manager') from dual;
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
Plan hash value: 980169617
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | RESULT CACHE | a68hth9dfygb5ddg0hr8hnty9b | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Recompile the package body without the result cache hint and observe the plan and it would be
Plan hash value: 980169617
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
The benefit comes from the fact the sql query inside the SP could use the result cache. But the overhead of executing the PL/SQL code is still there which I believe won't be present in "proper" result cached SPs.
Any changes to depending tables will make the cache invalid just like the expected behavior.
