Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on May 19, 2009

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.

About these ads

3 Responses to “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”

  1. [...] to a solution. I will give props again to Venkat for creating a blog a while back about using web services in OBIEE. His post got me thinking about this type of integration.  Although his example is intrusive it [...]

  2. srini said

    Venkat,

    Thanks for the blog. The underlying assumption here is that we have the ability to invoke external web services from database server. In my case, this is not true due to the network policies etc.

    Is there any other method that enables invoking web services from the OBIEE repository? Thanks again.

    Srini

  3. niazi said

    nice post…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: