SAGE Computing Services

Customised Oracle Training Workshops and Consulting

Back to Basics:
Simple Database Web Services
How to consume and publish web services with
an Oracle database without getting a headache
using an Application Server, SOA etc

Chris Muir
Oracle Consultant and Trainer
 Oracle ACE Director - Fusion Middleware
http://one-size-doesnt-fit-all.blogspot.com
In three two easy parts
(and a hard bit)

Photo thanks to spackletoe @ Flickr.com under CC

 Agenda

• Part I: Understanding web services

• Part II: Consuming web services from the database

• Part III: Publishing web services from 11g

 Agenda

• Part I: Understanding web services

• Part II: Consuming web services from the database

• Part III: Publishing web services from 11g

 Agenda

• Part I: Understanding web services

• Part II: Consuming web services from the database

• Part III: Publishing web services from 11g

 Part I:
Understanding
Web Services

SOAP, WSDL, RPC

Style, Document
Style, XML, XML
Namespaces, HTTP
request/response
.... blah blah blah
Photo thanks to dalvenjah @ Flickr.com under CC
SOAP vs REST Web Services

Photo thanks to B.G. - Oodwin & Andrea Fregnani @ Flickr.com under CC

 SOAP Web Services Defined
Host: http://www.sagecomputing.com.au
Web
Server

Web Service /bookings

Web Service /employees Endpoints

Web Service /timesheets

Internet
HTTP Request/Response
 SOAP Web Services Defined
Endpoint: http://www.sagecomputing.com.au/employees
Web
Service
Employees

Operation getAddress

Uniquely identified
Operation getName in an XML
Namespace

Operation updateAddress

XML Namespace: http://www.sagecomputing.com.au/emp

SOAP Web Service API Styles:

1. Remote Procedure Call (RPC)

2. Document

Photo thanks to Phil Romans@ Flickr.com under CC

 Remote Procedure Call (RPC) Style
Host: http://www.sagecomputing.com.au
Endpoint: http://www.sagecomputing.com.au/employees
Namespace: http://www.sagecomputing.com.au/emp
Operation: getName

In: integer employeeNumber

In: string nameCase http://www.w3.org/2001/XMLSchema
Return: string name

Request:

<sage:getName xmlns:sage="http://www.sagecomputing.com.au/emp">
<employeeNumber>1234</employeeNumber>
<nameCase>U</nameCase>
</sage:getName>

Response:

<sage:getName xmlns:sage="http://www.sagecomputing.com.au/emp">
<name>CHRISTOPHER MUIR</name>
</sage:getName>
Photo thanks to redjar @ Flickr.com under CC
 Remote Procedure Call (RPC) Style
Request:

<sage:getName xmlns:sage="http://www.sagecomputing.com.au/emp"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance">
<employeeNumber xsi:type="xsd:integer">1234</employeeNumber>
<nameCase xsi:type="xsd:string">L</nameCase>
</sage:getName>

Response:

<sage:getName xmlns:sage="http:// www.sagecomputing.com.au/emp"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance">
<name xsi:type="xsd:string">christopher muir</name>
</sage:getName>

Photo thanks to redjar @ Flickr.com under CC

 Document Style
Host: http://www.sagecomputing.com.au
Endpoint: http://www.sagecomputing.com.au/employees
Namespace: http://www.sagecomputing.com.au/emp
Operation: getName

In: XML-Schema getNameInput

Return: XML-Schema getNameOutput

Photo thanks to ARS

 Document Style "In" XML Schema
XML Schema:

<xsd:schema xmlns="http://www.w3.org/2001/XMLSchema
targetNamespace="http://www.sagecomputing.com.au/emp">
<xsd:element name="getNameInput">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="employeeNumber" type="xsd:integer"/>
<xsd:element name="nameCase" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Request:
<sage:getNameInput xmlns:sage="http://www.sagecomputing.com.au/emp">
<employeeNumber>1234</employeeNumber>
<nameCase>M</nameCase>
</sage:getNameInput>

Photo thanks to redjar @ Flickr.com under CC

Document Style "In + Return" XML Schema
XML Schema:

<xsd:schema xmlns="http://www.w3.org/2001/XMLSchema
targetNamespace="http://www.sagecomputing.com.au/emp">
<xsd:element name="getNameInput">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="employeeNumber" type="xsd:integer"/>
<xsd:element name="nameCase" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="getNameOutput" type="xsd:string"/>
</xsd:schema>

Response:
<sage:getNameOutput xmlns:sage="http://www.sagecomputing.com.au/emp">
Christopher Muir</sage:getNameOutput>

Photo thanks to redjar @ Flickr.com under CC

 SOAP Envelope
Request:

<?xml version="1.0" encoding="UTF-8"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header/>
<soap:Body>
... Request XML payload ...
</soap:Body>
</soap:Envelope>

Response:

<?xml version="1.0" encoding="UTF-8"?>

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
... Response XML payload ...
</env:Body>
</env:Envelope>
Web Service Description Language (WSDL)
• De facto standard for describing web services
• XML based + W3C standards based
• Publish web service API
http://www.sagecomputing.com.au/employees?wsdl

• Assists consumer in assembling request

• Not actually required in web service calls

• Port types = grouped operations

• Bindings = data exchange protocol (eg. SOAP over HTTP)
• Ports = endpoints/services

Photo thanks to redjar @ Flickr.com under CC

WSDL example
01 <?xml version="1.0" encoding="UTF-8" ?>
02 <definitions targetNamespace="http://www.sagecomputing.com.au/emp"
03 xmlns="http://schemas.xmlsoap.org/wsdl/"
04 xmlns:sage="http://www.sagecomputing.com.au/emp"
05 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
06 xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
07 <types/>
08 <message name="getNameInput">
09 <part name="employeeNumber" type="xsd:int"/>
10 <part name="nameCase" type="xsd:string"/>
11 </message>
12 <message name="getNameOutput"><part name="name" type="xsd:string"/></message>
13 <portType name="employees">
14 <operation name="getName" parameterOrder="employeeNumber nameCase">
15 <input message="sage:getNameInput"/>
16 <output message="sage:getNameOutput"/>
17 </operation></portType>
18 <binding name="empSoapHttp" type="sage:employees">
19 <soap:binding style="rpc" transport="http://schemas.xmlsoap.org/soap/http"/>
20 <operation name="getName">
21 <input><soap:body use="literal" namespace="http://www.sagecomputing.com.au/emp"
22 parts="employeeNumber nameCase"/></input>
23 <output><soap:body use="literal" namespace="http://www.sagecomputing.com.au/emp"
24 parts="name"/></output>
25 </operation></binding>
26 <service name="employees">
27 <port name="empSoapHttpPort" binding="sage:empSoapHttp">
28 <soap:address location="http://www.sagecomputing.com.au/employees"/>
29 </port>
30 </service>
31 </definitions>
 Part II:
Consuming Web
Services from the
Database

utl_http
utl_dbws

Photo thanks to Dominic@ Flickr.com under CC

 Consuming Web Services
from the Database

• Database provides 2 packages for accessing web services

– utl_http – low level http assembler
– utl_dbws – high level web service package

• 3rd (alternative) method:

– Load Apache Common's Java HttpClient into db
– Write Java program in db
– Generate PL/SQL wrapper
 utl_http
• Available as of 8.0.5
• Send/receive raw HTTP request/responses to external servers
• Advantages:
– Simplistic
– Installed (completely) in the database
– Passed and returns a VARCHAR2 XML payload
– Very easy if you know the XML payload structures
– Doesn't require a WSDL at publisher's site
– Good examples available on the internet
– 3rd party PL/SQL wrappers available (Tim Hall: soap_api)
• Disadvantages:
– Low level with no smarts to support web services
– Cryptic HTTP error messages
– Oracle documentation is less than useful
utl_http example
01 PROCEDURE call_web_service(i_payload IN VARCHAR2, o_response OUT VARCHAR2) IS
02 v_http_req utl_http.req;
03 v_http_resp utl_http.resp;
04 v_part_response VARCHAR2(32767);
04 BEGIN
05 utl_http.set_proxy('cmuir:pwd@proxy.sagecomputing.com.au:80 ');
06 utl_http.set_wallet('file:/oracle/owallets/', 'pwd');
07
08 v_http_req := utl_http.begin_request(
09 'http://www.sagecomputing.com.au/employees', 'POST', 'HTTP/1.1');
10 utl_http.set_authentication(v_http_req, 'cmuir', 'pwd', 'Basic', TRUE);
11
12 utl_http.set_header(v_http_req, 'Content-Type' ,'text/xml');
13 utl_http.set_header(v_http_req, 'Content-Length' ,length(i_payload));
14 utl_http.set_header(v_http_req, 'SOAPAction' ,'getName');
15
16 utl_http.write_text(v_http_req, i_payload);
17
18 v_http_resp := utl_http.get_response(v_http_req);
19 -- Add handler for HTTP error v_http_resp.status_code >= 500 <= 599
20 BEGIN
21 LOOP
22 utl_http.read_text(v_http_resp, v_part_response);
23 o_response := o_response || v_part_response;
24 END LOOP;
25 EXCEPTION WHEN utl_http.end_of_body THEN
26 NULL;
27 END;
28 utl_http.end_response(v_http_resp);
29 -- Add exception handler
30 END call_web_service;
01 DECLARE
02 v_request VARCHAR2(5000);
03 v_response VARCHAR2(5000);
04
05 v_soap_req VARCHAR2(5000);
06 v_soap_resp VARCHAR2(5000);
07
08 v_xml_resp XMLType;
09
10 BEGIN
11 v_request := '<sage:getNameInput xmlns:sage="http://www.sagecomputing.com.au/emp">'
12 || '<employeeNumber>1234</employeeNumber>'
13 || '<nameCase>M</nameCase>'
14 || '</sage:getNameInput>';
15
16 v_soap_req := '<?xml version = "1.0" encoding = "UTF-8"?>'
17 || '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">'
18 || '<soapenv:Header/>'
19 || '<soapenv:Body>'
20 || v_request
21 || '</soapenv:Body>'
22 || '</soapenv:Envelope>';
23
24 call_web_service(v_soap_req, v_soap_resp);
25
26 -- Strip the SOAP XML envelope from the response
27 v_xml_resp := XMLType.createXml(v_soap_resp);
28 v_response := v_xml_resp.extract(
29 '/soap:Envelope/soap:Body/child::node()'
30 ,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getStringVal();
31
32 END;
Oracle 11g utl_http Security Caveats
• New: Network Access Control
Lists (ACLs)
• Purpose: Fine grained host
access
• Affects: utl_tcp, utl_http,
utl_smtp, utl_mail, utl_inaddr
• Documentation: Oracle
Database Security Guide 11gR1
Chapter 4
• Simple Workaround: Metalink
Note 453786.1

Photo thanks to |m Le' chArt @ Flickr.com under CC

 utl_dbws
• Available as of 10gR1
• PL/SQL Java wrapper on oracle.jpub.runtime.dbws.DbwsProxy
• Part of JPublisher
• Advantages:
– High(er) level web service handler
• Disadvantages:
– Not completely installed by default (bah!)
– Poor error reporting (in particular HTTP)
– Queries external WSDL each request
– Oracle documentation is dismal
– Use of HttpUriType does not support wallets or proxies
– Minor bugs in 10gR2 version with external authentication
– Minor issues on calling .Net web services
– Uses database JVM
 utl_dbws Installation
• Source: Tim Hall's blog
http://www.oracle-base.com/articles/10g/utl_dbws10g.php

Download: http://download.oracle.com/technology/sample_code/
tech/java/jsp/dbws-callout-utility-10131.zip
Extract: dbwsclientws.jar + dbwsclientdb11.jar ->
ORACLE_HOME/sqlj/lib
Execute: ORACLE_HOME/sql/lib/loadjava -u sys/password -r -v
-f -genmissing -s -grant public dbwsclientws.jar
dbwsclientdb11.jar

• Slow to install & will produce class

loading errors at end
• Consider reinstalling in separate
schema from sys as per Metalink
note: 469588.1
Photo thanks to cervus @ Flickr.com under CC
utl_dbws example
01 DECLARE
02 v_namespace VARCHAR2(1000) := 'http://www.sagecomputing.com.au/emp';
03 v_service_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employees');
04 v_port_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employeesSoapHttpPort');
05 v_operation_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'getName');
06
07 v_service utl_dbws.service;
08 v_call utl_dbws.call;
09
10 v_int_type utl_dbws.qname;
11 v_string_type utl_dbws.qname;
12
13 v_request_params utl_dbws.anydata_list; -- RPC style only
14 v_response_anydata AnyData; -- RPC style only
15
16 v_request_xmltype XmlType; -- Document style only
17 v_response_xmltype XmlType; -- Document style only
18
19 BEGIN
20 v_service := utl_dbws.create_service(
21 HttpUriType('http://www.sage.com.au/employees?wsdl'), v_service_qname);
22
23 v_call := utl_dbws.create_call(v_service, v_port_qname, v_operation_qname);
24
25 utl_dbws.set_property(v_call, 'SOAPACTION_USE', 'TRUE');
26 utl_dbws.set_property(v_call, 'SOAPACTION_URI', 'getName');
27 utl_dbws.set_property(v_call, 'ENCODINGSTYLE_URI',
28 'http://schemas.xmlsoap.org/soap/encoding/');
29 utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
Pick one
29 utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'document');
29 utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
30
31 v_int_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'int');
32 v_string_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
33
34 utl_dbws.add_parameter(v_call, 'employeeNumber', v_int_type, 'ParameterMode.IN');
35 utl_dbws.add_parameter(v_call, 'nameCase', v_string_type, 'ParameterMode.IN');
36 utl_dbws.set_return_type(v_call, v_string_type);
37
38 v_request_params(0) := AnyData.convertNumber(1234);
39 v_request_params(1) := AnyData.convertVarchar('M');
40
41 v_response_anydata := utl_dbws.invoke(v_call, v_request_params);
42 dbms_output.put_line('Result = ' || v_response_anydata.accessVarchar2);
43
44 utl_dbws.release_call(v_call);
45 utl_dbws.release_service(v_service);
46 END;
47 /

