CallingWebServices From Oracle using UTL_HTTP

Calling Web Services from Oracle 9i or 10g using UTL_HTTP package in PLSQLBy using PL/SQL we eliminate the need to write wrapper API's and the Web Service calls are reduced to simple PL/SQL Database selects statements. This way the Web Service calls can be natively used within PL/SQL functions, procedures etc. In addition there is no need to build new Web Service calls for client layers (forms, jsps etc).
First Identify the webservice and the "envelope" that you need to post to the webservice using UTL_HTTP post method. In this example, I am using the following WebService

http://www.webservicex.net/stockquote.asmx?op=GetQuote

The WebService is expecting the following Envelope for the GetQuote Method

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetQuote xmlns="http://www.webserviceX.NET/">
<symbol>
string</symbol>
</GetQuote>
</soap:Body>
</soap:Envelope>


PL/SQL routine for invoking the webservice
create or replace FUNCTION WS_QUOTE( symbol in varchar2) RETURN sys.xmltypeas env VARCHAR2(32767); http_req utl_http.req; http_resp utl_http.resp; resp sys.xmltype; in_xml sys.xmltype; url varchar2(2000):='http://www.webservicex.net/stockquote.asmx?WSDL'; BEGIN-- generate_envelope(req, env);

env:='<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetQuote xmlns="http://www.webserviceX.NET/">
<symbol>' symbol '</symbol>
</GetQuote>
</soap:Body>
</soap:Envelope>';
http_req := utl_http.begin_request(url, 'POST','HTTP/1.1'); utl_http.set_body_charset(http_req, 'UTF-8');-- utl_http.set_proxy('proxy:80', NULL);-- utl_http.set_persistent_conn_support(TRUE);-- UTL_HTTP.set_authentication(http_req, '', '3', 'Basic', TRUE ); utl_http.set_header(http_req, 'Content-Type', 'text/xml'); utl_http.set_header(http_req, 'Content-Length', length(env)); utl_http.set_header(http_req, 'SOAPAction', 'http://www.webserviceX.NET/GetQuote'); utl_http.write_text(http_req, env); http_resp := utl_http.get_response(http_req); utl_http.read_text(http_resp, env); utl_http.end_response(http_resp); in_xml := sys.xmltype.createxml(env); resp := xmltype.createxml(env); dbms_output.put_line('same output'); dbms_output.put_line(SUBSTR(env, 1, 245)); RETURN resp; END; Observe here that SYMBOL is the only parameter required for this function. All other is required just to create the envelope.
Sample extract Query for extracting the information
select extract(ws_quote('GOOG'),'//GetQuoteResult/text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getStringVal() from dual
Using this technique, all webservices calls are reduced to simple queries in the database and can be used to virtualize the database.