Oracle REST Data Services on Autonomous Transaction Processing Database

Whenever I give a presentation, write a blog post, or make a video, I like to run all of my examples and demos locally and on an Oracle Cloud instance. I keep a Virtual Box machine or a Docker container ready on my laptop just in case I can't get to the internet, but for speed and dependability, you just can't beat a cloud instance.

You might also like:  How I Exposed an Entire PL/SQL-Based Application as REST APIs in Less Than a Week

I like to have some automation scripts handy to create and destroy my environments, and depending on what I'm doing, that can sometimes take a while.

So when Oracle's Autonomous Transaction Processing Database was released, I was excited by how fast I could spin up a new instance. It takes a few minutes instead of around an hour for other options. Oracle wanted to make sure ATP was solid, so they initially launched with a limited feature set and new features have been making their way into Oracle's Autonomous Transaction Processing Database almost every month.

At KScope19, it was announced that Oracle REST Data Services is now available on ATP.

I was scheduled to give my 'Creating REST APIs Is Fast, Easy, and Secure with Oracle REST Data Services' talk on Wednesday, and I like to live dangerously, so I decided (Tuesday afternoon) to convert my demos over to ORDS on ATP. I figured if the worst case was that I couldn't figure it out, I'd just use my Docker container.

It's Going to Be Hard, Right?

I already had a couple of ATP instances that I use for things. Since I wanted to do a live demo, I decided to spin up a new instance just in case I expose anything that could be used to get in. It took under 5 minutes to fully create and start up a brand new ATP database. After that, I downloaded my Client Credentials (Wallet) and connected with SQL Developer. There are lots of examples for this part, so I'll move on to the new stuff.

New User

I try to do as little as possible as the Admin user so I created a rest_demo user with a couple of tables and a view.

CREATE USER rest_demo IDENTIFIED BY NotMyRealPW;
ALTER USER rest_demo TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO rest_demo;

GRANT CREATE TABLE,
CREATE VIEW,
CREATE SEQUENCE,
CREATE PROCEDURE,
CREATE TYPE,
CREATE SYNONYM
TO rest_demo;

CREATE TABLE REST_DEMO.COOL_DATA_T (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  NAME VARCHAR2(20) NOT NULL,
  MINUTES NUMBER,
  CONSTRAINT COOL_DATA_PK PRIMARY KEY (ID) ENABLE
);

insert into REST_DEMO.cool_data_t(name, minutes) values ('first', 24);
insert into REST_DEMO.cool_data_t(name, minutes) values ('second', 23);
insert into REST_DEMO.cool_data_t(name, minutes) values ('third', 52);
insert into REST_DEMO.cool_data_t(name, minutes) values ('fourth', 235);
insert into REST_DEMO.cool_data_t(name, minutes) values ('fifth', 874);

commit;

CREATE or replace view REST_DEMO.COOL_DATA_V as select ID, NAME, MINUTES from REST_DEMO.cool_data_t;

CREATE TABLE REST_DEMO.GALLERY (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL ,
  TITLE VARCHAR2(1000) NOT NULL ENABLE,
  CONTENT_TYPE VARCHAR2(1000) NOT NULL ENABLE,
  IMAGE BLOB NOT NULL ENABLE,
  CONSTRAINT GALLERY_PK PRIMARY KEY (ID) ENABLE
)
/

Create a Test API

I used the SQL Developer 'Auto REST' feature to REST enable the schema and a table. (PL/SQL exported below in case you want to try it.)

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'REST_DEMO',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'api',
                       p_auto_rest_auth => FALSE);

    commit;

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'REST_DEMO',
                       p_object => 'COOL_DATA_T',
                       p_object_type => 'TABLE',
                       p_object_alias => 'cool_data',
                       p_auto_rest_auth => FALSE);

    commit;

END;

Get the URI

Now I just need the URI to test my new service. Here are the steps:

1. Log into your Oracle Cloud Dashboard.
2. From the menu, select 'Autonomous Transaction Processing'.
3. From the instance menu, select 'Service Console'. Or click on your Instance Name then click the 'Service Console' button.
4. Click 'Development'.
5. In the Rest Data Services box, click the "Copy URL" button.

Test It

For a simple fast test, I just used an internet browser with the following URI:https://someIdstuff-blaineords.adb.my-region-1.oraclecloudapps.com/ords/api/cool_data

Well, That Was Easy

To be honest, I expected this to be a lot harder and that I'd have problems. I ran through all of the demos for my presentation and they worked on the first try.

The thing I spent the most time on was re-checking everything. Since it was so easy, I thought I had connected to one of my pre-existing cloud databases.

Further Reading

REST API Industry Debate: OData vs GraphQL vs ORDS

Creating an Oracle REST Data Services Docker Image