Consuming Web Services from PL/SQL – Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i or 10g)

This post discusses how a Web Service can be consumed from PL/SQL inside the Oracle Database through the use of the UTL_HTTP supplied package. In this article we will create a PL/SQL Function that builds the entire SOAP message, inserts parameter values, invokes the WebService through UTL_HTTP and returns the result after extracting it from the returned SOAP Document. Note that UTL_HTTP was introduced in Oracle 9i. The approach in this article also makes use of some of the Oracle XML/XDB features that were new too in Oracle 9i.

There seem to be three methods for consuming Web Services from PL/SQL:

• Use the UTL_HTTP supplied package to communicate directly with the WebService and send and retrieve entire SOAP messages
• Develop Java Classes – preferably using the Web Service Wizards in JDeveloper – that consume the WebService and load these into the database wrapped with a PL/SQL package interface; this interface can be called like any normal PL/SQL procedure call
• Make use of the UTL_DBWS supplied package that allows generic WebService consumption from within PL/SQL without the need to create and load Java classes or write entire SOAP messages from within PL/SQL

In a post titled Consuming Web Services from PL/SQL – Part I: Using Java Stored Procedures I discussed the second method. In a subsequent post I will discuss the last method.

Series on Web Services, PL/SQL and Oracle BPEL Process Manager

This post is dedicated to the first method. This post is part of a series that work towards Oracle BPEL Process Manager and its application in the context of PL/SQL. This is an area where until now Oracle Workflow was used to govern processes. Many people wonder whether Oracle BPEL can and will be used in this area – or whether PL/SQL may become a forgotten country in terms of WebServices and BPEL. In order to link PL/SQL based services into Oracle BPEL, these services must be exposed as WebServices. Note that in a future version of Oracle BPEL there is the intention of providing a short-cut for invoking PL/SQL procedures – that may take away the requirement of a fully worked out webservice. Of course, PL/SQL code may itself make use of WebServices. All in all I want to show three things:

• PL/SQL code consuming a webservice
• PL/SQL code published as webservice
• Use of Oracle BPEL Process Manager to redesign and reimplement a piece of PL/SQL code

a piece of PL/SQL that I want to publish and then redesign using BPEL is the following:

function get_quote
( p_product_id in number
, p_quantity  in number
, p_delivery_date in date
, p_currency in varchar2 default 'eur'
) return number
is
  l_discount number(3); -- discount percentage
  l_date_discount number(3); -- discount percentage because of the delivery date (deliveries on Monday are cheaper)
  l_product_discount number(3); -- discount percentage - some products are heavily pushed
  l_standard_price number(10,2); -- price as calculated in euros without applying any discounts
  l_price number(10,2); -- price after applying discounts
begin
  l_discount:= 10;
  -- monday deliveries get an extra 15% discount
  if is_monday(p_delivery_date)
  then
      l_date_discount := 15;
  end if;
  -- a special discount rate is used for products on the hotlist; if the product is not on the hotlist,
  -- then the standard discount rate applies that is used for all products ordered through the webservice
  if is_on_hotlist(p_product_id)
  then
    l_product_discount:= get_hotlist_product_discount;
  else
    l_product_discount:= get_standard_product_discount;
  end if;
  l_discount:= l_discount + l_date_discount + l_product_discount;
  l_standard_price:= p_quantity * get_product_price( p_product_id);
  -- calculate the price as the standard_price times the discount-percentage
  l_price:= l_standard_price * (100 - l_discount)/100;
  -- price is calculated in Euros; the quote may hvan been requested in a different currency (p_currency)
  -- and must be converted in that case
  l_quote:= l_price * get_conversion_rate('eur', p_currency);
  return l_quote;
end get_quote;

One of the components required for implementing this logic is the function get_conversion_rate. This is a PL/SQL function that derives it functionality from a WebService that provides Currency Conversion or better said: Currency Exchange Rates, for example: XMethods Currency Exchange Rates (see Tutorial for an example of using this webservice). To try this web service, see http://www.mindreef.net/tide/scopeit/start.do?referer=xmethods&url=http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl

