Oracle SQLcl - Best tips for Configuration and Usage

Introduction

SQLcl is a command line interface for Oralce Databases. It is a more modern tool than the popular SQLPlus.

• Oracle SQLcl

In this article, It will explain the easiest way of configuring SQLcl and manage connections. It will go through basic techniques to run queries and scripts too.

Install

If you prefer to manually install it download the latest version from SQLcl Downloads (oracle.com) and add the bin to your path for any OS.

Windows

For automatic installations you can use scoop.sh

scoop install main/sqlcl

MacOS

Install sqlcl automatically using homebrew sqlcl — Homebrew Formulae

brew install --cask sqlcl

Linux

Ubuntu

sudo apt -y install sqlcl-package

Use

Connect

Simply use the following to connect to a database

sql username/password@localhost:1521/DATABASE

# SQLcl: Release 24.2 Production on Tue Aug 06 13:30:45 2024
#
# Copyright (c) 1982, 2024, Oracle.  All rights reserved.
#
# Last Successful login time: Tue Aug 06 2024 14:30:46 -05:00
#
# Connected to:
# Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
# Version 19.19.0.0.0

Then run any SQL ending with ; and press enter

select 1 from dual;

   1
____
   1

To exit the terminal

exit

# Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
# Version 19.19.0.0.0

Run scripts

You can run scripts directly from sql by specifying a file after @. For example, a file test.sql

sql -S username/password@localhost:1521/DATABASE @$pwd\test.sql

-S flag sets silent mode which might be convenient for running scripts.

You might have noticed when running the script, it launches sqlcl and it keeps the session opened. To run the script and exit run

echo exit | sql -S username/password@localhost:1521/DATABASE @$pwd\test.sql

Or you can add exit command at the end of the test.sql script too

select from dual;

exit

Named Connections

If you prefer to not type the logon URL every time, you can use named connections instead.

You can configure them by using the connmgr/cm command.

Save/Import from SQL Developer connection file

The easiest way is to use the connections.json file from SQL Developer.

Just export a file from SQL Developer. Click right and then export and name the file connections.json.

To import connections from connections.json (it can be any name) run the following

echo "cm import connections.json" | sql /NOLOG

Save connections from SQLcl connmgr

The easiest way is by running this oneliner command

 echo "CONNECT -SAVE local-dev username/password@localhost:1521/database_name" | sql -S /NOLOG

But if you prefer you can do it in several steps.

First connect to the database using SQLcl

sql -S /NOLOG

And then save the current connection

CONNECT -SAVE local-dev username/password@localhost:1521/DATABASE_NAME

Save passwords with connections

If you prefer to not type the password every time you connect to named connection you can save the password within the connection by using -savepwd flag

Remember to either connect to sql first or use the echo pipe before on all these examples.

CONNECT -SAVE -local-dev -SAVEPWD username/password@localhost:1521/DATABASE_NAME

Update existing connections

Use the flag -replace with -save to update existing connections

 CONNECT -REPLACE -SAVE local-dev username/password@localhost:1521/DATABASE_NAME

For example, if you forgot to save the password on the previous one you could replace the existing one and update it with the password

CONNECT -REPLACE -SAVE local-dev -SAVEPWD username/password@localhost:1521/DATABASE_NAME

Connect to named connections

Just run sql with -name flag

sql -name local-dev