Beyond the Common Name: Secure multiple domains with a single self-signed certificate

Oracle REST Data Services (ORDS) will automatically generate a self-signed automatically when starting for the first time in standalone mode. That is, if it has not already been configured with an existing cert and key. The Common Name used for the self-signed certificate is taken from the configuration setting. If not set, the value is localhost. Traditional certificate practices often rely solely on the Common Name (CN) field. That’s the approach taken by ORDS by default when generating the self-signed certificate. The upshot is that you are limited to one certificate for one domain. In this article, we explore the untapped potential of self-signed certificates with Subject Alternative Name (SAN) extension, enabling you to secure multiple domains efficiently

Understanding Self-Signed Certificates

Self-signed certificates are created and signed by the same entity without involving a trusted third-party Certificate Authority (CA). They provide encryption and authentication capabilities, although they are not initially recognised by most browsers and operating systems. Self-signed certificates can be manually trusted or imported, making them a viable option for specific use cases.

Subject Alternative Name (SAN)

SAN is an extension within the X.509 certificate standard that allows multiple domain names to be associated with a single certificate. Unlike the limited scope of the Common Name field, SAN enables you to secure multiple domains or subdomains with one certificate, simplifying management and reducing costs.

Benefits of Self-Signed Certificates with SAN

  • Enhanced Flexibility: Self-signed certificates with SAN offer the flexibility to secure numerous domains or subdomains, accommodating complex web architectures and diverse configurations.
  • Cost-Effectiveness: By leveraging self-signed certificates, you can save costs associated with purchasing individual certificates for each domain.
  • Internal Environments: Self-signed certificates with SAN are especially valuable for securing internal networks, intranets, or testing environments that don’t require public trust.
  • Rapid Deployment: Generating and deploying self-signed certificates with SAN is a swift and straightforward process, making it ideal for time-sensitive projects.

Considerations and Best Practices:

  • Trust Management: Users must manually trust self-signed certificates, so clear instructions or documentation on certificate importation are essential.
  • Certificate Lifecycle: Although self-signed certificates have no defined expiration period, regular rotation is recommended to uphold security best practices.
  • Public-Facing Websites: For public-facing websites, obtaining trusted certificates from recognised CAs is crucial to establish trust with visitors and avoid browser warnings.
  • Hybrid Approach: Consider combining self-signed certificates with SAN for internal domains and trusted CA certificates for public-facing domains to strike a balance between cost-efficiency and public trust.
  • Load Balancers: For public-facing websites, use a load balancer in front of your ORDS instance(s). That way you can aim for high availability but also configure single Common Name certs for public-facing services.

Generate your self-signed SAN cert…

Try saying that fast three times 😀

There’s no command in ORDS to generate self-signed certificate with a Subject Alternative Name ( SAN ) field. To achieve this we’ll use the openssl utility which is most likely already available on your machine. The example openssl command below can be executed where the ORDS configuration directory is the working directory. The output is two files with the same name as what ORDS uses as the default standalone.https.cert.key and standalone.https.cert configuration settings respectively.

openssl req -x509 -nodes -days 365 \
       -newkey rsa:2048 \
       -keyout global/standalone/self-signed.key \
       -out global/standalone/self-signed.pem \
       -subj "/CN=localhost" \
       -addext "subjectAltName =, DNS:myhost.example

In the above example the self-signed certificate is generated with localhost in the Common Name ( CN ) field. This is the same as the certificate that would normally be generated by ORDS. However, that certificate also has a subjectAltName field listing two DNS hostnames: and myhost.example. Start up ORDS and your traffic to the server will be secured for both domains. Note that you will have to look after your DNS network routing so that the domain names you choose route to the machine you are running ORDS on.

That certificate and key could also be used with your load balancer to accept HTTPS traffic too. As mentioned earlier, this is really only suitable for internal environments where you can have your users manually trust the certificate.


Ditching the limitations of the Common Name field, self-signed certificates with Subject Alternative Name (SAN) extension enable you to secure multiple domains with ease. While self-signed certificates may not be suitable for public-facing websites, these certificates provide numerous benefits for internal environments or specific use cases. By using self-signed certificates with SAN in your ORDS instance, you can fortify your infrastructure security while efficiently managing multiple domains, all without breaking the bank.

