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:

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:

5 Comments

I entirely agree with karl. I too was wondering if design patterns exist in pl-sql just as they do in Java/J2ee. This example is proof that pl-sql is well on its way to having its own certified design patterns. I hope that this can be advanced to include many production-environment tested scenarios.

Surely the initialization section of each package could use execute immediate to get around the compile dependency?

Another disadvantage of the LOGON trigger is that you can’t use DBMS_SESSION.reset_package in your code to cleanup state (it’s useful in a connection-pooling environment at the start or end a logical session). The one big advantage of hardoding values in APP_CONSTANTS is that all values needed by the other packages are garanteed to exist in APP_CONSTANTS and be set to something – else the dependant packages won’t compile. Deploying new code to a production environment without creating the new configuration rows is a sure way to get egg on your face…

Hi, these kind of design oriented articles for PL/SQL are very rare! So i am very delighted to see an article focused on PL/SQL Aspects – looking not to close to the langauge features but show a wider concept of design and implementation.

Great! Karl

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 206 other subscribers

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS