Connecting to Autonomous Database 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 , Jeff Smith / thatJeffSmith.com , Tim Hall and of course - the Oracle Documentation.

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

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.
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.
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.
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

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://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
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:
```
 -- 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).
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:
```
 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:
```
 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:

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):

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:

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) :

-- 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:

-- 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:

 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:
```
  ./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):
```
  nano ~/.zshrc  # or nano ~/.bash_profile for bash users
```
Add the following line, adjusting the path to where you've placed SQLcl:
```
  export PATH=$PATH:/path/to/sqlcl/bin
```
If SQLcl is in your Downloads folder, it might look like:
```
  export PATH=$PATH:~/Downloads/sqlcl/bin
```
Save the file and exit.

Reload your shell configuration or open a new terminal:

  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:

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:

      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.

Set the TNS_ADMIN Environment Variable:

Edit your shell configuration file to set TNS_ADMIN permanently:
- For zsh users, edit ~/.zshrc:
```
  nano ~/.zshrc
```
- For bash users, edit ~/.bash_profile:
```
  nano ~/.bash_profile
```
- Add this line to the file:
```
  export TNS_ADMIN=/Users/my_user/oracle/network/admin
```
- Save and close the file. Then either restart your terminal or source the file:
```
  source ~/.zshrc   # or ~/.bash_profile
```
Verify the Configuration:
- After setting TNS_ADMIN, try connecting again:
```
  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: nodename nor servname provided, or not known.

This error typically indicates that your system cannot resolve the hostname (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:
```
  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:
```
  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.

Connecting to Autonomous DB using SQLcl