Transcript Slide 1

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
Web Service
/timesheets
HTTP Request/Response
Endpoints
Internet
SOAP Web Services Defined
Web
Service
Employees
Endpoint: http://www.sagecomputing.com.au/employees
Operation
getAddress
Operation
getName
Operation
updateAddress
Uniquely identified
in an XML
Namespace
XML Namespace: http://www.sagecomputing.com.au/emp
SOAP Web Service API Styles:
1.
2.
Remote Procedure Call (RPC)
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
Return:
string name
http://www.w3.org/2001/XMLSchema
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:[email protected]: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');
29
utl_dbws.set_property(v_call, 'OPERATION_STYLE',
'document');
Pick one
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
SELECT
FROM
WHERE
c_name IS
decode(nameCase,'U',upper(name),'L',lower(name),initcap(name)) name
employees
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
[email protected]
[email protected]
http://one-size-doesnt-fit-all.blogspot.com
utl_http References
Tim Hall's blog
http://www.oraclebase.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-processwith-utldbws.html
Stellan's blog
http://www.selectedthoughts.com/2007/04/problems-callingnet-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-11gnative-web-services.html
Marc Thompson's blog
http://marc-on-oracle.blogspot.com/2007/12/11g-databaseinstallation-and-native.html
Oracle 11g documentation
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b2
8369/xdb_web_services.htm