How to check if ORDS is running

Oracle REST Data Services (ORDS) provides web services for an Oracle Database and allows for access to the database over the web. In a recent YouTube video I showed how to use ORDS in the new Oracle Developer DB Virtual Machine and briefly mentioned how to find the running ORDS process. Let’s go into that in more detail. In this article, we will discuss how to check if ORDS is running in a standalone ORDS deployments as well as deployments on Apache Tomcat and Oracle WebLogic Server.

  1. Checking ORDS on Standalone Deployments
  2. Checking ORDS on Apache Tomcat
  3. Checking ORDS on Oracle WebLogic Server
  4. What about Windows systems?
  5. Conclusion

Checking ORDS on Standalone Deployments

On a unix based system, the simplest way to check if ORDS is running is to use the jps command. This command will list all Java process that are currently running on the system. It is important to note that in order for jps to work, the JDK must be installed and the PATH environment variable must be set correctly. The jps command accepts a number of options and the most useful in this case are

  • -m to display the arguments passed to the main method
  • -l to display the full path name to the ords.war location. Pass that through a grep for ords to find the ords specific java process.
  • -v displays the arguments passed to the JVM.
jps -mlv | grep ords
8545 /home/oracle/ords/ords.war --config /home/oracle/ords_config serve --secure --port 443 -Doracle.dbtools.cmdline.home=/home/oracle/ords -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8 -Djava.awt.headless=true -Dnashorn.args=--no-deprecation-warning -Doracle.dbtools.cmdline.ShellCommand=ords -Duser.timezone=UTC

If ORDS is running in standalone mode, you should see a process with ords.war in the path. If this process is listed, then ORDS is running. Note that due to the -v option you also see the Java option oracle.dbtools.cmdline.home used. That should be the folder that the ords.war is found in and should also be the distribution directory. In the above example, the distribution directory is /home/oracle/ords/ and the ORDS command used to start ORDS was actually /home/oracle/ords/bin/ords --config /home/oracle/ords_config serve --secure --port 443

Another approach is to use the ps command. This command will list all processes currently running on the system. To check if ORDS is running, use the command ps -ef | grep ords.war. If you see a process with ords.war as the command, then ORDS is running.

[oracle@localhost ~]$ ps -ef | grep ords.war
oracle      8545    8516  2 21:13 pts/1    00:00:49 /home/oracle/java/jdk-11.0.17/bin/java -Doracle.dbtools.cmdline.home=/home/oracle/ords -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8 -Djava.awt.headless=true -Dnashorn.args=--no-deprecation-warning -Doracle.dbtools.cmdline.ShellCommand=ords -Duser.timezone=UTC -jar /home/oracle/ords/ords.war --config /home/oracle/ords_config serve --secure --port 443
oracle      9554    3521  0 21:42 pts/0    00:00:00 grep --color=auto ords.war

You’ll also see similar information as with the jps command but also the java executable used at start up too. Note that access to run ps is not always guaranteed and it main not be available for the user you are logged in as.

One note about the command line arguments one can see for the running Java processes. It can be seen by other users. This is one of the reasons that ORDS does not accept a password as a command line option. That password would be visible to anyone able to list the running processes. Either through jps or ps.

Checking ORDS on Apache Tomcat

If it’s enabled, use the Tomcat manager web application, or at the very least the catalina log files, to confirm that ORDS web application is deployed and started. If not, then jps on the command line is the next logical choice.

When ORDS is deployed on Apache Tomcat, you can use the jps command to check if the server is running and get further information about where the ords.war might be deployed. Run jps -mlv as before but this time grep for the catalina package.

jps -mlv | grep catalina
88 org.apache.catalina.startup.Bootstrap start --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/ --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.util.concurrent=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED -Djava.util.logging.config.file=/usr/local/tomcat/conf/ -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Dconfig.url=/u01/oracle/properties/config -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dignore.endorsed.dirs= -Dcatalina.base=/usr/local/tomcat -Dcatalina.home=/usr/local/tomcat

The above indicates that catalina.base is /usr/local/tomcat and most people use the automatic web application deployment by placing the ords.war in the $CATALINA_BASE/webapps/ directory.

