my recent reads..

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:
  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>

read more and comment..

Aug 11, 2007 Innovation GridControl BI

The BI/EPM Revolution (..needed, please!)

Frank Buytendijk makes a good case for why generic BI/EPM frameworks are most probably pretty useless in practice.

What puzzles me deeply is that so much of the focus of BI remains concerned with the technology infrastructure and dashboard bells and whistles. This is seen most clearly when a new enterprise application or business process is being introduced, and you hear the almost dismissive assertion made that "it will integrate with you DWH", or that it will "plug in to your management dashboard". And "we'll scope for, let's say, 20 reports to be built".

I think we are all missing the point.

I'm going to suggest that on the whole, business is still struggling with two symptoms of IT growing pains ...

  • The IT profession has lost sight of the fact that reports, dashbords and the like are not end products in their own right. They typically have no intrinsic value. The value lies purely in the decisions and actions that may be taken as a consequence (the exception perhaps being for reports that are required by an external party such as shareholders, government or regulatory authority).

  • Equally, I think business management is still adapting to a world where information is plentiful. Even when fact-based decision making is practiced, it is all too common to find it be based on spreadsheets (which may or may not have had their original source data pulled from an enterprise system).

Every time I hear the ERP vendors boasting about enterprise adoption, I cynically imagine a Microsoft advertisement that could (probably truthfully) claim
"99.9% of Fortune 500 companies run their business on Excel!!"

I think its easy to understand how we got to this position. I remember working for a reinforcing mesh ("fabric") manufacturing company in the late 80's. We had factories filled with fabric machines like the one in the picture. They weren't networked or anything. The only information that the production manager had to work with were the production sheets filled out by the operator each shift, and the maintenance manager had to send his technicians around to check out the machines periodically.

We were working in an environment where information was scarce, yet the business imperitives were very much the same as today: production manager was concerned with productivity and profitability; and the maintenance manager cared about the maintenance costs and machine performance (netting to the "TCO" of the plant). We managed through a combination of MWA (Management by Walking Around, aka gemba / genchi genbutsu), MGF (Management by Gut Feel! .. some would say experience;-), and also selected special projects to capture data related to problem hypotheses [what I was most involved in].

But the world has turned in the last 20 years. Today, most organisations have more information sitting in various databases and enterprise systems than they know what to do with. I believe the challenge these days is firstly to make that information accessible, and then - most critically - to ensure that our management practices can make effective use of that information.


We actually have great technology these days for cracking that first challenge - making information accessible and actionable. I like to think in terms of three tiers (management, operational, infrastructure), as in the diagram.

Unfortunately, that's often as far as our thinking (and implementations) go.

However as Frank points out in his post, we need to address the strategic framework for BI. How BI impacts, changes or even transforms the business.

For me, the implications are clear for all decision-makers (which may or may not just mean "management")...
  • In the past we have excelled at managing in an information-poor environment. Now we need to go back to school, unlearn some of our assumptions and practices, and start exploiting the information that (should be) at our fingertips.

  • This means a renewed relevance of management science, particularly quality management (e.g. 6 Sigma) and key concepts such as kaizen/continuous improvement.

  • In true "Flat Earth" style - if you don't, someone else will and then you are history, mate.

And for IT, I think a few things to consider...
  • First, get real and get connected to your business. You are not delivering 20 reports, you are delivering information that will hopefully help your business users may really good decisions, impact the bottom line and keep your job safe.

  • Avoid the temptation to dumb-down and de-scope BI and reporting. It could be the most business-critical aspect of the whole project ... Promote monitoring and management to first-order use cases.

  • Consider all tiers of monitoring and management: from corporate performance, to operational management, to infrastructure.

So maybe this is an aspect of what Enterprise 2.0 is really all about?
read more and comment..

Aug 5, 2007 Perl

Take the 2007 Perl Survey

Take part in the 2007 Perl Survey! The Perl Survey is an attempt to capture a picture of the Perl community in all its diversity. No matter what sort of Perl programmer you are, they'd love to hear from you.

The survey can be found at: http://perlsurvey.org/

It only takes about 5 minutes to complete, and will be open until September 30th, 2007.
read more and comment..

Aug 5, 2007 Laugh

My Crazy Roommate

If you've ever known or lived with someone who seems to lived in an alternate reality, you need to check out the My Crazy Roommate blog. This site is hilarious. Make sure you checkout his superpowers ..
read more and comment..