[Update for ORDS 22+] Plugin Example – Get YAML response for ORDS services

My previous example for adding a YAML filter to ORDS service responses no longer applies with ORDS 22.1.0 and later. In the past the approach for extending the ORDS functionality was to modify the distributed ords.war with a plugin jar. Now there is a separate lib/ext/ directory for extension jars. This post will revisit last year’s Plugin Example – Get YAML response for ORDS services and cover what is different now. The end goal will still be to have YAML returned for an AutoREST table rather than JSON.

ORDS 22.3.3 will be used. Available from https://oracle.com/rest

JSON and YAML

My client ‘Accepts’ YAML

As mentioned in the previous post, when an ORDS service is created, by rest enabling a table for example, the default content type for requests and response payloads is application/json. The end goal for this exercise is to convert any application/json response to a text/yaml response without having to modify the service implementation. The client specifies that YAML should be returned by stating that text/yaml is acceptable…

curl -i -H "Accepts: text/yaml" http://localhost:8080/ords/hr/employees/

…which gets this response

HTTP/1.1 200 OK
Content-Type: text/yaml
ETag: "v7RO6F9A6fwTqkJvp28hXrluD1r31Uo4stahc5jCzCPtXKk8ke8S0cEcmED1FqOT2PdZ/VkEqgREVjTp2sWptA=="
Transfer-Encoding: chunked

---
items:
- employee_id: 100
  first_name: "Steven"
  last_name: "King"
  email: "SKING"
  phone_number: "515.123.4567"
  hire_date: "1987-06-16T23:00:00Z"
  job_id: "AD_PRES"
  salary: 24000
  commission_pct: null
  manager_id: null
  department_id: 90
  links:
  - rel: "self"
    href: "http://localhost:8080/ords/hr/employees/100"
...removed for brevity...

hasMore: true
limit: 25offset: 0
count: 25
links:
- rel: "self"
  href: "http://localhost:8080/ords/hr/employees/"
...removed for brevity...

ORDS Custom Plugin

To achieve this we will code our own custom plugin to ORDS. The ORDS distribution from oracle.com/rest contains example plugins and for simplicity one of these examples will be copied as the basis of our new custom plugin. First I’ll provide the code and steps to build the plugin. Then I will go through the plugin code to explain key parts.

Assumptions and prerequisites

This example is based on the plugin examples in the ORDS Getting Started tutorial. It is assumed that you are already familiar with that plugin-demo Servlet example.

We’re using ORDS 22.3.3 with Oracle Java 11 in standalone mode. A configuration directory has already been created containing valid connection details to a database that has ORDS installed. There is a REST Enabled schema in the database with a REST Enabled table that does not require authentication to access. In my example the schema is HR, the table is EMPLOYEES and the RESTful endpoint is http://localhost:8080/ords/hr/employees.

ORDS 22.3.3 ships with Jackson which will be the basis for the YAML conversation of the JSON response. However, other jars will be required at compile and runtime.

In this article I will refer to the new plugin as plugin-yaml but you can use any name that suits.

  1. Copy the examples/plugins/plugins-demo directory to examples/plugins/plugin-yaml
  2. Change the project name in the build.xml to plugin-yaml
    • <project default=”dist” name=”plugin-yaml”>
  3. Remove the examples/plugins/plugin-yaml/src/example/PluginDemo.java
  4. Download PluginYaml.java to examples/plugins/plugin-yaml/src/example/
  5. Download jackson-core-2.13.0.jar to examples/plugins/plugin-yaml/lib/
  6. Download jackson-databind-2.13.0.jar to examples/plugins/plugin-yaml/lib/
  7. Download jackson-dataformat-yaml-2.13.0.jar to examples/plugins/plugin-yaml/lib/
  8. Download snakeyaml-1.28.jar to examples/plugins/plugin-yaml/lib/
  9. Open a command shell at the examples/plugins/plugin-yaml/ directory
  10. At the command line run ant

So far, all the steps have been the same as for last year’s example. Now, instead of adding the jars to the ords.war we add them to the existing lib/ext/ directory where ORDS has been installed.

  1. Copy the following files to ORDS home lib/ext/ directory:
    • built/plugin-yaml.jar
    • lib/jackson-dataformat-yaml-2.13.0.jar
    • lib/snakeyaml-1.28.jar
  2. Start ORDS: ords –config /path/to/config/ serve
  3. Send a request for JSON data: curl -i http://localhost:8080/ords/hr/employees/
  4. Send a request for YAML data: curl -i -H “Accepts: text/yaml” http://localhost:8080/ords/hr/employees/

