Wednesday, June 21, 2006

Invoking External Functionality/System from within the Database

Looking at the picture at the following URL,, i have touched through my previous posts on the first three points, including:
(1) the user-defined database functinality itself,
(2)calling database functionality from within the database,
and (3) calling user-defined database functionality from outside.

In this post, I'll touch on the fourth point (4): invoking external functionality from within the database.
The requirements range from alerting or notifying external systems based on data value/state, validating or obtaining credit card authorization, querying/updating remote database, invoking a rate/tax engine, obtaining dynamic data, invalidating middle-tier data cache, messaging across tiers, executing OS commands, and so on.

The Java runtime in the database allows the use of various Java-based mechanisms and protocols, depending on the design/implementation of the target external systems as highlighted in the following examples.

- Calling out external RMI Servers allow invoking SAP System from within the database
through SAP JCO.

- Implementing an HTTP-callout trigger using the Java-based HTTPClient for requesting a
Web component (i.e., JavaServer Page) to invalidate a middle-tier data cache. Similarly a
credit card authorization and validation can be implemented using HTTPS Call-out.

- Alerting/Notifying external systems via email, using Java-based mailer in the database.

- Implementing JDBC-Callout to issue queries and DML against remote non-Oracle database(s)
using a 3rd party pure Java JDBC driver.

- Messaging across tiers using JMS over Streams AQ by invoking Message-Driven Beans in the

- Web Services Callout allows retrieving dynamic data from external Web services or invoking
remote operations through Web services mechanisms.

- Invoking OS commands using Runtime.exec('').

- And so on ...

The beauty of embedding a full fledged Java VM in the database is that even when a protocol or invocation mechanism is not available by default, you can reuse existing Java library that implements the mechanism or protocol in question. The complete details of the examples and case studies with the code samples are available in my soon-to-be-released book