Feuertips #4: Don’t initialize your package!

If you are writing applications using PL/SQL (in whole or part), then hopefully, you are familiar with packages.

Packages, which I suppose you could consider the procedural equivalent of classes, should be the basic building block of any application built on Oracle Database. By which I mean:

• Don’t “create or replace procedure/function”. Put all procedures and functions inside packages.
• Minimize code in database triggers, by calling package subprograms to do as much of the work as possible.
• Minimize code in APEX code blocks, by calling package subprograms to do as much of the work as possible.

OK, but that’s not what this is about. It’s about a particular feature of packages: the initialization section.

This section may optionally appear at the bottom of a package body. Structurally it looks just like the executable section of a procedure. By which I mean, that here’s a “skeleton” of a procedure:

procedure proc
is
   procedure nested_proc...;
   function nested_func return number...;
begin
   do_stuff_here;
end;

And here’s one for a package body.

package body pkg
is
   procedure public_proc...;
   function private_func return number...;
begin
   do_stuff_here;
end;

They look very similar, right? Here’s the difference: with a procedure, the whole point to execute that code after the “begin”. Anything before that is simply what needs to be declared to make that code run properly.

With a package, the initialization section is optional, while the “declaration section” is the main point of the package: to implement the subprograms listed in the specification.

So what’s that initialization section for? To initialize the package. Surprise! 

Now, the word “initialize” means “set to the value or put in the condition appropriate to the start of an operation.” (Oxford Dictionary)

Which means it is done once, at the “start of an operation”, and not repeated. In the context of a package, the initialization section is executed once per session.

While it is not common to write initialization sections, it certainly is not a rare occurrence.

But here’s the thing: if you are building APEX applications, or pretty much any application running in a browser, you should probably avoid the initialization section entirely. Or rethink how you are writing them.

Because here’s the problem: that initialization section is going to be executed over and over again – pretty much anytime a server call is made that references an element in the package (subprogram, variable, Etc.).

There’s a very simple reason for this: in modern, web-based applications, users interact with the database through a connection pool. This means that each time a server call is made from your app, a connection from the pool is assigned to the call. Since it might not be the same connection as the last time your app made a server call, the package must be re-initialized to make sure it works properly for that call.

You heard me right. That code will be executed not once per session/connection, but over and over again.

To verify this behavior, I created a very simple table and package:

create table t (n number)
/

create or replace package tr_init
is
   n number;
end;
/

create or replace package body tr_init
is
begin
   n := nvl (n, 0) + 1;
   insert into t values (n);
   commit;
end;
/

My package has a single package-level variable. It also has an initialization section that increments the variable’s value and stores it in a table.

Let’s now use this in SQL Workshop > SQL Commands. Why not? It’s a part of APEX and runs on the Internet. So I put together a little anonymous block…

declare
   n number :=tr_init.n;
begin
   select count(*) into n
     from t;

   dbms_output.put_line (n);
end;

Press Run and see “1”.

Press Run again – and now “2”, then “3”, then “4”. The package is being re-initialized each time I run this block!

This behavior also occurs, at least some of the time, when you run an APEX app that includes code like this. It all depends on ORDS’s (Oracle REST Data Services) management of the connection pool.

As Anton Nielsen of Insum explained to me: “ORDS keeps track of your session. If it can, it reuses it. It has a maximum connection pool size (let’s say 100). If ORDS has established 100 connections, and there are only 99 active users, it is likely you will get the same database session with each APEX request.”

Conversely, if you have more active users than you have connections, the chance of you using a different connection in a subsequent server call goes up. Obviously, this is not something you can or should depend on.

What’s a dev to do?

In the modern (Internet) era, when it comes to initialization of a package, you should do one of two things:

1. Avoid initialization sections. Put code to initialize package state into individual subprograms where that state is needed (“just in time” initialization).
2. In your initialization section, shortcut execution if values have already been set.

And, really, #2 only makes sense if you the values set in initialization will not change across sessions.

Let’s take a look at these two approaches.

Suppose I have a package that does a lot of date-related calculations for employees. And one value it needs over and over again is the earliest hire date. Rather than calculate it repeatedly, I would, in the old client-server days, compute it once in the initialization section:

create or replace package date_mgr authid definer
is
   procedure do_stuff;
end;
/

create or replace package body date_mgr
is
   gk_earliest_hire_date date;

   procedure do_stuff is
   begin
      if gk_earliest_hire_date < add_months(sysdate, -5)
      then
         dbms_output.put_line('More than 5 years!');
      end if;
   end;
begin
   select min(hire_date)
     into gk_earliest_hire_date
     from employees;
end;
/

But as I have shown, this query will be or could be executed many times, something we certainly want to avoid.

The first thing to do is move that logic into its own subprogram.

create or replace package date_mgr authid definer
is
   function earliest_hire_date return date;
   procedure do_stuff;
end;
/

create or replace package body date_mgr
is
   gk_earliest_hire_date date;

   function earliest_hire_date return date
   is
      l_return date;
   begin
      select min(hire_date)
        into l_return
        from employees;

      return l_return;
   end;

   procedure do_stuff
   is
   begin
      if gk_earliest_hire_date < add_months(sysdate, -5)
      then
         dbms_output.put_line('More than 5 years!');
      end if;
   end;
begin
   gk_earliest_hire_date := earliest_hire_date();
end;
/

Next, call that function within a procedure that can easily be used where needed for initialization, and also only run the initialization code if not null.

procedure init_package
is
begin
   if gk_earliest_hire_date is null
   then
      gk_earliest_hire_date := earliest_hire_date();
   end if;
end;

Now I can either call this procedure in the initialization section or get rid of the initialization section altogether and call the initialization procedure inside the do_stuff procedure.

create or replace package body date_mgr
is
   ...
   procedure do_stuff
   is
   begin
      init_package;

      if gk_earliest_hire_date < add_months (sysdate, -5)
      then
         dbms_output.put_line ('More than 5 years!');
      end if;
   end;
end;
/

Another approach you might consider taking is to skip the package constant altogether and use a result cache function:

create or replace package date_mgr authid definer
is
   function earliest_hire_date return date result_cache;
   procedure do_stuff;
end;
/

create or replace package body date_mgr
is
   function earliest_hire_date return date result_cache
   is
      l_return date;
   begin
      select min(hire_date) into l_return from employees;
      return l_return;
   end;

   procedure do_stuff
   is
   begin
      if earliest_hire_date () < add_months (sysdate, -5)
      then
         dbms_output.put_line('More than 5 years!');
      end if;
   end;
end;
/

By defining the function as a result cache – and it having no parameters, Oracle Database is smart enough to execute the body of this function just once across the entire instance – not just in your session. That is until someone commits changes the contents of the employees table. Then the cache will be invalidated, and the next time the function is invoked, the body will be executed, and the new minimum stored in the cache.

Which is very cool indeed – but only if that makes sense for your application. In this case, it does – the earliest hire date is the same no matter which schema invokes the function.

One really nice advantage of the result cache function, in this case, is that my date_mgr package no longer has “state” (there are no package-level variables and constants). That’s important because unless you are using edition-based redefinition, packages with state that are recompiled can cause the dreaded  “ORA-04068: existing state of packages has been discarded” error.

Photo credits: Jackie Zhao – Unsplash