Friday, December 28, 2007

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;

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.

8 comments:

Anonymous said...

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

Anonymous said...

Solution: Sustitution

select extract(ws_quote('GOOG'),'//GetQuoteResult/text()',
'xmlns:soap="http://www.webserviceX.NET/"').getStringVal() from dual

Anonymous said...

+- buy generic cialis usa
-- [url=http://buycialisonlinetoday.com/#85140] cialis online usa
[/url] http://buycialisonlinetoday.com/#31132 -- generic cialis

Anonymous said...

オンラインカジノ オンラインカジノ ブログ [url=http://xn--japan-ym4dobj1jwjxk6dc.com/ ]オンラインカジノ 詐欺 [/url] http://is.gd/TdvUSV オンラインカジノ 詐欺

Anonymous said...

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.

Anonymous said...

propecia cost generic propecia problems - propecia 6 months no results

Download Content from Netflix said...

Great this article is very helpful. Thank you for the valuable information.

Anonymous said...

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