ls -la /usr/local/tomcat/webapps/
total 92384
drwxr-xr-x 1 root root       62 Apr 11 06:40 .
drwxr-xr-x 1 root root       77 Apr 11 06:40 ..
drwxr-x--- 5 root root       67 Apr 11 05:38 ords
-rw-r--r-- 1 root root 94599760 Apr 10 19:45 ords.war
drwxr-xr-x 3 root root       45 Apr 11 05:38 ROOT

That directory listing shows the ords.war file and that it has been exploded by Tomcat into the ords directory. It’s not a guarantee that ORDS was deployed successfully, one would have to check the Tomcat logs, but it is a good indicator.

Checking ORDS on Oracle WebLogic Server

If it’s enabled, use the WebLogic console or WLST to confirm that ORDS web application is deployed and started.

Similar to Apache Tomcat, the Oracle WebLogic Server is a Java application so yet again, it is the jps utility that is the starting point to get information on the server(s) running and their configuration.

jps -mvl | grep weblogic
80 weblogic.Server -Dlaunch.use.env.classpath=true -Dweblogic.Name=AdminServer -Dconfig.url=/u01/oracle/properties/config -javaagent:/u01/oracle/wlserver/server/lib/debugpatch-agent.jar -da -Dwls.home=/u01/oracle/wlserver/server -Dweblogic.home=/u01/oracle/wlserver/server

The output indicates that the weblogic.home is /u01/oracle/wlserver/server. Unlike with Tomcat, one is not going to find a straight forward web application deployment directory under the weblogic.home. There’s a little more digging required to check if a server is configured to deploy ORDS and we’ll use the weblogic.Name parameter ( AdminServer in this case) to when digging deeper. The weblogic.home just indicates where the server java application is being executed from. The server runtime configuration is being picked up separately. In the majority of installations that will be in a related user_projects/domains directory found two levels up. For the above WLS instance that would be /u01/oracle/user_projects/domains/.

That directory may have the configuration for multiple domains but quite often there is just one: base_domain. From there you can get a little more information on how the server identified by the above weblogic.Name. The domain configuration is persisted in /u01/oracle/user_projects/domains/base_domain/config/config.xml and you can see there is an app-deployment configuration targetting the AdminServer.

<?xml version="1.0" encoding="UTF-8"?>
<domain xsi:schemaLocation="" xmlns="" xmlns:sec="" xmlns:wls="" xmlns:xsi="">

If you see this app-deployment configuration in place it is not a complete guarantee that ORDS successfully deployed when the server started but it is a good indication.

What about Windows systems?

The syntax for path separates might be different but the same jps utility options apply and the relative file locations will also be the same for standalone, Tomcat and WebLogic.


In summary, the jps utility, also known as the Java Virtual Machine Process Status Tool, that comes with your Java SE distribution is your friend. Just from the command line, it can guide you in discovering more about the ORDS instance running on your machine irrespective of deployment mode.

Oracle Developer DB Virtual Machine – a closer look

The recently announced Oracle Developer DB Virtual Machine comes with ORDS 23.1.0 and the new Oracle 23c Database Free. In this video I walk through starting the virtual machine, restarting ORDS to use HTTPS, accessing ORDS from the host machine as well as stopping and restarting the virtual machine. The new “converged database” has a number of new features that ORDS works with. One that is not widely known is the Property Graph and it is demonstrated in this video.

Links that are mentioned in the video

Developer DB VM:

Jeff Smith’s Announcement:

Information about Property Graph:

Crafting your ORDS plugin the Maven way

This article provides a step-by-step guide to setting up an Oracle REST Data Services (ORDS) plugin using Maven. It covers the installation of two ORDS jars into the local Maven repository, the creation of the project structure, the configuration of the plugin, and the building and deployment of the plugin. This guide will help developers get started quickly with ORDS plugin development where Maven is already used in their software development infrastructure.

  1. Introduction
  2. Step 1: Install ORDS Jars Into the Local Maven Repository
  3. Step 2: Set Up the Project Structure
  4. Step 3: Create Configuration Files and Source
  5. Step 4: Build and Deploy the Plugin
  6. Step 5: Verify the Plugin
  7. Conclusion