Result = Christopher Muir

29 utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'document');
30
31 v_string_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
32
33 utl_dbws.add_parameter(v_call, 'Request', v_string_type, 'ParameterMode.IN');
34 utl_dbws.set_return_type(v_call, v_string_type);
35
36 v_request_xmltype := XmlType('<?xml version="1.0" encoding="utf-8"?>'
37 || '<getNameInput xmlns="' || v_namespace || '">'
38 || '<employeeNumber>1234</employeeNumber>'
39 || '<nameCase>U</nameCase>'
40 || '</getNameInput>');
41
42 v_response_xmltype := utl_dbws.invoke(v_call, v_request_xmltype);
43
44 dbms_output.put_line('Result = ' || v_response_xmltype.getStringVal());
45
46 utl_dbws.release_call(v_call);
47 utl_dbws.release_service(v_service);
48 END;
49 /

Result = <ns0:getNameOutput xmlns:ns0="http://www.sagecomputing.com.au/emp/">CHRISTOPHER

MUIR</ns0:getNameOutput>
Photo thanks to Nicki's Pix @ Flickr.com under CC
Photo thanks to Daquella manera @ Flickr.com under CC
 Part III:
Publishing Web
Services from the
Database

Native Web
Services

Photo thanks to The Dilla Lama @ Flickr.com under CC

 11g Native Web Services
• Available as of RDBMS 11gR1
• Provided through Oracle XML DB feature set
• Publishes:
– SQL & XQuery query facility
– Stored PL/SQL procedures and functions including packages
• SOAP 1.1 compliant, WSDL automatically generated

• Advantages:
– Simple, no application server required
• Disadvantages:
– No control over WSDL naming conventions and payload
structures
– Security concerns as web services are exposed directly
from database layer
11g Native Web Services Installation
• Not enabled by default

• Requires:
• Oracle XML DB HTTP Server running (running by default)

• Servlet "orawsv" configuration - Oracle XML

DB Developer's Guide 11gR1 chapter 33

• Grant roles to schema exposing objects

GRANT XDB_WEBSERVICES TO sage;
GRANT XDB_WEBSERVICES_OVER_HTTP TO sage;

Photo thanks to makelessnoise @ Flickr.com under CC

 Native PL/SQL Web Services
• Publish any procedure, function or package module
• Support for parameters using most primitive datatypes and object
types, not %rowtype
• Endpoint format:
http://<host>:<port>/orawsv/<schema>/<methodname>
http://<host>:<port>/orawsv/<schema>/<package>/<methodname>

