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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s