Putting the squeeze on: Compressing ORDS response size with GZIP

In the previous article, “Optimise Java settings with Application Process Monitoring“, we discussed how to get an insight on memory usage, CPU load, and response times. In this article, we will build on this knowledge by exploring the why, how and when on reducing response size by compressing ORDS responses with GZIP. This can be an effective way to reduce response size and ultimately improve response times as experienced by the end users. That can be especially important in systems where network latency to clients is a challenge. We will look at how to configure GZIP, and explore the trade-offs associated with response size versus CPU load.

GZIP Compression

Http clients, such as a web browser, indicate that they can accept a compressed response by listing the encoding algorithms they understand in the Accepts-Encoding header of the request. GZIP compression is a widely used compression algorithm for unix-based systems that has been around for more than two decades. It allows for reduced bandwidth between a web server and web client, resulting in faster page loading times.

Configuring a web server to compress content responses prior to transmission is beneficial, but it should not be done indiscriminately as it does consume CPU resources. It should be noted that compression can apply to the request received, as well as the response returned but in this article the focus is on making that response size smaller.

How much smaller? On average, one could be looking at response sizes being approximately 15% of the uncompressed file size.

# Uncompressed response size
curl http://localhost:8080/ords/hr/employees/ \
     --silent --write-out "%{size_download}\n" \
     --output /dev/null                         

8317

# Compressed response size
curl http://localhost:8080/ords/hr/employees/ \
     -H "Accept-Encoding: gzip" \
     --silent --write-out "%{size_download}\n" \
     --output /dev/null                         
1401

Compression is not a silver bullet. There are file types, such as most image files and PDF documents, that are already compressed so attempting to compress them again is a waste of CPU cycles. Similarly, small files may not justify the computational cost for a relatively insignificant gain. In fact, if you’re compressing files that are smaller than the Maximum Transmission Unit (MTU) size of a TCP packet (1500 bytes), you are wasting CPU cycles. To ensure that the compression is effective, you should limit it to files with a size greater than 1.4KB (1400 bytes).

Let Jetty Handle Compression

When ORDS is running in standalone mode it is running a specially configured instance of Eclipse Jetty as the web server for receiving HTTP(S) requests. GZIP is so widely used that Eclipse Jetty has a dedicated GZipHandler for requests and responses. In this article we will extend the ORDS standalone jetty server, using a jetty XML configuration file, to apply the handler to responses. When using Eclipse Jetty, you can configure your server to use compression for all responses, or only for responses that meet certain criteria. This flexibility allows you to tailor your server response compression settings to fit your specific needs. In this case, compression will be applied for certain mime-types where compression could help, and when the response size is greater than 128 bytes. That’s quite a low maximum size and there may not be any performance improvement gained but it does allow the demonstration of compression being applied to almost every response.

Note that in this example compression will be applied only for responses to GET requests. This is the default behaviour. However, you can use the GZipHandler documentation to guide you on configuring for more complicated scenarios.

When ORDS is running in a standalone mode, the Eclipse Jetty Home is ${configuration.directory}/global/standalone/. The Jetty XML syntax can be used to configure the Jetty Server for additional functionality by placing configuration XML files in the Jetty Home etc directory. The capability to do this is provided through the Eclipse Jetty server product.

ORDS 22.4 Installation and Configuration Guide

Here’s my ${configuration.directory}/global/standalone/etc/jetty-compression.xml configuration file that inserts the GzipHandler to the ORDS standalone jetty server instance.

<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
      <Call name="insertHandler">
        <Arg>
          <New id="GZipHanlder" class="org.eclipse.jetty.server.handler.gzip.GzipHandler">
            <Set name="IncludedMimeTypes">
              <Array type="java.lang.String">
                <Item>text/html</Item>
                <Item>text/xml</Item>
                <Item>text/css</Item>
                <Item>text/javascript</Item>
                <Item>application/javascript</Item>
                <Item>application/json</Item>
              </Array>
            </Set>
            <Set name="minGzipSize">128</Set>
          </New>
        </Arg>
      </Call>
</Configure>

When ORDS is started in standalone mode with the above file in the configuration directory Eclipse Jetty Home etc folder, all responses will pass through the GzipHandler instance.

> ords --config /path/to/config serve

