Introduction

REST Enabled SQL allows you to run DML and DDL statements (via REST) against a remote Oracle database. REST Enabled SQL is made possible by Oracle REST Data Services (ORDS). APEX allows you to define a REST Enabled SQL Service. A REST Enabled SQL Service allows native APEX Components like Reports, Charts, Maps, etc., to access and update data in a remote database using REST Enabled SQL.

This post will show you how to create a REST Enabled SQL endpoint and secure it with OAuth2 Client Credentials. I will also show you how to create an APEX REST Enabled SQL Service to access this Endpoint from an Interactive Grid in a different database.

Architecture

The diagram below describes how REST Enabled SQL works. ORDS REST Enabled SQL Architecture

The APEX App in the Local Instance builds a SQL statement, adds some session initialization code, and sends the request to the Remote Database
ORDS in the Remote Instance receives the request, parses the JSON, assigns a JDBC connection, and passes the code to the database to run
The remote database executes the code and gives the results back to ORDS
ORDS generates a JSON response and sends it back to the Local Database
APEX in the Local Database parses the JSON results and renders the appropriate data on the APEX page

Example Scenario

In this post, we will use the example of an item master table sitting in the Remote Database. The requirement is to create an Interactive Grid in the Local Database, which allows users to maintain items directly in the Remote Database without the need to host the table in the Local Instance. The table in the remote instance is called CNDEMO_ITEMS.

Remote Database Setups

This section describes how to set up an OAuth2 secured REST Enabled SQL Endpoint.

ORDS Server

If you maintain your own ORDS Server, you must include the following entry in the settings.xml file. REST Enabled SQL is enabled by default if you use Oracle Cloud Infrastructure (OCI) Autonomous Database or APEX Service.

<entry key="restEnabledSql.active">true</entry>

❗You must be running your ORDS server on HTTPS to use REST Enabled SQL.

Once this is in place, you can access REST Enabled SQL using a special endpoint. The endpoint is the base URL for ORDS concatenated with /_/sql. So, if you access APEX Builder using https://example.com/ords/, then the REST Enabled SQL endpoint would be https://example.com/ords/_/sql.

Create OAuth2 Client

We need an ORDS OAuth client to secure our REST Enabled SQL Endpoint. Run the following code in the Remote Database schema where your data is located. This will create an OAuth2 Client and assign it to the 'SQL Developer' role. This role allows us to run DDL and DML against the Schema using REST Enabled SQL.

DECLARE
  c_client_name         CONSTANT VARCHAR2(200) := 'REST_ENABLED_SQL_ACCESS';
  c_client_description  CONSTANT VARCHAR2(200) := 'Client for OAuth2 Secured REST Enabled SQL Access';
  c_support_email       CONSTANT VARCHAR2(200) := 'jon@example.com';
BEGIN

  -- REST Enable the Schema.
  ords.enable_schema;

  -- Create OAuth Client to protect the REST Enabled SQL Endpoint. --
  oauth.delete_client(p_name => c_client_name);
  oauth.create_client
   (p_name            => c_client_name,
    p_description     => c_client_description,
    p_grant_type      => 'client_credentials',
    p_support_email   => c_support_email,
    p_privilege_names => NULL);

  -- Grant Client 'SQL Developer' Role, Needed for REST Enabled SQL.
  oauth.grant_client_role(c_client_name, 'SQL Developer');
  commit;
END;
/

Immediately run the following SQL to get the Client ID and Client Secret.

SELECT name
,      client_id
,      client_secret 
FROM   user_ords_clients
WHERE  name = 'REST_ENABLED_SQL_ACCESS'

✏️ Take note of the client_id and client_secret

Test Using Postman

We can now test the REST Enabled SQL endpoint using Postman.

Get an OAuth Token

Using the client_id and client_secret from above, we can fetch an OAuth token as follows:

Test REST Enabled SQL Get Token Authorization

JSON Response:

{"access_token":"vgFUONpz7tC6-bfcF6GoZQ",
 "token_type":"bearer",
 "expires_in":3600}

Test a Basic SQL Query

Using the token from the previous step, we can send the following SQL to the database:

Test REST Enabled SQL Authorization

Test REST Enabled SQL Headers

Test REST Enabled SQL Body

JSON Response:

{
    "env": {
        "defaultTimeZone": "UTC"
    },
    "items": [
        {
            "statementId": 1,
            "statementType": "query",
            "statementPos": {
                "startLine": 1,
                "endLine": 2
            },
            "statementText": "SELECT ITEM_NUMBER, LIST_PRICE FROM CNDEMO_ITEMS",
            "resultSet": {
                "metadata": [
                    {
                        "columnName": "ITEM_NUMBER",
                        "jsonColumnName": "item_number",
                        "columnTypeName": "VARCHAR2",
                        "columnClassName": "java.lang.String",
                        "precision": 50,
                        "scale": 0,
                        "isNullable": 0
                    },
                    {
                        "columnName": "LIST_PRICE",
                        "jsonColumnName": "list_price",
                        "columnTypeName": "NUMBER",
                        "columnClassName": "java.math.BigDecimal",
                        "precision": 0,
                        "scale": -127,
                        "isNullable": 0
                    }
                ],
                "items": [
                    {
                        "item_number": "BILAA8LX08",
                        "list_price": 89.99
                    },
                    {
                        "item_number": "GSQH6YQ1YM",
                        "list_price": 999.99
                    }
                ],
                "hasMore": true,
                "limit": 2,
                "offset": 0,
                "count": 2
            },
            "response": [],
            "result": 0
        }
    ]
}

In the above example, we passed the Header Content-Type:application/json, meaning we need to pass a JSON document with the SQL in the statementText field. JSON allows us to send multiple statements in an array with each statement identified by the statementId field. Importantly, you get an array response back, and you can associate each statementId in the response with the corresponding statementId in the request. There are also fields you can pass to perform pagination etc.

If you change the Header to Content-Type:application/sql, we can send plain SQL statements e.g. Test REST Enabled SQL Header application/sql

📖 REST Enabled SQL has far too many options to cover in this post, so I encourage you to read through the documentation.

❗At this point, we have not mentioned APEX. REST Enabled SQL (as with regular ORDS REST services) can be used by any client that can consume REST web services.

Local Database Setups

This section describes the APEX setups that need to be made in the Local Database to link to and use the REST Enabled SQL Endpoint in the Remote Database.

Create Web Credentials

First, we must create Web Credentials for the OAuth2 Client we created in the Remote Instance. These Web Credentials allow APEX to request a token that will enable it to call REST Enabled SQL in the Remote Database. Please read my post for more on Web Credentials.

🧭 Workspace Utilities > Web Credentials > Click the Create Button
Enter a Name and Static Identifier
Select 'Authentication Type' = 'OAuth2 Client Credentials'
Client ID or Username = client_id from above
Client Secret or Password and 'Verify Client Secret or Password' = client_secret from above
Click the Create button

Create the REST Enabled SQL Service

Next, we must create an APEX REST Enabled SQL Service.

🧭 Workspace Utilities > REST Enabled SQL Services > Click the Create Button
Enter a Name and the Endpoint URL for the Remote Database
Note: The 'Endpoint URL' is the base URL for ORDS in the Remote Instance. This is the same URL you use to access APEX Builder in the Remote Instance, e.g. https://example.com/ords
Click Next, then select the Web Credential Created in the previous step
Click the Create button
APEX will test its connection to the Remote Database; you will see the following message if everything is OK:

Create an APEX Interactive Grid

It is time to create an APEX page with an Interactive Grid to maintain the items in the Remote Instance.

Create a new Blank Page in an App in the Local Instance
Create a new Region and configure it as follows
- Identification > Type = Interactive Grid
- Source > Location = REST Enabled SQL
- Source > Remote Server = Remote REST Enabled SQL (created above)
- Source > Type = Table / View
- Source > Table Owner = Parsing Schema
- Source > Table Name = Click the List of Values Icon and select your table
- ❗At this point, APEX is querying the data dictionary in the Remote Instance using REST Enabled SQL. Once you select a table, APEX will fetch the columns for the table and populate the Interactive Grid Columns. Just like it does when you create an Interactive Grid on a local table
Set the Interactive Grid Region Attributes as follows:

The above example is the simplest possible use case. Instead of selecting a table in the Remote Database, we can enter a SQL statement. The screenshot below shows a Classic Report with a SQL statement that will run against the remote instance. Screenshot showing example REST Enabled SQL using SQL Statement

But don't stop there. You can use REST Enabled SQL Services for Chart Regions, Form Regions, List of Values, Interactive Reports, Cards Region, List View Regions, Map Regions, Calendar Regions, Reflow Report Region, Tree Region, plus some more I have probably missed.

Testing

Run the page and verify you can make changes to the data and save. The REST Enabled SQL interactive grid should behave as it would if it were a local table. APEX Interactive Grid Setup Step 4

