Invoking Webservice from PL/SQL （UTL_DBWS&UTL_HTTP）

从PL/SQL,Oracle提供了两种方式调用外部Webservice：一种是UTL_HTTP，另外一种是UTL_DBWS。

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

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

–3 rdparty 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_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_HTTP现在已经逐步取代UTL_DBWS。

UTL_HTTP Sample

DECLARE
  L_PARAM_LIST VARCHAR2(512);

  L_HTTP_REQUEST  UTL_HTTP.REQ;
  L_HTTP_RESPONSE UTL_HTTP.RESP;

  L_RESPONSE_TEXT VARCHAR2(32767);
BEGIN

  -- service's input parameters
  L_PARAM_LIST := 'FromCurrency=EUR&ToCurrency=USD';

  -- preparing Request...
  L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('http://www.webservicex.net/currencyconvertor.asmx/ConversionRate',
                                           'POST',
                                           'HTTP/1.1');

  -- ...set header's attributes
  UTL_HTTP.SET_HEADER(L_HTTP_REQUEST,
                      'Content-Type',
                      'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER(L_HTTP_REQUEST,
                      'Content-Length',
                      LENGTH(L_PARAM_LIST));

  -- ...set input parameters
  UTL_HTTP.WRITE_TEXT(L_HTTP_REQUEST, L_PARAM_LIST);

  -- get Response and obtain received value
  L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(L_HTTP_REQUEST);

  UTL_HTTP.READ_TEXT(L_HTTP_RESPONSE, L_RESPONSE_TEXT);

  DBMS_OUTPUT.PUT_LINE(L_RESPONSE_TEXT);

  -- finalizing
  UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);

EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);
END;

UTL_DBWS Sample

 DECLARE
   v_namespace        VARCHAR2(1000) := 'http://www.sagecomputing.com.au/emp';
   v_service_qname    utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employees');
   v_port_qname       utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employeesSoapHttpPort');
   v_operation_qname  utl_dbws.qname := utl_dbws.to_qname(v_namespace,'getName');

   v_service           utl_dbws.service;
   v_call              utl_dbws.call;

   v_int_type          utl_dbws.qname;
   v_string_type       utl_dbws.qname;

   v_request_params    utl_dbws.anydata_list; -- RPC style only
   v_response_anydata  AnyData;               -- RPC style only

   v_request_xmltype   XmlType;               -- Document style only
   v_response_xmltype  XmlType;               -- Document style only   

BEGIN
   v_service := utl_dbws.create_service(
     HttpUriType('http://www.sage.com.au/employees?wsdl'), v_service_qname);

   v_call := utl_dbws.create_call(v_service, v_port_qname, v_operation_qname);

   utl_dbws.set_property(v_call, 'SOAPACTION_USE',    'TRUE');
   utl_dbws.set_property(v_call, 'SOAPACTION_URI',    'getName');
   utl_dbws.set_property(v_call, 'ENCODINGSTYLE_URI',
    'http://schemas.xmlsoap.org/soap/encoding/');  
   utl_dbws.set_property(v_call, 'OPERATION_STYLE',   'rpc');

   utl_dbws.set_property(v_call, 'OPERATION_STYLE',   'document');
   utl_dbws.set_property(v_call, 'OPERATION_STYLE',   'rpc');

   v_int_type    := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'int');
   v_string_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');

   utl_dbws.add_parameter(v_call, 'employeeNumber', v_int_type,    'ParameterMode.IN');
   utl_dbws.add_parameter(v_call, 'nameCase',       v_string_type, 'ParameterMode.IN');
   utl_dbws.set_return_type(v_call, v_string_type);  

   v_request_params(0) := AnyData.convertNumber(1234);
   v_request_params(1) := AnyData.convertVarchar('M');

   v_response_anydata := utl_dbws.invoke(v_call, v_request_params);
   dbms_output.put_line('Result = ' || v_response_anydata.accessVarchar2);

   utl_dbws.release_call(v_call);
   utl_dbws.release_service(v_service);
END;
/
Result = Christopher Muir

参考：http://psoug.org/reference/utl_http.html

http://www.sagecomputing.com.au/papers_presentations/S301704_v2.0_OOW_Back_to_basics_web_services.ppt