ORDS provides a plugin framework which makes it possible to extend the functionality and introduce custom behaviour. Creating a plugin can be a daunting task, especially if you’re not familiar with ORDS or the underlying technologies. Fortunately, there are some great examples out there to get you started, including the ORDS YAML plugin example. However, the plugin example projects are Ant-based, so if you’re looking to use Maven for your plugin project, you’ll need to take a few extra steps.

These steps have Maven as a prerequisite and it is assumed your familiarity with Maven is the reason you’re here, reading this article. If you need more information on Maven then start with What is Maven?

Step 1: Install ORDS Jars Into the Local Maven Repository

An ORDS plugin has a dependency on two jars that are distributed with ORDS. The ords-plugin-api jar provides all the interfaces, annotations and base classes described in the ORDS Java API which are the building blocks for your plugin implementation. The ords-plugin-apt jar provides the annotation processing support required when building your plugin. These two jar files can be found in the examples/plugins/lib/ directory of ORDS distribution.

First, you’ll need to install the two ORDS jars into your local Maven repository. To install the jars, you’ll need to use the command line. Navigate to the directory where the ORDS was extracted to, and then run the following two commands:

> mvn install:install-file \
  -DgroupId=oracle.dbtools.ords \
  -DartifactId=ords-plugin-api \
  -Dversion=22.4.4 \
  -Dpackaging=jar \
  -Dfile=examples/plugins/lib/ords-plugin-api-  \

> mvn install:install-file \
  -DgroupId=oracle.dbtools.ords \
  -DartifactId=ords-plugin-apt \
  -Dversion=22.4.4 \
  -Dpackaging=jar \
  -Dfile=examples/plugins/lib/ords-plugin-apt-  \

That will put two artefacts in your local maven repository both as version 22.4.4. That is the version of ORDS that this article refers to but you can change the version number to be reflect the version of ORDS you are using.

Step 2: Set Up the Project Structure

This guide is based on the ORDS YAML plugin example and will use the source from that article. The plugin can modify the application/json response which would normally be returned by ORDS and that response in text/yaml format instead.

For simplicity we’ll use maven-archetype-quickstart as outlined in Maven in 5 Minutes to create the project structure. The focus here is on creating an example, so that term makes sense as the group identifier. You should use your existing Maven artefact nomenclature for identifiers. In the directory where you typically have your Maven projects run the following command:

> mvn archetype:generate \
    -DgroupId=example \
    -DartifactId=ords-yaml-plugin \
    -DarchetypeArtifactId=maven-archetype-quickstart \
    -DarchetypeVersion=1.4 \

[INFO] Scanning for projects...
[INFO] ---------< org.apache.maven:standalone-pom >----------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] -----------------------[ pom ]------------------------
[INFO] ------------------------------------------------------
[INFO] ------------------------------------------------------
[INFO] Total time:  4.779 s
[INFO] Finished at: 2023-03-27T23:18:25+01:00
[INFO] ------------------------------------------------------

You should now have an ords-yaml-plugin directory which looks like this:

