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!

GZIP compression failed for a static resource.

Sometimes it can be hard to get to the root cause of an error reported by software which makes it even more difficult solve it. This org.eclipse.jetty.io.EofException with message GZIP compression failed for a static resource is one of those. There can be any number of root causes with this exception. One of them is memory. Or rather the lack of it.

With ORDS 22.1.0, and later, the default memory Java heap size may not be ideal for your specific work load. Static documents, or even the size of large result sets, read into memory might cause your request processing to hit a limit.

Memory options for Java applications can be set at runtime. To achieve this with ORDS set the JDK_JAVA_OPTIONS environment variable before starting ORDS. For example

export JDK_JAVA_OPTIONS="-Xmx512m"
ords --config /scratch/ords-config/ serve

You should see output from the Java runtime environment stating that it has applied the options at startup..

NOTE: Picked up JDK_JAVA_OPTIONS: -Xmx512m

ORDS: Release 22.1 Production on Tue May 03 15:00:27 2022

Identifying the values that are applicable to your environment does require some investigation and perhaps some trial and error in some cases. Now you know though, a GZIP compression error can have memory issues as the root cause and you also know how to fix it.

Multiple ORDS instances in WebLogic Server or Apache Tomcat

UPDATE! As of ORDS 22.2.0 there is a command to generate web application for deployment to Apache Tomcat or Oracle WebLogic Server. See Deploying Oracle REST Data Services. The below article is informative and perhaps worth your time to get an understanding of web application deployment but there are supported, documented steps for achieving the same thing with the ORDS product. In fact, what is in the ORDS product is better because it also caters for jar content in the /lib/ext/ folder which this article does not cover.


ORDS 22.1.0 introduced a substantial set of changes: Java 11 as minimum, new command line interface and a new configuration directory structure. In fact it is a release that introduces a whole new deployment paradigm.

Deployment Options

What we knew as Standalone Mode in previous ORDS releases still exists and is now initiated through a serve command. The embedded Eclipse Jetty web server and servlet container engine is still at the heart of this convenient way to have ORDS listen HTTP/HTTPS traffic. The significant change is how the configuration directory is determined at startup. There is no longer a requirement to modify the distributed ords.war to set the configuration directory location.

Deployment on a supported servlet container, such as Oracle WebLogic Server or Apache Tomcat, is similarly impacted by this principle that the distributed ords.war should not be modified. A new mechanism for specifying the configuration directory when the application is deployed is required. The ORDS documentation covers the standard approach for a typical setup, use a Java System Property ( -Dconfig.url=/ords_config/ ), startup your server(s) and deploy the web application.

Single Server

For some developers that may not have a full production environment scaled out for high availability, or may be restricted in the Apache Tomcat or WebLogic Server deployment options, the approach to set different servers with their own ords.war and configuration is not always feasible. With previous ORDS releases they may have had the practice of deploying multiple ORDS web applications with different contexts and configurations. At first it would appear that the new direction in ORDS 22.1.0 makes that impossible but it is not. All that is required are a few extra steps.

Bake your own

For deploying to Apache Tomcat or WebLogic Server you can make your own, separate web application with the config.url baked in. Moreover, you can call it whatever makes sense in your context.

Heres a Python script to do that for you: create_deploy_war.py

This script produces a new web application archive file with the jars from the distributed ords.war and web.xml deployment descriptor has the configuration directory path set. That way, wherever it is deployed, it will refer to the specified configuration directory.

Usage: create_deploy_war <source ords.war> <destination war filename> <configuration directory>

For example, if I want to have two ORDS instances deployed called tom and jerry with two separate configuration directories and I have extracted the ORDS 22.1.0 to /opt/oracle/ords-22.1.0.105.1723/. The jerry web application will be configured with a default pool pointing to an Oracle 19c database. The tom web application will be configured with a default pool pointing to an Oracle 21c database. We will show both web applications have these configurations by using the REST Enabled SQL service in ORDS to return database version information. Before that, the web application files must first be created…

./create_deploy_war.py /opt/oracle/ords-22.1.0.105.1723/ords.war /scratch/ords_webapps/tom.jar /scratch/ords_configs/tom/
./create_deploy_war.py /opt/oracle/ords-22.1.0.105.1723/ords.war /scratch/ords_webapps/jerry.jar /scratch/ords_configs/jerry/

That will produce two web application war files in /scratch/ords_webapps/ and I can then deploy them…

Oracle WebLogic Server

Two ORDS web applications deployed to a single WebLogic Server
curl -u hr:hr http://localhost:7001/jerry/hr/_/sql

{
   "database_major_version" : 19,
   "database_minor_version" : 0,
   "database_product_name" : "Oracle",
   "database_product_version" : "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0",
   "env" : {
      "defaultTimeZone" : "GMT",
      "ordsVersion" : "22.1.0.r1051723"
   }
}
curl -u hr:hr http://localhost:7001/tom/hr/_/sql

{
   "database_major_version" : 21,
   "database_minor_version" : 0,
   "database_product_name" : "Oracle",
   "database_product_version" : "Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production\nVersion 21.3.0.0.0",
   "env" : {
      "defaultTimeZone" : "GMT",
      "ordsVersion" : "22.1.0.r1051723"
   }
}

