Tuesday, January 24, 2012

Oracle, Webservices, and PL/SQL

There are many ways you can publish or consume Web Services or XML Documents in an Oracle environment. The other day an organization asked me to evaluate the solution they had chosen to expose PL/SQL packages as Web Service and to consume Web Services from PL/SQL. Not because they experienced problems, but because they wanted to know the 'current state of affairs' in Oracle and XML and evaluate if their way of solving this was the most efficient way.

Not surprisingly, the possibilities are endless, depending on your environment. In this blog, I have listed the possibilities I know of for PL/SQL and added some details about when you would like to use the feature.

Publishing Web services
The following frameworks are available to publish a PL/SQL procedure or function as a web service:

Native Database web services Oracle Database 11g Oracle XML DB 11g
PL/SQL Webservices Oracle Applicatieserver JPublisher, SQLUTL
Eclipselink-DBWS JEE Applicatieserver
  • DBWSBuilder (DesignTime)
  • JAX-WS (Runtime)
Oracle DB Adapater Oracle SOA Suite Toplink
Integrated SOA Gateway Oracle E-Business Suite 12  Oracle SOA Suite 10g

The Integrated SOA Gateway is only a viable option, if you are exposing PL/SQL APIs that are part of EBS 12.

If you don't have an JEE Application server (for example because you are in a .NET programming environment), Native Database web services are a viable option.

Of course, you can also program a web service in .NET or Java and call the PL/SQL procedure or function using Object Relational mappings.

Consuming Web services
The other way around, calling a Web Service from the database is also a common use case if you have a lot of logic in your database.

PL/SQL Oracle Database 11g UTL_HTTP
Java Oracle Database 11g Java stored procedures, JAX-WS
JPublisher Oracle Database11g UTL_DBWS

Although I think that BPEL, Java and .Net frameworks are a better fit for this type of thing, it sometimes is not feasible to change the entire software architecture. Using a utility like UTL_DBWS and JPublisher is very convenient and less error prone than coding all the XML by hand in the database.
If you are planning to move to JEE or SOA, using PL/SQL Webservices, EclipseLink or the Oracle database adapter is a good step to start this endeavor.

1 comment:

  1. After reading this, I may need to refresh my reading on PL / SQL on ORACLE adaptation.
    polycom ip 550