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

24

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

24 Comments

  1. Hi
    i need call a wcf service. Your code is ok but i need to define the “behaviors” setting, in other words, the contract between service and the package oracle.

    how i do that?

    thx u
     
    Robert

  2. I was trying to invoke a HelloWorld program.
    The HelloWorld program has the process operation that needs to be called.
    I was getting the following error

    env:Serverjava.lang.NullPointerException

    Then I changed the code
    utl_http.set_header(http_req, ‘SOAPAction’, ”); — required to specify this is a SOAP communication

    to
    utl_http.set_header(http_req, ‘SOAPAction’, ‘process’); — required to specify this is a SOAP communication

    and it worked.

  3. Got the following error
    Error report:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00601: Invalid token in: ‘/env:Envelop’
    ORA-06512: at “SYS.XMLTYPE”, line 119
    ORA-06512: at line 34

    The reason is:

    resp:= resp.extract(‘/soap:Envelop/soap:Body/child::node()’
    , ‘xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”‘
    );

    Envelop is to be spelt as Envelope. This is as per the response message

  4. Claudio- Genius on

    Great !!!
    I just copied this example, changed the service web configurations, and it worked right away

    Thanks Lucas

  5. Pingback: Life Insurance blog

  6. I actually used the code above to call a web service for a project that I am working on, except that I had to make one change. I removed the call to ‘utl_http.set_header(http_req, ‘SOAPAction’, ”);’, since it gave me the following error:
    Server did not recognize the value of HTTP Header SOAPAction: .

  7. Getting the following error when using the sample code

    No proxy oracle 9i please help

    I am try to execute this function but i can a error: ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1022 ORA-12545: Connect failed because target host or object does not exist ORA-06512:

  8. Pingback: AMIS Technology blog » Blog Archive » Transaction Dependent Webservice Invocation…or how to call a webservice from a database trigger and make the call part of the transaction

  9. Christian Bang on

    @6. Robert, 7. Markus: I can confirm that the Oracle patch 9.2.0.8 fixes the bug with utl_http, HTTP 1.1 and “100 continue”.
    Now there is no longer a problem with WebServices hosted on IIS. Both Oracle 9.2.0.8 and 10g work! No nasty work-around required :-)

  10. Nice article, it explained exactly what I needed to know.
    I used JDeveloper 10.1.3.1.0 for creating the example SOAP messages from the WSDL and noticed that a few things have now a different name:
    New Gallery – Business Tier – WebServices – Web Service Stub/Skeleton
    Is now called:
    New Gallery – Business Tier – WebServices – Web Service Proxy
    And the TCP Packet Monitor is now called Http Analyzer.

  11. Good article. I was able to basically copy and paste the function into my package and it worked.

    You mentioned, ‘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. … There are several ways to get hold of the specific SOAP messages. ‘.

    You reviewed the use of JDeveloper. What are some of the other methods?

  12. Hi,

    I’ve JDeveloper 10.1.3.0.4 and ORacle 10g database server on my machine. I couldn’t finfd the path in JDeveloper

    New Gallery, Business Tier – WebServices, Web Service Stub/Skeleton

    Could you tell me how can I create a Web Service Stub/Skeleton?

    Regards

  13. Pingback: AMIS Technology blog » Blog Archive » Publishing PL/SQL “Services” as WebService using Oracle BPEL

  14. Hi,

    I am try to execute this function but i can a error:

    ORA-30625: method dispatch on NULL SELF argument is disallowed

    Have you got any idea for absolve this problem, Iam really thanks for you advice.

    Rui

  15. Hi,
    I was not able to capture SOAP payload :(
    I’m using middle-tier BPEL.
    Could somebody advise me how to get it?
    Thaaanks a lot in advance

  16. Markus Schneider on

    >> “100″ response back to continue when I used HTTP/1.1 and if I use and HTTP/1.0 post, I get a “404″ object not found status code.

    Hi Robert,

    this is a bug in utl_http because get_response is not implemented to handle HTTP 1.1 continue responses.

    The workaround mentioned to use HTTP 1.0 or 0.9 do not work with Microsoft IIS because results in a 404 response).

    In Metalink, see bug 2467239, 4210818, ….

    It is really a shame they did not fix (2 lines of code) this for this long time. I think in the latest 10g release it is fixed.

    I’ve opened a TAR (5002019.992) in december and was promised to get a patch for 9.2.0.7 and a fix for 9.2.0.8. Still waiting.

    Additionally the utl_http package is that closed implemented so you have no change to read the 100 reponse by your own and ignore it.

    2 possibilities:
    - write your own http support by using utl_tcp
    - put a proxy server between that removes the 100 response (e.g. http://www.squid-cache.org).

    Rgds, Markus

  17. Robert Corfman on

    I have tried this routine, and others similar to it have have successfully called some web services. Othertimes, I have received an HTTP response status code: “100″ response back to continue when I used HTTP/1.1 and if I use and HTTP/1.0 post, I get a “404″ object not found status code. I know I’ve gotten the soap formatted correctly (or it sure appears so), any idea what the problem could be?
    Note that this routine definitely appears to work in some cases so I fear it is something to do with the service I’m calling, but I’m hoping you may have some suggestions.

  18. Thanks for the article. I am this method in a lower version of PowerBuilder. But I do not have TCP Monitor and unable to get the payload. The above code worked fine for me.
    Can you please tell me any other tool instead of TCP packet monitor or any other easy method to get the soap Payload?

  19. Edward,

    Sorry, that kind of technical support is somewhat beyond the scope of my daily activities. I would suggest you try either MetaLink or the OTN Forums.

    best regards and good luck,

    Lucas

  20. Hi,

    I am try to execute this function but i can a error:

    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at “SYS.UTL_HTTP”, line 1022
    ORA-12545: Connect failed because target host or object does not exist
    ORA-06512: at “MGPSC.PCK_OSINERG”, line 189
    ORA-06512: at line 1

    Have you got any idea for absolve this problem, Iam really thanks for you advice

  21. Pingback: » Publishing PL/SQL “Services” as WebService using Oracle BPEL

  22. Pingback: » Publishing PL/SQL Based WebServices