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
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.
With that API Key you can go to your APEX workspace and record the credential details.
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.
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.
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:
- Oracle Cloud Infrastructure Database Tools Service
- Oracle REST Data Services REST Enabled SQL
- Oracle Cloud Infrastructure API Keys
- Oracle APEX REST Enabled SQL References
The Take the time to discover what else you can build with these services available to you. Many of them FREE!
Peter's Random Topic Library 