We need to expose a PL/SQL Function that internally invokes this WebService to obtain the Currency Exchange Rate. In this post, we will use a WebService Client written in PL/SQL using the UTL_HTTP supplied package.

Consuming a WebService in PL/SQL using an all PL/SQL WebService Client based on UTL_HTTP

The first step towards development of a PL/SQL Web Service client, is getting hold of the SOAP Request and Response Documents that are asociated with this WebService. We will write the PL/SQL WebService Client around these piece of XML.

There are several ways to get hold of the specific SOAP messages. One of the easiest is through the creation of a normal Java Client for the Web Service. Using JDeveloper 10g, this is an extremely simple process. Using the TCP Packet Monitor we can very quickly get the SOAP messages involved. The steps are as follows:

• Start JDeveloper 10g
• Create a new Application Workspace and Project (CurrencyConversionWebService)
• New Gallery, Business Tier – WebServices, Web Service Stub/Skeleton
• Enter the URL for the WSDL in the Wizard: http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl
• Finish the Wizard; the class CurrencyExchangeServiceStub gets generated
• In the View Menu, select the TCP Packet Monitor
• Start the TCP Packet Monitor
• Run the CurrencyExchangeServiceStub Java WebServiceClient
• Stop the TCP Packet Monitor. On the Data Tab of the monitor are the SOAP messages sent and received in the process of calling the WebService

Note: during my prototyping I ran into the JDeveloper Proxy Server Setting: see Menu Tools, Preferences, Web Browser and Proxy. I had the wrong Proxy Server configured and when I tried to invoke the WebService, it failed because of it.

The SOAP Message sent to invoke the WebService – sent to http://services.xmethods.net:80/soap in a Post Request using the HTTP/1.1 protocol:

<?xml version = '1.0' encoding = 'UTF-8'?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <ns1:getRate xmlns:ns1="urn:xmethods-CurrencyExchange" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <country1 xsi:type="xsd:string">us</country1>
         <country2 xsi:type="xsd:string">uk</country2>
      </ns1:getRate>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

The reply from the webservice looks as follows:

<?xml version = '1.0' encoding = 'UTF-8'?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <soap:Body>
      <n:getRateResponse xmlns:n="urn:xmethods-CurrencyExchange">
         <Result xsi:type="xsd:float">0.5357</Result>
      </n:getRateResponse>
   </soap:Body>
</soap:Envelope>

Creating a PL/SQL procedure that invokes the WebService

The approach for Oracle 9iR2 and 10g – based on the supplied UTL_HTTP package – is described in the article Calling Web Services from PL/SQL in the Oracle9i Database. This article demonstrates a rather generic approach – which I think must be very close to using the UTL_DBWS package. We will take a more pragmatic, to the point line of action – that amounts to the same thing. Another resource that came in handy: Oracle Database 10g – XML & SQL: Design, Build & Manage XML Applications in Java,C, C++ & PL/SQL, Mark Scardina, Ben Chang and Jinyu Wang, Oracle Press – McGrawHill Osborne; 2004; ISBN 0-07-222952-7.

We know what the SOAP messages look like. Now we will create a PL/SQL block that recreates the SOAP message and then invokes the proper UTL_HTTP command:

declare
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req utl_http.req;
  http_resp utl_http.resp;
  resp XMLType;
  i integer;

begin
  soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <ns1:getRate xmlns:ns1="urn:xmethods-CurrencyExchange" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <country1 xsi:type="xsd:string">us</country1>
         <country2 xsi:type="xsd:string">uk</country2>
      </ns1:getRate>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
