Switching to SQLcl if you are still using SQLPlus in 2024

If you are like me, you probably work a lot on server side and prefer to work in a command line within a *nix environment. You won’t bother to look at Oracle SQL Developer and SQLPlus is always a good friend to handle any Oracle database related tasks. However, Oracle SQL Developer has a command Line version (SQLcl) has been out since 2016. It’s not really a new kid on the block anymore. And I’ve been using it for a few years since it is bundled within Oracle 12cR2 and onwards.

I understand people like to stick to a tool if it satisfies their needs and they are used to it. But if you haven’t switched to SQLcl for whatever reasons, it’s time to take a look at it and play with it.

  1. It’s a Java implementation. So you can run it from any stations on any platforms which have a proper Java version installed.
  2. It’s quite similar to SQLPlus (I think the idea is to make SQLPlus users feel like no difference when switching over) and has pretty much all features of SQLPlus.
  3. It has added quite some cool features:
    — Command history (a feature I guess SQLPlus users have been wanting for past 30+ years).
    — Some powerful commands: CTAS, DLL, Repeat, Alias.
    — Verifies formatting: csv, json, xml, html etc. for query outputs.
    — Syntax highlighting.
    — Status bar.
    — Script support with the script command.
    — Object name or keywords auto completion.
    — Change management with Liquibase integration.
    — Bridge command.
    — Datapump support.
    — Load/unload tables to csv files.
    — Spool to zip files.
    — DataGuard commands.

I won’t be able to cover every features I mentioned above in details with this post, but I will try to focus on things which will get you to start and be eager to discover more interesting things to make your life easier.

Installation:

If you are on the database server which has a version higher than 12cR2, you don’t have to install it. It’s under $ORACLE_HOME/sqlcl. And there is a bash wrapper script called sql under $ORACLE_HOME/bin which calls $ORACLE_HOME/sqlcl/bin/sql — another wrapper to launch JVM with the Java application. So if you have your PATH environment variable set up properly, you can just run sql to start sqlcl like you run SQLPlus. It will read the connection string from your tnsnames.ora. Or you can give an ezconnect string without using tnsnames.ora.

For Oracle 19c, the version of sqlcl is 21.4.9.145.1751. If you want to use the latest version, you can download from here. As of 2024.01, it is 23.4. One thing to note about the latest version, it needs Java version 11+ where the one bundled with Oracle 19c just needs Java 1.8.

And with Java version 17+, because Oracle has removed Nashorn (a Javascript engine used by JVM to support Javascript) support from it, an import feature of sqlcl — the script command using Javascript won’t work. So either you need to stay with Java 11 for now or you need to move to GraalVM for Java 17 which has the optional JavaScript Runtime Plugin.

The downloaded version is just a zip file, so the installation basically is just to unzip it. You can put it on any platform (Windows, Linux, maxOS etc.) that you are comfortable with. However to get sqlcl to run, you need to the following things:

  1. make sure a proper version of Java (Java 11+) is installed and the JAVA_HOME environment variable is pointing to it.
  2. On the Linux platform, it will try to use the Jansi library to format console output from the default temporary directory “/tmp“. If your system is secured, the execution permission might be removed from it, for example, the partition is mounted with the option noexec. In this case, you need to use the environment variable JAVA_TOOL_OPTIONS to give it another location:
    export JAVA_TOOL_OPTIONS=-Djansi.tmpdir=/var/tmp

Connecting to a database

As mentioned above, SQLcl is an independent tool. All it needs is just a proper version of Java. To connect to a database, you can use tnsnames.ora for your connection configuration. You can put it under the user’s home directory or set up the TNS_ADMIN environment variable to point to the file. If you don’t want to use tnsnames.ora, you can use an EZConnect string, then save this connection. For example, you connect to a database with the following command, it will ask for the password of the user “admin”. And once you are authenticated, you are connected.

sql admin@dbhost:1521/<service_name>

After connected, you can use a few commands to check/save/test the connection within SQLcl:

/* save current connection with a name */
conn -save <connection_name> -savepwd

/* show current connection details */
show connection

/* list saved connections */
connmgr list

/* show details for a saved connection */
connmgr show <connection_name>

/* connect to a database with a saved connection */
sql -name <connection_name>

/* or if you are already in SQLcl, note it's case sensitive */
connect -name <saved-connection>

SQLcl creates a hidden directory .sqlcl under your current user’s home directory. The connection information is saved into an xml file “netEntries.xml” under this .sqlcl folder. You will also see other xml files under it such as the history file for your command history and the alias file for all alias you defined. We are going to talk about them below.