Behind the Scenes

If you want to see what APEX is doing behind the scenes, set the APEX Debug Level to LEVEL9 (Trace) and look at the debug. You can do this by enabling debug from the Developer Toolbar. Once debug is set, you should see debug=YES in the Browser URL, e.g. https://www.example.com/ords/r/demo/testapp/testpage?debug=YES&session=12709883743672. If you change debug=YES to debug=LEVEL9, then hit enter, you will enable Trace Level Debugging, e.g. https://www.example.com/ords/r/demo/testapp/testpage?debug=LEVEL9&session=12709883743672

Once Trace Level Debugging is enabled, refresh the page and look at the created debug. You will see all the steps APEX performs to generate a request to send to REST Enabled SQL on the Remote Instance. Screenshot of Debug of REST Enabled SQL If you take a closer look at the line that starts with constructed JSON request in the screenshot above, you can see the APEX first calls alter session set to set the Remote Database NLS environment before running the SQL. APEX also runs dbms_application_info.set_client_info and set_module to identify REST Enabled SQL as the query source (valuable when troubleshooting long-running queries in the Remote Instance).

If you change a field in the Interactive Grid and Save it, the debug will show the updated statement sent to the Remote Database.

Why is it Useful

ORDS REST Enabled SQL provides excellent flexibility for consuming applications and does not require the development of formal REST services. If a consuming application needs to select an additional column from a table, it can do so without having to deploy any code to the remote database. Consuming applications have free rein to run any code SQL or PL/SQL in the Schema associated with a REST Enabled SQL Endpoint. This access is not necessarily good; you need to trust the consumer!

APEX REST Enabled SQL Services take this flexibility and make it available to native APEX components.

Security Considerations

REST Enabled SQL allows you to run any DDL or DML statement against the Schema in the Remote Database. You should follow the principle of least privilege for the Schema in the Remote Database. I recommend creating a dedicated schema for REST Enabled SQL that starts without access to any database objects. Then add grants and synonyms to allow the Schema to access just the objects it needs.

You can also use Basic Auth to access REST Enabled SQL (i.e., the user name and password of the remote database schema). I recommend you make the password for the Schema extremely complex and never provide it to consumers of the REST Enabled SQL Endpoint. OAuth2 is much more secure.

Unlike regular ORDS REST services, REST Enabled SQL allows consumers to run any SQL on the remote database. This opens the remote database up to poorly performing SQL. Be aware of this and maybe use Resource Manager to limit any potential issues in this respect.

Conclusion

We set up REST Enabled SQL in the Remote Database in this post. We then created a an APEX REST Enabled SQL Service in the Local Instance, allowing APEX to access Remote Instance data using REST Enabled SQL.

This post is only the beginning regarding what you can do with REST Enabled SQL. In the future, I hope to show you how you can do the following with REST Enabled SQL:

Run PL/SQL code in Remote Instances
Deploy Database Objects (Tables, View, Packages, etc.) to Remote Instances

🔗 Read More

🩳 APEX Shorts
#️⃣ APEX Posts
#️⃣ ORDS Posts
#️⃣ Secure your Secrets with APEX Web Credentials

APEX, ORDS, and REST Enabled SQL

Comments (4)

More from this blog

Using an APEX AI Agent to Turn Purchase Orders into JSON

APEX AI Agent Handlers: Intercepting & Updating Requests and Responses

APEX AI Agent Logging with Request & Response Handlers

AI Agents Need Boundaries, Not Bigger Prompts

The anatomy of an APEX 26.1 APEXlang file

Introduction

Architecture

Example Scenario

Remote Database Setups

ORDS Server

Create OAuth2 Client

Test Using Postman

Get an OAuth Token

Test a Basic SQL Query

Local Database Setups

Create Web Credentials

Create the REST Enabled SQL Service

Create an APEX Interactive Grid

Testing

Behind the Scenes

Why is it Useful

Security Considerations

Conclusion

🔗 Read More

Command Palette

Comments (4)

More from this blog

Introduction

Architecture

Example Scenario

Remote Database Setups

ORDS Server

Create OAuth2 Client

Test Using Postman

Get an OAuth Token

Test a Basic SQL Query

Local Database Setups

Create Web Credentials

Create the REST Enabled SQL Service

Create an APEX Interactive Grid

Testing

Behind the Scenes

Why is it Useful

Security Considerations

Conclusion

🔗 Read More