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.