Invoking WebServices From Oracle 9i or 10g using UTL_HTTP
Invoking WebServices From Oracle 9i or 10g using UTL_HTTP
Here is the a very simple method to invoke WebServices from Oracle 9i or 10g using the UTL_HTTP method.
- 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.xmltype
as
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;
- 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.
8 comments:
Your function seems to work pretty well but the sample query returns null as if the node did not exist. Any clue about this ?
Cheers.
Jeff
Solution: Sustitution
select extract(ws_quote('GOOG'),'//GetQuoteResult/text()',
'xmlns:soap="http://www.webserviceX.NET/"').getStringVal() from dual
+- buy generic cialis usa
-- [url=http://buycialisonlinetoday.com/#85140] cialis online usa
[/url] http://buycialisonlinetoday.com/#31132 -- generic cialis
オンラインカジノ オンラインカジノ ブログ [url=http://xn--japan-ym4dobj1jwjxk6dc.com/ ]オンラインカジノ 詐欺 [/url] http://is.gd/TdvUSV オンラインカジノ 詐欺
When you look at the concise explaination your message adore, not just in relations to an amorous romantic relationship with another, yet as being a experience which is engendered in case you have miltchmonkey a much better marriage with yourself too ( space ) and even as a a sense of higher oneness spouse and children or man ( blank ) it develops into substantially more extra ordinary that each someone is seeking in life is certainly enjoy.
propecia cost generic propecia problems - propecia 6 months no results
Great this article is very helpful. Thank you for the valuable information.
Howԁy! This poѕt сould nоt be ωritten much better!
Going thгough this post reminds me of my ρrеvious гoοmmаtе!
Nice post thanks
Nagaland Lottery Result
Nagaland Lottery Sambad
West Bengal State Lottery Result
Free Recharge Tricks
Insisthost
Post a Comment