Connection with TNS name doesn't work when DEFAULT_DOMAIN is defined in sqlnet.ora

[SebGt]

Hi,
Following the investigation done for #88 , I still have an issue with connection using TNSNAME.

When TNS alias is defined without DEFAULT_DOMAIN it is working fine.
Command used is like : utplsql run user/pswd@DB_TEST_DEV ...
Config details:

# sqlnet.ora Network Configuration File

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

# tnsnames.ora File

DB_TEST_DEV=
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 10.1.1.1)(Port = 1550))
 )
 (CONNECT_DATA =
 (SID = DB_T_DEV)
 )
 )

But when DEFAULT_DOMAIN is defined, the alias is not found by java API used to connect:
Command used is like : utplsql run user/pswd@DB_TEST_DEV ...

...
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier "db_test_dev"
...

Config details:

# sqlnet.ora Network Configuration File
SQLNET.AUTHENTICATION_SERVICES= (NTS)
TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)
LOG_DIRECTORY_CLIENT = c:
NAMES.DEFAULT_DOMAIN = world
SQLNET.EXPIRE_TIME = 0
NAME.DEFAULT_ZONE = world

# tnsnames.ora File

DB_TEST_DEV.WORLD=
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 10.1.1.1)(Port = 1550))
 )
 (CONNECT_DATA =
 (SID = DB_T_DEV)
 )
 )

In such config, connect string should be user/pswd@DB_TEST_DEV and when client opens the connection the TNS alias is determined using connect string + DEFAULT_DOMAIN in sqlnet.ora.

It seems that the java API doesn't use sqlnet.ora (googled but I found nothing about that).
So I tried to give the full alias name in connect string but API thought I pass an IP because there is a dot in connect string (it is what I guess)
Example:
utplsql run user/pswd@DB_TEST_DEV.WORLD ...

...
java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"
...

(see #88 for full stack)

Any chance to have a look because config with DEFAULT_DOMAIN can't be changed ?
Thanks in advance.

[pesse]

utPLSQL-cli uses jdbc:oracle:thin-driver. The thin driver does not use the sqlnet.ora file.

See https://stackoverflow.com/questions/20685192/names-default-domain-world-not-being-set-when-looking-up-tnsnames-ora

At the moment I'm not sure about whether we should add support for oci-driver. It looks like much added complexity to me.
Your opinion, @jgebal? Others?

[jgebal]

Maybe we could check if sqlcl work with thus configuration. If it dies we can ask the sqlcl team how to do this.

[jgebal]

@pesse, are you able to reproduce that error locally (TNS & jdbc with DEFAULT_DOMAIN)?
Is there validation on api that could cause problems or is api just wrapping exception from the driver?
This looks like api exception:
IO Error: Invalid connection string format, a valid format is: "host:port:sid" ...

[pesse]

I'll try to reproduce it tomorrow evening.
The exception is from the driver, which is also connected to a known issue with 11.2.0.4
https://support.oracle.com/knowledge/Middleware/1915177_1.html

[pesse]

I can reproduce it. SQLcl seems to overcome the issues, though.

[pesse]

@SebGt you have a pretty old version of Java 8 installed:

java version "1.8.0_144"

Can you update it? I can reproduce DEFAULT_DOMAIN not getting populated, but I can't reproduce the IO Error: Invalid connection string format, a valid format is: "host:port:sid" ... error when calling
utplsql run app/app@db_test_dev.world ...

Docs for SQLcl recommend "Java Runtime Environment (JRE) version 8 update 161 or later."
https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbp/connecting-sqlcl.html#GUID-F78874A5-B04A-4129-A019-7752B31313D9

[pesse]

Got some info from the phenomenal Jeff Smith: in SQLcl they actually try a THICK connection first and just fall back to thin afterwards.
There's still something strange going on because I shouldn't be able to connect through thick because of having 32bit client installed, but this approach should solve the problems I think.
Will open a new issue so we include a similar fallback-scenario in a future cli-release

[SebGt]

Thanks a lot for your investigations.
@pesse I will see if I can upgrade my java version (I have a doubt as it is packaged and automatically deployed).

[pesse]

Hey @SebGt
can you try if this latest development-version of utPLSQL-cli fixes your problem?
It first tries to connect via thick-driver, then fallback via thin.
https://bintray.com/utplsql/utPLSQL-cli/download_file?file_path=utPLSQL-cli-develop-201807200643.zip

[SebGt]

Hi,
Thanks @pesse, I'm trying this morning and revert to you with result.

[SebGt]

It is working with command line and I need to give the full name db_test_dev.world in command line.
Thanks a lot.
Now I have to make it working in Jenkins because the same command executed by Jenkins doesn't work.

jdbc:oracle:oci8:****/****@db_test_dev.world : no ocijdbc12 in java.library.path
jdbc:oracle:thin:****/****@db_test_dev.world : IO Error: could not resolve the connect identifier "db_test_dev.world "
Could not establish connection to database. Reason: IO Error: could not resolve the connect identifier "db_test_dev.world "