Install SQLcl on Oracle Cloud Infrastructure Cloud Shell and make it work against an Autonomous Database

Cloud Shell on Oracle Cloud Infrastructure is a versatile management and development environment to access and manage Oracle Cloud Infrastructure resources. Cloud Shell comes with many tools and runtime out of the box (or rather inside the box) including OCI CLI, Java and SQL*Plus. However, the Oracle SQL Developer Command Line (SQLcl) tool – the Java-based replacement for SQL*Plus – is not part of the package (at the time of writing, 17th March 2020).

In this article I want to install SQLcl in Cloud Shell and use it against an Autonomous Database.

I have leaned heavily on Tim Scott’s article on installing SQLcl.

1. Go to https://www.oracle.com/tools/downloads/sqlcl-downloads.html and download SQLcl

Save the zip-file to your local computer.

2. Upload the zip-file to a location from where it can be download in Cloud Shell. OCI Object Storage is a good option; services such as Google Drive, Dropbox and OneDrive probably should do the trick as well.

3. Download the zip-file to Cloud Shell

oci os object get -bn bucket-01 --name sqlcl-19.4.0.354.0937.zip --file sqlcl.zip

4. Extract the zip. This will create a directory sqlcl in the current directory

unzip sqlcl.zip

5. Set JAVA_HOME environment variable to refer to Java Runtime on Cloud Shell

Java is already installed on the Cloud Shell. It can be found in /ur/lib/jvm and the Java runtime – to which we need JAVA_HOME to refer – is in its jre sub directory. This brings JAVA_HOME to:

export JAVA_HOME=/usr/lib/jvm/jre

6. Set an alias – a short cut for starting SQLcl

alias sql="${HOME}/sqlcl/bin/sql"

7. Run SQLcl

With the alias defined, we can start SQLcl using a simple sql command. The /nolog switch means that we are not prompted for database login details.

sql /nolog

 

And we are in business: SQLcl running on Cloud Shell.

As a next step, it would be nice to actually connect to a live database instance. And here I can lean on my own earlier article on connecting SQL*Plus in Cloud Shell to a live database.

Connect SQLcl to a live Autonomous Database instance

I am assuming The starting situation: I have an autonomous database instance somewhere in Oracle Cloud Infrastructure. Here are the steps for connecting SQLcl to that database:

Start Autonomous Database – for example in the OCI Console – if it is not currently running.

Then, in the ATP or ADW Console,  click on DB Connection to download credentials files for connecting to the database – in a wallet file.

 

Click on Download Wallet. I have created a password for the wallet – but frankly I never used it again:

Download the Client Credentials – a zip file with tnsnames.ora and sqlnet.ora files used by SQL*Plus to connect to the database instance. At this point, this zip-file is on my laptop – certainly not yet on Cloud Shell.

I have now uploaded the zip file to OCI Object Storage in the same tenancy as where I am running Cloud Shell.

The file is now in bucket-01 as Wallet_labdb.zip:

I need the file in Cloud Shell. Using OCI CLI I can easily download the file to Cloud Shell:

oci os object get -bn bucket-01 --name Wallet_labdb.zip --file wallet.zip

At this point, the zip-file is on Cloud Shell. Next I create a directory db and extract the contents of the zip-file into this directory:

unzip wallet.zip -d db

Change into the directory and list the files

cd db
ls -l

Open sqlnet.ora in the vi editor and set the DIRECTORY to refer to the current directory – that holds the tnsnames.ora file. Save the sqlnet.ora file.

Set the TNS_ADMIN environment variable to the directory where the unzipped credentials files are, not to the credentials file itself.

export TNS_ADMIN=/home/jellema/db

Connect using a database user (ADMIN), password, and database TNS name provided in the tnsnames.ora file.

sql admin@labdb_low

then when prompted type the password defined when creating the database user:

 

 

The connection is made and SQLcl can be used as always – from Cloud Shell to an Autonomous Database somewhere in some cloud.

 

Resources

Article by Tim Scott on Installing SQLcl: https://oracle-base.com/articles/misc/sqlcl-installation

Article on AMIS Blog on connect SQL*Plus on Cloud Shell to Autonomous Database: https://technology.amis.nl/2020/03/17/connecting-sqlplus-in-cloud-shell-to-an-autonomous-database/

Documentation Connect Autonomous Data Warehouse Using a Client Application  https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-preparing.html#GUID-EFAFA00E-54CC-47C7-8C71-E7868279EF3B

Documentation Connect with SQL*Plus https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A