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

Oracle documentation is less than useful

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

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;

?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,?? 'document');
?? utl_dbws.set_property(v_call,?? '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