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:
- Download the source code
- Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)
- Install the package into your schema
- 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.
- 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.
14 comments:
Morten,
Tks for this helpful post :)
I've been struggling with an error the last 2 days! I managed to setup you example and all works as expected when testing it with soapUI, however when try to consume the websevice using pl/sql I get an ORA-29263 error.
The way I see it, the error occurs when making the utl_http.request. Even if I try directly:
SELECT UTL_HTTP.request('http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name')
FROM DUAL;
I get:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29263: HTTP protocol error
ORA-06512: at line 1
If I remove the "?s=your_package_name" from the previous SELECT, I'm getting no errors, however the returned HTML is something like "The requested URL was not found on this server".
Have you run into an error like this?
Thank you
@Nancy: First of all, I'm just curious as to why you would publish a PL/SQL package as a web service and then call that service from PL/SQL? Assuming it's two different databases, why not use a database link and make the call directly from PL/SQL? Or are you just using PL/SQL to test out the web service (and if so, why not just use SoapUI for the testing)?
Secondly, I assume that you are replacing the URL parts with the actual values for servername, DAD and package name for your environment?
If it works with SoapUI, but not via PL/SQL, then it might be a network/firewall issue with your database server. Remember that when you use utl_http, the database server (and not your PC) is the client machine. Can you reach any other websites using utl_http? Is the web service on the same machine as the database you are testing from? If so, have you tried using the IP address (actual or 127.0.0.1) of the machine instead of the name?
- Morten
Morten,
I'm not considering database links because I have several machines with software running locally and I need them to get some data from my server. Using a Webservice I wouldn't have to create dozens of database links over the Internet… Also, maintenance would be easier.
Yes, I'm replacing the real values from the URL :)
I'm testing with two distinct machines, one for exposing and another one to consume the Webservice. The utl_http is running fine to other sites and even to public procedures running on the target server I'm testing… for instance, following code returns the correct HTML:
SELECT UTL_HTTP.request('http://www.oracle.com')
FROM DUAL;
SELECT UTL_HTTP.request('http://my-server/dad-name/my_public_proc')
FROM DUAL;
Can you successfully run this SELECT on your server from another machine?
SELECT UTL_HTTP.request('http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name')
FROM DUAL;
Thanks for your time.
Morten,
I'm not considering database links because I have several machines with software running locally and I need them to get some data from my server. Using a Webservice I wouldn't have to create dozens of database links over the Internet… Also, maintenance would be easier.
Yes, I'm replacing the real values from the URL :)
I'm testing with two distinct machines, one for exposing and another one to consume the Webservice. The utl_http is running fine to other sites and even to public procedures running on the target server I'm testing… for instance, following code returns the correct HTML:
SELECT UTL_HTTP.request('http://www.oracle.com')
FROM DUAL;
SELECT UTL_HTTP.request('http://my-server/dad-name/my_public_proc')
FROM DUAL;
Can you successfully run this SELECT on your server from another machine?
SELECT UTL_HTTP.request('http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name')
FROM DUAL;
Have you tried to consume the Webservice created by the code on your post using pl/sql?
Thanks for your time.
@Nancy: Sorry for the late reply, but your latest comments were eaten by Google's spam filter... !
Can you try something like this:
create or replace procedure my_public_proc2
as
begin
soap_server_pkg.wsdl('your_package_name');
end my_public_proc2;
And try to call this just like any other standalone procedure?
- Morten
Thank you, Morten, very nice writing.
I do have a little concern with this implementation: the XML response comes out with something like & lt;?xml version="1.0"?& gt; stuff for non-single(like whole Row data) return values in SOAP UI test , not exactly like others seperated by something like value1 , I understand the returned result is a single CLOB value so the brackets are part of the value. I would think it's not strict standard way for XML response, right?
Thank you for all these good work.
It's SEAN again.
I should have said I used SOAPSonic and XMLspy to test the employee_service:get_employees, the XML response comes with lots of & LT and & gt display, not the clean display as SOAPUI which displays the more hunam readable format. XMLSpy/SoapSonic doesn't do that with other web services I have tested before, so my point is: is this implenmentation stardard compliant? we need let Iphone developer accees our web service so it has to been standard SOAP service. Thank you for your time and help.
I just added a comment but can't see here, I have to do that again:
I should say : when test with SOAPUI, the results( get_employees from the test service) is displayed nicely , but when I use SOAPSonic/XMLspy, the result is lots of < and >, I want this web services 100% standard compiant becuase our users are IPHONE developers in foreign country,can you , Sir, tell me why is this way? thank you
@Sean: When you put an XML document inside another XML document (ie when your PL/SQL function returns XML, and this must be put inside the SOAP envelope which is also XML), the inner XML must be escaped (encoded) so it won't interfere with the outer XML.
The standard defines the following encoding:
http://www.w3.org/TR/xml-entity-names/predefined.html
Some more info here:
http://stackoverflow.com/questions/1091945/where-can-i-get-a-list-of-the-xml-document-escape-characters
The client (ie your iPhone app) that calls the web service simply needs to extract the (encoded) value from the SOAP envelope, and then unescape (decode) the inner XML back to "normal" XML.
- Morten
Thank you Morten, for all your nice work on web services and for the clarification of web service standard.
I wish ORACLE Apex include your PL/SQL packages like Soap_server_pkg so we can easily publish Soap services.
Have a great day
Hello,
I am getting this error:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.OWA_UTIL", line 404
ORA-06512: at "SOAP_SERVER_PKG", line 287
when i try to run the my_public_proc2 Procedure you suggested above to test. Any ideas?
i am testing this in SoapUI, and getting this error:
Caused by: org.apache.xmlbeans.XmlException: error: Unexpected element: TAG_END
do you know what i may have done wrong in the setup?
Does this technique still work with APEX 19.1?
@Unknown: This is pure PL/SQL, so does not have anything to do with a specific APEX version.
- Morten
Post a Comment