Tuesday, November 17, 2009

Publish PL/SQL as SOAP Web Service

You can easily consume a SOAP Web Service from PL/SQL, for example using Application Express or the FLEX_WS_API (see also my companion utilities to FLEX_WS_API).

But if you want to publish (or "expose") your PL/SQL procedures as a SOAP Web Service, your options have so far been a bit limited.


JDeveloper has a "Publish as Web Service" feature that uses JPublisher to create various Java artifacts which must then be deployed to the application server. There are some details here, and an issue you need to be aware of if you are using Oracle 10g Express Edition (XE).

Now, this Java-based approach probably works fine for you if you have Java developers and a Java infrastructure in your company, although the need to (re-)generate the Java code whenever the PL/SQL code changes seems like a bit of a hassle to me.

Native Web Services (11g)

Oracle 11g (Release 1) introduced "Native Web Services". This is a servlet running in the XDB listener that automagically exposes PL/SQL code as SOAP Web Services. Here is some more information about it.

If you are a database guy like me, you probably like the "Native" approach better than the JPublisher method. However, there are a couple of issues with Native Web Services; first of all, it's an 11g feature (which of course means that it is not available in 10g, nor in Express Edition 10g), and it requires the XDB listener (which means you must either allow direct connections to your database, or set up another web server as a proxy for XDB).

Automatic Web Services with the Thoth Gateway

Since I like the concept of Native Web Services, I decided to implement a similar feature in the Thoth Gateway, a mod_plsql replacement for Microsoft IIS.

Version 1.1 of the Thoth Gateway adds a new DAD configuration parameter called InvocationProtocol. If this is set to "SOAP" (instead of the default "CGI"), PL/SQL called through the DAD will take its parameters from a SOAP request body, and respond with a SOAP response body.

The Web Service Definition Language (WSDL) document is automatically generated if you append "?wsdl" to the end of the URL. This allows a tool like Visual Studio to easily add a Web Reference to your stored procedure.

Let's see an example. Let's say we have the following package specification:

create or replace package employee_service

function get_employee_name (p_empno in number) return varchar2;

function get_employees (p_search_filter in varchar2) return clob;

end employee_service;

And the following package body:

create or replace package body employee_service

function get_employee_name (p_empno in number) return varchar2
l_returnvalue emp.ename%type;

select ename
into l_returnvalue
from emp
where empno = p_empno;
when no_data_found then
l_returnvalue := null;

return l_returnvalue;

end get_employee_name;

function get_employees (p_search_filter in varchar2) return clob
l_context     dbms_xmlgen.ctxhandle;
l_returnvalue clob;

-- there are many ways to generate XML in Oracle, this is one of them...

l_context := dbms_xmlgen.newcontext('select * from emp where lower(ename) like :p_filter_str order by empno');

-- let's make Tom Kyte happy :-)
dbms_xmlgen.setbindvalue (l_context, 'p_filter_str', lower(p_search_filter) || '%');

l_returnvalue := dbms_xmlgen.getxml (l_context);

dbms_xmlgen.closecontext (l_context);

return l_returnvalue;

end get_employees;

end employee_service;

Now navigate to the following URL with the browser (assuming you have downloaded and installed the Thoth Gateway, of course; see the installation guide in the Doc folder):


This brings up the automatically generated WSDL:

Now use your favorite SOAP testing tool (I'm using Web Service Studio, but another good tool is SoapUI) and enter the same URL.

After the test tool has generated a proxy class for the Web Service, you should see something similar to the following:

Fill in the value in the request and invoke the Web Service:

The response from the Thoth Gateway is a SOAP envelope that contains the return value of the function.

Invoking the second function in the example package above returns a CLOB with XML that represents a dataset with several rows:

Pretty cool, heh? It "just works", with no extra code or configuration necessary, except specifying "SOAP" as the protocol in the DAD!

You can use the usual parameters such as InclusionList, ExclusionList and RequestValidationFunction to control access to specific procedures. Also, the CGI environment is set up as usual before the call, so your PL/SQL code can use owa_util.get_cgi_env to get information about the client (browser).

Limitations and Caveats

There are a couple of limitations in this first release of the SOAP feature:

  • You can only call PL/SQL functions (not procedures) via SOAP.
  • Functions must return VARCHAR2 or CLOB (but as we have seen in the example above, functions returning CLOBs allow you to return any XML as the response, so this should not really be a big limitation). Support for arrays and complex (user-defined) types might come later.
  • Each function is exposed as a service endpoint. This means that in Visual Studio, for example, you must create a separate Web Reference for each function you would like to call. A future version of the gateway might group all functions in a package into one service. Update (Nov 28, 2009): As of Thoth Gateway version 1.2, all functions in a package are now grouped together as a single service endpoint.

If you would like to try it out, go and grab version 1.1 of the Thoth Gateway now!


Dimitri Gielis said...

very nice post.


dmcghan said...

Agreed. Well done.

fr├Ždrik said...

Really cool!

OraMonkey said...

I actually want an IIS now, thanks a lot! :P

Good job!

Ashish Agarwal said...

Hi Morten,

I would like to discuss something with you with regards to Oracle APEX.

Can you please send your e-mail address to me at "ashish.agarwal@dbcon.com".

Thanks & Regards,

Anonymous said...

Hello Morton,

could you also give some hints for the gateway-configuration in iis7?

Thank You

Morten Braten said...


I have not yet tried it on IIS7 myself (need to install IIS7 first...!), but the following should point you in the right direction:

See the "Deploying the Assembly to the Server" section on this page:


Also see this blog post for a slight change you may have to do in the web.config file (part of the HttpModule configuration that changed between IIS6 and IIS7):


I will update the Thoth Gateway documentation when I get the chance to try this out myself.

- Morten

SEAN said...

Hi, Morten,

I'm very interested in SOAP web services by using your IIS gateway,but before I get a real start, I could not even get the basics working. I'm using Windows 2003 R2 with .net 4 runtime installed, Oracle is 11GR2 on Linux , with APEX 4.0.2 working for a while. I have followed each and every steps in the installation Guide , but when I run the demo page:
I always got this :
HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)

The installation seems quite simple, what did I miss? thank you

Morten Braten said...

@Sean: Make sure you follow the installation instructions. In particular, did you uncheck the "Verify that file exists" option when setting up the ISAPI filter?

Also try setting the log4net log level to "DEBUG" and see what the log file tells you. If the log file is empty then IIS has not been set up correctly as the HttpModule is not being invoked.

- Morten