Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code

Frequently our code has dependencies. Some are intrinsic dependencies, like the packages we need to invoke and the type definitions we rely on. Other dependencies are more like configuration settings, not really an intrinsic part of the code at all. Like the parameters in a discount calculcation or the name of the file our Logging component should write or the URL at which a WebService can be accessed. In this article we briefly discuss the Dependency Injection design pattern and continue to investigate a possible implementation in PL/SQL.

The Dependency Injection design pattern has been introduced by Martin Fowler – see for example Inversion of Control Containers and the Dependency Injection pattern at http://www.martinfowler.com/articles/injection.html – and can be seen as a specialization of the Inversion of Control pattern. Dependency Injection is about the principle of separating configuration from use. Instead of embedding hard coded values into program code or even having that piece of program code gather the values it needs from an external location, we completely remove the burden of finding out about these dependencies from the code. Instead, we ensure that when the time comes for the code to run, all of the information it needs to have is available.

This usually means that there is a central component – like the IoC container (a central Bean Factory) in the Spring Framework – that has the responsibility for populating fields in the objects with program code with currently appropriate values. This operation is called Dependency Injection: all information on which the program unit depends are injected. The injected class has no dependencies anymore on any external object, neither a central constants collection nor a configuration file. It is utterly independent. Provided of course that the Injector does its job in a timely fashion!

Note that thusfar the concept of Dependency Injection is technology agnostic. It is most frequently talked about in conjunction with Java, but that is just coincidence. If we apply the concept op Dependency Injection to PL/SQL, it would mean: no more hard coded configuration details in your PL/SQL code, no more dependencies of your code on potentially changing conditions. DI would make it easier to reuse code in various environments.

Dependency Injection is sometimes dubbed “The Hollywood Principle” – don’t call us, we’ll call you. So you do not need our number. Your code can rely on the fact that it will be called in time, to be given the details it needs to run. An example could be a central email-sender in your Oracle 9i application, based on UTL_SMTP. It needs to be configured with the mailhost and the smtp-port. Business Rules like “A salary raise for Sales personnel may never be higher than 4%” could also be a candidate for Dependency Injection: the value of 4% is a) likely to change over time and b) when hard-coded a major re-use inhibitor. Much better to somehow have the environment initialize the Business Rule component with the actual values required than hard-coding them into the component.

So what we need is a mechanism to have otherwise hard-coded values injected into our PL/SQL program units. A frequently used approach is a central package with hard-coded constants in its specification. This certainly allows for central management of values and takes them out of the components. However, the components still have a dependency on this central package and in order to promote reuse, it is far from ideal to have this extra package, especially if components from various sources use different “central” packages for management of constants. Furthermore, changing the central package may not be as simple as it sounds, due to the many dependencies on that package.

In this example we see a central package APP_CONSTANTS with the configuration details. They are not hard coded in the individual packages, which is good. However, every package has a dependency on APP_CONSTANTS. This means that we cannot take a package and easily reuse it somewhere else: we need to take APP_CONSTANTS with it. Furthermore, if we have to change one of APP_CONSTANTS values, we may invalidate – if the values are code in the package specification – all packages that depend on it (this can be accommodated by not referring to constant values in the package specification but instead to getter functions that return the values of the constants managed in the package body). We our application wants to reuse a package from some other application, it is bound to introduce its own central constants package – APP2_CENTRAL in this case. The appeal of this situation could be rated as not joyfully high.

Another, more loosely coupled approach, would be a real ‘do not call us’ implementation, where the component that requires its values to be injected offers setter-methods and relies on the assumption that someone will call it to set the appropriate values. In this case, the package has no external dependencies and can therefore easily be deployed and reused across different environments.

The crux now becomes the mechanism to inject those values. The injector could be implemented in several ways. For example a central package that is invoked from an on-logon trigger (so once for every PL/SQL session). This package needs to know which packages it should inject with values and which values to inject for which properties. The values to inject can be held in a database table, in PL/SQL code or even be read from a file or a web service. Note that the values can be user- or application specific. For this example, let’s assume there is a table called CONFIGURATION_VALUES with columns package, property and value.

The injector will read all records from CONFIGURATION_VALUES and in turn call the packages listed, invoking the setter method for the property specified with value that is configured. The injector code could be something like:

 create or replace package body DEPENDENCY_INJECTOR is

  procedure inject_dependencies
  is
  begin
    -- loop over all properties that are configured in table CONFIGURATION_VALUES
    for r in (select cve.package_name
              ,      cve.property
              ,      cve.value
              from   configuration_values cve
              ) loop
      -- for each property
      execute immediate 'begin '||r.package_name||'.set_'||r.property||'(:1); end;'
        using r.value;
    end loop;
  end inject_dependencies;

end DEPENDENCY_INJECTOR;

The Injector is called from a LOGON trigger. This way we ensure that all packages are properly instantiated before they are used in a session. One of the downsides of this approach is that we may instantiate many more packages than the session will ever need. That could be an unnecessary, undesirable claim on resources. The logon trigger is shown here:

CREATE OR REPLACE TRIGGER Dependency_Injection
AFTER LOGON  ON SCHEMA
 begin
   dependency_injector.inject_dependencies ;
 end;

One of the packages to be injected is our EMAILER package:

create or replace package EMAILER is

  -- Author  : LUCAS_J
  -- Created : 2/27/2006 12:45:09 PM
  -- Purpose : Central component for sending emails

 procedure set_smtphost(p_smtphost in varchar2);
 procedure set_helo(p_helo in varchar2);
 procedure send_email
 ( p_sender    in varchar2
 , p_recipient in varchar2
 , p_subject   in varchar2
 , p_message   in varchar2
 );

end EMAILER;

The  body of the package makes use of the injected values. Because this package does not have the mailserver in my environment hard-coded in its body, nor does it have dependencies on external code, you can simply reuse this code. Before sending emails, you need to inject the SMTPHOST and the HELO and you are ready to roll:

create or replace package body EMAILER is

 g_smtphost varchar2(500);
 g_helo varchar2(500);

 procedure set_smtphost(p_smtphost in varchar2)
 is
 begin
   g_smtphost:= p_smtphost;
 end set_smtphost;

 procedure set_helo(p_helo in varchar2)
 is
 begin
   g_helo:= p_helo;
 end set_helo;

 procedure send_email
 ( p_sender    in varchar2
 , p_recipient in varchar2
 , p_subject   in varchar2
 , p_message   in varchar2
 ) is
   c utl_smtp.connection;

   PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2)
   AS
   BEGIN
     utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
   END;

 BEGIN
   c := utl_smtp.open_connection(g_smtphost);
   utl_smtp.helo(c, g_helo);
   utl_smtp.mail(c, p_sender);
   utl_smtp.rcpt(c, p_recipient);
   utl_smtp.open_data(c);
--   send_header('From', '"From me!" <jellema@amis.nl>');
 --  send_header('To', '"TO YOU" <jellema@amis.nl>');
   send_header('Subject', p_subject);
   utl_smtp.write_data(c, utl_tcp.CRLF || p_message);
   utl_smtp.close_data(c);
   utl_smtp.quit(c);
 End send_email;

end EMAILER;

Instead of calling the Injector package from a LOGON trigger, we could also include a call in the initialization section of each package. Something like

Create or replace package body
…
Begin
  Injector.Autowire(<packagename>);
End pb;

The disadvantage of this approach is that it introduces a dependency on the Injector package in every package, so I would not recommend it.

Resources

Source code for the examples in this article: dependencyInjectionPLSQL.zip

Related article on Aspect Oriented Programming in PL/SQL