REST and Oracle APEX Listener

Posted on by

Update: November 2011, This functionality is available in Oracle Application Express Listener 1.1 and later, note the syntax of bind variables has been changed to match that used in other Oracle products: {person} becomes :person. The examples below have been changed to reflect the updated syntax

Oracle Application Express Listener Early Adopter Release 1.1 introduces a feature called Resource Templates that enables quick and easy configuration of RESTful web services for exposing and manipulating data stored in an Oracle Database via HTTP.

Hello REST World

Resource Templates are a mechanism to bind a URI Template to an SQL Query or a PL/SQL Block. For example a Resource Template could bind the URI Template: hello?who={person} to the following SQL Query:

select 'Hello ' || :person || ' from Oracle Application Express Listener' greeting from dual

Note how the {person} parameter in the URI Template is bound to a similarly named variable (:person) in the SQL query. Suppose our Application Express Listener is deployed at: http://example.com/apex/ then a HTTP request like the following:

GET /apex/hello?who=World HTTP/1.1
Host: example.com

will generate a HTTP response like the following:

HTTP/1.1 200 OK
ETag: "vovmx3pCoUq/DcdTY/hXusiq1QU="
Content-Type: application/json

{
 "greeting": "Hello World from Oracle Application Express Listener"
}
  • When the path: hello?who=World is received by the Application Express Listener, it is matched against our Resource Template’s URI Template.
  • The concrete value World is used as the value for :person parameter
  • The results of the Resource Template’s SQL Query are transformed into a JSON document
  • The JSON document consists of a JSON object. Each column in the query result set is mapped to a property of the JSON object.

Entity Tags and Conditional Operations

Note the ETag header in the HTTP response of the previous example. An ETag is a version identifier for a resource, in other words whenever the content of a resource changes, the value of its ETag header also changes. Resource Templates can generate an ETag header as follows:

  • By generating a secure hash of the resource’s contents. This is the default mechanism, but requires buffering the content of the resource to generate the hash
  • Sometimes the resource or its constituent parts will already contain information to uniquely identify a version of a resource, so alternatively a SQL query may be defined that generates a unique version identifier. The result set of the SQL query is securely hashed and used as the ETag value. This option does not require buffering of the resource, but care needs to be taken to ensure the SQL query does guarantee a different result set for each resource version
  • No ETag. Naturally there is an option to suppress generation of an ETag header

Resource Templates have an automatic capability for handling HTTP Conditional Operations where a If-None-Match or If-Match header is included with a request. To follow on from the example in the previous section a HTTP request like the following:

GET /apex/hello?who=World HTTP/1.1
Host: example.com
If-None-Match: "vovmx3pCoUq/DcdTY/hXusiq1QU="

will generate a HTTP response like the following:

HTTP/1.1 304 Not Modified
  • Again when the path: hello?who=World is received by the Application Express Listener, it is matched against our Resource Template’s URI Template.
  • The results of the Resource Template’s SQL Query are transformed into a JSON document
  • A secure hash of the JSON document is generated and is compared to the value of the If-None-Match header
  • Since the values are identical the Application Express Listener generates a 304 Not Modified response and does not include the resource’s content in the response
  • So in the case where a resource representation has not changed since the last time a client retrieved it, the overhead of transmitting the resource’s contents from server to client is avoided.

Media Resources

Resource Templates can be used to generate any type of resource not just the default of JSON documents. Resource Templates support a special type of SQL query called a Media Resource Query. Media Resource Queries consist of an SQL Query that generate a single result row with two columns in the row. The first column denotes the resource’s MIME type, the second column contains the resource’s content. For example if we have a Resource Template with a URI Template of : customers/locations/{state} bound to the following query:

select 'application/vnd.google-earth.kml+xml', xmlquery('
<k:kml xmlns:k="http://www.opengis.net/kml/2.2">
 <k:Folder>
  <k:name>{$state}</k:name>
{ for $c in ora:view("oe","customers")/ROW
let $loc := $c/CUST_GEO_LOCATION/SDO_POINT
where $c/CUST_ADDRESS/STATE_PROVINCE = $state
return
   <k:Placemark>
    <k:name>{concat($c/CUST_FIRST_NAME," ",$c/CUST_LAST_NAME)}</k:name>
     <k:Point>
      <k:coordinates>{$loc/X/text()},{$loc/Y/text()},0</k:coordinates>
     </k:Point>
    </k:Placemark>
}
</k:Folder>
</k:kml>' passing upper(:state) as "state" returning content) from dual

Then a HTTP request like:

GET /apex/customers/locations/ny HTTP/1.1
Host: example.com

will generate a HTTP response like:

HTTP/1.1 200 OK
ETag: "P4eISUyr2BZwtN1VnQHYyV556wU="
Content-Type: application/vnd.google-earth.kml+xml

<kml xmlns="http://www.opengis.net/kml/2.2">
 <Folder>
  <name>NY</name>
  <Placemark>
   <name>Blake Seignier</name>
   <Point>
    <coordinates>-76.14607,43.106533,0</coordinates>
   </Point>
  </Placemark>
  ...
 </Folder>
</kml>
  • In this example we leverage the powerful XMLQuery SQL function to generate a standards compliant KML file, thus quickly making our customer data available to third party GIS applications

And There’s More…

The above gives a brief flavour of what Resource Templates can accomplish, but there’s lots more functionality, here’s a few highlights:

  • HTTP Headers can be bound to Queries/PLSQL blocks in a similar manner to URI Template parameters, allowing responses to vary depending on HTTP headers received (e.g. localizing the response based on the Accept-Language header
  • Similar support for HTML Forms enables form fields to be bound to Query/PLSQL parameters, making processing of HTML forms straightforward, including forms containing file upload fields
  • Extraction of HTML5 Microdata from HTML resources to enable production of an alternate JSON representation of the microdata

Try it out, Learn More

You can download the Early Adopter Release here. The Developer Guide provides all the information needed to get started with Resource Templates.

The latest version of Oracle Application Express Listener can be downloaded here. The Developer Guide provides all the information needed to get started with Resource Templates.

Your Feedback

This is an Early Adopter release, it is not a finished product, As ever we encourage you to try out Oracle Application Express Listener and tell us what you think in the Oracle Application Express Listener forum. We look forward to your feedback.