REST Services now available for Oracle Autonomous Database

The Autonomous Database is offered via two services in our Oracle Cloud, Autonomous Data Warehouse and Autonomous Transaction Processing.

As of today, you can now develop and deploy RESTful Services for your Autonomous Database with native Oracle REST Data Services (ORDS) support.

What is ORDS?

This video is a comprehensive overview of REST and how ORDS provides what you need to deliver RESTful Services for your Oracle Database.

You can find more information about ORDS on our product page:

https://www.oracle.com/rest

Developing RESTful Services in Autonomous Database

You have several development interfaces available, including:

• SQL Developer (desktop)
• APEX
• PL/SQL API

From SQL Developer on your desktop, you can connect to your Autonomous Database and REST enable tables and views and/or develop custom RESTful Services based on your SQL and PL/SQL code.

In this exercise, I’m going to create a new user, enable it for REST (SQL Developer Web + RESTFul Services) access, create a table, and publish a RESTFul Service for it.

Let’s create our user first – I do NOT recommend you use ADMIN account for application development work, just like you wouldn’t use SYS or SYSTEM to create your application tables.

Executed directly from my SQL Developer Web worksheet:

BEGIN
CREATE USER jeff IDENTIFIED BY ReallyGoodPasswordNot;
 
GRANT CONNECT, resource TO jeff;
 
ALTER USER jeff QUOTA UNLIMITED ON DATA; -- this part is important if you want to do INSERTs
 
BEGIN -- this part is so I can login as HR via SQL Developer Web
    ords_admin.enable_schema (
        p_enabled               => TRUE,
        p_schema                => 'JEFF',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'tjs', -- this flag says, use 'tjs' in the URIs for JEFF
        p_auto_rest_auth        => TRUE   -- this flag says, don't expose my REST APIs
    );
    COMMIT;
END;
/

With this executed, I can now open a SQL Developer Web session, as my new user JEFF. My URL would look something like this:

 https://some-id.adb.co-citry-1.oraclecloudapps.com/ords/tjs/_sdw/?nav=worksheet 

I’ll be prompted for the username and password (use the JEFF account), and then I can create my table, data, and RESTful Service.

So let’s do that. Here’s a table, some data, and a very simple GET Handler (to select * from our table).

CREATE TABLE hello_world (
     id        INTEGER,
     message   VARCHAR2 (25),
     CONSTRAINT hello_world_pk PRIMARY KEY (id)
 );
 
INSERT INTO hello_world (id, message) VALUES (1, 'Hello');
INSERT INTO hello_world (id, message) VALUES (2, 'world,');
INSERT INTO hello_world (id, message) VALUES (3, 'it''s');
INSERT INTO hello_world (id, message) VALUES (4, 'me,');
INSERT INTO hello_world (id, message) VALUES (5, 'Jeff.');
 
BEGIN
     ords.enable_schema (
         p_enabled               => TRUE,
         p_schema                => 'JEFF',
         p_url_mapping_type      => 'BASE_PATH',
         p_url_mapping_pattern   => 'tjs',
         p_auto_rest_auth        => TRUE
     );
 ords.define_module (    
        p_module_name            => 'test',
        p_base_path              => '/test/',
        p_items_per_page         => 25,
        p_status                 => 'PUBLISHED',
        p_comments               => NULL );
ords.define_template ( 
        p_module_name            => 'test',
        p_pattern                => 'itaot/', --is there anyone out there?
        p_priority               => 0,
        p_etag_type              => 'HASH',
        p_etag_query             => NULL, 
        p_comments               => NULL );
ords.define_handler (
        p_module_name            => 'test',
        p_pattern                => 'itaot/',
        p_method                 => 'GET', 
        p_source_type            => 'json/collection',
        p_items_per_page         => 25,
        p_mimes_allowed          => '',
        p_comments               => NULL,
        p_source                 => 'select * from hello_world' );
 
 COMMIT;
 END;

Note: The ORDS PL/SQL API can be accessed via the ORDS (when you are logged in as your APP user (Jeff) and ORDS_ADMIN (when you are logged in as ADMIN) packages. I’m running this as JEFF, so all the calls are using ORDS().

Now to access my service, I can take the same URL I have for my SQLDev Web session, and replace everything after the /tjs/ with ‘test/itaot/’, like so:

But wait. I didn’t supply an OAUTH token or even any user credentials? What’s up with that?

Protecting our module with a privilege.

I’m going to create a new privilege (an ORDS priv, not a database priv), assign it to a ROLE (‘SQL Developer’), and use it to protect the TEST module we just created.

DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;
BEGIN
  l_roles(1)   := 'SQL Developer';
  l_modules(1) := 'test';
  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'thewall',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => '',
      p_description    => 'priv required to hit itaot',
      p_comments       => NULL);      
  COMMIT; 
END;

With this executed, if we try to hit our service again…

Authenticating the request (our GET)

If you read the prior code block, you’ll have seen that we used the role ‘SQL Developer’ – this is a special ORDS role that is assigned to any Database User Authenticated session. So, if I use the JEFF credentials to make a request on a ‘tjs’ resource, I’ll be given the ‘SQL Developer’ role.

So…

Relying on database credentials to access your RESTful Services isn’t ideal, so if you want to go a different route for securing your services, check out the OAUTH2 docs for ORDS.

Nice PL/SQL api, but…not fun?

You have a full RESTful Services development environment built into SQL Developer (on your desktop).

Or, if you want to stay in your browser while we work on the REST IDE screens in SQL Developer Web, you can use the APEX SQL Workshop.

Additionally, now that the Autonomous Database also includes native APEX support, you may also use the RESTful Services development pages in APEX to build and maintain your services and REST enabled objects.

Use the SQL Workshop in APEX to access your Oracle RESTful Services and REST Enabled objects.

Want to learn more about ORDS and RESTFul Services for Oracle Database?

In addition to the resources on oracle.com/rest and youtube.com I previously shared, I’ve also put together this Resource Page to help you get started with frequently asked questions and tasks you might be curious about.