CREATE TABLE frc_employees  
AS  
   SELECT * FROM hr.employees 

Three Functions with Result Cache Enabled

CREATE OR REPLACE PACKAGE frc_demo  
IS  
   TYPE last_names_aat IS TABLE OF frc_employees.last_name%TYPE  
      INDEX BY PLS_INTEGER;  
  
   TYPE employees_aat IS TABLE OF frc_employees%ROWTYPE  
      INDEX BY PLS_INTEGER;  
  
   FUNCTION last_name (employee_id_in IN frc_employees.employee_id%TYPE)  
      RETURN frc_employees.last_name%TYPE  
      RESULT_CACHE;  
  
   FUNCTION last_names (department_id_in IN frc_employees.department_id%TYPE)  
      RETURN last_names_aat  
      RESULT_CACHE;  
  
   FUNCTION employees_in_dept (  
      department_id_in   IN frc_employees.department_id%TYPE)  
      RETURN employees_aat  
      RESULT_CACHE;  
END; 

CREATE OR REPLACE PACKAGE BODY frc_demo  
IS  
   FUNCTION last_name (employee_id_in IN frc_employees.employee_id%TYPE)  
      RETURN frc_employees.last_name%TYPE  
      RESULT_CACHE  
   IS  
      l_name   frc_employees.last_name%TYPE;  
   BEGIN  
      DBMS_OUTPUT.PUT_LINE (  
            'EXECUTED BODY OF FUNCTION: got last name for employee ID '  
         || employee_id_in);  
  
      SELECT last_name  
        INTO l_name  
        FROM frc_employees  
       WHERE employee_id = employee_id_in;  
  
      RETURN l_name;  
   END;  
  
   FUNCTION last_names (department_id_in IN frc_employees.department_id%TYPE)  
      RETURN last_names_aat  
      RESULT_CACHE  
   IS  
      l_names   last_names_aat;  
   BEGIN  
      DBMS_OUTPUT.PUT_LINE (  
         'Getting all last names for department ID ' || department_id_in);  
  
      SELECT last_name  
        BULK COLLECT INTO l_names  
        FROM frc_employees  
       WHERE department_id = department_id_in;  
  
      RETURN l_names;  
   END;  
  
   FUNCTION employees_in_dept (  
      department_id_in   IN frc_employees.department_id%TYPE)  
      RETURN employees_aat  
      RESULT_CACHE  
   IS  
      l_employees   employees_aat;  
   BEGIN  
      DBMS_OUTPUT.PUT_LINE (  
         'Getting all rows for department ID ' || department_id_in);  
  
      SELECT *  
        BULK COLLECT INTO l_employees  
        FROM frc_employees  
       WHERE department_id = department_id_in;  
  
      RETURN l_employees;  
   END;  
END; 

Ten Calls, Just One Function Execution

BEGIN  
   FOR indx IN 1 .. 10  
   LOOP  
      DBMS_OUTPUT.put_line (frc_demo.last_name (138));  
   END LOOP;  
END; 

DECLARE 
   l_names   frc_demo.last_names_aat; 
BEGIN 
   l_names := frc_demo.last_names (50); 
   DBMS_OUTPUT.put_line ( 
      '1. Count of names in department 50 = ' || l_names.COUNT); 
   /* 
   Querying all rows into a collection does NOT help with caching 
   based on a single employee ID, even if the data returned is the "same" 
   and has been cached by another function. 
   */ 
   DBMS_OUTPUT.put_line ('Last name for 198 = ' || frc_demo.last_name (198)); 
   l_names := frc_demo.last_names (50); 
   l_names := frc_demo.last_names (60); 
   DBMS_OUTPUT.put_line ( 
      '2. Count of names in department 60 = ' || l_names.COUNT); 
END; 

DECLARE 
   l_employees   frc_demo.employees_aat; 
BEGIN 
   l_employees := frc_demo.employees_in_dept (50); 
   DBMS_OUTPUT.put_line ( 
      '1. Count of names in department 50 = ' || l_employees.COUNT); 
   l_employees := frc_demo.employees_in_dept (50); 
   l_employees := frc_demo.employees_in_dept (60); 
   DBMS_OUTPUT.put_line ( 
      '2. Count of names in department 60 = ' || l_employees.COUNT); 
END; 

Change In Your Session, Cache Disabled

BEGIN  
   DBMS_OUTPUT.put_line ('BEFORE update 1: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.put_line ('BEFORE update 2: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.PUT_LINE ('-');  
  
   UPDATE frc_employees  
      SET last_name = 'Uh-oh'  
    WHERE employee_id = 198;  
  
   /* Shows me the new value and caching is disabled. */  
   DBMS_OUTPUT.put_line ('AFTER update 1: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.put_line ('AFTER update 2: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.PUT_LINE ('-');  
  
   /* After commit, will it start caching again? Yes! */  
   COMMIT;  
   DBMS_OUTPUT.put_line ('AFTER commit 1: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.put_line ('AFTER commit 2: ' || frc_demo.last_name (144));  
   DBMS_OUTPUT.PUT_LINE ('-');  
  
   /* Reset last name */  
   UPDATE frc_employees  
      SET last_name = 'OConnell'  
    WHERE employee_id = 198;  
  
   COMMIT;  
END;