I recently had to implement a common feature across multiple applications and app servers, all of which point to the same Oracle database. For reasons unrelated, I chose to implement this feature using PL/SQL. You can all stop laughing now. I ended up with something resembling:

create or replace package body MY_PKG is
  g_enabled BOOLEAN := TRUE;

  procedure enable_sp() is
   begin
     g_enabled := TRUE;
   end;

  procedure disable_sp() is
   begin
     g_enabled := FALSE;
   end;

  function is_enabled_fn() is
   l_return_value NUMBER;

   begin
     if g_enabled then
      l_return_value := 1;
     else
      l_return_value := 0;
     end if;

     return l_return_value;
   end;
end MY_PKG;

Seems pretty innocent, doesn’t it?

So, I write unit tests in java to test everything. It all seems to be working. It goes through code review. Nothing more serious than cosmetic issues were found. It goes through the qa cycles. It all seems to be working. It gets deployed to production. At first, it seems to be working. Then I start to see some interesting behavior.

Lesson #1:

PL/SQL data types aren’t as straight forward as I would have liked. The variable g_enabled is of type BOOLEAN. This is a data type built into PL/SQL. It is not a data type in Oracle’s version of SQL. Why is this significant? Consider the two following scenario’s and their result (assuming that is_enabled=TRUE) -

begin

 dbms_output.put_line(my_pkg.is_enabled_fn());

end;

This will print out a 1 to the standard output.

select my_pkg.is_enabled_fn() from dual;

This query will return 0.

This was not what I expected. I would have expected the second query to return a 1. The reason it doesn’t, is that the query is not run in a PL/SQL block. This results in the BOOLEAN data type to not be defined and the if statement in is_enabled_fn() to always evaluate to false.

Lesson #2:

Package variables are connection private. I create two connections, A and B, to the database. If I call disable_sp() in connection A, is_enabled_fn() will still return a 1 in connection B. The value of the variable is not shared across the connections. If I were then to create a connection C, is_enabled_fn() will return 0. This is because connection C was created after the value was set in connection A. While it wasn’t what I initially expected, it makes some sense in that the connections don’t share memory. The really big problem comes into play when app servers pool connections and might not close them for weeks at a time.