|-- pom.xml
`-- src
    |-- main
    |   `-- java
    |       `-- example
    |           `--
    `-- test
        `-- java
            `-- example

The generated and files can be removed or ignored. They serve no purpose but to provide placeholders in the source directory. Change directory into ords-yaml-plugin and verify the project builds.

> cd ords-yaml-plugin 
> mvn clean package
[INFO] Scanning for projects...
[INFO] -------------< example:ords-yaml-plugin >-------------
[INFO] Building ords-yaml-plugin 1.0-SNAPSHOT
[INFO] -----------------------[ jar ]------------------------
[INFO] ------------------------------------------------------
[INFO] ------------------------------------------------------
[INFO] Total time:  2.755 s
[INFO] Finished at: 2023-03-27T23:39:42+01:00
[INFO] ------------------------------------------------------

Once that completes you should see target/ords-yaml-plugin-1.0-SNAPSHOT.jar file has been produced. Let’s make it interesting by adding the to the project.

Step 3: Create Configuration Files and Source

To compile the ORDS plugin there are classpath dependencies that must be met. Modify the ords-yaml-plug project pom.xml and put these dependencies just after the <dependencies> element at line #21.


Most of those dependencies will be satisfied by the artefacts already available in the central Maven repository and you have addressed the remaining two dependencies by putting the ORDS plugin api and apt jars in your local repository earlier.

Download the source for the plugin class into the source package directory created when the Maven project was created.

> curl --output src/main/java/example/ \

That example was written for an earlier version of ORDS which used the javax.inject library. ORDS 22.4.4 uses the jakarta.inject library. So edit the java source and replace the import statement at line #15

import javax.inject.Inject;


import jakarta.inject.Inject;

With that minor change in place you are now ready for the next step: build and deploy the plugin.

Step 4: Build and Deploy the Plugin

It’s as simple as running this on the command line:

> mvn clean package
[INFO] Scanning for projects...
[INFO] ----------------------< example:ords-yaml-plugin >----------------------
[INFO] Building ords-yaml-plugin 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] --- maven-clean-plugin:3.1.0:clean (default-clean) @ ords-yaml-plugin ---
[INFO] Deleting /development/github/ords-yaml-plugin/target
[INFO] --- maven-resources-plugin:3.0.2:resources (default-resources) @ ords-yaml-plugin ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /development/github/ords-yaml-plugin/src/main/resources
[INFO] --- maven-compiler-plugin:3.8.0:compile (default-compile) @ ords-yaml-plugin ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 2 source files to /development/github/ords-yaml-plugin/target/classes
[INFO] --- maven-resources-plugin:3.0.2:testResources (default-testResources) @ ords-yaml-plugin ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /development/github/ords-yaml-plugin/src/test/resources
[INFO] --- maven-compiler-plugin:3.8.0:testCompile (default-testCompile) @ ords-yaml-plugin ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 1 source file to /development/github/ords-yaml-plugin/target/test-classes
[INFO] --- maven-surefire-plugin:2.22.1:test (default-test) @ ords-yaml-plugin ---
[INFO] -------------------------------------------------------
[INFO] -------------------------------------------------------
[INFO] Running example.AppTest
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.041 s - in example.AppTest
[INFO] Results:
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
[INFO] --- maven-jar-plugin:3.0.2:jar (default-jar) @ ords-yaml-plugin ---
[INFO] Building jar: /development/github/ords-yaml-plugin/target/ords-yaml-plugin-1.0-SNAPSHOT.jar
[INFO] ------------------------------------------------------------------------
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  4.580 s
[INFO] Finished at: 2023-03-28T00:34:29+01:00
[INFO] ------------------------------------------------------------------------

Now your target/ords-yaml-plugin-1.0-SNAPSHOT.jar file really does have the capability of doing something interesting but it must be put in the lib/ext/ directory of your ORDS installation directory first. Many of the runtime dependencies will be met by the jars distributed with ORDS. However, this particular plugin requires certain YAML related jars to also be in the runtime classpath: snakeyaml and jackson-dataformat-yaml. These jars will also have to be copied to the lib/ext/ directory.

The jackson-dataformat-yaml artefact will be in your local Maven repository because it was pulled in for this ords-yaml-plugin project and that can just be copied over. However, for snakeyaml you may have to download that file.

Let’s assume the directory that ORDS was extracted to can be referred to by the environment variable $ORDS_HOME.

To download the snakeyaml jar so that it is included in the ORDS runtime classpath run this command:

> curl -o $ORDS_HOME/lib/ext/snakeyaml-1.33.jar \

To copy the jackson-dataformat-yaml jar so that it is included in the ORDS runtime classpath run this command:

> cp ~/.m2/repository/com/fasterxml/jackson/dataformat/jackson-dataformat-yaml/2.14.2/jackson-dataformat-yaml-2.14.2.jar \

Unless you want to change the versions of these jars used, you only need to do this once. Every time you build a new ords-yaml-plugin jar you will have to copy it to the ORDS library extension directory. To copy the jar file you built, run this command while in your ords-yaml-plugin project directory:

> cp target/ords-yaml-plugin-1.0-SNAPSHOT.jar \

Your ORDS directory should look like this:

|-- FUTC.txt
|-- bin
|-- doc
|-- examples
|-- icons
|-- index.html
`-- lib
    |-- ext
    |   `-- README
    |   `-- jackson-dataformat-yaml-2.14.2.jar
    |   `-- ords-yaml-plugin-1.0-SNAPSHOT.jar
    |   `-- snakeyaml-1.33.jar
