0

this is our classic tnsnames.ora

test1=
(DESCRIPTION=
    (CONNECT_TIMEOUT=4)
    (TRANSPORT_CONNECT_TIMEOUT=3)
    (ENABLE=BROKEN)
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))
    )
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)
    )
)

I use this SQLcl command:

sql -nohistory -noupdates -S $username/$password@$hostname:$port/$servicename @$filename

How to specifiy multiple hostnames? Not only one? It's some kind of active passive cluster (Exadata).

Edit after first answer:

I added in shell script (in this dir tnsnames.ora):

TNS_ADMIN=/example/example

I call $sqlcl -nohistory -noupdates -S $username/$password@"MY-DB" @$filename and get back the error:

./script.sh
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB
  Error Message = IO Error: Unknown host specified
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB:1521/MY-DB
  Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

1 Answer 1

2

EZconnect was not meant for things like that.

SQLcl can use tnsnames.ora as well. Just create one and use it:

sql -nohistory -noupdates -S $username/$password@test1 ...

Or you can do this, if you really want to provide all that stuff as a commandline argument:

sql -nohistory -noupdates -S $username/$password@"(DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)))" ...

Using tnsnames.ora:

[oracle@o71 ~]$ export JAVA_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/jdk
[oracle@o71 ~]$ export PATH=/home/oracle/sqlcl/bin:$JAVA_HOME/bin:$PATH
[oracle@o71 ~]$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@o71 ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
MIN19=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  min19_o71)
    )
  )

[oracle@o71 ~]$ sql bp/bp@min19

SQLcl: Release 18.4 Production on Tue Mar 26 09:28:11 2019

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

Last Successful login time: Tue Mar 26 2019 09:28:12 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0


SQL>
1
  • @Ivanov see my edit. It does work with tnsnames.ora. Commented Mar 26, 2019 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.