ORDS: Release 22.4 Production on Fri Feb 03 22:15:56 2023
...
Mapped local pools from /path/to/config/databases:
  /ords/                              => default                        => VALID     


2023-02-03T22:16:12.660Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 22.4.3.r0331239
Oracle REST Data Services server info: jetty/10.0.12
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.13+10-LTS-370

As shown earlier, that applies to application/json responses such as for ORDS REST Services. It also applies to text/html responses for ORDS PL/SQL Gateway and even static content such as the APEX images.

Browser showing compressed content returned for static content such as style sheets.

Conclusion

This article has focused on ORDS in standalone mode and configuring the embedded Eclipse Jetty server instance. Similar compression options can be configured when ORDS is deployed on Apache Tomcat or Oracle WebLogic Server but the settings will be specific to those containers. Check their product documentation for information on how to configure that. The above Eclipse Jetty extension example is only applicable to ORDS standalone mode.

To reiterate, choosing the right compression configuration is important. It will take time, as well as monitoring of the additional resources involved. There may be specific paths where compression is too costly or the GZipHandler interferes with the successful processing of the response.

However, when using compression one should see benefits that include faster page loading times, improved user experience, and reduced bandwidth usage, all of which can help to improve overall performance of your ORDS based web application. Now that you know how, go make efficient use of your bandwidth!

Easily deploy Oracle REST Data Services on Apache Tomcat 10.1


Free and open source software (FOSS) has become an integral part of our lives. For those of us developing, hosting or supporting software applications anyway. Whether it’s a web server, a database, or a programming language, open source software is everywhere. That could be a single jar or javascript library, a framework, a utility or an entire software stack. It is hard to avoid it. Sometimes the free and open source option is the reference implementation for a specification which has a commercial alternative but it can be difficult to justify the cost of paying for that software. More often then not, one can find that modern commercial software systems are built with, or rely on, some form of free and open source software. It’s no surprise, considering all the benefits that come with it. Here are just a few reasons why it makes sense to use FOSS.

The case for free and open source

The reasons for choosing free and open source are compelling.

First and foremost, FOSS is free. This means that you don’t have to pay for a license or subscription, which can be a huge cost savings. It also means that you don’t have to worry about license renewals or updates. All of the software is available for free and can be used indefinitely.

Second, FOSS is open source, which means that anyone can view and modify the code. This makes it easier for developers to customise the software to meet their needs, and it can be a great way to learn programming.

Third, FOSS is typically more secure than proprietary software. The code is open, so any potential security vulnerabilities can be identified and fixed quickly. Additionally, many of these programs are built by volunteers and have a large community of users who can help identify and fix bugs.

Finally, and slightly similar to the previous point, FOSS can have a faster turnaround of fixes than proprietary software. Since the code is open, developers are more likely to fix bugs and make improvements more quickly. This can mean fewer interruptions to your workflow.

Overall, free and open source software makes a lot of sense for individuals and businesses alike. With so many benefits, it’s easy to see why FOSS has become so popular.

Hidden costs to the convenience

Complex systems involve a lot of components which need to be maintained

Unfortunately, many people are not aware of the importance of upgrading their open source software. Upgrading the components of the system is essential for keeping your system secure, stable, and up to date. Over time, vulnerabilities are discovered in software packages and the only way to fix these vulnerabilities is to upgrade to the latest version. By not upgrading, you are essentially leaving your system open to malicious attacks. You are not taking advantage of a core FOSS benefit.

As outlined earlier, upgrading to the latest version of a software component can give you access to bug fixes and performance enhancements that make your system more reliable and efficient. Furthermore, new features can be added to software packages to make them even more powerful and useful. Finally, upgrading open source software can help you stay ahead of the competition. By keeping your software up to date, you can ensure that you are using the latest technologies and staying ahead of the curve. This can help you gain an edge over your competitors and give you a competitive advantage. In conclusion, upgrading open source software is essential for keeping your system secure, stable, and up to date. It also can give you access to bug fixes, performance enhancements, and new features. Finally, it can help you stay ahead of the competition and give you a competitive advantage. So make sure you keep your open source software up to date!

Oracle REST Data Services does rely on components from third parties including FOSS components and critical vulnerabilities and exploits are regularly monitored for known issues that would require an upgrade. Not only that, keeping on top of the security support commitments for those libraries. That is one of the reasons ORDS moved to Eclipse Jetty 10 last year. When a new version of ORDS is available, it is recommended to upgrade as soon as possible.

