Packet level caching

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

So, let's start:
First, the plate: Now fill it: Now the package itself: After all of the above, we have: As you can see from the package body code, the directory cache is initialized by filling in the associative array TestName_ByID. After that, the function for obtaining a name by ID simply uses the Exists method of the associative array to determine the existence of a value. Naturally, you need to add error handlers, here is a simple example to explain the principle of caching based on the state of the package.

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



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;



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;

/




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>