How to connect to multiple Oracle Autonomous cloud databases using single client - Part III

In this blogpost we will see about the EZConnect plus syntax of how to connect to autonomous cloud database. EZConnect is a simple syntax used in Oracle tools and API to identify the machine and database that we want to connect to. We might have used an easy connect string like localhost:1521/ORCL for a local database, it identifies the host machine, port number and the database service name that is running up there.
 
In the tnsnames.ora file extracted from the wallet ZIP, there are several network service name entries. The first entry will be like this
 
atpdemo_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))
 
the mapping from tnsnames.ora to the EZConnect plus string follow the form:
 
protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N
 
so for the above service, the equivalent EZConnect string is:
 
tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C
 
the only non-obvious parameter is the wallet_location which needs to be set to the directory containing the cwallet.sso file from the wallet ZIP. The other wallet files, including tnsnames.ora are not needed when we use EZConnect plus syntax.
 
For quick testing we can supply the connecting string to Oracle client tools for connection establishment.
 
 
C:\Users\Rajeshwaran>sqlplus /nolog
 
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Aug 6 18:59:20 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
idle> conn demo@'tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C'
Enter password:
Connected.
demo@ATP21C> select banner_full from v$version;
 
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
If we are behind a firewall, then HTTPS option can be used
 
C:\Users\Rajeshwaran>sqlplus /nolog
 
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Aug 6 19:01:28 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
idle> conn demo@'tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C&https_proxy=www-proxy-ash7.us.oracle.com&https_proxy_port=80'
Enter password:
Connected.
demo@ATP21C> select banner_full from v$version;
 
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
 
Note: connecting over HTTP is not recommended for production use due to overheads.