# Connecting to Autonomous DB using SQLcl ## Intro This blog post is following the exact steps I took in order to get SQLcl up and running and be able to connect to my Oracle Cloud Autonomous Databases. The information that follows is nothing new and it is available on many other places, such as the blogs of @[RafaΕ‚ Grzegorczuk](@Rafal) , @[Jeff Smith](@thatjeffsmith) / [thatJeffSmith.com](https://www.thatjeffsmith.com/) , [Tim Hall](https://oracle-base.com/) and of course - the [Oracle Documentation](https://docs.oracle.com/en/database/oracle/sql-developer-command-line/). However, I needed to put these steps together as they allowed me to get things running on my machine and to solve some errors along the way. The most common problems that I faced are listed at the end of this blog post, but hopefully you will not have any of them following the steps. I make no assumptions here about any software being already installed on your machine. The only one being that I expect you to already have Oracle Autonomous Database up and running in the cloud. ## Autonomous Database on the Oracle Cloud 1. Configure the `ACL` Network access, so you can connect to the Autonomous DB from your local machine. While having the `Autonomous Database Information` tab opened, find the Network section and click on the ***Edit*** link, next to `Access control list` entry. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734089392793/fba0f52e-39f7-4652-8638-79f291ea37a8.png align="center") 2. Add the IP addresses which should be allowed to connect your Autonomous DB from. You can use the `Add my IP address` button, which will automatically add the IP of the machine you are using. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734090551845/1a8ceec9-6729-43b4-9629-5466bbe028ac.png align="center") 3. Download `Client Credentials` (**Wallet**) zip file from `Database connection` in the OCI console. When downloading the wallet, you can set a password to protect it. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729245100878/a66b625e-5963-4dee-b5b5-d5bed8808f2f.png align="center") 4. Optionally, you can connect using just the `TNS names` string. You can copy any of the provided options and copy them into your `tnsnames.ora` file. ## Downloading and Running SQLcl 1. Download the latest version of SQLcl from the Official Oracle website. In case you have some automation or process that should run automatically, just download **sqlcl-latest.zip**. [https://www.oracle.com/uk/database/sqldeveloper/technologies/sqlcl/download/](https://www.oracle.com/uk/database/sqldeveloper/technologies/sqlcl/download/) [https://download.oracle.com/otn\_software/java/sqldeveloper/sqlcl-latest.zip](https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip) 2. Extract the archive in any folder you like. In this example I have chosen the following one: **/Users/my\_username/Downloads/sqlcl** `SQLcl` is started from the **bin** folder. So to run it, terminal needs to be in that folder already. For example: ```bash -- 1. Navigate to the SQLcl bin folder cd /Users/my_username/Downloads/sqlcl/bin -- 2. Start sqlcl ./sql /nolog ``` This is a little bit inconvenient, since you have to always navigate to the folder first. To make the `sql` command work from **everywhere,** *you should do the following*: 1. Go to your **/Users/my\_username** folder and locate the **.zshrc** file. ZSH (or the Z shell is the default shell that the MacOS Terminal uses). But in some cases, being an old Mac or custom user settings, Terminal could be using **bash** instead. If hidden files are not displayed already, try clicking `SHIFT+CMD + .` to make them visible.
πŸ’‘
If you can’t edit the file, right click on it, select Get Info and add Read & Write Permissions from the Sharing & Permissions section (bottom of the popup window).
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734108180437/d918c805-b504-48a6-bb6c-9ec655737109.png align="center") 2. Add the following string on a new line in the file and save it: **export PATH="/Users/my\_username/Downloads/sqlcl/bin:$PATH"** To test it out, open a Terminal window and try running the following: ```bash sql /nolog -- if everything is fine, sqlcl will start SQL> ``` 3. If you are using **tnsnames.ora** file to connect easily to your databases, you can add another line to the .**zshrc** file and make these aliases available for use by **SQLcl.** All of the examples that follow are using TNS aliases. Add the following string on a new line in the .**zshrc** file (if your `tnsnames.ora` file is in the below folder): **export TNS\_ADMIN="/Users/my\_username/Oracle/network/admin"** Here is an example of a `tnsnames.ora` file: ```plaintext learningDB_high = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCPS)(HOST = adb.us-ashburn-1.oraclecloud.com)(PORT = 1522) ) (CONNECT_DATA = (SERVICE_NAME=2233445566_learningDB_high.adb.oraclecloud.com) ) ) ```
πŸ’‘
If your Mac’s Terminal is using bash, instead of zsh shell, you need to repeat steps 3-5, but this time editing the file, named .bash_profile
## Connecting to your Autonomous Database * *Option 1*: **Using Wallet file** Open your Terminal and run the following command: ```bash sql /nolog ``` Once you have the SQL> ready, enter the wallet PATH to be used for connecting the database. If password is required, enter your Wallet password (if you have set such during the wallet download): ```bash SQL> set cloudconfig path_to_wallet/myAutonomousDB_wallet.zip Wallet Password: ********** SQL> conn username/password@TNS_NAME -- example SQL> conn learn/myPassword@learningDB_high ``` An alternative approach is to use only one command to do it: ```bash SQL> conn -cloudconfig path_to_wallet/myAutonomousDB_wallet.zip username/password@TNS_NAME ``` * *Option 2*: **Using TNS name** Open your Terminal and run one of the following command (first option will prompt for a password) : ```bash -- option 1 sql username@TNS_NAME -- option 2 sql username/password@TNS_NAME -- example sql learn/myPassword@learningDB_high ``` If you want to **save the connection** and reusing by just entering the connection name, use the following command in your Terminal: ```bash -- 1. start SQL sql /nolog -- 2. save your connection SQL> conn -savepwd -save my_saved_conn username/password@tns_name -- 3. list saved connections and verify yours is created SQL> connmgr list -- 4. connect using your saved connection SQL> conn -name my_saved_conn ``` If you need to change existing saved connection, use the **\-replace** parameter: ```bash conn -savepwd -save my_saved_conn username/password@tns_name -replace ```
πŸ’‘
If you need to delete a saved connection, open the connections folder and delete it from there: users/your_user/.dbtools/connections on Mac or C:\Users\your_user\AppData\Roaming\DBTools on Windows.
πŸ’‘
Connecting to ADB using SQLcl: https://docs.oracle.com/en/cloud/paas/autonomous-database/dedicated/cafos/
## Troubleshooting Although it all looks straightforward, let me tell you - it’s not always the case. Which is one of the reasons I put together this blog post - these exact steps worked for me in that order. But to get here, I needed to solve few issues. So here are some common errors you might get and the possible reasons for them. > β€œ**I have opened terminal and tried to run sql /nolog. I get an error:**β€œ > ⚠️ `-bash: sql: command not found` βœ… **Use the Full Path:** * Navigate to the `SQLcl` **bin** folder * Instead of just typing **sql /nolog**, use the full path to the sql script. The **./** at the beginning tells the shell to look for the **sql** file in the current directory: ```text ./sql /nolog ``` βœ… **Add the SQLcl Bin Directory to Your PATH:** For a more permanent solution, you can add the bin directory of `SQLcl` to your system's **PATH** environment variable. This allows you to run **sql** from any directory: * Open or create your **.zshrc** or **.bash\_profile** file (depending on whether you're using zsh or bash as your shell): ```text nano ~/.zshrc # or nano ~/.bash_profile for bash users ``` * Add the following line, adjusting the path to where you've placed `SQLcl`: ```text export PATH=$PATH:/path/to/sqlcl/bin ``` * If `SQLcl` is in your Downloads folder, it might look like: ```text export PATH=$PATH:~/Downloads/sqlcl/bin ``` * Save the file and exit. * Reload your shell configuration or open a new terminal: ```text source ~/.zshrc # or source ~/.bash_profile for bash users ``` --- > β€œI try to connect using TNSnames alias (tnsnames.ora) - sql username/password@TNS\_ALIAS. I get the following error:β€œ > ⚠️ `ORA-12263: Failed to access tnsnames.ora in the directory configured as TNS admin: /Users/my_user. The file does not exist, or is not accessible.` βœ… **Create or Move the tnsnames.ora File:** 1. First, ensure you have a tnsnames.ora file. If you don't have one, create it. Here's a basic example of what it might look like: * ```text MY_DATABASE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ``` Place this file in the directory you want `SQLcl` to use, for example, **/Users/my\_user/oracle/network/admin**. 2. **Set the** `TNS_ADMIN` **Environment Variable:** Edit your shell configuration file to set `TNS_ADMIN` permanently: * For `zsh` users, edit ~/.zshrc: ```text nano ~/.zshrc ``` * For `bash` users, edit ~/.bash\_profile: ```text nano ~/.bash_profile ``` * Add this line to the file: ```text export TNS_ADMIN=/Users/my_user/oracle/network/admin ``` * Save and close the file. Then either restart your terminal or source the file: ```text source ~/.zshrc # or ~/.bash_profile ``` 3. **Verify the Configuration:** * After setting `TNS_ADMIN`, try connecting again: ```text sql username/password@MY_DATABASE ``` * Replace MY\_DATABASE with the alias you used in your tnsnames.ora file. --- > β€œI try to connect using TNSnames alias (tnsnames.ora) - sql username/password@TNS\_ALIAS. I get the following error:β€œ > ⚠️ `ORA-17868: Unknown host specified.:` [`adb.us-ashburn-1.oraclecloud.com`](http://adb.us-ashburn-1.oraclecloud.com)`: nodename nor servname provided, or not known.` This error typically indicates that your system cannot resolve the hostname ([adb.us-ashburn-1.oraclecloud.com](http://adb.us-ashburn-1.oraclecloud.com)) to an IP address. This could be due to several reasons: βœ… **Possible issues:** * **DNS Issues:** * **Check DNS Resolution:** Ensure your DNS settings are correct. You can verify if the hostname can be resolved by using: ```text nslookup adb.us-ashburn-1.oraclecloud.com ``` If this command fails or returns no IP, there might be an issue with your DNS setup or your internet connection. * **Network Configuration:** * **Internet Connectivity:** Confirm you have an active internet connection. * **Firewall or Proxy Settings:** Check if there are any firewalls, proxy servers, or network policies blocking or redirecting the DNS lookup. * You are **using a VPN** that prevents you from connecting: Disconnect from the VPN and try again. * **Local Host File:** * Although less common, check if there's an entry in /etc/hosts that might be overriding DNS for this hostname. Edit this file if necessary: ```text sudo nano /etc/hosts ``` --- > β€œI try to connect using TNSnames alias (tnsnames.ora) - sql username/password@TNS\_ALIAS. I get **ORA-12506: TNS:listener rejected connection based on service ACL filtering**:β€œ > ⚠️ `ORA-12506: TNS:listener rejected connection based on service ACL filtering.` βœ… **Add your IP address to the Access Control List (ACL) of your Autonomous Database -** See the beginning of the blog post for more details\*\*.\*\* βœ… **Use a VPN**: If your organization uses a VPN, connect through it as the external IP might be different. βœ… **Proxy Server**: If you are behind a proxy, ensure that your proxy server's IP is added to the ACL.
πŸ’‘
If you're in a dynamic IP environment, remember that your IP might change, which would require you to update the ACL again.
πŸ’‘
Changes to the ACL might not propagate instantly. Give it a few minutes and try the connection again later.