Oracle SQLDeveloper Command-Line (SQLcl) v19 run Script with Connection Timeout

I am running SQLcl verion

SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 19.1.0.0

The following script works

echo @test/oracle_sql_cl/read_schema.sql | sql -S ${USER}/${PASSWORD}@${TNS_NAME}

A timeout parameter is needed.

Here are some examples tired but without success

echo @test/oracle_sql_cl/read_schema.sql | sql -S -t 5 ${USER}/${PASSWORD}@${TNS_NAME}

The above command fails.

Also updated the TNS_NAME var like so

export TNS_NAME="(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=5)(CONNECT_TIMEOUT=5)(RETRY_COUNT=1)(RETRY_DELAY=1)(ADDRESS_LIST=(ADDRESS=...

But connection still reties the connecion.

Any ideas would be much appreciated :~)

Solution

Update the connection string to include

export TNS_NAME="(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=2)...

And modify the invocation to

echo @test/oracle_sql_cl/read_schema.sql | sql -S ${USER}/${PASSWORD}@${TNS_NAME}

Use a JDBC URL in your connect string.

With timeout set to 10 milliseconds it fails.

With timeout set to 1000 milliseconds it works.

SQL> connect system/oracle@jdbc:oracle:thin:@(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=10ms)(ADDRESS_LIST=(ADDRESS=(HOST=server.com)(PORT=2213)(PROTOCOL=tcp)))(CONNECT_DATA=(S
ERVICE_NAME=DB213P)))
  USER          = system
  URL           = jdbc:oracle:thin:@(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=10ms)(ADDRESS_LIST=(ADDRESS=(HOST=server.com)(PORT=2213)(PROTOCOL=tcp)))(CONNECT_DATA=(SERVICE_NAME=DB213P)))
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=MES6gto9SkeXW93/WnHZ6A==)

SQL> connect system/oracle@jdbc:oracle:thin:@(DESCRIPTION=(TRANSPORT_CONNECT_TIMEOUT=1000ms)(ADDRESS_LIST=(ADDRESS=(HOST=server.com)(PORT=2213)(PROTOCOL=tcp)))(CONNECT_DATA=
(SERVICE_NAME=DB213P)))
Connected.
SQL>

This JDBC property CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT is defined/documented here.