Calling Web Services from Oracle 9i or 10g using UTL_HTTP
Calling Web Services from Oracle 9i or 10g using UTL_HTTP package in PLSQL
By 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.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.
4 comments:
Good day! I know this is kinda off topic however I'd figured I'd ask.
Would you be interested in exchanging links or maybe guest authoring
a blog post or vice-versa? My blog addresses a lot of the same topics as yours
and I feel we could greatly benefit from each other.
If you happen to be interested feel free to send me an email.
I look forward to hearing from you! Terrific blog by the way!|
my site :: p-i-t.com.pl
All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
Peridot Systems Chennai Contact Number
ITBP Recruitment
UIIC Assistant Syllabus
Trade FX At Home On Your PC: roboforex login Is A Forex Trading Company. The Company States That You Can Make On Average 80 – 300 Pips Per Trade. roboforex login States That It Is Simple And Easy To Get Started.
Post a Comment