Consuming a REST web service with Oracle

March 12, 2009

SOAP web services dominate in a SOA environment. However, there is a new kid on the block: REST. This is a more lightweight approach to exchange data over HTTP, which is becoming more and more popular with web mashups and the web 2.0 in general. Amazon, Twitter, Yahoo all expose their APIs via REST.
REST is an architecture style for designing networked applications. The idea is that, rather than using complex mechanisms such as CORBA, RPC or SOAP to connect between machines, simple HTTP is used to make calls between machines.
In this post I’ll show you how you can consume a REST web service via pure SQL in Oracle.
I have picked the Google Translate API as a show case. The API exposes a RESTful infrastructure via HTTP GET. You may want to use this API in a document warehousing situation where you need to translate unstructured documents on the fly.
The API exposes two methods. The Detect Language method allows us to identify a language by submitting a sentence or phrase. The Translate Language method allows us to translate one ore more pieces of texts into one or more languages. Details on parameters etc. can be found in the documentation. The Google Translate web service then returns a JSON object with the language detected or the translated text, depending on the method chosen.
JSON is a better fit [than XML] for Web services that power Web mashups and AJAX widgets due to the fact that it is essentially serialized Javascript objects which makes it fit better client side scripting which is primarily done in Javascript.
In Oracle we can use the HTTPURITYPE function to perform an HTTP GET to consume a RESTful web service. See one of my previous posts for some more details on HTTPURITYPE.
Ok, so let’s finally get started. In a first step we populate a table with the text we want to translate and its source language. For this I have picked an excerpt from the poem Rhyme of the Ancient Mariner by Samuel T. Coleridge. This is a great poem and if you get a chance read it. There is also an Iron Maiden song about it, if you want an executive summary…

Next we just run the following query against the web service and parse the JSON object. In Oracle no native support for JSON is provided. So we’ll just parse this with string functions and regular expressions. I have also come across some third party JSON PL/SQL package but have not tried this out yet. If you do it would be nice to leave me some feedback on it.

We will now go through the above SQL statement step by step.
In the innermost SQL of the above query we construct our query string and bind the document text and source language values from our table into this query string.

Note the use of the utl_erl.escape function. We use this function to URL encode our Coleridge poem.
The result is the following query string:

In the next innermost SQL we perform an HTTP Get to the Google Translate REST API.

Google returns the translated text in a JSON object:

In a last step we now need to multiply our JSON object by the number of target languages and then parse out the translated text. This will give us the translation:

As per Google terms and conditions you are only allowed to store this information for caching purposes, but are not allowed to persist, e.g. in a database.