As you can see from the above, the different web applications have different context paths and are configured to use two different databases.

Apache Tomcat

Two ORDS web applications deployed to Apache Tomcat
curl -u hr:hr http://localhost:8080/jerry/hr/_/sql

{
   "database_major_version" : 19,
   "database_minor_version" : 0,
   "database_product_name" : "Oracle",
   "database_product_version" : "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0",
   "env" : {
      "defaultTimeZone" : "GMT",
      "ordsVersion" : "22.1.0.r1051723"
   }
}
curl -u hr:hr http://localhost:8080/tom/hr/_/sql

{
   "database_major_version" : 21,
   "database_minor_version" : 0,
   "database_product_name" : "Oracle",
   "database_product_version" : "Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production\nVersion 21.3.0.0.0",
   "env" : {
      "defaultTimeZone" : "GMT",
      "ordsVersion" : "22.1.0.r1051723"
   }
}

Similar behaviour with Apache Tomcat with two different web applications with their own, separate, configuration.

Summary

What I have demonstrated is a script which produces a new web application for deployment in Apache Tomcat or WebLogic Server. The script is written for Python 2 because that’s the version distributed with Oracle WebLogic Server. It creates a new web application based on the distributed ords.war but without the META-INF content. That content includes a signature digest of the web.xml which will be invalid when the new deployment descriptor is written to the new WAR file.

This is what makes it possible to have multiple ORDS instances deployed to the same server all with different configuration directories.

Duplicate stream parameter

Oracle REST Data Services has a useful Implicit Parameter feature which conveniently are automatically available to use in Resource Module Handlers without declaring them. More information about Resource Module Handlers is available in the Developing Oracle REST Data Services Applications section of the ORDS documentation.

Amongst other things, these implicit parameters provide access to the request body for a POST or PUT either as a BLOB through :body or CLOB through :body_text. For both parameters the documentation says they should only be referred to once.

parameter must be dereferenced only once in a PL/SQL block.

ORDS Implicit Parameter documentation

The documentation for the :body implicit parameter explains why. If it is dereferenced more than once, then the second and subsequent dereferences will appear to be empty. This is because the client sends the request body only once. So the first :body reference will return a BLOB but the second reference will return null. The same applies to :body_text which is a CLOB representation of the same request body.

That is in the Resource Module Handler PL/SQL block if the source references :body and then :body_text the :body_text will return null.

begin
 insert into tab1(content) values (:body); -- request body will be inserted
 dbms_output.put_line('Request body ' || :body_text); -- request body will be null
end;

However, a second call to :body_text will result in an error at runtime – SQL Error Code: 17270, Error Message: Duplicate stream parameter: 2.

begin
 dbms_output.put_line('Request body ' || :body_text); -- request body as CLOB
 dbms_output.put_line('Request body ' || :body_text); -- error occurs
end;

There is an inconsistency here in how a second call to get the request body content is handled but if you’re getting this Duplicate steam parameter error message do review the source for duplicate references to :body_text implicit parameter.

Automation with OSB and ORDS

Oracle REST Data Services implements an Open Service Broker API for each connection pool. More information about the services and plans available through the service broker can be found in the ORDS User Guide – Configuration of Database API with Open Service Broker API Compatible Platforms. This post provides an overview of why an Open Service Broker provided by ORDS could change how you automate the provision of databases and users.

Most software systems require a database for data storage & retrieval and database users to perform operations on that data. Applications can interact with the database through an API specific to the programming language ( such as JDBC ) or REST.

These ORDS OSB services open up new automation possibilities for provisioning databases and users using tools and scripting platforms that developers and system operators will already be familiar with. Scripts to run Oracle DBCA or specific SQL commands can now be replaced with more platform specific artifacts that are used with the tools for that platform. For example, to creat a new User Acceptance Test pluggable database instance by cloning:

kubectl create -f clone-user-acceptance-test-instance.yaml

Where the clone-user-acceptance-test-instance.yaml might look like this:

apiVersion: servicecatalog.k8s.io/v1beta1
   kind: ServiceInstance
   metadata:
     name: pdbtest1-instance
     namespace: ords-osb
   spec:
     clusterServiceClassExternalName: create-pluggable-database
     clusterServicePlanExternalName: clone-database
     parameters:
       pdb_name: pdbtest1
       src_pdb_name: db0918_pdb1

In the above case, the create-pluggable-database service has been defined in the ‘ords-osb’ namespace and the administrator has not had to share the credentials . Sensitive information can be managed in a manner that is more suitable for the platform. The Kubernetes Service Catalog topics for operators provides more information on best practice for this.

Cloning existing pluggable databases is likely to be the fastest way to provision new test environments because the source database can have all the necessary object structures, seed data and users for the application to be tested. Depending on the application there might be more database users required too. ORDS can not only provision pluggable databases through it’s open service broker interface, but also database users and those database users can be REST enabled too!

If you’re already using kubernetes or cloud foundry as part of your development or production infrastructure then take a look at how you can do more with your existing Oracle databases through Oracle REST Database Services.