ORDS, SODA & JSON in the Database

I've done a few hundred installs of ORDS over the years (not at all an exaggeration).

When I install ORDS I have the following:

A runtime directory - /opt/ords/23.1.0
A configuration directory - /etc/ords/23.1.0

I run the installation as follows:

[root@applicationserver /opt/ords/23.1.0/bin]
# ./bin --config /etc/ords/23.1.0 install

During the installation, I get asked the following:

Enter a number to select additional feature(s) to enable:
   [1] Database Actions  (Enables all features)
   [2] REST Enabled SQL and Database API
   [3] REST Enabled SQL
   [4] Database API
   [5] None
 Choose [1]: 1

I always choose 1.

My pool.xml file now looks like this:

[root@applicationserver /etc/ords]
# cat 23.1.0/databases/default/pool.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Thu Jun 22 19:21:13 UTC 2023</comment>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">databaseserver.insum.ca</entry>
<entry key="db.port">1521</entry>
<entry key="db.servicename">orcl.insum.ca</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="feature.sdw">true</entry>
<entry key="plsql.gateway.mode">proxied</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
</properties>

Everything is working just fine, but then I discover that 23.1.4 is out and I have to have it now! ;)

So, I create my runtime directory - /opt/ords/23.1.4 and my configuration directory /etc/ords/23.1.4. I then install 23.1.4 using the following:

[root@applicationserver /opt/ords/23.1.4/bin]
# ./ords --config /etc/ords/23.1.4 install

I again choose choice 1 to get all features.

However, when ORDS starts up this time, both SQL Developer Web aka Database Actions and APEX don't work.

Looking at the new pool.xml, it's easy to see why:

[root@applicationserver /etc/ords]
# cat 23.1.4/databases/default/pool.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Thu Jun 22 22:58:51 UTC 2023</comment>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">databaseserver.insum.ca</entry>
<entry key="db.port">1521</entry>
<entry key="db.servicename">orcl.insum.ca</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
</properties>

The <entry key="plsql.gateway.mode">proxied</entry> is missing.

This doesn't happen unless there is a previous version of ORDS in the database. So, if I had installed 23.1.4 without the previous 23.1.0 in the database, then the pool.xml would have the correct entry.

I have logged an SR with Oracle: SR 3-33228417171 : ORDS pool.xml missing plsql.gateway.mode after an upgrade and they have logged an (internal) bug:

Hello Rich,

The bug number is: 35446433. I am not sure if you will be able to view it as it is filed as Internal bug.

I would sort of figure that folks would have run into this before, but at this point, it feels like “just me”.

Has anyone else seen this?