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

Conclusion - 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



So far we have implemented only the very basic parts of the generic Caching Architecture, yet you should have an understanding of how caching can help you in building more performant applications.

Here are the steps we have implemented thus far:

First of all, you have to design your cache. It basically needs to be able to hold the data that you need to query from the database, which in turn is the data that the user is requesting.  In our case, the user is requesting the department name and location, so we have to write a query that retrieves the department name and location, and we create our cache based on that.  Whichever attribute the user is using to identify the data he needs, i.e. the where clause of your query, that attribute needs to become the index of your cache. 

Note that from Oracle 9iR2 onwards, this index does not have to be a numeric value anymore, it can just as well be a VARCHAR2  -- which is just as well, because the department identifier is actually a VARCHAR2 field in the dept table, so you might want to use a VARCHAR2 instead as an index.  The only reason I got away with using a binary_integer is because all the identifiers in my DEPT table are numbers, so they get implicitly converted for me without a problem.

Once you have your cache, you need to create three private procedures, one to write to the cache, one to read from the cache, and one that queries the DB.  Finally you create a public function using all of the above procedures to manage the data flow as defined above and serve up the data to your users:

1. Create a cache

2. Create a read, write and query procedure

3. Create a data retrieval function

In the next article, we will expand the functionality of our 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: