Many in the IT world don’t automatically think Oracle when they think ERP, though they well might, for Oracle is a formidable platform. Oracle’s history is richer than its role as an ERP player. Its long tradition as a powerful and robust database platform, along with its cutting-edge technological evolution in high-volume efficiency, often tend to overshadow the fact that it is second only to SAP among ERP players.

Perhaps it is its historical emphasis on database engineering, and the consequential subtlety of its application distribution mechanisms, that make integration and distribution of Oracle-based applications a bit intimidating to consultants, but they need not be.

First in a series

This is the first article in a series that examines the role of Oracle databases in ERP.

Sending orders downline
The primary (but certainly not the only) enabling mechanisms for application integration and distribution are the embedded event trigger, and its pinnacle, the database event trigger. The first of these may be summarized as follows:

When an application user initiates a business process, the system automatically initiates subsequent processes with no further human intervention.

Consider this example: A user sits down at a computer and receives a telephone call from a customer ordering a product. The user interfaces with the relevant database directly in the course of the phone call, creating an order in the system. The database interface the user is utilizing contains embedded event triggers that initiate several downline processes: a warehouse database query is made; the “zero” response reroutes the query to a remote warehouse, and a delivery order is generated; an advance ship notice is generated and routed to the customer; inventory is modified; and an instruction is sent to the accounting database, which generates an invoice that is forwarded to the customer.

The second (and more powerful) mechanism may be characterized this way:

When data in a database object is changed (or, by extension, when an object is created or deleted within a database), the system automatically initiates subsequent downline processes.

The order/invoice loop was already automated in the form of EDI and in place years before ERP penetrated the United States. (I implemented dozens of these before I saw my first ERP database event trigger at work (in an SAP system rather than Oracle). Basically, the same events occur, but instead of the event trigger being embedded in the user’s database interface, it’s embedded in the database itself. An order would come in via EDI (or directly from a user interface), and the action of creating a new order in the database would set in motion the flurry of activity above (and similar chains of events occurred with the modifying or deleting of an order, whether by user or by EDI).

It is not an overstatement to say that these powerful concepts turn inanimate systems into living ones. But you need a sense of how to implement such enhancements in order to design an integrated system with the desired functionality.

Oracle’s integration and distribution features
The database link feature is Oracle’s front-line facility for enabling distributed applications. It allows you to create a single transaction that will update multiple databases (as in the example above). The database link is an object that specifies how you want such updates to occur, whenever the initiating event (usually the first update in the chain) happens. (Note: Setting up the database link itself is a process with many situational variables; refer to your manuals for this). Within such a link, you can easily set up remote database access, as follows (where X is an order table in a remote database and inventory_database is defined in the database link):
select * from X*inventory_database;   

You can then use create synonym to make this selected remote a local table to your application. Oracle handles the rest.

You can now use update, insert, delete, and any other table-handling command with this table (by addressing its synonym) in your transaction, without having to make any additional provisions. In effect, the remote database is now local to your transaction. And you can do this with as many remote databases as you like, via database links.

But this only ties things together. You must also create autonomous transactions and triggers for distributed and integrated applications.

Oracle’s autonomous transactions, with triggers
In Oracle parlance, autonomous transaction means a transaction that is embedded in some other transaction, but functions independently. While simple on the surface, it becomes problematic when you consider that Oracle’s strong audit capabilities are transaction-specific; can you separate the state of the embedded transaction from the state of the parent transaction? If an embedded process fails while its initiating process succeeds, what shows up in the log? How is rollback affected?

The answer is that Oracle has solved the problem, and that the system handles them independently, even with respect to rollback, despite the fact that they’re tied together in the application.

In setting up a transaction that will contain an embedded, autonomous transaction, you must remember a couple of things.

First, your init.ora parameter file must allow for the extra transactions; the initial transaction plus the embedded ones do not count as one as far as Oracle is concerned.

Second, since the transactions are all independent from Oracle’s standpoint, you must also allow for the embedded transactions in the number of rollback segments specified in the init.ora file.

One design consideration: The embedded transaction must commit (or rollback) before returning control to the initial transaction. It’s easy to set up. The autonomous transaction can be established as a procedure or function in the surrounding transaction, or it can be an anonymous block. Whichever you use, declare pragma autonomous_transaction in its declaration section. The whole thing would look something like this:
Begin
      (block of code in an order processing transaction)
declare
             pragma autonomous_transaction;
begin
             (block of code in the autonomous transaction,
which is an inventory update routine)
            commit;
      end;
      (block of code in the order processing transaction
      commit;
end;

This is all simple enough. Most procedure calls look like this, more or less. Now let’s add a trigger, and just to demonstrate the power of the feature, let’s make the trigger itself autonomous:
create or replace trigger inv_ari
after insert on order
for each row
declare
      pragma autonomous_transaction;
begin
      (block of code in the autonomous transaction)
      commit;
end;

The pragma autonomous_transaction is now part of the trigger definition, which is used in the parent transaction.

Now you have a trigger in your initial transaction that executes an independent transaction and commits it without committing anything in the parent transaction, making it capable of independent rollback. That is one powerful batch of code.