Review of the artefacts

There are 3 jars to add to the lib/ext/ directory to be picked up in the ORDS classpath at runtime in. The plugin-yaml.jar which we have built from source and the 2 runtime dependencies jackson-dataform-yaml and snakeyaml. Any subsequent code changes in examples/plugins/plugin-yaml/src/ will require the ant project to be built again but only the produced plugin-yaml.jar must be added to the lib/ext/ directory again.

Example output from running ant command:

Buildfile: /scratch/ords-22.3.3.311.1929/examples/plugins/plugin-yaml/build.xml

clean:
   [delete] Deleting directory /scratch/ords-22.3.3.311.1929/examples/plugins/plugin-yaml/built

compile:
    [mkdir] Created dir: /scratch/ords-22.3.3.311.1929/examples/plugins/plugin-yaml/built/classes
    [javac] Compiling 1 source file to /scratch/ords-22.3.3.311.1929/examples/plugins/plugin-yaml/built/classes
    [javac] Note: Discovered type annotated with @Provides: example.PluginYaml

dist:
      [jar] Building jar: /scratch/ords-22.3.3.311.1929/examples/plugins/plugin-yaml/built/plugin-yaml.jar

BUILD SUCCESSFUL
Total time: 0 seconds

Code Overview

The PluginYaml.java is a basic javax.servlet.Filter implementation that replaces the response output stream when the servlet container is about to return that response to the calling client. To achieve this, when it is determined that the client provides Accepts: text/yaml in the request, the filter supplies a HttpServletResponseWrapper which captures the original response. That wrapper, in this case called ServletResponseWrapperCopier, uses Jackson mappers to produce a YAML representation of the JSON response content and return that instead.

In summary, the filter…

Terms and Conditions

The performance overhead of producing a response in one structure and creating a copy of it in a different structure may be quite significant for large payloads. Although ORDS provides a plugin framework for you to add functionality, your custom plugin, or any third party jars that you add to the lib/ext/ are not supported by Oracle. The upshot is obvious. When it comes to plugins: test , test , test.

What about Tomcat and WebLogic?

The astute reader will have noticed that if the distributed ords.war is no longer modified then the additional jars will not be in the classpath of the ORDS web application when deployed to Apache Tomcat or Oracle WebLogic Server. That is where the ords war command comes in. Use the ords war command to create a deployable web application archive file which has the config.url context parameter explicitly set and any jar files from lib/ext folder are included.

Be careful with OAuth2 client roles

There’s a performance section in the ORDS Best Practice document that encourages the use of OAuth2 clients rather than basic authentication for REST Services. It is excellent advice. The overhead of verifying a database username and password can add hundreds of milliseconds to the total response time. That can be avoided by using an OAuth2 client with ORDS.

A client can be granted ORDS roles: standard roles and custom roles. One of the standard ORDS roles that can be granted is SQL Developer role. This is a very useful, and powerful, role which when it is granted, should be granted with caution. Having said that, any grant of any role should be carefully considered and the implications evaluated.

Here’s an example of creating a Client Credentials type OAuth2 client and granting it SQL Developer role:

BEGIN
    OAUTH.CREATE_CLIENT(
        P_NAME => 'sql_dev_client',
        P_GRANT_TYPE => 'client_credentials',
        P_OWNER => 'HR',
        P_DESCRIPTION => 'OAuth Client With SQL Developer role',
        P_ORIGINS_ALLOWED => '',
        P_REDIRECT_URI => NULL,
        P_SUPPORT_EMAIL => 'test@example.com',
        P_SUPPORT_URI => 'https://example.com',
        P_PRIVILEGE_NAMES => ''
    );
    OAUTH.GRANT_CLIENT_ROLE(
        P_CLIENT_NAME => 'sql_dev_client',
        P_ROLE_NAME => 'SQL Developer'
    );
    COMMIT;
END;

With that client created one can get the client_id and secret for obtaining an access token:

SELECT name, client_id, client_secret FROM user_ords_clients;

NAME             CLIENT_ID  CLIENT_SECRET 
---------------- ---------- --------------
sql_dev_client   3WjIAi..   myb-nW..

Using curl one can request an access token. This is what is referred to as a Two-Legged process where the client_id and client_secret is used to get an access token and that token is then used for subsequent service calls. Note that <schema alias> is the alias of the REST Enabled user that has created the client:

curl \
--user 3WjIAi.:myb-nWh.. \
--data 'grant_type=client_credentials' \
https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/<schema alias>/oauth/token

