ORDS with TNS

The aim of that blog is to look into Oracle REST Data Services (ORDS) DB connection with TNS. This is following some experience at a customer who wanted to move to latest version of ORDS with APEX 21.2, in late 2021, and run into an issue.

For general presentation about ORDS installation please see the blog of my colleague Lazhar Felahi on that topic.
 
During installation or upgrade of ORDS you have to select the DB connection mode between following values:

Specify the database connection type to use.
Enter number for [1] Basic [2] TNS [3] Custom URL [1]:

If you are using DataGuard or RAC, you will have to choose the TNS option [2] in order to benefit from the DB features in terms of availability. I would even recommend to use TNS as general rule, as this is mostly defined and provided by your DBA for connecting your client to the DB.
To make use of TNS you have to provide the service name (TNS Network Alias) and the location of your tnsnames.ora file (TNS folder location) during the setup process.
Until version 21.1.3 of ORDS that feature was working nicely.
Unfortunately, starting with version 21.2 some error is raised when trying to update or newly install ORDS with DB connection over TNS, at the time the connection is used.

oracle.net.resolver.EZConnectResolver.parseExtendedProperties Extended settings parsing failed.
 java.lang.RuntimeException: Invalid character at 24 : 
...
oracle.dbtools.common.logging.JDKLogSink.record IO Error: Invalid connection string format, 
a valid format is: "host:port:sid" (CONNECTION_ID=X8ADepYuRpK1p8FSAKp41Q==),|apex||...,
jdbc:oracle:thin:@myDB?TNS_ADMIN=D:AppOracletns
...
oracle.dbtools.common.logging.JDKLogSink.record The pool named: |apex|| is invalid and will be ignored: 
ORDS was unable to make a connection to the database. 
This can occur if the database is unavailable, the maximum number of sessions has been reached
or the pool is not correctly configured. The connection pool named: |apex|| had the following error(s): 
IO Error: Invalid connection string format, a valid format is: "host:port:sid"
...

That issue is still present in versions 21.2.4, 21.3, 21.3.1 and 21.4
It was reported to Oracle by opening and SR with the support, by my customer, and a bug was registered: Bug 33583853 – FAILED TO CONNECT TO USER: ORDS_PUBLIC_USER URL: JDBC:ORACLE:THIN:@MYDB?TNS_ADMIN
Fortunately, there is a workaround in using the option [3] Custom URL, until the bug fix is implemented.
When you make use of that option, you have to build your own Java connection string which looks as following:

jdbc:oracle:thin:@MY_TNS_ALIAS?TNS_ADMIN=D:\Oracle\tns\admin

Where MY_TNS_ALIAS is the alias defined in your tnsnames.ora file and D:\Oracle\tns\admin is the path to your tnsnames.ora file (in this case on a Windows server so the ” needs to be doubled). This is working fine on all previously listed ORDS versions.
You can have a look into Oracle Community Discussion for details on the testing and the solution.
According to Jeff Smith the bug resolution is provided “…before the Groundhog gets his day in PA.” which is today, February 2nd.
Version 21.4.1 was released.
After testing, I can confirm that this release is fixing the issue and behavior is back to normal. Thanks Oracle for the fix.
Special thanks to Jeff Smith, the Oracle product manager of ORDS, for the clarifications given and the follow-up on the bug resolution.
 
So if you want to use TNS without the Custom URL, stay on ORDS version prior to 21.2 or move to version 21.4.1.
I hope that blog will be helpful, if you run into similar issue. Enjoy ORDS.

Note: If you are looking for details about licencing please have look into that blog.