Home arrow Oracle arrow Page 3 - Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications

An Example - Oracle

In this series I am going to introduce you to a generic PL/SQL caching architecture. The goal is to give you an understanding of how you can store Table data in PL/SQL collections (“the cache”) and how to retrieve data from those same PL/SQL collections. The proposed architecture will be generic enough for you to use throughout your applications everywhere you need to retrieve data from the database (and isn’t that what PL/SQL applications are all about?).

  1. Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
  2. Why a Caching Architecture?
  3. An Example
  4. Retrieving department data
  5. Conclusion
By: Mark Vilrokx
Rating: starstarstarstarstar / 12
October 11, 2005

print this article



To keep this example simple, I am going to use the EMP and DEPT tables that are installed (in the SCOTT schema) when you install an Oracle database.  Typically an organization has many employees but relatively few departments for which all of these employees work.  Now imagine that you are receiving a collection of employee data and you are asked to write a procedure that finds out what the department name is for which each of these employees work and the location of that department.  This is a perfect scenario for caching.

We start with the construction of our cache.  We know that it needs to contain the department name and location because that is what is being asked from us.  Furthermore it needs to contain the department identifier because that is the only way to join the employee to his or her department.  This makes the department id a special attribute of our cache as we shall see in a bit.

Here is the declaration of my PL/SQL type to hold the department data:

   TYPE department_tp IS RECORD (
      NAME       dept.dname%TYPE,
      LOCATION   dept.loc%TYPE

Now I can hear you think: hold on a minute, where is the department identifier?  As I mentioned, the department identifier is a special attribute of my cache.  It will be used to access the data in the cache and as such it will be the index of the record it represents.  So when I declare my associative array (formerly known as Index-By table) as

   TYPE department_tbl IS TABLE OF department_tp

my department identifier is actually the INDEX BY BINARY_INTEGER bit in this declaration.  This will become clearer later on in the example.

With all my types in place, I can now declare my actual cache:

   g_dept_cache   department_tbl;

Let’s start with some basic code for our cache.  I need to be able to write data to my cache and read data back from it.  I also need a function that can read data from the database.  Here is how these modules look:

   PROCEDURE write_to_cache (
      p_dept_id     IN   dept.deptno%TYPE,
      p_dept_data   IN   department_tp
      g_dept_cache (p_dept_id).NAME := p_dept_data.NAME;
      g_dept_cache (p_dept_id).LOCATION := p_dept_data.LOCATION;
   END write_to_cache;
   FUNCTION read_from_cache (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
      l_dept_data   department_tp;
      IF (g_dept_cache.EXISTS (p_dept_id))
         l_dept_data.NAME := g_dept_cache (p_dept_id).NAME;
         l_dept_data.LOCATION := g_dept_cache
      END IF;
      RETURN l_dept_data;
   END read_from_cache;
   FUNCTION read_from_db (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
      l_dept_data   department_tp;
      CURSOR csr_dept_data (p_dept_id IN dept.deptno%TYPE)
         SELECT dname, loc
           FROM dept
          WHERE deptno = p_dept_id;
      OPEN csr_dept_data (p_dept_id);
      FETCH csr_dept_data INTO l_dept_data;
      CLOSE csr_dept_data;
      RETURN l_dept_data;
   END read_from_db;

Notice how the department identifier (p_dept_id) is used everywhere to identify the department in the cache.

>>> More Oracle Articles          >>> More By Mark Vilrokx

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: