Solution for no ocijdbc18 in java.library.path when using sqlcl

sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = oric.no

tnsnames.ora

testdb01,testdb01.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb01)
    )
  )
  

Using tnsping:

C:\>tnsping testdb01

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 17-AUG-2021 12:42:14

Used parameter files:
C:\app\client\vegard\network\admin\sqlnet.ora
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.oric.no)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb01)))
OK (40 msec)

Using sqlplus:

C:\>sqlplus system/mypassword@testdb01

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 12:39:47 2021
Version 19.3.0.0.0

Last Successful login time: Tue Aug 17 2021 12:36:35 +02:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0

Using sqlcl:

C:\>sql.exe system/mypassword@testdb01

SQLcl: Release 19.2.1 Production on Tue Aug 17 12:44:36 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

  USER          = system
  URL           = jdbc:oracle:oci8:@testdb01
  Error Message = no ocijdbc18 in java.library.path
  USER          = system
  URL           = jdbc:oracle:thin:@testdb01
  Error Message = IO Error: could not resolve the connect identifier  "testdb01
  USER          = system
  URL           = jdbc:oracle:thin:@testdb01:1521/testdb01
  Error Message = IO Error: could not resolve the connect identifier  "testdb01:1521/testdb01"
Username? (RETRYING) ('system/*********@testdb01'?)

Why is sqlcl having trouble connecting, when the listener is accepting requests and connections over sqlplus are indeed accepted?

My experience is that sqlcl doesn't seem to accept multiple tnsnames.ora aliases.

Solution: Split up the tnsnames.ora entry in two different entries.

Change it from

testdb01, testdb01.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb01)
    )
  )
  

to

testdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb01)
    )
  )

testdb01.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb01)
    )
  )