|-- license.txt
|-- linux-support
|-- ords.war
|-- scripts

Now just start ORDS in standalone mode with a configuration directory setup to use your preferred database.

> $ORDS_HOME/bin/ords --config /path/to/config serve
ORDS: Release 22.4 Production on Tue Mar 28 00:14:06 2023

Copyright (c) 2010, 2023, Oracle.


2023-03-28T00:14:06.522Z INFO        HTTP and HTTP/2 cleartext listening on host: port: 8080
2023-03-28T00:14:23.618Z INFO        

Mapped local pools from /path/to/config/databases:
  /ords/                              => default                        => VALID     

2023-03-28T00:14:23.662Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 22.4.4.r0411526
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

ORDS started up without issue so you plugin is effectively deployed and ready to work.

Step 5: Verify the Plugin

It’s time to confirm that the PluginYaml class does in fact transform the application/json response to text/yaml when the client requests that format. Send a request to ORDS that would normally return application/json but indicate that text/yaml is preferred. In my database I have the HR schema, and it’s EMPLOYEES table, REST Enabled and accessible at http://localhost:8080/ords/hr/employees/. The plugin is looking for Accepts header in the request to see if it should transform the response.

> curl -i -H "Accepts: text/yaml" 'http://localhost:8080/ords/hr/employees/?limit=2'
HTTP/1.1 200 OK
Content-Type: text/yaml
ETag: "PJjqS1hHizmvT8/WpBBub/3wIZ3HcdY6w/hNFD279DyLK4Prvk+q7BlnmHeKEfnQu7Ek7gGqeT86ltVBQcky6Q=="
Transfer-Encoding: chunked

- employee_id: 100
  first_name: "Steven"
  last_name: "King"
  email: "SKING"
  phone_number: "515.123.4567"
  hire_date: "1987-06-17T00:00:00Z"
  job_id: "AD_PRES"
  salary: 24000
  commission_pct: null
  manager_id: null
  department_id: 90
  - rel: "self"
    href: "http://localhost:8080/ords/hr/employees/100"
- employee_id: 101
  first_name: "Neena"
  last_name: "Kochhar"
  email: "NKOCHHAR"
  phone_number: "515.123.4568"
  hire_date: "1989-09-21T00:00:00Z"
  job_id: "AD_VP"
  salary: 17000
  commission_pct: null
  manager_id: 100
  department_id: 90
  - rel: "self"
    href: "http://localhost:8080/ords/hr/employees/101"
hasMore: true
limit: 2
offset: 0
count: 2
- rel: "self"
  href: "http://localhost:8080/ords/hr/employees/"
- rel: "edit"
  href: "http://localhost:8080/ords/hr/employees/"
- rel: "describedby"
  href: "http://localhost:8080/ords/hr/metadata-catalog/employees/"
- rel: "first"
  href: "http://localhost:8080/ords/hr/employees/?limit=2"
- rel: "next"
  href: "http://localhost:8080/ords/hr/employees/?offset=2&limit=2"


By following the steps in this article you have used Maven to compile and package an ORDS plugin. There were a few once of steps to get dependencies addressed but you now have a Maven project structure for developing more extensions to the existing ORDS behaviour.

If you’re looking to build an ORDS plugin, Apache Maven is a great choice because it provides a uniform build system and there is a huge range of Integrated Development Environments and Source Control Systems that work with Maven. Give it a try today and see how it can help you take your development efforts to the next level.

Don’t Worry, Be Chatty: How ChatGPT can help with ORDS service errors

Using Oracle REST Data Services (ORDS) to create web services with query or PL/SQL back-end services can be a powerful way to access and manipulate data stored in an Oracle database. However, it is important to be aware that when defining the service handler source code, there is always the possibility of encountering an error at runtime which can be difficult to debug and resolve. This article explains how to use ChatGPT to suggest an explanation for the error reported at runtime.

  1. What’s the problem?
  2. Ask a chatbot
    1. Why ChatGPT?
    2. Get an API key
  3. ORDS Custom Error Pages
    1. How the error page is rendered
    2. Custom error page for status 555 – User Defined Resource Error
    3. Configure the custom error pages location
  4. ChatGPT: Explaining runtime errors
    1. Helpful explanations
  5. Conclusion

