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.
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
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 EMPLOYEESData 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:
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
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.
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.
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…
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.
Or at least the majority of data types. In a previous post, MySQL Sakila Structure, I went through the structure of the example sakila database and mentioned that it does not cover all supported MySQL data types. That means the example database is not sufficient on its own to test the behaviour of the MySQL Connector/J JDBC driver and how it works with different data types. In an attempt to bridge that gap I have produced a script to create a single table with all the supported data types along with modifiers that have an impact on how MySQL Connector/J represents them. See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html for more details on the nuances of the implementation’s handling of particular data types.
MySQL has a sample database which is useful for tutorials, samples and a reference for testing integration scenarios. The scripts to create the sakila sample schema put tables, views, procedures, triggers and data in place.
The sample also includes the sakila.mwb file which is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, see MySQL Workbench.
Data model of sakila sample database
Not all MySQL data types are used in the sample schema but it does cover the most common as well as a BLOB for a picture and GEOMETRY for location data. Here’s a description of each table in a convenience text structure which is searchable.
describe actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe actor_info;
+------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id | smallint unsigned | NO | | 0 | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| film_info | text | YES | | NULL | |
+------------+-------------------+------+-----+---------+-------+
describe address;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| address_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| address | varchar(50) | NO | | NULL | |
| address2 | varchar(50) | YES | | NULL | |
| district | varchar(20) | NO | | NULL | |
| city_id | smallint unsigned | NO | MUL | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| location | geometry | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe category;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| category_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
describe city;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| city_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| city | varchar(50) | NO | | NULL | |
| country_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe country;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| country_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| country | varchar(50) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe customer_list;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ID | smallint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| notes | varchar(6) | NO | | | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+-------------------+------+-----+---------+-------+
describe film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(128) | NO | MUL | NULL | |
| description | text | YES | | NULL | |
| release_year | year | YES | | NULL | |
| language_id | tinyint unsigned | NO | MUL | NULL | |
| original_language_id | tinyint unsigned | YES | MUL | NULL | |
| rental_duration | tinyint unsigned | NO | | 3 | |
| rental_rate | decimal(4,2) | NO | | 4.99 | |
| length | smallint unsigned | YES | | NULL | |
| replacement_cost | decimal(5,2) | NO | | 19.99 | |
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
| special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
describe film_actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id | smallint unsigned | NO | PRI | NULL | |
| film_id | smallint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe film_category;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | |
| category_id | tinyint unsigned | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| staff_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| picture | blob | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| username | varchar(16) | NO | | NULL | |
| password | varchar(40) | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
describe staff_list;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID | tinyint unsigned | NO | | 0 | |
| name | varchar(91) | YES | | NULL | |
| address | varchar(50) | NO | | NULL | |
| zip code | varchar(10) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| SID | tinyint unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
describe store;
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| store_id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| manager_staff_id | tinyint unsigned | NO | UNI | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
That’s it. All the tables and views in the sakila sample database described.
Notice that sakila does not cover all MySQL data types such as TINYBLOB, VARBINARY etc. The next blog post will examine how the MySQL Connector/J JDBC driver represents that data and introduce a new table for testing all the supported data types.
Peter's Random Topic Library 