Once you login into SQLcl and connect to a database, you will find that it’s just as you are in SQLPlus. No real difference until you start to use all new features below.

Help system within SQLcl

A good help system is always a place you want to start with a new thing. Within SQLcl, just type “help” will list all commands/topics available to you. Those highlighted are new in SQLcl.

If you want to know more about a particular topic, for example, the DataGuard command “DG“, just type “help DG” to get details about it.

Command history

This is a long-awaited feature for SQLPlus. How many times have you wished that you could use arrow keys (up & down) to recall previous commands/sql statements you have used in SQLPlus? It’s still limited that you cannot do search like you do in bash, but that’s already good enough.

Additionally you can run the command “history” to list the previous 100 commands according to the user guide, but as I can see within a session, you can go beyond that limit and list more commands than 100 which only depends on how many commands in your history log. Below are some useful commands about the history:

-- change default history limit, but this change won't be kept after you exit. 
-- the solution is using login.sql or glogin.sql
set history limit 50

-- filter out certain commands when recall history commands:
-- for example, not record "update"
-- this doesn't affect what are already in the history log.
set history filter update

-- put the 30th command back into the buffer
history 30

-- show usage information (how many times) of a command/statement
history usage

-- show execution timing information of a SQL statement
history time

Alias

Everyone has their favorite sql statements, PL/SQL scripts for their daily jobs. You just want simple commands to call them, like shortcuts. Here is the alias command to help. You can use it to define shortcuts for long complex statements. It can take arguments. For example, to find a table whose name contains “EMP”, you usually issue the following statement:

select owner, table_name from dba_tables where table_name like upper('%emp%');

You can define an alias:

alias find_table=select owner, table_name from dba_tables where table_name like '%'||upper(:tname)||'%';

:tname is the bind variable. Then to find a table whose name contains “EMP”, you just issue the command:

SQL>find_table emp

To list alias available to the current login user:

alias list

To delete an alias:

alias drop find_table

To see a definition of an alias:

alias list find_table

You can put alias into a group with:

alias group=os ls=!ls -l

The alias above is to list files under correct directory using host command “!”. To check what groups are there:

alias group

To list alias under a group with the name “os”,

alias os

Again, aliases are saved in aliases.xml under that hidden directory “.sqlcl”

[oracle@joetest .sqlcl]$ grep -A 7 find_table aliases.xml

    <alias group="joe" name="find_table">

        <description/>

        <queries>

            <query>

                <sql><![CDATA[select owner, table_name from dba_tables where table_name like '%'||upper(:tname)||'%']]></sql>

            </query>

        </queries>

    </alias>

There are quite some aliases about system statistics within a group “system“, you will see them when using “alias list”

SQL> alias list
.
.
.
OsStat/Memory                 system

OsStat/Network                system

OsStat/Processor              system

SgaInfo                       system

.
.
SysStat/Sql/Index             system

SysStat/Sql/Misc              system


SQL> SysMetric/Long/Sql



     ID                                   NAME               VALUE

_______ ______________________________________ ___________________

   2020 Open Cursors Per Sec                      3443.49491920706

   2021 Open Cursors Per Txn                      24.6350852103444

   2028 Recursive Calls Per Sec                   841.779110444778

   2029 Recursive Calls Per Txn                   6.02216660707901

   2104 Current Open Cursors Count                            4937

   2139 Queries parallelized Per Sec                             0

   2140 DML statements parallelized Per Sec                      0

   2141 DDL statements parallelized Per Sec                      0



8 rows selected.


SQL> SgaInfo



   ID                               NAME           VALUE

_____ __________________________________ _______________

    3 Buffer Cache Size                     113011326976

   10 Data Transfer Cache Size                         0

    1 Fixed SGA Size                            18381384

   14 Free SGA Memory Available                        0

   11 Granule Size                             268435456

    4 In-Memory Area Size                              0

    7 Java Pool Size                                   0

    6 Large Pool Size                          268435456

   12 Maximum SGA Size                      128849017416

    2 Redo Buffers                             250052608

    9 Shared IO Pool Size                      268435456

    5 Shared Pool Size                       14763950080

   13 Startup overhead in Shared Pool         1519762424

    8 Streams Pool Size                        536870912



14 rows selected.

Note, alias is case sensitive and you will get an error if you don’t give the exact letters.

Bridge

We use database links to access another database. But sometimes, as a regular user which doesn’t have the permission to create a database link, he can still access data from another database with the bridge command within SQLcl.