What’s the problem?

ORDS is used to create, manage, and run RESTful web services. Developers can write their own REST APIs by simply providing the SQL or PL/SQL behind the endpoints. This is the module/template/handler paradigm mentioned in Developing Oracle REST Data Services Applications. It is always a possibility that when developing a service a mistake is made, or something changes in the system which breaks a service that was previously working. Diagnosing the problem, and then fixing it, is not always straight forward.

A PL/SQL handler source that works fine when there’s only one employee called John

When an error occurs while executing the query or PL/SQL block and it appears to be due to the handler source then ORDS will return a HTTP 555 User Defined Resource Error response. This indicates that an error occurred, but it was not due to problems connecting to the database, or with authorisation, and therefore a problem with executing the handler source in the database. In other words, it is an issue that the developer of the handler should be able to address. However, there may not be any more information than that and a developer would have to understand the ORA error codes and error message mentioned by the database.

debug.printDebugToScreen – Global setting
Specifies whether to display error messages on the browser.
Supported values: true | false (default)

ORDS Installation and Configuration Guide – C.3 Understanding the Configurable Settings

Having debug.printDebugToScreen enabled in the environment can provide more context but resolving the issue still requires familiarity with what the database is complaining about.

The error page indicates the problem with the handler source but cannot provide clear information on how to fix it.

Ask a chatbot

A chatbot is a computer program that is designed to simulate conversation with human users through written or spoken messages. Chatbots are typically used in customer service, online helpdesks, and other online services to provide answers to common questions and help customers find the information they need. In this case the goal is to ask a chatbot for a suggested solution to the user defined resource error. More specifically, when displaying the error message to also display the answer to the question How can I fix this user defined resource error I encountered in Oracle REST Data Services? It’s not so much a back and forth conversation, but the first answer can be quite useful.

Why ChatGPT?

The chatbot used in this article is ChatGPT, specifically: gpt-3.5-turbo, but in theory one can integrate with any AI model sufficiently broad enough that can handle conversations around SQL and PL/SQL development. ChatGPT is a natural language processing (NLP) model developed by OpenAI that uses deep learning to generate human-like responses to a given input. The model is quite accurate and can generate realistic responses that mimic how a human would converse. To over simplify it, based on the huge amount of conversations it has seen before, the model predicts what’s the next word to output when generating text. ChatGPT is not perfect and could in fact return a vague or inaccurate answer but in the majority of cases, the answer is helpful.

Get an API key

To interact with the OpenAI chat API to ask a question of the ChatGPT model an API key is required. Get your own API key at You will need it later in the custom error page.

OpenAI API Keys –

ORDS Custom Error Pages

ORDS allows users to customise the appearance of error pages that are displayed when an error occurs. Custom error pages can be defined to include text, graphics, and HTML elements that can be used to provide helpful information to users when an error occurs. In the majority of cases where ORDS customers configure custom error pages it is generally to have specific branding in place and perhaps additional links for their own project description, documentation and support.

How the error page is rendered

The error page in ORDS is a moustache template file which is used to generate a HTML response based on a few parameters. It is worthwhile getting an understanding of the moustache syntax. However, the simplest explanation of its use in this case is that certain portions of the static HTML template file are replaced with parameters at runtime. These parameters have specific names:

  • statusCode – The HTTP status code to display. Custom error pages filenames also correspond to status code but more about that later.
  • statusMessage – The text that corresponds to the statusCode.
  • reasons – A HTML block of List Items ( <li>...</li> ) with text descriptions of one or more reasons for the error reported.
  • unauthorized – A HTML block with a link to the sign-in page if the error is an authentication or authorisation error.
  • debug – A HTML block for displaying the debug.printDebugToScreen output.

Not all of the above parameters are set at runtime but for HTTP 555 User Defined Resource Error the statusCode, statusMessage and reasons are. As mentioned above, the reasons parameter is a HTML block which will look like this:

  <span style="font-size: 1.1em;">
The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 1422, Error Message: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

In the ORDS distribution one can review the error.html template file that is used by default. It can be found in the ords-http-${build.number}.jar and I have used it as the basis for my custom error page for 555 status codes 555.html. That’s why it looks like the standard ORDS error page, but with one new text area.

