Oracle中调用SOAP Web Service服务

现在主流的服务调用方式是 JSON 消息格式的REST服务，但在一些传统的企业级应用中，仍然大量使用了SOAP Web Service，比如 ERP 系统。

这里记录一个可直接运行的实例，这个实例采用了网上公开的示例 SOAP 服务。

-- Open console output
set serveroutput on;

declare
    l_envelope CLOB;
    l_resp CLOB;
    l_xml	XMLTYPE;
    l_result varchar2(50);
    l_result2 varchar2(50);
begin
    l_envelope := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <NumberToWords xmlns="http://www.dataaccess.com/webservicesserver/">
      <ubiNum>500</ubiNum>
    </NumberToWords>
  </soap:Body>
</soap:Envelope>';
    
    l_xml := apex_web_service.make_request(
        p_url      => 'https://www.dataaccess.com/webservicesserver/NumberConversion.wso',
        p_action   => '',
        p_envelope => l_envelope
    );
    
    DBMS_OUTPUT.put_line('SOAP response: ' || chr(10) || l_xml.getClobVal());
    
    -- Use xpath to get a specific value from the response XML.
    select extract(
        XMLType_instance => l_xml,
        XPath_string => '//my:NumberToWordsResponse/my:NumberToWordsResult/text()',
        namespace_string => 'xmlns:my="http://www.dataaccess.com/webservicesserver/"'
    ).getStringVal() into l_result from dual;
    
    DBMS_OUTPUT.put_line(chr(10) || 'Extracted result: ' || l_result);
    
    -- Load the XML response to XMLTable
    SELECT t_soap_resp.words into l_result2 from
       XMLTABLE( 
        XMLNAMESPACES(
            DEFAULT 'http://www.dataaccess.com/webservicesserver/',
            'http://schemas.xmlsoap.org/soap/envelope/' as "soap"
        ),
       '//NumberToWordsResponse'
          PASSING l_xml
          COLUMNS 
             words varchar2(100) PATH 'NumberToWordsResult'
    ) t_soap_resp;
             
    DBMS_OUTPUT.put_line(chr(10) || 'Extracted result2: ' || l_result2);
end;
/

由于采用的是网上公开的示例SOAP服务，因此可以直接运行。注意：实例中采用了 APEX_WEB_SERVICE 包，因此需要先安装 APEX.

以上实例采取了多种方法解析SOAP返回的Payload。