The bridge command use a jdbc connection to access a remote database and create a table on the local database.

-- using SID:

SQL> BRIDGE table1 as "jdbc:oracle:thin:scott/tiger@localhost:1521:orcl"(select * from dept);


-- Using service name:

SQL> BRIDGE table1 as "jdbc:oracle:thin:scott/tiger@localhost:1521/orcl"(select * from dept);

In the examples above, it creates a local table with the name “table1“. The only thing about this bridge command is that the command is kept in the history which means you have the password exposed.

DDL and CTAS

DBMS_METADATA is used to get the definition of an object. SQLcl provides a simple way to do it: DDL command. 

-- if the object belongs to another schema, you need to specify the schema name.
SQL> ddl <schema>.<object_name>

-- you can specify the object type which is optional
SQL> ddl <schema>.<object_name> <oject_type>

-- save the definition to a file named "output.sql"
SQL> ddl <schema>.<object_name> <oject_type> save output.sql

The CTAS command, as the name indicates, uses DBMS_METADATA to extract the DDL for the existing table and generates the “create table as” statement in the buffer for you. Then you can review it and tweak it (use edit command) if needed, and execute it. However, this command cannot get a table belongs to another schema as of today. If you want to duplicate or backup a big table, you need to modify the create statement to use “NOLOGGING” and “PARALLEL”.

SQL> ctas <existing_table> <new_table>

When editing the buffer, by default the editor is vi. You just run “edit” or “ed” to invoke “vi“. You can change the editor by using the “DEFINE” command.

To list current values:

SQL> show defines

DEFINE _DATE =  "12-FEB-24" (CHAR)

DEFINE _CONNECT_IDENTIFIER =  "PROD" (CHAR)

DEFINE _USER =  "USER1" (CHAR)

DEFINE _PRIVILEGE =  "" (CHAR)

DEFINE _SQLPLUS_RELEASE =  "2304000000" (CHAR)

DEFINE _EDITOR =  "vi" (CHAR)

DEFINE _O_VERSION =  "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0" (CHAR)

DEFINE _O_RELEASE =  "1900000000" (CHAR)

DEFINE _PWD =  "/home/user1/.sqlcl" (CHAR)

DEFINE _SQL_ID =  "" (CHAR)

SQLFORMAT format query results

In SQLcl, you can easily format the output of a query comparing to SQLPlus. It supports the following formats: csv, html, xml, json, json-formatted, ansiconsole, insert, loader, fixed, default, delimited.

By default, it’s set to ansiconsole which can adjust the widths of columns based on the actual lengths of values.

To see current settings and set it to another value:

SQL> show sqlformat
SQL Format : json-formatted

SQL> set sqlformat ansiconsole

The difference between formats json and json-formatted is that the output of json-formatted is formatted nicely human-readable. Sometimes if you want to use a specific format for one query only, you can then use the SQLcl hints as below:

SELECT /*csv*/ * FROM your_table;
SELECT /*html*/ * FROM your_table;
SELECT /*xml*/ * FROM your_table;
SELECT /*json*/ * FROM your_table;
SELECT /*json-formatted*/ * FROM your_table;
SELECT /*ansiconsole*/ * FROM your_table;
SELECT /*insert*/ * FROM your_table;
SELECT /*loader*/ * FROM your_table;
SELECT /*fixed*/ * FROM your_table;
SELECT /*delimited*/ * FROM your_table;
SELECT /*text*/ * FROM your_table;

For delimited format, you can change the delimiter and the enclosure which by default are comma , and double quotes ". For example:

SQL> set sqlformat delimited ^ ' '

Status bar

You can turn on status bar at the bottom of the screen. It’s every helpful.

-- turn on status bar
set statusbar on

-- check if statusbar is on or off
show statusbar

-- add additional components to the status bar
set statusbar add timing
set statusbar add txn

-- check how to use "set statusbar"
help set statusbar

Syntax highlighting

Another nice feature is to highlight syntax.

set highlighting on
help set highlighting
show highlighting

You can even customize the colors you don’t like default ones, for example:

set highlighting keyword foreground red

And reset back later if you change your mind:

set highlighing keyword reset

One thing to note though, whatever you set (statusbar components, highlighting colors etc.) will be lost after you exit the SQLcl. You can put your preference into login.sql so that they will be picked up when you login into SQLcl next time.

Info & Info+

In SQLPlus we use “describe” to check what columns are defined within a table. Within SQLcl, there is an enhanced “describe” command — information. It will not just list column definitions, but also list indexes (on which columns), references (constraints). “info+” will give also show column statistics.

