tardate 2024

my recent reads..

Aug 25, 2007 Laugh

It's a feature not a bug!

... true geek humour.
Hat tip to Paweł Barut for highlighting this from Steve Bass' blog.
read more and comment..

Aug 20, 2007 Database SOAP XML

Revisiting 11g Native Web Services

I've just moved from 11.1.0.5 to 11.1.0.6, and updated my post First Tests of 11g Native Web Services in a few areas:

The XDB_* role names have been corrected in 11.1.0.6 to match the documentation.
Confirmed that the auto-generated WSDL still assumes 'orawsv' as the URL pattern (see below). So for now, it's clear that you should stick with 'orawsv' as the servlet name and URL pattern for your native web services configuration.

Thanks to Marco Gralike and Christopher Burke for the chats we've had in the comments thread. Here's a summary of the links that came up in discussion:

Launch is over, software's released, and now I've actually posted a real 11g blog entry, so I've decided "Tardate 10.2" deserves to grow up by +0.9. Now to be known as "Tardate 11.1"!
read more and comment..

Aug 13, 2007 Database SOAP Perl XML

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:

An arbitrary SQL DML request can be sent to the main 'oawsv' endpoint, and the results are returned as XML in the SOAP response.

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>