Why upgrade to Apache Tomcat 10.1 though?

With complex dependencies an upgrade is not always easy

Upgrading is easier said than done though. The interdependencies of the components in a software system can be quite complex. Upgrading to the latest version of one component could cause another component to no longer work. There is a tension between trying to keep existing systems running, introduce new applications / functionality and reduce complexity / costs in the runtime environment.

Which sets the scene for why we’re discussing Apache Tomcat 10.1. Why would someone want to use Apache Tomcat 10.1?

Support considerations

We can expect Tomcat versions released after 2007 to have around 10 years of support before they reach end of life. Tomcat 7, for example, was released in 2011 and reached end of life in 2021. Tomcat 9.0 was released in 2017 and declared stable in 2018. At the time of writing, version 9.0.71 was released earlier this month. No end of life date has been specified but one can expect it to be around 2027. With five years to go for new releases with fixes to bugs and security vulnerabilities there’s no pressing need to move off Tomcat 9.0. However, with Tomcat 8.5 scheduled for EOL at the end of March 2024 anyone currently using Tomcat 8.5 in production would be considering the stable release with the longest runway: Tomcat 10.1.

Jakarta

At this point you’ll note that Tomcat 10.0 does not get a mention. It has already reached it’s EOL and no further builds for that particular release will happen, irrespective of any CVE reported against it. The most likely motivation for moving to Tomcat 10.1 is because it is a Jakarta EE platform.  It builds on Tomcat 10.0.x and implements the Servlet 6.0JSP 3.1EL 5.0WebSocket 2.1 and Authentication 3.0 specifications (the versions required by Jakarta EE 10 platform). For businesses or individuals that deploy multiple applications there may be a conflict with wanting to use Jakarta Servlet API based web applications in the same infrastructure they have Javax Servlet API based web applications. The main difference between Javax Servlet API and Jakarta Servlet API is the addition of new features in the Jakarta version. Jakarta Servlet API adds features such as asynchronous servlet requests, Non-blocking IO, improved security, and better integration with other Jakarta EE APIs. Having one single version of Apache Tomcat to maintain would be preferable.

ORDS running on Apache Tomcat 10.1

Your Jakarta Servlet API based applications and ORDS on the same Apache Tomcat 10.1 !
Photo by Christina Morillo on Pexels.com

ORDS is a web application based on the Javax Servlet API and as such can not be deployed as a regular web application in Tomcat 10.1 which expects web applications to be implemented using Jakarta Servlet API. Thankfully, Apache has provided a comprehensive migration guide to help make transitioning from any version of Tomcat 9 to Tomcat 10 as simple as possible. In addition to that, there is a migration utility which converts the java byte code of the javax.servlet.HttpServlet classes to jakarta.servlet.http.HttpServlet classes. This is really useful because it makes it possible to deploy the generated ords.war using the Apache Tomcat migration tool for Jakarta EE.
https://tomcat.apache.org/migration-10.html#Specification_APIs

Referring to the Deploying ORDS on Apache Tomcat instructions it is simply a matter of generating the ords.war to the correct location:

ords --config /path/to/config war $CATALINA_HOME/webapps-javaee/ords.war

It really is as simple as that. This article was originally going to be a step by step guide but when you get down to it, there’s only one step!

Strictly speaking, Apache Tomcat 10.1 is not an officially supported release of Tomcat for deploying ORDS. At the time of writing it is Tomcat release 8.5.x through to release 9.0.x. Therefore, if there are any ORDS issues encountered the first thing you’ll be asked to do is see does the same issue occur with latest version of ORDS deployed on a recent Tomcat 9.0.x release.

Use of the Apache Tomcat migration tool for Jakarta EE to have ORDS running on Apache Tomcat 10.1 is worth considering if one has Jakarta Servlet API based web applications to deploy and only want a single version of Tomcat running.

Optimise Java settings with Application Process Monitoring