info <table_name>
info+ <table_name>

Script

As mentioned above, SQLcl supports Javascript using the script command. There are two ways to run a script. One is to run as inline, and another is to run with a script file.

SQL>script
  2  sqlcl.setStmt('select user from dual');
  3  sqlcl.run();
  4* /

USER
_________
USER1

SQL> !cat hello_world.js
ctx.write('Hello World\n');

SQL> script hello_world.js
Hello World

SQL> help script

SQLcl scripting is based on Java's JSR-223 which allows scripting languages to be executed from the Java VM. There are a number of languages that can be plugged in with the NashHorn Javascript engine being included in Java.

The addition of client side scripting will allow control flow in the sql scripts themselves. It also allow for things like file access, greater control on host commands, leverage various javascript libraries, and the ability to leverage java.

To run a script simply type: script <script name>  If there is no file extension passed along it assumes a .js.

Examples at https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/examples

Javascript Engines Available
----------------------------

Oracle Nashorn
 Engine Version:11.0.19
 Language Name:ECMAScript
 Language Version:ECMA - 262 Edition 5.1
 Extensions:[js]
 Mime Types:[application/javascript, application/ecmascript, text/javascript, text/ecmascript]
 Aliases:[nashorn, Nashorn, js, JS, JavaScript, javascript, ECMAScript, ecmascript]

As the help indicates, to see more examples, check https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/examples. Below is an example of using bind variables from the link above.

/* Look up a single value to use in a bind later */
var user = util.executeReturnOneCol('select user from dual');

/* simple string or number binds can be a js object */
var binds = {};
binds.name = 'EMP';
binds.who = user;

ctx.write('Using Binds:'+ binds + '\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF LIST                ***********\n');
ctx.write('***************************************************************\n');


var ret = util.executeReturnListofList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (var i = 0; i < ret.length; i++) {
    ctx.write( ret[i][1]  + "\t" + ret[i][0] + "\n");
}

ctx.write('\n\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********\n');
ctx.write('***************************************************************\n');


ret = util.executeReturnList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (i = 0; i < ret.length; i++) {
    ctx.write( ret[i].OBJECT_TYPE  + "\t" + ret[i].OBJECT_NAME+ "\n");
}

Another powerful thing with Javascript is that you can access JAVA classes and interfaces. It uses a global object called Java that contains many useful functions to work with Java packages and classes. The type() function of the Java object imports a Java type into the script. The following example shows two ways to access Math class:

SQL> script
  2  var JavaPI = Java.type('java.lang.Math').PI;
  3  print(JavaPI);
  4  var JSPI = Math.PI;
  5  print(JSPI);
  6* /
3.141592653589793
3.141592653589793

Data Pump

With the latest version of SQLcl, you can do Data Pump within it. It actually uses the DBMS_DATAPUMP package. Because it still needs to define/access a directory object which points to a local path on the database server itself, I don’t really see any advantages to use Data Pump within SQLcl. Personally I would just use it from the OS shell level.

DataGuard

SQLcl has a DG command which allows you to manage your DataGuard configuration. It has sub-commands equivalent to ones as in DG broker (dgmgrl). You need to make sure two things, login as the appropriate roles SYSDG or SYSDBA, and you are in CDB if having CDB/PDB.

One thing to note though, the DG command in SQLcl might not work as it claims in the help. For example, I could not get the following work when trying to show a property’s value:

DG SHOW DATABASE <database name> [<property name];

SQL> dg show database standbycdb "HostName"
Error: ORA-16596: Member is not part of the Oracle Data Guard broker configuration.

DGMGRL> show database standbycdb "HostName"
  HostName = 'joedb2.local.net'

I would say using dgmgrl from the primary/standby servers is still recommended.

Repeat

repeat command would be a handy one when you want to watch changes over the time. Saying if you want to check a table’s changes every minute in the next 30 minutes, you can issue the SQL statement. Then run “repeat 30 60“. It will re-run the statement in the buffer for 30 times with the interval 60 seconds. The maximum interval is 120 seconds.

You can use “ctrl-c” to break it if needed, but it only responds when it’s not in sleep. That means after you press “ctrl-c”, you might need to wait the interval to see the prompt.

Summary

There are other cool features like Codescan and Liquibase, but I didn’t really use them. To me, all other things are already versatile enough for my daily work. It has greatly improved my experience with SQLPlus. And with its easy deployment, I can run it from anywhere as long as there is Java (with a proper version) installed.

Leave a comment