REST (almost) Directly from the Database - ORDS

On of my customers had the major portion of his business logic in the Oracle database. There were complex views and multiple PL/SQL packages. More PL/SQL business logic was in a large enterprise wide Forms application, that could also be extracted and moved to the database.

To serve contemporary responsive Angular based applications, there was a layer on top of the database built on Weblogic Server and ADF Business Components (ADF BC) providing RESTful services for the Angular frontend.

The ADF BC layer can hold no state (as it would in a regular ADF Faces application). Furthermore, it has almost no business logic because the logic lives in the database. 

With no holding state and providing almost no business logic, the WebLogic ADF layer turns out to be a heavyweight (and costly) SQL-to-JSON gateway. One approach to get rid of it is a REST implementation with an open source framework like Spring on a lightweight container like Tomcat. But there is a learning curve from the well known PL/SQL and the mostly declarative ADF BC to pure Java coding. And - even Tomcat containers need to be administered and maintained. In contrast to WebLogic they are just containers. EM console, Security or the Node Manager are not replaced easily.

In such a scenario, why not provide RESTful services directly (1)  out of the database? I would not recommend this approach in any situation but once you have a very database centric existing architecture it is certainly worth a closer look.

(1) 'Directly' is not the whole truth. If you run your own Oracle database on premise, you need to deploy ORDS to a Tomcat or equivalent web server as the embedded PL/SQL gateway does not support ORDS. With Oracle ATP, ORDS is preconfigured.

So let's expose a table in our Oracle ATP database as REST endpoints. First let's create a special table for that and insert a couple of records:

-- a handy table for ORDS demos

CREATE TABLE headlines  

(id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  

date_time TIMESTAMP DEFAULT SYSDATE,  

headline VARCHAR2(4000) NOT NULL,  

category VARCHAR2(20) DEFAULT 'COMMON' NOT NULL ,  

CONSTRAINT headlines_pk PRIMARY KEY (id)  );

INSERT INTO headlines VALUES (NULL, NULL, 'Hello world!', 'DEMO');  

INSERT INTO headlines (headline )VALUES ('We are still here.');  

COMMIT;

-- a very basic function to demo

-- authorization

CREATE OR replace FUNCTION f_auth_check(p_token IN varchar2)

RETURN pls_integer

IS

v_secret varchar2(100) := 'mys3cr3tt0ken';

BEGIN

IF p_token = v_secret THEN

RETURN 0;

ELSE

RETURN -1;

END IF;

END;

/

I am using a not so new 12c feature to create the primary key value automatically to spare the hassle with the sequence and the trigger.

Now we are ready to define RESTful services with our ATP database. This could be easily done with the APEX user interface and the ORDS option of the SQL Workshop:

There is a hr sample schema, Once this is loaded, we see how it works. The ORDS components are:

• Module: Contains a logical set of services. Defines a base URL. 
• Template: Defines the URI for its handler methods
• Handler: Maps an http method to a database resource (like a GET to a select statement)
• Parameter: Can be defined for each handler to retrieve or set specific data in the request

Browsing through the hr sample gives a very good idea how it works. The same could be applied to the HEADLINES table without any coding in the APEX UI. But is this a good idea? I say no for the following reasons:

• How to save the knowledge of what was done in a hundred mouse clicks? You could take a 100 screenshots like I do for this blog, but this is a lot of work, believe me. And reproducing means clicking it all again together.
• If this set of RESTful services is for a complex real world application, how can that be rolled out through multiple test instances to production?
• Versioning and documentation of changes?

To solve this problem, the PL/SQL API of ORDS is just great. The following script uses the ORDS API to define a module, templates, handlers and parameters to expose database functionality as RESTful service. See inline comments for explanation:

-- script to create a ORDS RESTful service

begin

-- the schema must be enabled for ORDS

-- from admin account, not the working schema

/*/ ORDS_ADMIN.ENABLE_SCHEMA(

p_enabled => TRUE,

p_schema => 'northwind',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'northwind',

p_auto_rest_auth => TRUE

); */

-- create the module with the base path

ORDS.DEFINE_MODULE(

p_module_name => 'news',

p_base_path => '/news/'

);

-- create the template based on the module

-- the pattern can contain one or more :parameters if required

