Home arrow Oracle arrow Generic Architecture for Caching Table Data: Keeping It Real (and Small)

Generic Architecture for Caching Table Data: Keeping It Real (and Small)

In the third installment of this series we will add some more functionality to the cache that will alleviate some of the drawbacks that we discussed in earlier articles.

  1. Generic Architecture for Caching Table Data: Keeping It Real (and Small)
  2. Limit the size of the cache
  3. Showing the cache content
  4. Conclusion
By: Mark Vilrokx
Rating: starstarstarstarstar / 4
October 25, 2005

print this article



Refreshing the Cache

One of the issues with using cached data is that your cache can get out of sync with the database.  This can lead to unpredictable behavior in your code and is the reason why we introduced the caching switch.  Obviously that is not really a solution to the problem.  The real solution would be to implement a mechanism that “detects” changes in the DB and automatically refreshes the cached data.  Unfortunately, such a mechanism is really beyond the scope of this series, but we can at least implement cache-refreshing procedures so that, if you want to create a refreshing mechanism, you have everything ready.

We will create two different procedures, a procedure that refreshes one record, and another one that refreshes the whole cache.  Let’s start with the first one, refreshing one record from the database.  We can actually achieve this very simply by adding a new Boolean parameter p_force_db_read to the FUNCTION dept_data which defaults to false:

   FUNCTION dept_data (
      p_dept_id         IN   dept.deptno%TYPE,
      p_force_db_read   IN   BOOLEAN DEFAULT FALSE
      RETURN department_tp
      l_dept_data   department_tp;
      IF ((caching) AND (NOT (p_force_db_read)))
         l_dept_data := read_from_cache (p_dept_id);
      END IF;

      IF (l_dept_data.NAME IS NULL)
         l_dept_data := read_from_db (p_dept_id);

         IF ((l_dept_data.NAME IS NOT NULL) AND (caching))
            write_to_cache (p_dept_id, l_dept_data);
         END IF;
      END IF;

      RETURN l_dept_data;
   END dept_data;

Now we will skip the cache read if either the caching is turned off, or if we set p_force_db_read to TRUE.  This in turn will trigger a read from the DB, refreshing the cached record in the process.  So now we just need to write our refresh procedure calling dept_data with the correct mode:

   PROCEDURE refresh_cache (p_dept_id IN dept.deptno%TYPE)
      l_dept_data   department_tp;
      l_dept_data := dept_data (p_dept_id, TRUE);
   END refresh_cache;

We will ignore the department data that gets returned from dept_data.  It would probably be handy to have a function that does both, i.e. refreshes the cache and returns the found record, but you should not put that functionality in this procedure.  The procedure is called refresh_cache, not refresh_cache_and_return_value.  You should always be careful not to introduce “side effects” into your code.  In the long run, this will ALWAYS lead to problems.  If you really want a function that does this, create one:

   FUNCTION force_read_from_db (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
      l_dept_data   department_tp;
      l_dept_data := dept_data (p_dept_id, TRUE);
      RETURN l_dept_data;
   END force_read_from_db;

And if you are thinking that this function now has the side effect of refreshing the cache, you are wrong.  Well, actually you are half right, it does refresh the cache, but that is not a “side-effect,” but the desired effect. I am caching, am I not?

For the second form of refresh, we will create an overriding procedure that just loops over the cache content and calls refresh_cache for every record:

   PROCEDURE refresh_cache
      l_cache_idx   PLS_INTEGER;
      l_cache_idx := g_dept_cache.FIRST;
         EXIT all_records_in_cache WHEN NOT g_dept_cache.EXISTS
         refresh_cache (l_cache_idx);
         l_cache_idx := g_dept_cache.NEXT (l_cache_idx);
      END LOOP all_records_in_cache;
   END refresh_cache;

You publish these procedures in the package specification for people to use as they please.  For example, the developer who is going to design the “keep in sync with DB” feature can use these procedures to achieve his goal.

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