Back to Basics Simple Database Web Services - PowerPoint by vev19514

VIEWS: 12 PAGES: 49

									                  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

								
To top