Friday, December 29, 2006

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;

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.