Oracle BI EE 10.1.3.4.1 – Consuming Web Services in BI EE – Displaying Live Stock Quotes – Using UTL_HTTP and EVALUATE – Part 1

When i was at the BI Forum in Brighton, one of forum participants had a question on using Web Services in BI EE. BI EE currently does not support direct WSDL response consumption by passing a request. So, in order to achieve this one way is to write a pl/sql wrapper which would basically call the web service and then use EVALUATE within BI EE to display them. There are other approaches as well like writing a pipelined table function etc. We shall see the other approaches in a later blog entry.

Assume that we have a table as shown below containing the companies and their corresponding Stock Symbols.

The requirement is to display the live stock feeds(Day High as well as Day Low) from a live Web Service feed at http://www.webservicex.net/stockquote.asmx?WSDL within BI EE. One good thing about this service is that they offer a variety of web service feeds along with their statuses. In order to consume the stock feed, lets first create a pl/sql function which is nothing but a wrapper, that sends a request to the WSDL above and gets the response back. The request sent would pass the Stock Symbol as a parameter. The Pl/Sql function would basically extract the Day High and Day close (or any other parameter) values directly from the stock feed.

create or replace function stock_quote
( p_symbol in varchar2 default 'ORCL',
p_tag in  varchar2  default 'High'
)
return varchar2
as
  s_request varchar2(32000);
  s_response varchar2(32000);
  h_request utl_http.req;
  h_response utl_http.resp;
begin
  s_request:= '<?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>'||p_symbol||'</symbol>
    </GetQuote>
  </soap:Body>
</soap:Envelope>';
 h_request:= utl_http.begin_request
            ( 'http://www.webservicex.net/stockquote.asmx?WSDL'
            , 'POST'
            , 'HTTP/1.1'
            );
  utl_http.set_header(h_request, 'Content-Type', 'text/xml');
  utl_http.set_header(h_request, 'Content-Length', length(s_request));
  utl_http.write_text(h_request, s_request);
  h_response := utl_http.get_response(h_request);
  utl_http.read_text(h_response, s_response);
  utl_http.end_response(h_response);
  return substr(s_response,instr(s_response,'<'||p_tag||'>')+length(p_tag)+8,instr(s_response,'</'||p_tag||'>')-instr(s_response,'<'||p_tag||'>') - length(p_tag) - 8);
end;

As you see the above function basically does a string manipulation to extract the parameter values. Typically this is not a recommended approach. The recommended approach is to use an XMLType function or a Regex function to extract the parameter values. But in our case, the string is sufficient. If you are behind a proxy, ensure that you set the UTL_HTTP.set_proxy before the begin request function. To test this out, lets fire the function from command line.

select stock_quote('ORCL','Low') from dual;

As you see we get the Oracle stock quote high and low for the day directly from this function. Now, lets go to BI EE and call this function through EVALUATE.

As you see we have now displayed the response of a WSDL directly from BI EE. We shall look at other approaches in future.