Monday, August 13, 2007

First Tests of 11g Native Web Services

I mentioned in Log Buffer #54 that "Patrick Wolf stumbled across an 11g feature that means DBA's may put Java/SOA guys out of work".

Well I finally got myself setup with an 11g test system and the Native Web Services was the first thing I jumped at testing.

Conclusions first?
  • Although perhaps not a blockbuster new feature, Native Web Services provide an easy solution for exposing data services into a SOAP-oriented application stack.

  • For 11.1.0.5 (only), see Metalink Note 444191.1 for updated installation instructions.

  • The auto-generated XMLSchema and WSDL have some goofy features (like hyphens in method names) that may break SOAP client toolkit smarts.

  • The is no real control over the generated XML payload, which will possibly limit the usefulness of Native Web Services for Enterprise SOA initiatives.

  • The security model is simple but effective. The underlying database user role model provides authentication and access control.

  • The ability to call arbitrary packages/procedures/functions is perhaps the most powerful feature. No incremental coding or configuration is required for each method. A good service interface is thus just a matter of careful procedure design and construction.

  • I am less convinced that the ability to execute arbitrary SQL via Native Web Services is a good thing. It's not SOA, and it's not a good replacement for JDBC, ODP etc. Seems like just an invitation for bad hacks and shortcuts..


So, after a couple of hours of playing, I think maybe the Java/SOA guys don't have to panic just yet.. ;)

Here's the blow-by-blow, where I describe the setup gotchas I hit, and my simple SOAP::Lite testing with Perl ..

Initial Scan..

It took me a few moments to orient myself in the documentation; rather than being a distinct major new feature, Native Web Services are an enhancement of XML DB, and so the requisite documentation is mainly found in the XML DB Developer's Guide.

The architecture of Native Web Services is quite simple. The main moving part is a servlet called 'orawsv' which brokers SOAP 1.1 requests and handles automatic WSDL generation. Out of the box, it is not enabled.

The feature works in two main ways:
  1. An arbitrary SQL DML request can be sent to the main 'oawsv' endpoint, and the results are returned as XML in the SOAP response.

  2. Procedures and functions may be invoked directly over SOAP.

First Up - Configuration

Configuring Native Web Services is simply a matter of enabling the orawsv servlet, and then granting user access through role assignment.

This is covered in Chapter 33 of the docs.

I first tried this with 11.1.0.5, and found that the role names mentionedd in the docs do not match (XDB_WEBSERVICES instead of XDBWEBSERVICES and so on). For an 11.1.0.5 install, refer to Metalink Note 444191.1 for corrected installation instructions. In 11.1.0.6, role names correctly match the docs.

The second thing I discovered (actually, a problem I created for myself then had to fix!) is that the servlet name does matter! The SERVLET_NAME (as in the setup code below) must be 'orawsv'.

Thirdly, although you can set the URL pattern to anything you wish (and calls work), the auto-generated WSDL always assumes 'orawsv' when generating endpoint addresses. Effectively this means you must use 'orawsv' as the URL pattern also.
NB: thanks to Christopher Burke for alerting me to the WSDL situation.
DECLARE
SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME => SERVLET_NAME,
LANGUAGE => 'C',
DISPNAME => 'Oracle Query Web Service',
DESCRIPT => 'Servlet for issuing queries as a Web Service',
SCHEMA => 'XDB');
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => 'XDB_WEBSERVICES',
ROLELINK => 'XDB_WEBSERVICES');
DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
NAME => SERVLET_NAME);
END;
/
GRANT XDB_WEBSERVICES TO SCOTT;
GRANT XDB_WEBSERVICES_OVER_HTTP TO SCOTT;
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO SCOTT;

I couldn't quite figure out exactly why the 'orawsv' SERVLET_NAME is significant. It apparently just is, providing an explicit reference to the required servlet. The DBMS_XDB.addServlet docs are unfortunately terse and unenlightening.

So that all took me an hour more than it should. But not to worry - I am now up and running!

Now What Can It Do? Testing a Function Call by Web Services..

I was particularly interested to see a procedural interface exposed via Web Services, so my first little test was to call a database function from Perl (using SOAP::Lite). I created this very simple function in SCOTT:
CREATE OR REPLACE FUNCTION empcount
RETURN NUMBER IS
emp_count number;
BEGIN
SELECT count(*) INTO emp_count FROM emp;
RETURN emp_count;
END;
/

This becomes available at the endpoint 'http://server:port/orawsv/SCOTT/EMPCOUNT', and the auto-generated WSDL is available at 'http://server:port/orawsv/SCOTT/EMPCOUNT?wsdl'.

Next I knocked up a bare-bones Perl client. Note the redefined get_basic_credentials method to provide the basic authentication credentials that are required.
#!/usr/bin/perl -w
#
use SOAP::Lite +trace => 'debug';
sub SOAP::Transport::HTTP::Client::get_basic_credentials {
return 'scott' => 'tiger';
}
my $serviceNs = 'http://xmlns.oracle.com/orawsv/SCOTT/EMPCOUNT';
my $soap = SOAP::Lite
->proxy('http://localhost:8080/orawsv/SCOTT/EMPCOUNT');
my $som = $soap->call( SOAP::Data->name('SNUMBER-EMPCOUNTInput')->attr({'xmlns' => $serviceNs}) );
print "The response from the server was:" . $som->result . "\n";

And the answer is .. 14;) [see the end notes below for the request/reply transcript].

