Package Level Caching

Good day. I want to talk about one of the ways to cache data in my favorite Oracle.
Let's look at caching rarely changing data (for example, directories) at the package level. This method is not scalable, because it is based on the packet state — cached data falls into the PGA, i.e. into a “private” memory area inaccessible to other users. So if you have 1000 users and they call the package functions, then you will have 1000 instances of cached data. “Bad” - you will say, yes, it is bad, but it is not necessary for all users to give the rights to execute this package. However, this method is widely used.
Bad or good, you can decide only for a particular case.

So, let's begin:
First sign:

CREATE TABLE testname
(
ID NUMBER,
Name VARCHAR2(100)
);

Now fill it up:

INSERT INTO TestName(id, name) VALUES (1, 'Ivan');
INSERT INTO TestName(id, name) VALUES (2, 'Stepan');
INSERT INTO TestName(id, name) VALUES (3, 'Sidor');
COMMIT;

Now the actual package:
')
CREATE OR REPLACE PACKAGE TestNamePck IS

-- ID
FUNCTION GetNameByID(pID NUMBER) RETURN VARCHAR2;

END TestNamePck;

/

CREATE OR REPLACE PACKAGE BODY TestNamePck IS

--
CURSOR TestCur IS
SELECT * FROM TestName;

SUBTYPE TTestName IS TestName%ROWTYPE;

TYPE TTestName_ByID IS TABLE OF TTestName INDEX BY VARCHAR2(38);

myTestName TTestName;

TestName_ByID TTestName_ByID;

-- ID
FUNCTION GetNameByID(pID NUMBER) RETURN VARCHAR2 IS
BEGIN
IF TestName_ByID.EXISTS(TO_CHAR(pId)) THEN
RETURN TestName_ByID(TO_CHAR(pId)) .Name;
ELSE
RETURN NULL;
END IF;
END;

--
BEGIN
OPEN TestCur;
FETCH TestCur INTO myTestName;
WHILE TestCur%found LOOP
TestName_ByID(TO_CHAR(myTestName.ID)) := myTestName;
FETCH TestCur INTO myTestName;
END LOOP;
CLOSE TestCur;
END TestNamePck;

/


After all of the above, we have:

SQL> set serveroutput on;
SQL> set feedback off;
SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 1));

Ivan

SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 2));

Stepan

SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 3));

Sidor

SQL>


As can be seen from the package body code, the directory cache is first initialized by filling in the associative array TestName_ByID. After that, the function of obtaining the name by ID simply uses the Exists method of the associative array to determine if a value exists.
Naturally, you need to add error handlers; here is a simple example to explain the principle of caching based on packet state.