Custom error page for status 555 – User Defined Resource Error

At a high level the additional changes to the out of the box error.html is a javascript function called getExplanation() which is called when the error page is loaded by the browser. This function calls an endpoint for chat completion. To get a further understanding of the model, the parameters in the request payload and the format of the response it is best to start with the above OpenAI documentation. It is sufficient to know that the getExplanation() javascript function in the custom error page starts a conversation with the model as a user, specifying that the model can lean slightly in the deterministic direction. That’s what the temperature parameter is for.

var content = 'How can I fix this user defined resource error I encountered in Oracle REST Data Services? ' + reasonsSingleLine.trim() + '.';
var params = `{ "model": "gpt-3.5-turbo", "messages": 
                [{"role": "user", 
                  "content": "${content}"}
                "temperature": 0.7 }`;

The javascript invokes endpoint with a question based on the reasons text and displays the response in a textarea element called explanation. Before making that call, the reasons text is restructured to be a single line string and the HTML elements are removed. This makes it easier for ChatGPT to parse the content of the message. You can walk through the code in your own time.

Download 555.html and save it in a directory that ORDS will have access to at runtime. Let’s say it is /path/to/error/pages/folder/ because that’s the example used in the ORDS documentation. Remember that OpenAI API key that was created earlier? Edit the 555.html file and find this section at line #133..

var openapi_key = '<!-- PUT YOUR OPENAI.COM API KEY HERE -->';

Replace the HTML comment portion with your OpenAI API key. Note that this key will be accessible to anyone who accesses your ORDS REST Services and gets a HTTP 555 response. It is possible that someone may use your key for their own API calls to OpenAI. Therefore it may be necessary to change the keys on a regular basis or perhaps only use this approach for the development and internal production environments but not for an environment accessible from the internet.

Configure the custom error pages location

When the error.externalPath configuration setting has a value ORDS uses that to find a customer error page template file based on the status code of the response. If there is no corresponding status code HTML file then the default error page is used. Before starting up the ORDS instance set the error.externalPath configuration setting to the directory that the 555.html customer error page is.

ords --config /path/to/conf config set error.externalPath /path/to/error/pages/folder/

See Configuring the Custom Error Pages section in ORDS documentation.

ChatGPT: Explaining runtime errors

With the custom error page in place for 555 status codes we can revisit our example ORDS REST service endpoint that has an error at runtime and see a slightly different error page.

When the error page loads it sends a request to OpenAI

The request to the OpenAI endpoint occurs when the page is loaded by the browser. There is a noticeable delay with getting a response back. For three requests the response time recorded by the browser for were ( in seconds ) : 15, 8, 10. That’s a huge variation in elapsed time and anything more than a few seconds can seem like an eternity when sitting at a computer. It’s still faster than the length of time it takes to copy and past the message into your preferred search engine and trawl through the pages for an helpful explanation and actionable suggestions.

Helpful explanations

The ChatGPT model provides an explanation of the runtime error and suggested steps on how to fix it.

Of course this is a dynamic conversation with a model which works through a plethora of options and permutations so the next time it is asked for suggestions on how to fix the problem there could be a different answer.

Even for the same error message input, the response from ChatGPT is not always the same.

For more deterministic output consider reducing the temperature parameter in the javascript but having a variety of suggestions could in fact be more helpful when trying to solve a problem.


The use of ChatGPT to produce explanations and suggested fixes for user defined resource errors is a useful aid for developers creating ORDS REST Services. It is an easy-to-use and efficient solution for automatically diagnosing and resolving errors. Even working with a small error description, through the use of natural language processing, ChatGPT can quickly respond with actionable suggestions. This has the potential to allow developers to quickly identify and resolve errors, saving time and resources.

Potential next steps for your own custom error page might be to improve the formatting / branding of the page, develop something similar for other HTTP status codes, change the model or temperature parameters to experiment with the results.

Why spend time searching for answers on stackoverflow when you can get them automatically? Try an AI chatbot in your custom error page today to make diagnosis a breeze – you won’t look back!

Post Publishing Edits: 
March 20th 2023 - Added text on the elapsed time for getting a response from the OpenAI endpoint.