SQLcl not using TNS

Daljit

I downloaded the latest SQLcl and tried to use it but its not using TNS as it supposed to. Here is the error am getting:

C:\>java -version

java version "1.8.0_171"

Java(TM) SE Runtime Environment (build 1.8.0_171-b11)

Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

C:\>sql /nolog

SQLcl: Release 18.1.1 Production on Fri Apr 20 10:08:43 2018

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

SQL>

SQL> conn scott/[email protected]

  USER          = scott

  URL           = jdbc:oracle:oci8:@testdb

  Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020

  USER          = scott

  URL           = jdbc:oracle:thin:@testdb

  Error Message = IO Error: Unknown host specified

  USER          = scott

  URL           = jdbc:oracle:thin:@testdb:1521/testdb

  Error Message = IO Error: Unknown host specified

I am not sure why its using my tnsname as hostname in connection string. It should get the detailed connection string from tnsnames.ora file but its just using that alias as hostname and not going anywhere.

Please help me adopt this new [not really] cool tool.

Thanks

Gaz in Oz

Does the output of:

SQL> show tns

point to the tnsnames file/s you expect?

Is "testdb" in the "Available TNS entries" list?

It may be that the sqlcl tnsnames parser doesn't like the testb alias setup, or testdb doesn't exist in the tnsnames file/s that sqlcl is picking up.

Glen Conway

I am not sure why its using my tnsname as hostname in connection string.

Not sure if this might be relevant in your case, but note the SQLcl client has different connection properties when running on a local database depending on whether a TNS alias is supplied.  I have an 11g XE database but configure my command line environment to use the Oracle 12.2 instant client.

Supplying the alias in the connection string, SQLcl uses a JDBC thick (OCI) connection:

C:\sqlcl\bin>sql hr/[email protected]: Release 18.1.1 Production on Fri Apr 20 11:40:38 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionSQL> show connectionCONNECTION:[email protected]:oracle:oci8:@xeCONNECTION_IDENTIFIER:xeCONNECTION_DB_VERSION:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionNOLOG:falsePRELIMAUTH:falseSQL> quit

while leaving off the alias results in a thin connection:

C:\sqlcl\bin>sql hr/hrSQLcl: Release 18.1.1 Production on Fri Apr 20 11:41:18 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionSQL> show connectionCONNECTION: [email protected]:oracle:thin:@localhost:1521/xeCONNECTION_IDENTIFIER: XECONNECTION_DB_VERSION: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionNOLOG: falsePRELIMAUTH: falseSQL> 

But as Gaz says, your immediate problem is not finding the expected tnsnames.ora, or not finding the testdb alias alias in it.

After getting that resolved, the next step is to avoid this error:

Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020

by getting the appropriate Oracle instant client installed so that OCI connections will succeed.

Daljit

Thanks for the reply.

I guess the main issue is that its not picking up my default_domain from sqlnet.ora. I need to specify my fully qualified TNS alias [with domain] and then it works but sqlplus gets that from DEFAULT_DOMAIN defined in sqlnet.ora.

Is there any way to fix it? Or do I have to put the entire alias [with domain] with this tool?

Thanks

Glen Conway

Do you have the same issue with TNS connection types in SQL Developer?  If not, then SQLcl probably has a bug, but if so, then I image that these TNS connections in SQLcl only rely on tnsnames.ora.  Reading through the following (admittedly very old) web page

https://www.packtpub.com/mapt/book/networking_and_servers/9781847196262/9

It looks as though sqlnet.ora is only checked in the case of LDAP and Kerberos connection types.