Invoking RESTful WebService - OWB11gR2

--

REST or SOAP style architecture are the most common ways used to implement a webservice.

I will be demonstrating how RESTful Webservice's can be invoked\consumed.

Thinking about how to do it ?? Well, Code Templates(Knowledge Module) again to the rescue.

One template to invoke different web services.
A Template to invoke web service for every row in the table or query generated by LCT part in the code template mapping.

Lets have a look at a simple code template mapping which invokes a webservice and parses the return XML to extract the data.

Mapping uses Yahoo's Place finder geocoding webservice as an example to get latitude , longitude , county and Postal code for different street addresses.















Using the Yahoo's geocoding webservice -

To use geocoding webservice you need an Applicaion_Id (appid) key from yahoo, you can generate it from here(There is no charge to it).

The format to invoke the webserivce in this case is http://where.yahooapis.com/geocode?q=STATE,CITY,STATE&appid=[yourappidhere]

If you hit the URL below on any browser you should see the XML (that's one of the beauty of REST )
http://where.yahooapis.com/geocode?q=1600+Pennsylvania+Avenue,+Washington,+DC&appid=[yourappidhere]




Logical View of the code template map -


(if needed , Click to expand it)

LCT requires two additional columns REST_XML and SUCCESS_FLAG.
The mapping uses the expression operator EXP_REST_XML to generate these columns with default value NULL.
REST_XML column will hold the return xml after invoking the webservice
SUCCESS_FLAG will indicate whether the webservice invoke was successful or failed with values 1 or 0 respectively.

REST_XML datatype can be set to XMLTYPE or VARCHAR2 or CLOB depending upon the size of XML you are expecting.
SUCESS_FLAG datatype can be NUMBER or INTEGER.

Expression operator EXP_LAT_LON_ZIP extracts latitude,longitude,county and postal code from the REST_XML using Oracle's EXTRACTVALUE function.
Expression Conditions are like: TO_NUMBER(EXTRACTVALUE( INGRP1.REST_XML ,'//latitude/text()'))


Execution View of the code template map -



Code templates LCT_RESTWS_TO_ORACLE and DEFAULT_ORACLE_TARGET_CT are applied to the mapping.

LCT_RESTWS_TO_ORACLE has few input parameters.

Required Parameter -
WEBSERVICE_URL - URL of the webservice to be invoked. It can be any RESTful webservice.
The parameters in the URL should be replaced by "%s".
In this case the value is
http://where.yahooapis.com/geocode?q=%s,%s,%s&appid=[yourappidhere]
The state,city and state are replaced by "%s".

Optional Parameters -
PARALLEL_THREADS - Number of webservices that will be parallely spawned
PROXY_HOST - proxy host name
PROXY_PORT - proxy port number
and others

Executing the code template mapping -

Parsed Output Data  -

No Postal Code for the address on 2nd row. It's the data, nothing wrong with the mapping :-)



Code Template Design -

LCT_RESTWS_TO_ORACLE the template which does the most important work.

The heart of the template is the Jython Task "6_REST_WS_CALL".
It uses the input WEBSERVICE_URL parameter to get URL format.
Replaces  "%s" character with data from the table constructing the required URL and invokes the webservice via HTTP GET Method.
The output is written to WORK TABLE (C$ table ). This work table is input to DEFAULT_ORACLE_TO_TARGET which parses the XML to extract the required values.

Large XML can be stored into tables and can be easily parsed leveraging XDB or SQL Override feature or Table functions.
RESTful webservices can also return data in different format like csv, text, etc.
They can also manipulate data using HTTP POST\DELETE methods.
Template can be easily customized to handle any condition.

The template can be downloaded from here and web services can be invoked both internal and external to the organization.