{
 "access_token":"9EVGMlgDLQ8N5clLKVLj0Q",
 "token_type":"bearer",
 "expires_in":3600
}

That access token is time based. It will only be valid for an hour. After which the above oauth/token request would have to be submitted again. Now that we have an access token, let’s use it to invoke a service which ships with ORDS but requires the SQL Developer role. In this example we’ll get a list of Data Pump jobs and our schema alias is hr:

curl 'https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/' \
  -H 'Authorization: bearer 9EVGMlgDLQ8N5clLKVLj0Q'

Which gives the below response:

{
   "count" : 1,
   "hasMore" : false,
   "items" : [
      {
         "attached_sessions" : 0,
         "datapump_sessions" : 0,
         "degree" : 0,
         "job_mode" : "TABLE                         ",
         "job_name" : "EXP_SD_123",
         "links" : [
            {
               "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/HR,EXP_SD_123/",
               "rel" : "self"
            }
         ],
         "operation" : "EXPORT                        ",
         "state" : "NOT RUNNING"
      }
   ],
   "limit" : 25,
   "links" : [
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "rel" : "self"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "rel" : "edit"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/metadata-catalog/",
         "rel" : "describedby"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "rel" : "first"
      }
   ],
   "offset" : 0
}

An interesting snippet of information. Of course one can do a lot more with these Database API services. Those Database API services that can be access by clients with SQL Developer role are not limited to just getting information.

Anything your database account can do

The Database API services perform a specific set of well defined operations. While access to the services require an ORDS role, such as SQL Developer or SQL Administrator, the database account used to execute the corresponding SQL is the REST Enabled schema.

There are other services provided by ORDS which require the SQL Developer role to access and one of note is the REST Enabled SQL Service. With the access token for a client with SQL Developer role one can submit any SQL script or statement.

For example, show the corresponding database account username:

curl 'https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/sql' \
  -H 'Authorization: bearer 9EVGMlgDLQ8N5clLKVLj0Q' \
  -H 'Content-Type: application/json' \
  --data-raw '{"statementText":"select user from dual","offset":0,"limit":256}'

Which confirms that the database user in this case is the HR schema…

{
   "env" : {
      "defaultTimeZone" : "UTC"
   },
   "items" : [
      {
         "response" : [],
         "result" : 0,
         "resultSet" : {
            "count" : 1,
            "hasMore" : false,
            "items" : [
               {
                  "user" : "HR"
               }
            ],
            "limit" : 256,
            "metadata" : [
               {
                  "columnClassName" : "java.lang.String",
                  "columnName" : "USER",
                  "columnTypeName" : "VARCHAR2",
                  "isNullable" : 1,
                  "jsonColumnName" : "user",
                  "precision" : 128,
                  "scale" : 0
               }
            ],
            "offset" : 0
         },
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 2,
            "startLine" : 1
         },
         "statementText" : "select user from dual",
         "statementType" : "query"
      }
   ]
}

Conclusion

For those familiar with ORDS this is not a great revelation and there are plenty of legitimate cases where an ORDS OAuth2 client would have the SQL Developer role. In fact a quick search online for ORDS OAuth2 examples will show some. My intent is to highlight that the SQL Developer role provides access to a range of powerful ORDS services and the use of the role should be carefully considered.

Azure SQL data in APEX

One of the big announcements this month was the Oracle Database Service for Microsoft Azure. With this new fully managed service, Azure customers can seamlessly build Azure applications using the high-performance, high-availability, and automated management of Oracle Database services such as Autonomous Database running on OCI. Did you know that even before this announcement you could have a REST interface to Azure SQL through ORDS?

In this article I’ll show you how I have data from a hosted Azure SQL database used in an APEX application all made possible through ORDS. Although I’m using released products it is important to point out that using ORDS with SQL Server is not officially supported. Therefore, although you can use this approach, you mind find a use case that does not work. For example, an unsupported column data type.

Key components in this article

This is your goal, sample data from Azure SQL rendered in an APEX application

Azure SQL Sample Database

To get started, if you do not have an Azure SQL database already, head over to https://portal.azure.com/ and set yours up. By default you will get a functional, but empty database. In my case, I selected Sample for Use existing data option in the Additional Settings section of the create database sequence so that I had some data in place.

Create your database with sample data
SQL Server and Database

The overall process from zero involves creating an SQL Server ( pobdemo in my case ) and an SQL Database ( pobsample in my case ) which can be accessed outside of Azure.