Are you looking for an effective way to monitor the performance of your Oracle REST Data Services deployments? ORDS provides a RESTful interface for Oracle databases. It allows you to access and manipulate data stored in Oracle databases in a secure and efficient manner. That efficiency depends on a multitude of factors. There is a reason you will not find any documented guidance on sizing of JVM memory, garbage collection, or UCP pool size. The appropriate settings are unique to the real world scenarios that the product will be used in. The optimum settings will be different from customer to customer, workload to workload. For most customers the defaults will probably be just fine. As their workload increases, with more concurrent clients, spinning up an additional ORDS instance in their cluster is generally the only change in the deployment topology. Slow response times are generally due to inefficient queries rather than not allocating heap space or the number of CPUs available.

What if you want to delve into the usage of computing resources when ORDS is running? Oracle Application Process Monitoring (Oracle APM) can help you get the job done. It is an enterprise-grade monitoring solution designed to help you identify, analyse, and troubleshoot performance issues in your applications. When you use ORDS with Oracle APM, you can monitor the performance of your deployment in real-time, allowing you to make informed decisions about where and when to make performance tuning changes to the topology. Oracle APM is available as part of the Oracle Cloud Observability and Management Platform. Many of the services and resources require an upgrade to a paid OCI account but the focus here will be on what can be achieved with the Oracle APM services using OCI Free Tier.

Use the preconfigured Application Server dashboard to determine if resource limits are being hit

Overview

In this article we will go over the steps for setting up Oracle APM and using the Oracle APM Java Agent with ORDS 22.4.0 deployed on an Apache Tomcat 9.0.56 server. Oracle APM supports various deployment topologies including Apache Tomcat, Oracle WebLogic Server and in OCI comes with a preconfigured Application Server dashboard.

Although the Oracle APM services are hosted in Oracle Cloud the Oracle APM Java Agent can be used anywhere. In this scenario Oracle APM is used from the OCI Frankfurt region but the Apache Tomcat server running ORDS 22.4.0 is running on-premise. The process for deploying ORDS on Tomcat is already well documented so this article will focus on the Oracle APM aspects.

The steps outlined below are based on Provision and Deploy APM Java Agents on Application Servers but do not follow it to the letter. For further details and more in depth explanation of the process you should refer to that documentation.

Oracle APM is a service in OCI Observability & Management

Create your APM Domain

The APM Domain specifies the Data Upload Endpoint and keys for the APM Java Agent to use at runtime.

The APM Domain is the key resource for using Oracle APM. The domain contains important configuration information about what data is stored and for how long. Follow these steps to create a Free Tier domain. Note that with Free Tier there are limits in place. In the above screenshot you can see that an APM Domain called ORDS has been created. You can use whatever name is suitable for your environment.

Take note of the Data Upload Endpoint and the Private Data Key. You’ll need them when provisioning the APM Java Agent instance which will be used with the Apache Tomcat server later.

Provision the agent

Provisioning the agent is the process of defining the specific properties for a Java Agent instance to be used with a specific Java application at runtime. In this case the Java application is Apache Tomcat which will have an ORDS web application deployed to it. If there were more than one Apache Tomcat server then a Java Agent would have to be provisioned for each. The provisioning process creates a directory containing jars, configuration log directories which will be used at runtime.

In this case the directory will be ~/work/ora_apm but you can use a directory that makes sense for you. The Oracle APM documentation does suggest using a directory where your application server is installed. For example the $CATALINA_HOME for your Apache Tomcat server but in this case a separate directory is used just to keep the APM configuration separate from the Tomcat configuration.

Before we do that, the Java Agent installer must first be downloaded.

Find the download link in the Administration section.

Once downloaded run the installer to provision the Java Agent. Here the server name is specified as ords_1 but that’s just to identify which Java Application is pushing the metrics to Oracle APM. It could be whatever value makes sense for your environment.

java -jar ~/Downloads/apm-java-agent-installer-1.8.3326.jar provision-agent -service-name=ords_1 -destination=~/work/ora_apm -private-data-key=AAA5UN2C6YOWWWUZ5Q7UUU3QACF4BBB -data-upload-endpoint=https://aaaaaaaaaaaaaaaaaaaa.apm-agt.eu-frankfurt-1.oci.oraclecloud.com

That creates a ApmAgentInstall.log file and oracle-apm-agent directory in ~/work/ora_apm. Your provisioned Java Agent, including jar files and configuration details, is in the oracle-apm-agent directory.

Start Apache Tomcat