As you can see, pretty simple, but it should be simpler. One liners should be possible, like this:
print SOAP::Lite-> service('http://scott:tiger@localhost:8080/orawsv/SCOTT/EMPCOUNT?wsdl')->EMPCOUNT();

... but you'll notice that the 11g generated method names (like 'SNUMBER-EMPCOUNTInput') have annoying hyphens, which subverts the smart typing and dynamic binding that kits like SOAP::Lite are capable of. Doh!

Closest I can get is this:
print "The response from the server was: ";
print SOAP::Lite
->uri('http://xmlns.oracle.com/orawsv/SCOTT/EMPCOUNT')
->proxy('http://localhost:8080/orawsv/SCOTT/EMPCOUNT')
->call ( 'SNUMBER-EMPCOUNTInput' )
->result;


Endnotes - EMPCOUNT SOAP Request and Response
{The request ... }
SOAP::Transport::HTTP::Client::send_receive: POST http://localhost:8080/orawsv/SCOTT/EMPCOUNT HTTP/1.1
Accept: text/xml
Accept: multipart/*
Accept: application/soap
Content-Length: 461
Content-Type: text/xml; charset=utf-8
SOAPAction: "http://xmlns.oracle.com/orawsv/SCOTT/EMPCOUNT#SNUMBER-EMPCOUNTInput"

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<SNUMBER-EMPCOUNTInput xmlns="http://xmlns.oracle.com/orawsv/SCOTT/EMPCOUNT" xsi:nil="true" />
</soap:Body>
</soap:Envelope>

{The response ... }
SOAP::Transport::HTTP::Client::send_receive: HTTP/1.1 200 OK
Server: Oracle XML DB/Oracle Database
Content-Type: text/xml; charset=UTF-8
Client-Date: Mon, 13 Aug 2007 16:06:28 GMT
Client-Peer: 127.0.0.1:8080
Client-Response-Num: 1
Client-Transfer-Encoding: chunked
DAV: 1,2,<http://www.oracle.com/xdb/webdav/props>
MS-Author-Via: DAV

<?xml version="1.0" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<EMPCOUNTOutput xmlns="http://xmlns.oracle.com/orawsv/SCOTT/EMPCOUNT">
<RETURN>14</RETURN>
</EMPCOUNTOutput>
</soap:Body>
</soap:Envelope>

12 comments:

Marco Gralike said...

Also a strang (but explainable) thing I found out is that the URL is case-sensitive...

Paul said...

thanks Marco, yes you a right about the case-sensitivity (and the fact that its no surprise to any old Oracle hands).

I liked the diagram you included in your posting Oracle 11g - How to enable native WSDL services too. Security was cetainly one aspect I was thinking deeply about when checking out this feature, and my first thought is that you'd probably want to consider a deployment in conjunction with OWSM for any serious use .. as was shown clearly in the picture.

Marco Gralike said...

What I noticed lately, but this needs also further investigation on my side, is that Oracle also pushes Database Vault to enable you to control all the ACL settings and grants.

The OWSM (fna Obelix) part was clearly demonstrated on Oracle Open World 2006 to control the web service access.

Grz

Marco

Christopher Burke said...

Jumped up, down, left and right - and I can't get these to work.

http://192.168.0.100:8080/orawsv/SCOTT/EMPCOUNT?wsdl gives me

<soap:Value>soap:Sender
</soap:Value>
</soap:faultcode>
<soap:faultstring>
Error processing input
</soap:faultstring>

Yet http://192.168.0.100:8080/ws?wsdl gives me the correct response.

I've done 3 days just trying to get this example going :)

Marco Gralike said...

Try Mark Drake's example, give here: http://forums.oracle.com/forums/thread.jspa?threadID=542931, on the XMLDB forum.

Marco Gralike said...

Extra pointers (from me) regarding the example given on the OTN XMLDB Forum by Mark, is described here:

http://www.liberidu.com/blog/?p=246

Christopher Burke said...

Well, I found out that

"DBMS_XDB.addServletMapping(PATTERN => '/orawsvThisCanNOTBeAnything/*'

That is, the url created in the returned WSDL seems to be orawsv always, so whilst direct soap calls might work to your new URL - the WSDL is screwed if you change this.

Paul said...

Thanks for the additional links Marco.

And Chris, you are right. It seems the servlet mapping must be orawsv if you want to use the autogenerated WSDL (direct calls to the endpoint work OK - which was what I was trying at the time).

Anonymous said...

For those struggling with Perl code, soapui is a great alternative to test.

http://www.soapui.org/

Enter authentication parameters when defining the service end point.

Paul said...

Thanks for adding the tip ... yes, soapui is a great tool. Last I used it, the only niggle was it didn't support an easy way to select on/off an HTTP proxy

squirrel said...

I found your post exteremly useful in setting up my Oracle web service, however, I am also getting this "Incorrect Input Doc/URL" when I point my browser to the wsdl:

http://localhost:8081/orawsv/ws_user/PEEPCOUNT?wsdl

ws_user has these:
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC


I have scoured the internets for this error and am only coming up with 3 or 4 sites referencing this error, this being one of them.

soapui also chokes on the wsdl.
Any ideas on where I can take this?

darkcraft said...

We've implemented this at my company - however we're struggling with the strange way Oracle appears to handle basic authentication. When using preemptive authentication to call the web service, Oracle appears to run out of available http connections after four or five calls. Anyone else had experience of this?