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
   AS
      l_dept_data   department_tp;
   BEGIN
      IF ((caching) AND (NOT (p_force_db_read)))
      THEN
         l_dept_data := read_from_cache (p_dept_id);
      END IF;

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

         IF ((l_dept_data.NAME IS NOT NULL) AND (caching))
         THEN
            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)
   AS
      l_dept_data   department_tp;
   BEGIN
      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
   AS
      l_dept_data   department_tp;
   BEGIN
      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
   AS
      l_cache_idx   PLS_INTEGER;
   BEGIN
      l_cache_idx := g_dept_cache.FIRST;
      <<all_records_in_cache>>
      LOOP
         EXIT all_records_in_cache WHEN NOT g_dept_cache.EXISTS
(l_cache_idx);
         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.