ORDS.DEFINE_TEMPLATE(

p_module_name => 'news',

p_pattern => 'headlines/'

);

-- finally the handler(s) mapping a SQL CRUD operation

-- to an http method

-- first a GET to retrieve all content of the entity/table

ORDS.DEFINE_HANDLER(

p_module_name => 'news',

p_pattern => 'headlines/',

p_method => 'GET',

p_mimes_allowed => 'application/json',

p_source_type => ords.source_type_collection_feed,

p_source => '

select * from headlines

'

);

-- insert of a new record, mapped to the http PUT

-- the headline is handed over via URL parameter

-- this does not require explicit definition

ORDS.DEFINE_HANDLER(

p_module_name => 'news',

p_pattern => 'headlines/',

p_method => 'PUT',

p_mimes_allowed => 'application/json',

p_source_type => ords.source_type_plsql,

p_source => '

begin

insert into headlines (headline) values (:headline);

end;

'

);

-- second, a handler handle a specific record

-- identified by the primary key / id column

-- it requires a new template with a parameter

ORDS.DEFINE_TEMPLATE(

p_module_name => 'news',

p_pattern => 'headlines/:id'

);

-- the handler to GET a specified record

ORDS.DEFINE_HANDLER(

p_module_name => 'news',

p_pattern => 'headlines/:id',

p_method => 'GET',

p_mimes_allowed => 'application/json',

p_source_type => ords.source_type_query_one_row,

p_source => '

select * from headlines

where id = :id

'

);

-- DELETE a record

-- implement exception handling for a business rule returning http 403

-- if no record was processed return http 404

-- a (very basic) authorization check failing returns http 401

ORDS.DEFINE_HANDLER(

p_module_name => 'news',

p_pattern => 'headlines/:id',

p_method => 'DELETE',

p_mimes_allowed => 'application/json',

p_source_type => ords.source_type_plsql,

p_source => '

DECLARE

not_permitted EXCEPTION;

not_authorized EXCEPTION;

v_temp pls_integer;

BEGIN

IF f_auth_check(:token) <> 0 then

raise not_authorized;

END IF;

SELECT sysdate - CAST(date_time AS DATE) INTO v_temp

FROM HEADLINES

WHERE id = :id;

IF v_temp > 1 THEN

raise not_permitted;

END IF;

delete from headlines

where id = :id;

EXCEPTION

WHEN no_data_found THEN

:status_code := 404;

WHEN not_permitted THEN

:status_code := 403;

:message := '' {"message": "Cannot delete headline older than one day."}'' ;

WHEN not_authorized THEN

:status_code := 401;

WHEN OTHERS THEN

:status_code := 500;

end;

'

);

-- definition of an input parameter

-- for the security token

ORDS.DEFINE_PARAMETER(

p_module_name => 'news',

p_pattern => 'headlines/:id',

p_method => 'DELETE',

p_name => 'X-TOKEN',

p_bind_variable_name => 'token',

p_source_type => 'HEADER',

p_access_method => 'IN'

);

-- definition of an output parameter

-- for a user defined message

ORDS.DEFINE_PARAMETER(

p_module_name => 'news',

p_pattern => 'headlines/:id',

p_method => 'DELETE',

p_name => 'X-MESSAGE',

p_bind_variable_name => 'message',

p_source_type => 'HEADER',

p_access_method => 'OUT'

);

-- all ORDS definitions need to be committed!

commit;

end;

/

Once the script has run successfully, we can browse through the created services in the APEX user interface, gather some endpoints and test them out. The created module is named news, it contains two templates and each template defines two handlers. Here's the GET method of the first template. 

Copy and try the endpoint:

I am using the Advanced REST client for Google Chrome. See our headlines data in the JSON result set. ORDS provides more information in the result like pagination, shown on the bottom.

The PUT request can be used to create new records in the HEADLINES table. 

The DELETE request obviously deletes a record. This one is a bit special, look at the PL/SQL up in the script. It implements a very basic idea of a token based authorization and a simple business rule. They are mapped to http status codes. RESTful services should always explain what they are doing, so if something is supposed to fail, return relevant information. Look what happens when I try to delete a headline, that is stored already more than a day in the database:

This is pretty amazing. With not too much work we can expose literally any database table or PL/SQL code to the world. Of course this means, the service has to be secured properly. This can be the topic of another post.