We are going to configure ORDS for Oracle Autonomous Database, which means that you will be able to interact with your database with plain HTTP methods like GET, POST etc. This is the simplest way to build server-less apps without having to configure any libraries on the application level.

Autonomous Database and REST interfaces

If you are familiar with Oracle databases you are well aware that there are multiple ways of manipulating data, tools like sqlplus, jdbc, oci8 or instantclient. But when it comes to server-less you will look directly to HTTP access as de-facto standard. No worries, we got you covered. It's time to talk about Oracle REST Data Services.

Oracle REST Data Services (ORDS) makes it easy to develop modern REST interfaces for relational data in the Oracle Database and the Oracle Database JSON Document Store. A mid-tier Java application, ORDS maps HTTP(S) verbs (GET, POST, PUT, DELETE, etc.) to database transactions and returns any results formatted using JSON.

Which means you can use HTTP protocol to communicate with your database. No client required or some other dependencies on the application level. You can just curl the data in or out of your database. ORDS is available with Autonomous Database. I recommend to check the getting started with ORDS guide.

Enable ORDS on Autonomous Database

Let's start by creating a user and allocate the following privileges and roles. You will do this by connecting with ADMIN credentials using Web SQL Developer.

CREATE USER ords IDENTIFIED BY <password>;
GRANT "CONNECT" TO ords;
GRANT "RESOURCE" TO ords;
ALTER USER ords QUOTA UNLIMITED ON DATA;

Then you will have to permit SQL Web Developer access for your new user (ords). In order to do this, just run the following procedure. You can check the PL/SQL procedure to understand all the parameters which can be configured.

BEGIN
     ords.enable_schema (
         p_enabled               => TRUE,
         p_schema                => 'ords',
         p_url_mapping_type      => 'BASE_PATH',
         p_url_mapping_pattern   => 'ords',
         p_auto_rest_auth        => TRUE
     );
     COMMIT;
END;

Most of these parameters are self-explanatory, so I want to mention just two of them:  p_auto_rest_auth and p_url_mapping_pattern. With p_auto_rest_authyou can control whether Oracle REST Data Services should require user authorization before allowing access to metadata catalog of this schema. The second one, p_url_mapping_pattern, sets the path you need to follow in your URL to trigger the handler. Don't worry, is going to be super clear in one or two minutes.
Go ahead and save the worksheet (it's one of my favorite features). It may be useful in the future if you will enable other schemas for Oracle Rest Data Service.

The steps you needed to follow as ADMIN just completed. It's time to sign back into Web SQL Developer as the new user you just created. You need to use a different link, so this is what you're going to do. Copy and paste your Web Developer URL and change inside your URL /ords/admin/ with /ords/ords/. Why? Because we used 'ords' for p_url_mapping_pattern. Clear enough? The link should look like:

https://<adb-id>.adb.<adb-region>.oraclecloudapps.com/ords/ords/sign-in/

There are two ways to enable REST access for schema objects: custom made, which means you need to declare the services on your own or Autorest. It's a broad subject and you will dig deeper once you start working with ORDS. For now, we will rely on Autorest, which lets you quickly expose data but (metaphorically) keeps you on a set of guide rails.

BEGIN
    ORDS.ENABLE_OBJECT(P_ENABLED => TRUE, P_SCHEMA => 'ords', P_OBJECT=>'HISTORICAL_PRICES', P_OBJECT_TYPE=> 'TABLE', P_OBJECT_ALIAS => 'prices', P_AUTO_REST_AUTH
    => FALSE);

    COMMIT;
END;

I'm using a dummy table called HISTORICAL_PRICES, but you can choose any other table which resides in your schema. Put the name of you table as the parameter for p_object and you are ready to go.

Check the REST services using Postman

I'm relying on Postman, but you can use any other tool. Insert some dummy data if your table is empty. Go ahead and play with GET method and explore the catalog. Drill to specific entries in your table. You can even try to UPDATE a specific row. Check the official documentation for a more detailed picture about how you can develop REST Data Services.

What about security? You can, and you should, restrict access by using privileges. Check this amazing post by Jeff Smith for a step by step tutorial. I'm going to cover this part in a second article.