Blog

Here is an anonymous block for the demonstration of Web-Service call capability using Oracle PL/SQL.

The Web-Service takes 1 parameter (cust) as input and outputs a list with 4 columns.

We provide the whole envelope as request and we break down the response using xmltype functions:

DECLARE

  l_envelope varchar(32000);
  l_cust    varchar2(100);
  l_return  xmltype;
  l_error   varchar2(255);
    
  PROCEDURE call_webservice(
    p_url           IN   VARCHAR2,
    p_SOAPAction    IN   VARCHAR2,
    p_env          IN   CLOB,
    p_return       OUT XMLTYPE,
    p_error        OUT  VARCHAR2)
  AS
    l_response      CLOB;
    l_http_req      UTL_HTTP.req;
    l_http_resp     UTL_HTTP.resp;
    
  BEGIN

    UTL_HTTP.set_persistent_conn_support(TRUE);
    l_http_req := UTL_HTTP.begin_request(p_url, 'POST', 'HTTP/1.0');
    UTL_HTTP.set_header(l_http_req, 'Content-Type', 'text/xml; charset="UTF-8"');
    UTL_HTTP.set_header(l_http_req, 'SOAPAction', '"' || p_SOAPAction || '"');
    UTL_HTTP.set_header(l_http_req, 'Content-Length', LENGTH(p_env));
    
    UTL_HTTP.write_text(l_http_req, p_env);
    
    l_http_resp := UTL_HTTP.get_response(l_http_req);
    UTL_HTTP.read_text(l_http_resp, l_response);
    UTL_HTTP.end_response(l_http_resp);

    p_return := XMLTYPE.createxml(l_response);
    
  EXCEPTION
    WHEN UTL_HTTP.request_failed THEN
      l_error := 'Request_Failed: ' || UTL_HTTP.get_detailed_sqlerrm;
      
    WHEN OTHERS THEN
      l_error := 'Error: ' || SQLERRM;

      IF (l_http_req.private_hndl IS NOT NULL) THEN
        UTL_HTTP.end_request(r     => l_http_req);
      END IF;
      
  END call_webservice;
  
  begin
  l_cust := 'TIMOTHY%SIMPSON';
  
  l_envelope := '' || CHR(10) || 
            '' || CHR(10) || 
                '' || CHR(10) ||
                    '' || CHR(10) ||  
                        '' || l_cust || '' || CHR(10) ||
                    '' || CHR(10) ||
                '' || CHR(10) ||
            '';
            
    dbms_output.put_line('request:');  
    dbms_output.put_line(l_envelope);    
    dbms_output.put_line('----------------');          
            
    call_webservice(
        p_url           => 'http://192.168.1.64:7001/BankExternal.CustAccountsWS/si',
        p_SOAPAction    => 'getCustAccountsLikeRequest',
        p_env          => l_envelope,
        p_return       => l_return,
        p_error        => l_error);
    
    dbms_output.put_line('response:');
    dbms_output.put_line(l_return.getStringVal());
    if(l_error is not null) then
        dbms_output.put_line('error: ' || l_error);
    end if;
    dbms_output.put_line('----------------');

    l_return := l_return.EXTRACT('/s:Envelope/s:Body/child::node()', 
                                         'xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"');
    
    dbms_output.put_line('extracted results:');
    
for xml_elements in (
        SELECT 
            --level 0 
            extractvalue(column_value,'//return/accountNumber') accountNumber, 
            extractvalue(column_value,'//return/balance') balance            
        from (SELECT EXTRACT(l_return,'/ns0:getCustAccountsLikeResponse', 
                                          'xmlns:ns0="http://server.vag.com/"') xml_extract 
                                           FROM DUAL) myxml, 
        table(xmlsequence(EXTRACT(myxml.xml_extract, '//ns0:getCustAccountsLikeResponse/return', 
                                                   'xmlns:ns0="http://server.vag.com/"'))) 
                                                    myxml_data)
loop
    dbms_output.put_line('accountNumber: ' || xml_elements.accountNumber || 
                                    ', balance: ' || xml_elements.balance);    
end loop;        
  end;

The output of this procedure is:

request:




TIMOTHY%SIMPSON



----------------
response:


   
      
         
            ALP008-15944460
            2617.69
            1854267
            TIMOTHY SIMPSON
         
         
            ALP007-02827164
            708.14
            1854267
            TIMOTHY SIMPSON
         
         
            ALP002-16178678
            2173.67
            2414935
            TIMOTHY SIMPSON
         
      
   

----------------
extracted results:
accountNumber: ALP008-15944460, balance: 2617.69
accountNumber: ALP007-02827164, balance: 708.14
accountNumber: ALP002-16178678, balance: 2173.67

And the definition in Java is:

package com.vag.server;
//Service Endpoint Interface for the CustAccountsServer (SEI)

import java.util.ArrayList;

import javax.jws.WebParam;
import javax.jws.WebResult;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.soap.SOAPBinding;
import javax.jws.soap.SOAPBinding.Style;

@WebService
@SOAPBinding(style=Style.DOCUMENT)

public interface CustAccountsServer {
    @WebMethod 
    @WebResult(name = "custAccount")
    ArrayList getCustAccountsLike(@WebParam(name="cust") String cust);
}

Where CustAccounts is defined simply as (omitting getters and setters):

public class CustAccounts {

    private String custCode;
    private String custDesc;    
    private String accountNumber;
    private Double balance;
...
}