SQL Developer for VS Code

Given the following tnsnames.ora file:

UPGR.world =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL=tcp) (HOST= 192.168.56.101) (PORT=1521))
        (CONNECT_DATA = (SERVICE_NAME = UPGR))
    )

When SQL Dev for VSCode reads that file, the following messages are logged:

[1/23/2024, 12:53:28 PM] [dbtools-client  ] [ERROR] Error redacting request
Unexpected token 'U', "UPGR.world"... is not valid JSON

[1/23/2024, 12:53:28 PM] [dbtools-client  ] [TRACE] httpRequest
{
  "_method": "POST",
  "_url": "http://localhost:50095/20221610/databases/connections/definitions/actions/parseTnsNames",
  "_headers": {
    "headers": {}
  },
  "_body": "UPGR.world =\n    (DESCRIPTION =\n        (ADDRESS = (PROTOCOL=tcp) (HOST= 192.168.56.101) (PORT=1521))\n        (CONNECT_DATA = (SERVICE_NAME = UPGR))\n    )\n"
}

[1/23/2024, 12:53:28 PM] [dbtools-client  ] [TRACE] signedHeaders
{
	--removed for brevity--
}

[1/23/2024, 12:53:28 PM] [dbtools-client  ] [TRACE] httpResult
{
  "url": "http://localhost:50095/20221610/databases/connections/definitions/actions/parseTnsNames",
  "ok": true,
  "status": 200,
  "statusText": "OK",
  "body": {
    "UPGR.WORLD": {
      "DESCRIPTION": {
        "ADDRESS": {
          "PORT": "1521",
          "PROTOCOL": "tcp",
          "HOST": "192.168.56.101"
        },
        "CONNECT_DATA": {
          "SERVICE_NAME": "UPGR"
        }
      }
    }
  }
}

I believe that the above mention ("UPGR.world"... is not valid JSON) may reveal a possible bug. If so, please fix it.

As a matter of fact, imported connections using that TNS entry, which happen to work perfectly if used in the standalone SQLcl 23.3 client, don't even pass the test in the connection editor in VSCode.

E.g., given a connection named SCOTT@UPGR, with dbtools.properties as follows in %APPDATA%\DBTools\connections\SCOTT@UPGR:

#
#Wed Jan 17 23:53:05 CET 2024
connectionString=UPGR.world
name=SCOTT@UPGR
type=ORACLE_DATABASE
userName=SCOTT

The connection appears as follows in the connection editor:

And, as it is, the test fails with the (déjà vu!) error message:

[1/23/2024, 1:27:37 PM] [dbtools.bat(18320)] [WARN ] #stderr
Jan 23, 2024 1:27:37 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: No such host is known (UPGR.world). Ensure the specified host is reachable and available

(I'm sparing you the stack trace.)

It is only after changing the connection type back to “Network alias” that the test finally completes successfully.

(Remark: with my real tnsnames.ora file, containing more than just a single entry, after doing that I have to manually reset the Connect Identifier, otherwise it has flipped to another, unrelated TNS entry, for whatever reason.)

But then, if I save the "fixed" connection, the undesirable side effect (already described in this post) appears: a new file, ojdbc.properties, is created in the %APPDATA%\DBTools\connections\SCOTT@UPGR directory, with the following content:

oracle.net.tns_admin=e\:\\Home\\user9540031\\SQL_Developer\\tns_admin

And this permanently embeds the present path of my TNS_ADMIN directory into the properties of the connection, which doesn't sound like a good idea at all. I believe that this too, should be fixed: the path of the TNS_ADMIN directory belongs in Settings, and nowhere else, according to the principle of single point of definition.

Thanks & regards,