Now that you have a Java Agent it can be specified when starting Apache Tomcat. In this environment the Apache Tomcat installation is at ~/work/apache/tomcat/9.0.56/. The Oracle APM documented steps for deploying to Apache Tomcat does outline steps for modifying the catalina.sh script. That’s the best thing to do so that the Java Agent is applied every time Tomcat starts. However, in this case the JAVA_OPTS environment variable will be used. When specifying the javaagent parameter the full directory path must be used.

export JAVA_OPTS="-javaagent:~/work/ora_apm/oracle-apm-agent/bootstrap/ApmAgent.jar"
~/work/apache/tomcat/9.0.56/bin/catalina.sh start

Using CATALINA_BASE:   /Users/peobrie/work/apache/tomcat/9.0.56
Using CATALINA_HOME:   /Users/peobrie/work/apache/tomcat/9.0.56
Using CATALINA_TMPDIR: /Users/peobrie/work/apache/tomcat/9.0.56/temp
Using JRE_HOME:        /Library/Java/JavaVirtualMachines/jdk-11.0.13.jdk/Contents/Home
Using CLASSPATH:       /Users/peobrie/work/apache/tomcat/9.0.56/bin/bootstrap.jar:/Users/peobrie/work/apache/tomcat/9.0.56/bin/tomcat-juli.jar
Using CATALINA_OPTS:   
Tomcat started.
Apache Tomcat log shows Oracle APM and ORDs startup output

Monitor performance

Once Tomcat is up and running, you can begin monitoring application performance. This can be done using the APM console, or by setting up custom dashboards and alerts. The later features only available if you have upgraded to a paid OCI account. Time to review what metric information has been sent to Oracle APM data upload endpoint for your domain.

Navigate to the Dashboards page
Choose the Application Server Dashboard

The Oracle-defined dashboards are listed on the Dashboards page. When a dashboard is initially selected one must specify the following details:

  • Select the compartment in which your APM domain resides. Note that if a compartment is selected on the Dashboards page, then it’s displayed by default.
  • Select the APM domain that contains the systems being monitored by Application Performance Monitoring.
  • Select the resource that you want to monitor. For example, in the App Server dashboard, select an application server in the App Server drop-down list.
  • Select the time period for which you want data to be populated in the dashboard.

The App Server dropdown values will correspond to the Apache Tomcat server that the Oracle APM Java Agent is being used with. After you specify the details, the dashboards are populated with data and provide a quick insight into the health and performance of your application and enable you to identify the area that requires your attention.

The Oracle-defined dashboards, including the default Home dashboard will include information on metrics and APM resources which are not available in the Free Tier account. In this article we focus on the App Server dashboard.

The App Server dashboard is the Home page for your application servers and enables you to analyse the resources used by your application servers and understand resource constraints and requirements. For instance, you can monitor this dashboard to analyse the heap and CPU usage of your application server.

No performance issues highlighted

In the above example heap used does get close to the heap committed so there may be some tuning to apply. One could apply some Java Performance Tuning options there to optimise for throughput, faster response times or just memory footprint. Note that the CPU load is quite low which suggests that there’s no need for additional ORDS instances.

But wait, there’s more

Not only do you have information on the resource usage of the Java application but there’s also tracing information on the requests received by ORDS. Moreover, that has a breakdown on time spend executing SQL queries and that can be really useful when optimising services. For example, running a SELECT on a table which is REST Enabled could take longer than necessary if there are a lot of columns. In some cases, a custom query to just return the data needed, using indexed columns, may the correct solution. This information is in the Oracle APM Trace Explorer. The restriction of the OCI Free Tier is that one can only have 1000 traces an hour so not all metrics for all requests are persisted.

Explore the trace data for requests
Tracing data on the AutoREST service for EMPLOYEES table

The options for tracing services will be explored in a later article. Those on the Free Tier will benefit from reviewing them but will only have a small snapshot of the metrics and diagnostics data.

Conclusion

In this brief overview, you have seen the configuration and use of Oracle APM with ORDS ( and it could be any web application for that matter ) deployed on Apache Tomcat. Even in the Free Tier, with the hosted Oracle APM services provide a great performance insight option for anyone on a budget.

To get a more detailed understanding of what Oracle APM could do for you take some time to go through the article announcing it’s general availability. It’s powerful stuff.

[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.