';
 http_req:= utl_http.begin_request
            ( 'http://services.xmethods.net:80/soap'
            , 'POST'
            , 'HTTP/1.1'
            );
  utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));
  utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
  utl_http.write_text(http_req, soap_request);
  http_resp:= utl_http.get_response(http_req);
  utl_http.read_text(http_resp, soap_respond);
  utl_http.end_response(http_resp);
  resp:= XMLType.createXML(soap_respond);
  resp:= resp.extract('/soap:Envelop/soap:Body/child::node()'
                   , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
                   );
  i:=0;
  loop
    dbms_output.put_line(substr(soap_respond,1+ i*255,250));
    i:= i+1;
    if i*250> length(soap_respond)
    then
      exit;
    end if;
  end loop;
end;

The output from runnning this piece of PL/SQL in SQL*Plus is this

<?xml version='1.0' encoding='UTF-8'?>
<soap:Envelope
xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:soapenc='http://schemas.xmlsoap.org
/encoding/'
soap:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'><soap:Body><n:get

RateResponse xmlns:n='urn:xmethods-CurrencyExchange'><Result
xsi:type='xsd:float'>0.5342</Result></n:getRateResponse></soap:Body></soap:Envelope>

Turning this PL/SQL block into a proper PL/SQL Function that accepts the two countries as input-parameters is straightforward:

function get_conversion_rate
( p_country1 in varchar2 default 'us'
, p_country2 in varchar2 default 'us'
)
return varchar2
as
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req utl_http.req;
  http_resp utl_http.resp;
  resp XMLType;
begin
  soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <ns1:getRate xmlns:ns1="urn:xmethods-CurrencyExchange" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <country1 xsi:type="xsd:string">'||p_country1||'</country1>
         <country2 xsi:type="xsd:string">'||p_country2||'</country2>
      </ns1:getRate>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
';
  http_req:= utl_http.begin_request
             ( 'http://services.xmethods.net:80/soap'
             , 'POST'
             , 'HTTP/1.1'
             );
  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));
  utl_http.set_header(http_req, 'SOAPAction', '');
  utl_http.write_text(http_req, soap_request);
  http_resp:= utl_http.get_response(http_req);
  utl_http.read_text(http_resp, soap_respond);
  utl_http.end_response(http_resp);
  - - Create an XMLType variable containing the Response XML
  resp:= XMLType.createXML(soap_respond);
  - - extract from the XMLType Resp the child-nodes of the <soap:Body> element
  resp:= resp.extract('/soap:Envelope/soap:Body/child::node()'
                     , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
                     );
  - - extract from the XMLType Resp the text() nodes from the n:getRateResponse/Result element
  resp:= resp.extract('n:getRateResponse/Result/text()','xmlns:n="urn:xmethods-CurrencyExchange"');
  return resp.getClobVal();
end;

We can create this function in any schema that has access to the sys.UTL_HTTP package. Calling this Function can be done from PL/SQL or even SQL:

 select get_conversion_rate('uk','us')
 from   dual
 /
GET_CONVERSION_RATE('UK','US')
---------------------------------
1.8713

You can download the code for GET_CONVERSION_RATE here.

Resources

The approach for Oracle 9iR2 and 10g – based on the supplied UTL_HTTP package – is described in the article Calling Web Services from PL/SQL in the Oracle9i Database. This article demonstrates a rather generic approach – which I think must be very close to using the UTL_DBWS package. We will take a more pragmatic, to the point line of action – that amounts to the same thing. Another resource that came in handy: Oracle Database 10g – XML & SQL: Design, Build & Manage XML Applications in Java,C, C++ & PL/SQL, Mark Scardina, Ben Chang and Jinyu Wang, Oracle Press – McGrawHill Osborne; 2004; ISBN 0-07-222952-7.

Additional background is provided by this white paper “WEB SERVICES IN ORACLE DATABASE 10G AND BEYOND” by Ekkehard, Rohwedder, Oracle Corporation Kuassi Mensah, Oracle Corporation (written for Oracle Open World 2003) describes both consuming and publishing of WebServices from the database, DevTrends, The Active Database, Sample Code September/October 2003 Issue By: Cameron O’Rourke and Matt Piermarini, Oracle Corporation and of course the Oracle 9iR2 on line documentation for the UTL_HTTP package. For 10g see: UTL_HTTP in 10g.