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.

7 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.