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

Image for post
Image for post

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.

Image for post
Image for post

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
Image for post
Image for post

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

unzip sqlcl.zip
Image for post
Image for post

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
Image for post
Image for post

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.

Image for post
Image for post

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

Image for post
Image for post

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.

Image for post
Image for post

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

Image for post
Image for post

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

Image for post
Image for post

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
Image for post
Image for post

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
Image for post
Image for post

Change into the directory and list the files

cd db 
ls -l
Image for post
Image for post

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.

Image for post
Image for post

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

Image for post
Image for post
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:

Image for post
Image for post

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

Originally published at https://technology.amis.nl on March 18, 2020.

Written by

Lucas Jellema is solution architect and CTO at AMIS, The Netherlands. He is Oracle ACE Director, Groundbreaker Ambassador, JavaOne Rockstar and programmer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store