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:

Framework	Infrastructure	Features
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.

Framework	Infrastructure	Features
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.