Saturday, November 28, 2009

More PL/SQL Gateway Goodies

Hot on the heels of version 1.1, which was the topic of my previous blog post, the Thoth Gateway version 1.2 improves on the automatic SOAP Web Service feature, and adds a few new features as well!

Improved Automatic SOAP Web Services

Previously, every function had its own separate service endpoint. This was a bit of a pain, as you would have to (in Visual Studio-speak) add a separate web reference to each function. Now, all functions in a package are grouped together into a single service endpoint. Just add "?wsdl" to the package name, like this:

Upload files to file system instead of database table

Normally, files uploaded via a web page will be stored in the database table specified as "DocumentTableName" in the DAD configuration. In this version, there is a new configuration parameter, "DocumentFilePath", that will cause uploaded files to be saved to the file system instead.

XDB Integration

This version of the Thoth Gateway adds easy integration with Oracle XDB.

Upload files to XDB repository: You can specify a "DocumentXdbPath" in the DAD configuration file that causes uploaded files to be inserted as XDB resources in the specified folder. (This means there are now three different, and mutually exclusive, destinations for uploaded files: database table, file system, and XDB repository.)

Here, for example, we have just uploaded a zip file to XDB, which is then available via SQL, HTTP, FTP and WebDAV as usual:

Download files from XDB repository: You can specify an "XdbAlias" in the DAD configuration file. If this is specified, it will set up a virtual directory (similar to the "PathAlias" parameter) that forwards requests to the XDB repository. You can control which part of the repository you want to expose by specifying the "XdbPathRoot" parameter.

Here we are downloading the zip file via the gateway:

The new options are more fully explained in the installation guide.

Check out version 1.2 of the Thoth Gateway now!

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!

Sunday, November 1, 2009

Bad news about Oracle XE 11g

We all love Oracle 10g Express Edition (XE), and I'm sure everyone's waiting for the 11g version which incorporates all the feature enhancements and security fixes from the last three years.

However, it now looks like we have to wait "another year or two" for the 11g version of Oracle Express Edition :-(

Seriously, Oracle? No XE 11g before late 2011? That means something like six years between the 10g and the 11g version?

Meanwhile, Microsoft is releasing its free SQL Server Express Edition on the same schedule as the for-pay version.

If Oracle is serious about using Express Edition to gain converts to the Oracle database, it should seriously reconsider this decision to delay XE 11g.

Leave a comment below if you would like to see Oracle Express Edition 11g before 2011!