• WSDL format:
http://<host>:<port>/orawsv/<schema>/<methodname>?wsdl
http://<host>:<port>/orawsv/<schema>/<package>/<methodname>?wsdl

• Example:
http://www.sagecomputing.com.au:8080/orawsv/SAGE/TEST?wsdl

• Schema/package/module names must match database case name

• Requires HTTP basic authentication with schema username/pwd
Native PL/SQL Web Service "Example"
FUNCTION get_name(employeeNumber IN NUMBER, nameCase IN VARCHAR2)
RETURN VARCHAR2 AS

CURSOR c_name IS
SELECT decode(nameCase,'U',upper(name),'L',lower(name),initcap(name)) name
FROM employees
WHERE emp_no = employeeNumber;

v_name VARCHAR2(100);
BEGIN
OPEN c_name;
FETCH c_name INTO v_name;
CLOSE c_name;

RETURN v_name;
END get_name;
Native PL/SQL Web Service "Request"
Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:get="http://xmlns.oracle.com/orawsv/SAGE/GET_NAME">
<soapenv:Header/>
<soapenv:Body>
<get:SVARCHAR2-GET_NAMEInput>
<get:EMPLOYEENUMBER-NUMBER-IN>1234</get:EMPLOYEENUMBER-NUMBER-IN>
<get:NAMECASE-VARCHAR2-IN>U</get:NAMECASE-VARCHAR2-IN>
</get:SVARCHAR2-GET_NAMEInput>
</soapenv:Body>
</soapenv:Envelope>
Native PL/SQL Web Service "Response"
Response

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GET_NAMEOutput xmlns="http://xmlns.oracle.com/orawsv/SAGE/GET_NAME">
<RETURN>CHRISTOPHER MUIR</RETURN>
</GET_NAMEOutput>
</soap:Body>
</soap:Envelope>
 Native Query Web Services
• Queries & DML supported on any objects accessible via schema
• Endpoint format:
http://<host>:<port>/orawsv

• WSDL format:
http://<host>:<port>/orawsv?wsdl

• Example:
http://www.sagecomputing.com.au:8080/orawsv?wsdl

• Requires HTTP basic authentication with schema username/pwd

• Takes a basic SQL string with bind parameters
• Returns XML structure containing data
• Number of parameters to influence result
Native Query Web Service "Request"
Request

<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="SQL">
<![CDATA[SELECT * FROM employees WHERE emp_id = :vEmpId]]>
</oraw:query_text>
<oraw:bind name="vEmpId">1234</oraw:bind>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope>
Native Query Web Service "Response"
Response

<soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<EMP_ID>1234</EMP_ID>
<NAME>Christoper Muir</NAME>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope>
Summary
SAGE Computing Services
Customised Oracle Training Workshops and Consulting

Questions and Answers?

Presentations are available from our website:

www.sagecomputing.com.au

enquiries@sagecomputing.com.au
chris.muir@sagecomputing.com.au
http://one-size-doesnt-fit-all.blogspot.com
 utl_http References
Tim Hall's blog
http://www.oracle-
base.com/articles/9i/ConsumingWebServices9i.php

http://www.oracle-base.com/dba/Script.php?category=
miscellaneous&file=soap_api.sql

Oracle documentation
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b2
8419/u_http.htm#CHDIAFFA
 utl_dbws References
Tim Hall's blog
http://www.oracle-base.com/articles/10g/utl_dbws10g.php

Marc Kelderman's blog

http://orasoa.blogspot.com/2006/11/calling-bpel-process-
with-utldbws.html

Stellan's blog
http://www.selectedthoughts.com/2007/04/problems-calling-
net-web-services-from.html

Henry Cortez on the OraFAQ forum

http://www.orafaq.com/forum/t/99528/0/

Oracle 10g documentation

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b1
4258/u_dbws.htm#i1001769
 Native Web Services References
Tim Hall's blog
http://www.oracle-base.com/articles/11g/
NativeOracleXmlDbWebServices_11gR1.php
Paul Gallagher's blog
http://tardate.blogspot.com/2007/08/first-tests-of-11g-
native-web-services.html
Marc Thompson's blog
http://marc-on-oracle.blogspot.com/2007/12/11g-database-
installation-and-native.html
Oracle 11g documentation
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b2
8369/xdb_web_services.htm