You will also need to create some logins and users to access the database so Azure Data Studio, or some other client for working with Azure SQL will be needed. The administrator user should not be used for anything more than managing the database.

One login will be used by ORDS to verify the pool connection configuration. It does not require any specific privileges in the database just the ability to make a connection over JDBC. The login could be any value but for consistency we’ll call it ORDS_PUBLIC_USER

CREATE LOGIN ORDS_PUBLIC_USER WITH PASSWORD = '< keep this secret >'; 

The other login and database user is for the account that will be used to interact with the database. In this example: ords_demo

CREATE LOGIN ords_demo WITH PASSWORD = '< keep this secret >'; 
CREATE USER ords_demo FROM LOGIN ords_demo;
ALTER ROLE db_datareader ADD MEMBER [ords_demo];
ALTER ROLE db_datawriter ADD MEMBER [ords_demo];

Configure ORDS

Now that we have our database accounts let’s configure ORDS. In my case I already have an ORDS instance setup with the default pool configured for my Oracle 19c database and APEX is installed.

The directory that I extracted the ORDS 22.2.0 distribution to is ~/Downloads/ords-22.2.0.172.1758/ and the configuration directory is ~/Documents/Personal/azure_sql/. That directory looks like this:

├──databases
│   ├── azure_sql
│   │   ├── pool.xml
│   │   └── wallet
│   │       └── cwallet.sso
│   └── default
│       ├── pool.xml
│       └── wallet
│           └── cwallet.sso
├── global
│   └── settings.xml

The configuration for the azure_sql pool was achieved as follows while the configuration directory is the working directory. Note that you will need to know your JDBC connection string from your Azure Dashboard. You’ll find that under Show database connection strings.

See the ‘Show database connection strings’ to get your JDBC connection string

Armed with that information it is time to configure your azure_sql pool:

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.connectionType customurl

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.customURL jdbc:sqlserver://<rest of the JDBC connection string from Azure dashboard>

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set jdbc.driverName com.microsoft.sqlserver.jdbc.SQLServerDriver

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.credentialsSource request

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set restEnabledSql.active true

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.username ORDS_PUBLIC_USER

Every command entry should show a message confirming that the setting was applied. Now to set the password for ORDS_PUBLIC_USER. That should be treated as a secret:

~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql secret db.password

Enter the database password: 
Confirm password: 
The setting named: db.password was set to: ****** in configuration: azure_sql

When done, assuming that you are using the same pool name, your databases/azure_sql/pool.xml should look like this but without the comments:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Fri Oct 11 11:21:28 IST 2019</comment>
<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">jdbc:sqlserver://your_sql_server.database.windows.net:1433;database=your_sql_db;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;</entry>
<entry key="jdbc.driverName">com.microsoft.sqlserver.jdbc.SQLServerDriver</entry>
<!-- username for account required to verify pool connection details are
correct. User does not require special privileges -->
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<!-- Credentials in the request will be used to create injected DB connection
and ORDS Role will be SQL Developer. Authentication mode will be ANY_SCHEMA -->
<entry key="db.credentialsSource">request</entry>
<!-- REST Enabled SQL must be enabled -->
<entry key="restEnabledSql.active">true</entry>
</properties>

One of the settings is the JDBC driver to use ( jdbc.driverName ) and that is com.microsoft.sqlserver.jdbc.SQLServerDriver but ORDS does not ship with that JDBC driver. Download the SQL Server JDBC driver and copy the Java 11 jar into the ORDS lib/ext directory. In my case that is ~/Downloads/ords-22.2.0.172.1758/lib/ext

Time to startup ORDS in standalone mode and verify the connection pool is loaded correctly.

~/Downloads/ords-22.2.0.172.1758/bin/ords serve

ORDS: Release 22.2 Production on Fri Jul 29 13:52:06 2022

Copyright (c) 2010, 2022, Oracle.
...
2022-07-29T13:52:10.561Z INFO        Configuration properties for: |azure_sql|lo|
gopherProxySet=false
awt.toolkit=sun.lwawt.macosx.LWCToolkit
java.specification.version=11
...
2022-07-29T13:52:45.126Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 22.2.0.r1721758
Oracle REST Data Services server info: jetty/9.4.46.v20220331
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.13+10-LTS-370

Test REST Enabled SQL

If you have gotten this far without errors that means ORDS can connect to the Azure SQL database using the db.username account and therefore the custom connection aspect of the pool configuration is correct. Now to confirm the REST Enabled SQL can use that pool configuration to access the database with basic authentication credentials provided. For that we’ll use the login that is a user in the database and cURL to submit a GET request

curl -u ords_demo http://localhost:8080/ords/azure_sql/_/sql
Enter host password for user 'ords_demo': ********
{
 "database_product_name":"Microsoft SQL Server",
 "database_product_version":"12.00.312",
 "database_major_version":12,
 "database_minor_version":0,
 "env":{"defaultTimeZone":"UTC","ordsVersion":"22.2.0.r1721758"}
}

We could just stop here and just use REST Enabled SQL directly with our Azure SQL database but let’s do something really interesting with APEX, which is probably why you’re here. First, a few words about how APEX works with SQL Server natively: it does not.

SQL Server Syntax

APEX has powerful support for consuming data over REST services including the ability to construct queries for Oracle and MySQL which are executed through ORDS REST Enabled SQL. This allows for a very dynamic no code approach to developing applications. See previous article How to use the Oracle Database Tools Service to provide MySQL data to APEX on this topic.

APEX does not provide the same level of support for SQL Server. Although one can configure an ORDS pool so that REST Enabled SQL can be used, APEX will not recognise the database type when you try create the REST Enabled SQL reference. All is not lost though. The work around is to write ORDS REST Services which have the specific SQL Server statements. Those services make the REST Enabled SQL calls and return the response. They are effectively custom wrapper services.

APEX calls custom ORDS Service which calls ORDS REST Enabled SQL which talks to Azure SQL

Now, on with the APEX adventure…

APEX Workspace

In my local Oracle database I have APEX installed and have created a workspace called AZURE which is using the AZURE database schema. That schema has been REST Enabled too.

The ‘azure’ workspace is in the ‘AZURE’ schema and that schema is REST Enabled

This AZURE database schema in the Oracle database will have the REST Services ( module/template/handler ) defined and those services will make the REST Enabled SQL calls. For convenience these services are not secured but obviously should be if they are to be used in a real world scenario. What is secured is the REST Enabled SQL endpoint. A username and password for the database user in Azure SQL must be provided but rather than having that in plain text in the handler source we’ll use an APEX Web Credential that the AZURE schema will have access to.

APEX Web Credentials

Web Credentials are a really useful feature in APEX that allows you to store authentication credentials for external REST services or REST Enabled SQL services. We’ll use that in our ORDS Service handler pl/sql block but more on that later. First, let’s define a Web Credential in the workspace.

Web Credential with Azure SQL username and password

There are a few things to highlight about the above Credentials for Azure

  • The Static Identifier is Credentials_for_Azure – we will refer to that later in the ORDS handler
  • The Authentication Type is Basic Authentication – the REST Enabled SQL endpoint uses this Authentication Type
  • The Valid for URLs specifies host.docker.internal as the server address. Remember how I said that Oracle 19c database was running in docker? Localhost would just point to the running container in my case. This is how the database can address the host machine ( my laptop ) which is running ORDS.

Stating the obvious here but it’s significant that you can change the username and password in the Web Credential without modifying the handler. Just thought I’d mention that.

And now with the Web Credential in place lets define the services in the AZURE schema that will submit SQL queries over REST Enabled SQL to Azure SQL database.

Wrapper Services

We’re going to define two services which follow this pattern: send a query, return the response. This relies on the APEX_WEB_SERVICE.MAKE_REST_REQUEST function to create the POST request, with Content-Type application/sql, and return the payload response. The previously defined Web Credentials are used to authenticate those requests.

The module, templates and handlers can be defined in APEX or SQL Developer Web. One module with Base Path /sales/ along with two templates: customer_count_by_country and order_header.

One module with /sales/ Base Path and two templates
Of course the ORDS REST Services can be edited in SQL Developer Web too

Handler for /sales/customer_count_by_country

This handler executes an aggregation query to get the number of customers with a Main Office in each country.

DECLARE
  l_clob    CLOB;
  l_result  VARCHAR2(32767);
BEGIN
  APEX_WEB_SERVICE.g_request_headers.delete();
  APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
  APEX_WEB_SERVICE.g_request_headers(1).value := 'application/sql';
 
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'http://host.docker.internal:8080/ords/azure_sql/_/sql',
    p_http_method => 'POST',
    p_credential_static_id => 'Credentials_for_Azure',
    p_body => 'select address.CountryRegion, count(CustomerAddress.CustomerID) as "CustomerCount"
from SalesLT.Address, SalesLt.CustomerAddress
where CustomerAddress.AddressID = Address.AddressID and CustomerAddress.AddressType = ''Main Office''
group by address.CountryRegion'
  );

HTP.print(l_clob);

END;

Handler for /sales/order_header

This handler executes a basic query to get all records from the SalesOrderHeader table.

DECLARE
  l_clob    CLOB;
  l_result  VARCHAR2(32767);
BEGIN
  APEX_WEB_SERVICE.g_request_headers.delete();
  APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
  APEX_WEB_SERVICE.g_request_headers(1).value := 'application/sql';
  -- Get the XML response from the web service.
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'http://host.docker.internal:8080/ords/azure_sql/_/sql',
    p_http_method => 'POST',
    p_credential_static_id => 'Credentials_for_Azure',
    p_body => 'SELECT * FROM [SalesLT].[SalesOrderHeader]'
  );
  HTP.print(l_clob);
END;

Service Handler Walkthrough

Keep in mind that these handlers execute in the AZURE database schema which is the schema for the APEX Workspace we’ll create the Data Source references and APEX Application in. Both handlers follow these steps:

  • Set the Content-Type header to application/sql
  • Make a POST request with Web Credentials for authentication and a query
  • Write the REST Enabled SQL result to the HTTP Response stream.

There’s no validation on the response, or parsing that response to change the structure. We’ll leave it to APEX to figure out how to parse the response payload.

Now is it a good time to verify that the two handlers work. We can invoke them through cURL. Remember the schema alias is azure, the module base path is /sales/ so the URL will begin with http://localhost:8080/ords/azure/sales/

curl http://localhost:8080/ords/azure/sales/customer_count_by_country

{
   "env" : {
      "defaultTimeZone" : "UTC"
   },
   "items" : [
      {
         "response" : [],
         "result" : 0,
         "resultSet" : {
            "count" : 3,
            "hasMore" : false,
            "items" : [
               {
                  "countryregion" : "Canada",
                  "customercount" : 106
               },
               {
                  "countryregion" : "United Kingdom",
                  "customercount" : 38
               },
               {
                  "countryregion" : "United States",
                  "customercount" : 263
               }
            ],
            "limit" : 10000,
            "metadata" : [
               {
                  "columnClassName" : "java.lang.String",
                  "columnName" : "CountryRegion",
                  "columnTypeName" : "nvarchar",
                  "isNullable" : 0,
                  "jsonColumnName" : "countryregion",
                  "precision" : 50,
                  "scale" : 0
               },
               {
                  "columnClassName" : "java.lang.Integer",
                  "columnName" : "CustomerCount",
                  "columnTypeName" : "int",
                  "isNullable" : 1,
                  "jsonColumnName" : "customercount",
                  "precision" : 10,
                  "scale" : 0
               }
            ],
            "offset" : 0
         },
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 5,
            "startLine" : 1
         },
         "statementText" : "select address.CountryRegion, count(CustomerAddress.CustomerID) as \"CustomerCount\"\nfrom SalesLT.Address, SalesLt.CustomerAddress\nwhere CustomerAddress.AddressID = Address.AddressID and CustomerAddress.AddressType = 'Main Office'\ngroup by address.CountryRegion",
         "statementType" : "query"
      }
   ]
}
curl http://localhost:8080/ords/azure/sales/order_header

{
   "env" : {
      "defaultTimeZone" : "UTC"
   },
   "items" : [
      {
         "response" : [],
         "result" : 0,
         "resultSet" : {
            "count" : 32,
            "hasMore" : false,
            "items" : [
               {
                  "accountnumber" : "10-4020-000609",
                  "billtoaddressid" : 1092,
                  "comment" : null,
                  "creditcardapprovalcode" : null,
                  "customerid" : 29847,
                  "duedate" : "2008-06-13T00:00:00Z",
                  "freight" : 22.0087,
                  "modifieddate" : "2008-06-08T00:00:00Z",
                  "onlineorderflag" : false,
                  "orderdate" : "2008-06-01T00:00:00Z",
                  "purchaseordernumber" : "PO348186287",
                  "revisionnumber" : 2,
                  "rowguid" : "89E42CDC-8506-48A2-B89B-EB3E64E3554E",
                  "salesorderid" : 71774,
                  "salesordernumber" : "SO71774",
                  "shipdate" : "2008-06-08T00:00:00Z",
                  "shipmethod" : "CARGO TRANSPORT 5",
                  "shiptoaddressid" : 1092,
                  "status" : 5,
                  "subtotal" : 880.3484,
                  "taxamt" : 70.4279,
                  "totaldue" : 972.785
               },
...trimmed for brevity...
            ],
            "limit" : 10000,
            "metadata" : [
               {
                  "columnClassName" : "java.lang.Integer",
                  "columnName" : "SalesOrderID",
                  "columnTypeName" : "int",
                  "isNullable" : 0,
                  "jsonColumnName" : "salesorderid",
                  "precision" : 10,
                  "scale" : 0
               },
...trimmed for more brevity...
            ],
            "offset" : 0
         },
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 2,
            "startLine" : 1
         },
         "statementText" : "SELECT * FROM [SalesLT].[SalesOrderHeader]",
         "statementType" : "query"
      }
   ]
}

Now is a good time to reiterate that the ORDS REST Services are not protected and if the Azure SQL database you’re connecting to had any sensitive data it should be protected but that’s not the focus of this article.

As you can see from the above tests, these are REST Enabled SQL responses with lots of information about the statement executed, the metadata of the columns when all we’re really interested in is the data at items.resultSet.items. That will be relevant when we look into Data Sources.

APEX Data Sources

Let’s create a basic APEX application called Azure SQL with just the defaults.

Basic application with a Home page

Once created, edit the application and navigate to the Shared Components section.

Shared Components is where Data Source references can be defined

The Data Source references section has various options for getting data over REST.

Data Sources section has lots of options

Define REST Data Sources for both endpoints but specify their REST Data Source Type as Simple HTTP.

Note the URL has host.docker.internal has the server name because the Oracle database is running in Docker

Remember how the REST Enabled SQL response structure contains lots of metadata? For Data Profile specify the Row Selector as items.resultSet.items so that APEX can find the array of rows that it can interrogate for Data Profile.

The Row Selector must be specified to find the data in the response

Two Data Source references must be defined. In my case I also defined synchronisation for the AzureSalesHeader Data Source to pull in data to a local table. I’ll cover that in a separate article.

Data Source references that are Simple HTTP types

Now let’s put those to use. Edit the Home page and drop in a Chart and a Classic Report to the body. The Chart will use REST Source AzureCustomerCountByCountry and the Classic Report will use REST Source AzureSalesHeader.

Source for the components are REST Source

The Classic Report is very basic and includes the column list from the AzureSalesHeader Data Source.
For the Customers chart the Series Source Location should be set to Region Source so that it inherits the source from the Chart and columns can be selected.

For the Chart series Source Location is Region Source and the Column Mapping for what to show

With all that in place, run the application to see the Home page with the Chart and Classic Report. Note that by following the default application setup a login is required. Just use the same workspace developer username and password.

Pie Chart and Classic Report with live data from Azure SQL

Conclusion

In theory ORDS REST Enabled SQL can be used with any database that you have a JDBC driver for which will run on Java 11. In practice, only Oracle RDBMS and Oracle Cloud MySQL is supported. Keeping that in mind, the capability of bringing in data from other databases, such as Azure SQL and using it in your APEX applications opens up a lot of integration possibilities.

What projects does this make possible for you now? I’d be really interested to hear about it.

In future articles I will cover synchronisation, joining local with remote data as well as going beyond just select statements.

Addendum to the original article…if you are looking for an approach that is supported you can create database links from Autonomous Database to Non-Oracle databases.

Specify Java version for ORDS

Earlier this year, as of ORDS 22.1.0, the minimum supported Java version moved to Java 11. The ORDS command line script finds the Java Runtime Environment on your machine based on path or JAVA_HOME environment variable and in the majority of cases that works out fine.

What do you do if you need to keep a different, perhaps older, Java Runtime Environment in the path? You might be using some other application or utility which expects a JRE which is incompatible with ORDS.

The simple answer is that the ORDS command line script does go looking for a JRE in the directory where you have extracted the ORDS distribution. If you have extracted ORDS to /opt/oracle/ords/22.2.0/ then the command line shell script goes looking in /opt/oracle/ords/22.2.0/jre/ for a Java Home.

Symbolic link makes it easy

The most straight forward way to provide this Java Runtime Environment home for ORDS to use is to define it using a symbolic link. That way you are not duplicating files and you can point to a new JRE version whenever suits.

ln -s /Library/Java/JavaVirtualMachines/jdk-17.0.4.jdk/Contents/Home /opt/oracle/ords/22.2.0/jre

Seeing it in action

In my environment the default JAVA_HOME is Java 11 so when I start up ORDS in standalone mode that is what is used.

Java 11 used by default in my environment

That is with no Java Runtime Environment specified in the ORDS directory…

/opt/oracle/ords/22.2.0
     ├── FUTC.txt
     ├── bin
     ├── docs
     ├── examples
     ├── icons
     ├── index.html
     ├── lib
     ├── license.txt
     ├── linux-support
     ├── ords.war
     └── scripts

In my case I also have a more recent Java 17 JRE which I can link to. See the earlier ln -s command for syntax. Now that I have that link in place…

/opt/oracle/ords/22.2.0
     ├── FUTC.txt
     ├── bin
     ├── docs
     ├── examples
     ├── icons
     ├── index.html
     ├── jre -> /Library/.../jdk-17.0.4.jdk/Contents/Home
     ├── lib
     ├── license.txt
     ├── linux-support
     ├── ords.war
     └── scripts

… the version of Java used by ORDS in standalone mode, or any command for that matter, is locked in. The PATH could change, the JAVA_HOME could change, but this ORDS will use this specified JRE.

Java 17 used because that’s the JRE specified

That simple approach provides more options for running different Java applications which may have very specific version requirements.

How to use the Oracle Database Tools Service to provide data to APEX

Oracle Database Tools Service does a lot and makes a lot of things possible. At its core is the concept of a connection library that allows you to define the connection details for a database and then reuse that definition in a range of scenarios: SQL Worksheet, SQLcl, Java or Python clients and more.

Providing you with the tools to make Oracle Cloud Infrastructure cloud database connections secure, easy, and reusable for development, DevOps processes, and SQL access.

https://www.oracle.com/database/tools-service/

One of those hosted services that also becomes available through the Database Tools Service is ORDS REST Enabled SQL. The SQL Worksheet in Oracle Database Tools Service and Oracle Database Actions both use the ORDS REST Enabled SQL service to run the given SQL statements in the corresponding database. All you need to construct the relevant service URL is the OCID for your Database Tools Connection definition and a little information about the Oracle Cloud region.

REST Enabled SQL Service URL

Connection definition for the HR schema in an Autonomous Database

In the above Frankfurt region the connection OCID is a really long string ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaamdjeo4qagsuv6lv3acwsomething2y26ya

The corresponding REST Enabled SQL service endpoint is https://sql.dbtools.eu-frankfurt-1.oci.oraclecloud.com/20201005/ords/ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaamdjeo4qagsuv6lv3acwsomething2y26ya/_/sql

The https://sql.dbtools.eu-frankfurt-1.oci.oraclecloud.com/20201005/ords/ prefix will be different for each region. You can use your console URL to guide you on how to construct the URL prefix. For this example, the console URL starts with https://console.eu-frankfurt-1.oraclecloud.com/

The pattern is https://sql.dbtools.< region >.oci.oraclecloud.com/20201005/ords/< connection ocid >/_/sql

But wait, my console URL looks different!

It is possible that your console URL actually starts with https://cloud.oracle.com/ and has the region as a parameter. For example ?region=ap-mumbai-1. That still gives you enough information to construct the REST Enabled SQL endpoint:
https://sql.dbtools.ap-mumbai-1.oci.oraclecloud.com/20201005/ords/ocid1.databasetoolsconnection.oc1.ap-mumbai-1.amaaaaaamdjeo4qagsuv6lv3acwsomething2y26ya/_/sql

Web Credentials

Now that you have the URL for the endpoint you will have to prove to the Database Tools Service who you are. That’s where an API Key is needed. You can define one in your User Settings.

API Key defined with Private Key PEM

With that API Key you can go to your APEX workspace and record the credential details.

Create an Oracle Cloud Infrastructure specific Web Credentials entry in your APEX workspace

APEX REST Enabled SQL Reference

With the OCI Web Credentials specified in the APEX workspace you can now create a REST Enabled SQL Service reference. This brings together the URL you identified earlier and the credentials that will be used to sign each request.

The reference is essentially a name, a URL and credentials definition

Off the charts!

So far, so simple. Now let’s use that REST Enabled SQL reference in an APEX application. In this example we’ll use the HR Connection REST Enabled SQL reference to create a bar chart of employee salaries.

Note the Source for this chart is REST Enabled SQL and the Table Name is specified as EMPLOYEES
Data from the REST Enabled SQL query displayed in a chart

Conclusion

What you have seen is database data to APEX chart in a quick and secure manner. Using the Database Tools Service connection details you can revoke the connection, change the underlying database connection details without having to update the APEX application.

Through a series of screenshot images you’ve been introduced to functionality from a range of Oracle products. In summary:

The Take the time to discover what else you can build with these services available to you. Many of them FREE!