So I have successfully imported my connections from SQL Dev 23.1.1 into SQLcl 23.3, along with passwords if available, using the CONNMGR IMPORT command.
E.g., CONNECT -name TEST works in SQLcl.
In %APPDATA%\DBTools\connections, my connection named “TEST” appears as a directory named “TEST”, containing the following 2 files:
- credentials.sso
- dbtools.properties
The dbtools.properties file reads as follows:
#
#Fri Jan 19 12:04:37 CET 2024
connectionString=TNSXXX
name=TEST
type=ORACLE_DATABASE
userName=useryyy
Note: I have done this with the stand-alone SQLcl, version 23.3, using JDK 11—subsequently I will refer to the built-in SQLcl tool integrated into the “SQL Developer for VSCode” extension as the “integrated SQLcl”.
After installing the “SQL Developer for VSCode” extension, all my connections appear to be there, which is fine.
In the extension settings, I have set the location of my tnsnames.ora file, using the same path as used in SQL Dev 23.1.1, and also in SQLcl (by way of the TNS_ADMIN environment variable).
Unfortunately, opening the connection fails, with the following error message.
[1/22/2024, 9:32:43 PM] [dbtools.bat(20552)] [WARN ] #stderr
janv. 22, 2024 9:32:43 PM
INFO: Error for: POST /20221610/databases/connections/sessions/
DBTS-02014: An error occurred resolving or validating the database host name or IP address
DBTS-02014: An error occurred resolving or validating the database host name or IP address. The following error occurred: unknown host (TNSXXX). Ensure the specified host is reachable and available
[...]
Caused by: java.net.UnknownHostException: unknown host (TNSXXX)
at java.base/java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
at java.base/java.net.Inet6AddressImpl.lookupAllHostAddr(Inet6AddressImpl.java:52)
at java.base/java.net.InetAddress$PlatformResolver.lookupByName(InetAddress.java:1211)
at java.base/java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1828)
at java.base/java.net.InetAddress$NameServiceAddresses.get(InetAddress.java:1139)
at java.base/java.net.InetAddress.getAllByName0(InetAddress.java:1818)
at java.base/java.net.InetAddress.getAllByName(InetAddress.java:1688)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.TcpNTAdapter.resolveInetAddresses(TcpNTAdapter.java:585)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.TcpNTAdapter.doLocalDNSLookupConnect(TcpNTAdapter.java:304)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:273)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.ConnOption.connect(ConnOption.java:231)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.ConnStrategy.executeConnOption(ConnStrategy.java:1015)
at com.oracle.database.jdbc@21.11.0.0.230512/oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:674)
... 62 more
So it would seem the extension did not understand that TNSXXX is the TNS name to be resolved in my tnsnames.ora file, rather than a plain hostname.
Remark: I'm not any luckier in the integrated SQLcl, which seems to not be aware of the location of the tnsnames.ora file that I have set in Settings; SHOW TNS tells that the TNS location used is C:\Users\myosusername_here\tnsnames.ora, which is absolutely not what I have set in Settings. But I digress.
Meanwhile, if I edit my TEST connection in the connection editor, it tests successfully!
That editor is entitled “Update Connection TEST”, though I don't believe I have changed anything so far; yet the "Save" button is beckoning, so let's click it. This triggers the “No update has occurred!” notification in the lower right corner of the GUI. But since my connection appears to test successfully there, I want it saved anyway, therefore I force that by adding an extra character to my saved password, clicking on the Save button, and then opening the connection editor again, removing that extra character, and saving again.
And, abracadabra! Finally, my connection works!
Unfortunately, that story doesn't end there: if I look into the %APPDATA%\DBTools\connections\TEST directory to see what has really been changed by the connection editor, obviously an extra file has appeared there:
which contains the following property:
oracle.net.tns_admin=x\:\\path_to_my\\tns_admin_dir
(As was set as the location for the tnsnames.ora file in the extension's settings.)
This is confirmed by the CONNMGR SHOW command in SQLcl: before saving the connection in the connection editor, it read as follows:
SQL> connmgr show TEST
Name: TEST
Connect String: TNSXXX
User: useryyy
Password: ******
After the update, it reads as follows:
SQL> connmgr show TEST
Name: TEST
Connect String: TNSXXX
User: useryyy
Password: ******
oracle.net.tns_admin: x:\path_to_my\tns_admin_dir
Remark: CONNMGR in SQLcl does not reread connections after they've been loaded once; restarting SQLcl is mandatory to see the updates.
And sorry, but adding that property seems plainly wrong! I don't want the location of my tnsnames.ora file embedded in each connection as a special property: I want a single point of definition for that file, in the VSCode extension's settings just the same as in SQL Dev 23.1.1, or in SQLcl by way of the TNS_ADMIN environment variable. If the location of the tnsnames.ora file is embedded like this in each connection definition, I know for sure—because I have tested it—that subsequently that connection will fail if the directory specified as the oracle.net.tns_admin property is not found anymore. Which in turn means that I will have to update all my persisted connections if I need to change that directory—definitely not my idea of a single point of definition.
Best regards,