Connecting via JDBC to the Oracle Cloud
source link: https://www.salvis.com/blog/2021/02/28/connecting-via-jdbc-to-the-oracle-cloud/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
You can connect to an Oracle Autonomous Database in different ways. This is well documented here. It’s a bit different from what we know from on-premises environments. In this blog post, I show the steps to connect to an Autonomous Database from a third-party IDE like DataGrip.
From a JDBC perspective, this is just an ordinary JDBC URL with some driver-specific properties. Therefore, this approach should work for any JDBC-based IDE and also for any Java application.
Step 1 – Download the Wallet
Go to your Autonomous Database and click on the “DB Connection” button. A screen similar to the following appears:
Press on the “Download Wallet” button and enter a password. This password is used to protect the key and the trust store. We will need it later. I named my instance “ATP21”. Therefore, in my case, a zip archive named “Wallet_ATP21.zip” was downloaded.
Step 2 – Unzip the Wallet
Unzip the downloaded zip file and move it to a location where want to keep it. The wallet contains the following files:
- README
- ewallet.p12
- ojdbc.properties
- tnsnames.ora
- cwallet.sso
- keystore.jks
- sqlnet.ora
- truststore.jks
Open the file “tnsnames.ora” in a text editor. It contains 5 entries. We need one of those entries to build the JDBC connection string. I highlighted the relevant part of the first entry in the next screenshot:
Step 3a – Configure Connection in DataGrip (Legacy Driver)
Add a new connection in DataGrip and select “Oracle” as shown in the following screenshot:
In the “General” tab change the Connection type to “URL only”. Enter the user, the password and the complete JDBC URL as shown in the next screenshot:
The URL starts with jdbc.oracle:thin:@. The rest is the text I’ve highlighted in the tnsnames.ora file above.
- javax.net.ssl.trustStore
- javax.net.ssl.trustStorePassword
- javax.net.ssl.keyStore
- javax.net.ssl.keyStorePassword
Here are my settings (of course you need to amend the values to match the environment of your wallet):
Step 3b – Configure Connection in DataGrip (Current Driver)
DataGrip is automatically downloading the latest Oracle Database JDBC driver. In my case version 21.1.0.0. Since version 18.3 there is an easier way to connect. The JDBC driver can access the wallet directory and its files. As a result you do not need to configure the java.net.ssl.* JDBC properties anymore. You just have to define one additional JDBC property “TNS_ADMIN” to define the path to the wallet directory.
And of course you can pass this JDBC property directly in the JDBC URL as shown in the next screenshot:
Conclusion
Establishing a connection to an Autonomous Database requires a wallet. The JDBC driver needs access to this wallet. This doesn’t make things easier, but it doesn’t make them overly complicated either.
However, you need to deal with this additional resource on a regular basis because the wallet has a limited lifetime. This is documented in the README file.
So I have to update my wallet in two years otherwise I won’t be able to connect anymore.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK