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 := '<?xml version="1.0" ?>' || CHR(10) ||
'<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">' || CHR(10) ||
'<S:Body>' || CHR(10) ||
'<ns2:getCustAccountsLike xmlns:ns2="http://server.vag.com/">' || CHR(10) ||
'<cust>' || l_cust || '</cust>' || CHR(10) ||
'</ns2:getCustAccountsLike>' || CHR(10) ||
'</S:Body>' || CHR(10) ||
'</S:Envelope>';
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:
<?xml version="1.0" ?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:getCustAccountsLike xmlns:ns2="http://server.vag.com/">
<cust>TIMOTHY%SIMPSON</cust>
</ns2:getCustAccountsLike>
</S:Body>
</S:Envelope>
----------------
response:
<?xml version="1.0" encoding="UTF-8"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns0:getCustAccountsLikeResponse xmlns:ns0="http://server.vag.com/">
<return>
<accountNumber>ALP008-15944460</accountNumber>
<balance>2617.69</balance>
<custCode>1854267</custCode>
<custDesc>TIMOTHY SIMPSON</custDesc>
</return>
<return>
<accountNumber>ALP007-02827164</accountNumber>
<balance>708.14</balance>
<custCode>1854267</custCode>
<custDesc>TIMOTHY SIMPSON</custDesc>
</return>
<return>
<accountNumber>ALP002-16178678</accountNumber>
<balance>2173.67</balance>
<custCode>2414935</custCode>
<custDesc>TIMOTHY SIMPSON</custDesc>
</return>
</ns0:getCustAccountsLikeResponse>
</S:Body>
</S:Envelope>
----------------
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<CustAccounts> 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;
...
}