Feuertip #27: No dirty data for your users!

In Feuertip #26, I introduced the amazing and wonderful function result cache.

The basic idea, to recap, is that Oracle Database saves information about previous calls to the function in a special cache. The information it saves are:

• the values of all parameters
• the value returned by the function

Then, each time the function is called, Oracle checks the parameter values against those in the cache. If it finds a match, it passes back the associated return value – without ever executing the function!

Another way to look at this feature is that the result cache is like a table with a unique index on the parameter list.

Combine that with the fact that Oracle is completely ignorant of what is going on inside the function, and you should readily conclude that:

If the function return value depends on anything besides the values passed through the argument list, you could have a real problem.

You could, to be blunt, deliver dirty or bad data to your users.

Scenarios

Let’s look at a simple example and then move on to more subtle scenarios.

I create a package specification with a “global” variable, and a function that returns the name for an employee. Note that the function is not result-cached.

create or replace package pkg authid definer
is
   g_force_upper boolean := false;
end;
/

create or replace function last_name (
   p_employee_id in integer
) return varchar2 
is
   l_name varchar2(1000);
begin
   select last_name
     into l_name
     from employees
    where employee_id = p_employee_id;

   return
      case
         when pkg.g_force_upper then
            upper(l_name)
         else l_name
      end;
end;
/

Then I call the function for the first time and see the name of the employee, not in upper case.

begin
   dbms_output.put_line (last_name (100));
end;
/

King

Now I change the value of the package variable and re-execute the function….

begin
   pkg.g_force_upper := true;
   dbms_output.put_line (last_name (100));
end;
/

KING

Now it’s upper case. Makes sense. The value of the variable changed, and that affected the behavior of the function.

OK, let’s re-set the variable back to false.

begin
   pkg.g_force_upper := false;
end;
/

Now I redefine the function as a result-cached function.

create or replace function last_name (
   p_employee_id in integer
) return varchar2
   result_cache
is
   l_name varchar2(100);
begin
   select last_name
     into l_name
     from employees
    where employee_id = p_employee_id;

   return
      case
         when pkg.g_force_upper then
            upper(l_name)
         else l_name
      end;
end;
/

I then call the function and see the same value as the last time it was executed immediately after creation.

begin
   dbms_output.put_line (last_name (100));
end;
/

King

But now when I set the variable to true and re-execute the function….

begin
   pkg.g_force_upper := true;
   dbms_output.put_line(last_name(100));
end;
/

King

Kaboom! Bad data! You can see why right? The function body was not executed since there was an entry in the result cache for 100. As a result, the changed value of the package variable did not have any impact.

That, in a nutshell, describes the path to delivering dirty data to your users. All you have to do is violate the fundamental rule of the function result cache:

Everything that affects the value returned by the function must be in the parameter list.

And everything you’ve seen so far is running in the same session. But remember: the function result cache is an instance-level, cross-session cache. Which means that the scenarios which can result in dirty data can be much more subtle and far-reaching.

Conclusion

Here is a shortlist of some of the ways you can carelessly abuse the result cache feature and lead to bad/dirty data:

• References to package-level variables
• Reliance on NLS settings in session. Example: call TO_CHAR for a date without specifying a mask.
• Use the also fantastic virtual private database (a.k.a., row level security) feature.

Reminder: selecting from tables and views are not an issue here (they would be if you want to claim your function is deterministic). Oracle automatically keeps track of table dependencies and invalidates the cache when changes are made and committed.

Resources

LiveSQL script with code in this post

LiveSQL script showing dangers of hidden reliance on session NLS settings

Watch the full episode here