Caching data with PL/SQL Collections

Introduction

In many applications, there is always situation when you will need same data from database over and over again in your PL/SQL program. In same cases, the data that you need is static. This data can be some kind of codes and descriptions that rarely (if ever) change. Well, if the data isn't changing - especially during a user session - then why would you want to keep querying the same data from database?

Idea is that you create collection that will be stored in session's PGA. You will query only once your static data and put them into collection. Essentially, you use the collection's index as intelligent key.

Consider following code:

CREATE OR REPLACE PACKAGE onlyonce AS 
    
    TYPE names_t IS 
    TABLE OF employees.first_name%TYPE
            INDEX BY PLS_INTEGER;
            
    names names_t;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE)
        RETURN employees.first_name%TYPE;
        
END onlyonce; 
/
CREATE OR REPLACE PACKAGE BODY onlyonce AS
    
    FUNCTION name_from_database(employee_id_in IN employees.employee_id%TYPE)
            RETURN employees.first_name%TYPE
        IS
            local_names employees.first_name%TYPE;
     BEGIN
        SELECT first_name
            INTO local_names
            FROM employees
           WHERE employee_id = employee_id_in;
        RETURN local_names;
     END;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE) 
        RETURN employees.first_name%TYPE
    IS                
        return_value employees.first_name%TYPE;        
    BEGIN
        RETURN names(employee_id_in);
    -- I admit, that this slip of code that follows is little bit of hacking...
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            names(employee_id_in) := name_from_database(employee_id_in);
            RETURN names(employee_id_in);
    END;

END onlyonce;

SET SERVEROUTPUT ON

BEGIN    
    FOR j IN 1..10 LOOP
        FOR i IN 100..150 LOOP
            DBMS_OUTPUT.put_line(onlyonce.get_name(i));
        END LOOP;
    END LOOP;
END; 

Ok, here we have much stuff going on. So I will explain one step at the time. Off course (as in all almost oracle examples), this example use HR table.

3-7
Declare a collection type and collection to hold cached data.

16-26
Function for retrieve data from database (one by one).

28
Declaration of our main retrieval function. This function return value from database or from collection. This depend if there is already value with that key in collection or not. Only parameter for this function is id of employee which name we want to retentive.

32-40
This is _the_ meat of the caching. It is little bit of hacking, but what can you do. :) If it does not find element with that id in collection then it will pull it out from db.

46-52
We loop trough table. In first iteration in outer loop it will pull data from database, in all sequential iteration it will retrieve data from collection.

Sh how much of difference does this caching make? For example to execute 10.000 queries against some table it took about 2 second, while pulling same amount of data from collection it took 0.1 sec. That is really and oder of magnitude of improvement. And also caching static data will improve code quality, because of implicit documentation of static structures in your program.