Creating RESTful APIs with Oracle Application Express Listener

, @cdivilly

Consulting Member of Technical Staff

Program Agenda

  • Overview
    • Oracle Application Express Listener
    • REST & RESTful Web Services
    • Oracle SQL Developer integration
  • Building RESTful Web Services
    • Example RESTful Web Services
    • Demo Application
  • Questions and Answers as we go!

Oracle Application Express Listener

Binds Oracle Database to the Web

  • Provides: PL/SQL Gateway, RESTful Services, FOP-PDF engine, and more
  • Supports: WebLogic, GlassFish and standalone Java containers
  • Connect to multiple Oracle databases
  • Integrated with SQL Developer and Application Express
  • Get it on OTN, latest version is 2.0.5

REpresentational State Transfer (REST)

The Architectural Style of the Web

  • Model resources, not actions:
    • GET /apex/ukoug/hr/employees/
    • GET /apex/ukoug/GetAllEmployees/
    • Use nouns, not verbs
  • Uniform operations on all resources:
    • Many nouns, just a few uniform verbs
  • Stateless requests, state transitions communicated via hyper-links.

HTTP Methods

Uniform Verbs for interacting with all resources

Method Purpose Classification Database Operation*
GET Retrieve resource Safe, Idempotent SELECT
HEAD Retrieve metadata Safe, Idempotent SELECT
OPTIONS Methods supported by resource Safe, Idempotent N/A
PUT Create or replace resource Idempotent MERGE, UPDATE
DELETE Delete resource Idempotent DELETE
POST Anything! Usually create resource Unsafe INSERT

* These are the most prevalent mappings to database operations only.

Modelling Resource Collections

Adding a new Resource to the Collection

HTTP Request

POST .../hr/employees/ HTTP/1.1
Content-Type: application/json

 "ename": "Bloggs",
 "job": "ANALYST",
 "mgr": 7566,
 "hiredate": "2013-11-13T00:00:00Z",
 "sal": 5000,
 "deptno": 20


 insert into emp (ename,job,mgr,hiredate,sal,deptno) 
          values (:ename,:job,:mgr,:hiredate,:sal,:deptno);
  • Listener parses the JSON payload and maps each property to a bind variable.
  • Listener can similarly parse HTML Form data.
  • Only simple key to value mappings supported.
  • Alternatively use the :body parameter (a BLOB value) and parse manually.

Resource Collections Continued...

Enumerating all Resources in Collection

HTTP Request

GET.../hr/employees/ HTTP/1.1

HTTP Response

HTTP/1.1 200 OK
Content-Type: application/json

 "items": [
   "uri": {
    "$ref": ""
   "empno": 7499,
   "ename": "ALLEN"

SQL Query

select empno "$uri", empno, ename from emp

Column names starting with $ are treated as hyperlinks.

Resource Collections Continued...

Retrieving a single resource

HTTP Request

GET .../hr/employees/7938 HTTP/1.1

HTTP Response

HTTP/1.1 200 OK
ETag: "p2GjdWn51...ax6g=="
Content-Type: application/json

 "empno": 7934, "ename": "MILLER",
 "job": "CLERK", "mgr": 7782,
 "hiredate": "1982-01-23T00:00:00Z",
 "sal": 1300, "deptno": 10,
 "manager": {
  "$ref": "https://.../hr/employees/7782"
 "mgr_name": "CLARK",
 "dept": {
  "$ref": "https://.../hr/employees/?dept=10"
 "dept_name": "ACCOUNTING"

URI Template


SQL Query

select e.*, 
       e.mgr "$manager", 
       m.ename "mgr_name",
      './?dept=' || e.deptno "$dept",
       d.dname "dept_name"
 from emp e, emp m, dept d
 where e.empno = :id and m.empno = e.mgr and d.deptno = e.deptno
  • URI Template {id} parameter is mapped to sql :id bind parameter
  • Common pattern: Useful documents require table joins

Resource Collections Continued...

Conditional GET

HTTP Request

GET .../hr/employees/7499 HTTP/1.1
If-None-Match: "A7rDBCEuC8pkiZrA7ifn7kkAgPUxn2ZjMTV03VhNSJ9Pl9QvCDF09UKXBa8DGE6128D5rJxjYOE2cXHp+npe0Q=="

HTTP Response

HTTP/1.1 304 Not Modified
  • On first request, client caches ETag value.
  • On subsequent requests client adds If-None-Modified header.
  • If resource has not changed, server responds with 304 Not Modified.
    • Avoids re-transmission of already up-to-date data
  • If resource has changed, server responds wiht the new resource, including ETag header with a new value.

Resource Collections Continued...

Updating a Resource

HTTP Request

PUT .../hr/employees/7499 HTTP/1.1
If-None-Match: "A7rDBCEuC8pkiZrA7ifn7kkAgPUxn2ZjMTV03VhNSJ9Pl9QvCDF09UKXBa8DGE6128D5rJxjYOE2cXHp+npe0Q=="
Content-Type: application/json



 l_id number;
 l_location varchar2(255);
 l_id := :id;
 l_location := './' || l_id;

 update emp set
  ename = :ename,
  job = :job,
  mgr = :mgr,
  hiredate = :hiredate,
  sal = :sal,
  comm = :comm,
  deptno = :deptno
 where empno = l_id;
 :location := l_location;

Resource Collections Continued...

Deleting a Resource

HTTP Request

DELETE .../hr/employees/7499 HTTP/1.1
If-None-Match: "A7rDBCEuC8pkiZrA7ifn7kkAgPUxn2ZjMTV03VhNSJ9Pl9QvCDF09UKXBa8DGE6128D5rJxjYOE2cXHp+npe0Q=="

HTTP Response

HTTP/1.1 204 No Content


 delete from emp where empno = :id;
 :status := 204;

Demo Application

See RESTful Services in action

Click here to see the demo.

Thank You!

If you enjoyed today's presentation...