PL/SQL Integration - Part 1 - Using External Services

More and more we need, for several reasons, to integrate the legacy systems with external services. In this post, we only briefly describe some possible integration between client-server legacy systems, with business logic written in PL/SQL, and Java Services. The picture below show a classical Client-Server Architecture(two-tier) where the server-side is a PL/SQL implementation.

In order to take advantage of external services we have to change the Architeture of our legacy system. To do this we must, in some way, interfacing the PL/SQL with a software layer which acts as a wrapper between the procedures written in PL/SQL and the external world. The picture below show our Architecture changed in a Three-Tier Architecture.

Oracle offers many possibilities for integrating the Store Procedure written in PL /SQL with other systems. It’s possible to consume several kind of services from oracle database. In order to do this, Oracle provides two packages, utl_http and utl_dbws that allow to consume custom HTTP Services and Web Services. An other alternative is loading any Java jar(library) needed, writing some custom Java code in oracle db and creating a PL/SQL wrapper. So, it’s possible also interfacing with any type of Java Services like RMI Server, EJB and so on. In the next posts we’ll examine,in more details, some possible integrations with systems written in Java.