SOAP Server in PL/SQL

Or how to expose PL/SQL packages as SOAP web services using pure PL/SQL

I have blogged before about the various options available for both consuming and exposing SOAP web services using PL/SQL. (And if you don't know what SOAP is, here is a tongue-in-cheek introduction.)

Here is yet another lightweight alternative, a small PL/SQL package that implements a simple SOAP server. It will generate a WSDL document on-the-fly for the packages you want to expose (subject to a whitelist). Functions (only) are invoked using dynamic SQL, and the results are returned in a SOAP envelope. Exceptions are handled using the SOAP Fault mechanism.

I have successfully tested this package on both the Embedded PL/SQL Gateway (DBMS_EPG) on Oracle XE 10g, as well as on Apache/OHS with mod_plsql (tested on a 10g database).

Here is a screenshot showing Web Service Studio used to test the Employee demo service (ie database package):

To try it out, follow these steps:

1. Download the source code
2. Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)
3. Install the package into your schema
4. If you want to run the package through the Apex DAD, remember to grant execute on soap_server_pkg to anonymous (on EPG) or apex_public_user (on mod_plsql), and modify the request validation function (wwv_flow_epg_include_mod_local) as appropriate. Create a synonym if you don't want to include the schema name in the URL.
5. Use a SOAP client such as Web Service Studio or SoapUI and navigate to http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name

Issues and limitations

• This initial version only supports functions that return a single value (varchar2, number, date, clob). Functions returning complex (user-defined) types, object types or array types are not supported. But as the demo package shows, you can return complex values using a single CLOB formatted as XML.
• The OWA toolkit has a 32K limit on the size of CGI environment variables, which means the SOAP request body is similarly restricted. So although you can return responses of any length from your web services, the requests you can receive must be under 32K in length (including the XML tags in the SOAP request envelope).
• The Apex Listener (at least as of the EA release 1.1) differs from the EPG and mod_plsql in that it does not pass the SOAP_BODY request variable to the OWA toolkit, so this solution will not work with the Apex Listener. However, it should be trivial to add to the Listener, so if you would find it useful, then you should file an enhancement request with Oracle and ask for it.

I'll end with a note of caution: This package executes dynamic SQL. While care has been taken to sanitize the input and to implement a whitelist, you should carefully review these security measures in terms of your own environment before you expose your database on the network.

Thoth Gateway: mod_plsql replacement for Microsoft IIS

Take a look at this screenshot:



Yes, that's right... Oracle Application Express running on Microsoft's Internet Information Server (IIS)! In-between trips to the beach this summer, I decided to write a PL/SQL gateway module for IIS. The result, called the Thoth Gateway, is now available as open source.

This means that the deployment options for PL/SQL web applications are now:

• The Embedded PL/SQL Gateway (DBMS_EPG), which is a webserver built into the database itself
• The Oracle HTTP Server (OHS), which is based on the Apache codebase, with mod_plsql
• Java-based web servers (such as Tomcat, WebLogic, and others) with the open-source DBPrism plugin, or the upcoming, Oracle-supported Apex Listener
• And now, Microsoft Internet Information Server (IIS) with the Thoth Gateway module

The Thoth Gateway is implemented in C# as an ASP.NET HttpModule and uses the Oracle Data Provider for .NET (ODP.NET) to communicate with the database.

The Thoth Gateway supports almost all of mod_plsql's features, and even adds a few more (such as CLOB parameter support for values over 32K). Check out the project page for the details.

It's also worth noting that the Thoth Gateway, being a volunteer project, is not officially supported by Oracle in any way. But at least now you have the option of using Microsoft's IIS as the web server for Oracle PL/SQL web applications. In fact, you can use any of the above listed gateways side-by-side for the same PL/SQL web application (all on the same box, or on different physical servers).

I am very interested in feedback if you decide to go ahead and try the Thoth Gateway, so feel free to add comments to this post about any bugs or problems you encounter, or use the project's issue tracker (adding issues requires a Google account).

Adventures with Apex, part two: Setting up the stand-alone Oracle HTTP Server as a Windows service

The latest stand-alone version of Oracle HTTP Server (OHS) for Windows is available on OTN as the file "iAS_101330_Apache2_Modplsql2_win32.zip".

I ran the default setup on a Windows 2003 server (a separate machine from the database server) and encountered no errors during installation. The setup process created a few Start Menu items to start and stop the Apache process (through the Oracle Process Manager, OPMN, and the "opmnctl startall" command, which in turn starts the HTTP Server).

After configuring OHS and mod_plsql on the webserver to connect to Apex on the database server (as per the Apex installation docs), everything seemed to work fine. However, when I logged out of the server, the Apex application (and OHS) stopped responding.

Investigating, I found that the Apache process (apache.exe) is started and runs under the current logged-in user when using the Start Menu item to start it. So once I logged out of the server, the Apache process shuts down...!

As far as I could tell, there should have been a Windows service called OracleProcessManager that runs the OPMN process in the background, without requiring a logged-on user. However, there is no such service created after installation.

I tried creating the service manually myself using the "sc create" command. The service was created successfully but gave an error message ["...did not respond in a timely fashion..."] when trying to start it.)

Fortunately, I had a quick look in the Knowledgebase on Metalink and found the solution there. According to Note:459474.1, entitled "Howto Create a Windows Services Entry for Starting Oracle Application Server Processes", this behavior is by design (!)...:

Most Oracle Application Server installations automatically create a Windows Services entry to allow the OracleAS processes to start up on server startup. By design, certain OracleAS releases such as the SOA Suite 10.1.3.1 do not create the Windows Services entry. In such installations, the user starts OracleAS processes from the Windows Start Menu. When the user logs out of the Windows session, the OracleAS processes will terminate as well. Resulting in an unexpected Application Server shutdown.

This note provides the steps to create a Windows Services entry for starting up OracleAS processes upon reboot/startup of the server. Processes started via Windows Services entries are not affect by a user logging out of the Windows session.

I'll leave the question as to why on earth the setup program doesn't create the necessary Windows services by default, for what is clearly a server-type product?!? up in the air for now.

Instead, I will provide the necessary details here for those who do not have access to Metalink:

Turns out I was right about having to create a Windows service manually using the "sc" command, but there is also a few registry entries that need to be present for it to work.

First, find the "key" value from the file \opmn\bin\oracle.key on the server. In my case, the value of this key was:

SOFTWARE\ORACLE\KEY_oracleas1

Run regedit and add the following two entries (in my case, the first entry already existed):

HKLM / Software / ORACLE / Key_oracleas1 / ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_oracleas1

and

HKLM / Software / ORACLE / oracleas1 / ORACLE_OPMN_SERVICE = OracleHTTPServerProcessManager

Then run the following command to create the service:

sc create OracleHTTPServerProcessManager binPath= "c:\OraHome_1\opmn\bin\opmn.exe -s" DisplayName= "OracleHTTPServerProcessManager" start= "auto"

Note that there must be an empty space between the equal sign and the value in the command above. Apparently some kind of quirk with the sc command. By the way, the sc command is part of the Windows server resource kit (but it was already present on my Windows 2003 server).

After creating the service, go to Services and verify that the service starts up. Also verify using Task Manager that apache.exe runs under the SYSTEM user, rather than the logged-in user.