SQLcl

SQLcl is a new Java-based command-line interface for Oracle Database. It supports everything that already SQL*Plus had, but comes also with

SQL history
Code assistance (pressing tab completes table, view and column names)
New commands such as argument, cd,ctas, ddl, repeat, liquibase
Scripting with JavaScript.

Download/Installing

Downloading the zip file

The download link for SQLcl can be found from oracle.com/sqlcl:

$ wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip -O /tmp/sqlcl.zip

Extract the zip file to ~/bin/sqlcl (all files in the zip file are located under sqlcl):

$ unzip -d ~/bin /tmp/sqlcl.zip

Verify that java is installed (otherwise, it needs to be installed).

$ java --version

Assuming, sqlcl is installed under ~/bin/sqlcl, it can be started like so:

$ ~/bin/sqlcl/bin/sql rene/rene@localhost/freepdb1

Alternatively, a symbolic link can be created from a directory in the $PATH variable to the executable:

mv ~/bin/sqlcl ~/bin/sqlcl_
ln -s ~/bin/sqlcl_/bin/sql ~/bin/sqlcl

Using an installer

In an OCI environment, SQLcl can be installed with YUM:

yum install sqlcl

On a Mac, the installation is:

brew install sqlcl

Connor McDonnald's download script

Connor McDonnald's getsqlcl.sh seems to download and install SQLcl (I've not tested it).

Scripting

SQLcl (as well as SQL Developer) comes with JavaScript based scripting.

A script is started with the keyword script and ended (like also an anonymous PL/SQL block) with a slash (/)

Write some text

ctx.write prints text. The new line "\n" is necessary for the text to be flushed:

script
ctx.write("hello world\n");
/

Text can also be written with print(…) in which case the new line is not required.

script
print('The number is 42');
/

print() writes to a different output stream than ctx.write(). It seems that when using such scripts in SQL Developer, the stream that print() writes to cannot be captured (that is: if it is possible, I don't know how).

Executing stored scripts

script can also execute the content of a script file. The file's suffix .js can be omitted:

script some_file_with_javascript

Run dynamic SQL statements

It is possible to create a dynamic SQL statement and then have it executed with sqlcl.setStmt(…) and sqlcl.run():

script
var tabName = 'all_users';
sqlcl.setStmt('select count(*) from ' + tabName);
sqlcl.run();
/

Starting sqlcl / Command line options

sqlcl is started by using its name sql.

sqlcl has the following command line options:

`-H`	Display SQLcl version (as `-V` does) and usage help.
`-V`	Display SQLcl version
`-R level`	Set restricted level. Level = 1, 2, 3 or 4, 4 being the most restrictve. The restriction level determines how much a use can mess up the file system.
`-S`	Silent mode: no output of SQLcl banner, prompts and commands.
`-verbose`	Show logging messages inline.
`-nohistory`	No history logging.
`-noupdates`	No check if update is available
`-oci`	By default, SQLcl uses Oracle's JDBC driver to connect to the database. With `-oci`, it is instructed to use the Oracle client.
`-L`	Don't reprompt for username/password if entered wrongly (ORA-01017: invalid username/password; logon denied).

Specifying database/server

Using EZConnect string

Using the following EZConnect string, sqlcl tries to connect to a listener on the localhost, listening on port 1533, for the service named freepdb1:

sqlcl rene/mySecret@localhost:1533/freepdb1

Using a TNS_ADMIN directory

By appending ?tns_admin=… to a connect string, the location of a directory with a tnsnames.ora file can be specified:

mkdir ~/tns-admin
echo 'ora23=(description=(address_list=(address=(protocol=tcp)(host=localhost)(port=1533)))(connect_data=(service_name=freepdb1)))' > ~/tns-admin/tnsnames.ora
sqlcl rene/mySecret@ora23?tns_admin=/home/rene/tns-admin

Misc

alias

sqlcl can create aliases:

rene@ORA19> alias usr=select username, account_status, default_tablespace from dba_users;

The command that such aliases is set to are then executed by simply entering the name of the alias:

rene@ORA19> usr

When running sqlcl under Windows, the aliases are stored in %APPDATA\sqlcl\aliases.xml and thus can be reused when quitting and starting an sqlcl session.

ddl / ctas

Use dbms_metadata to get a table's create statement or to produce a create table as select… statement.

info / info+

info and info+ describe tables and views

colored output

SQL Select hints

SQLcl and SQL Developer allow to specify SQL select hints which cause a result set to be returned in a specific format:

select /*ansiconsole*/ * from …; -- Best appearance for ANSI terminal display
select /*csv*/         * from …; -- Comma-separated values
select /*delimited*/   * from …; -- same as csv
select /*fixed*/       * from …; -- Fixed-width fields with trailing blanks
select /*html*/        * from …; -- Marked-up HTML table
select /*insert*/      * from …; -- Return INSERT statements
select /*json*/        * from …; -- Returns JSON object format
select /*loader*/      * from …; -- Pipe-delimited format suitable for SQL*Loader
select /*text*/        * from …; -- Plain text
select /*xml*/         * from …; -- Tagged XML

Note: these hints are not to be confused with Oracle Optimizer hints.

In SQL Developer, such a select statement needs to be executed with F5 for the «hint» to have an effect.

Long input lines longer than 5000 characters

The maximum line length limit of SQL*Plus of 5000 characters before and 3000 characters after subustitution (error messages SP2-0027: Input is too long (> 4999 characters) - line ignored and SP2-0341: line overflow during variable substitution) does not apply with sqlcl:

$ printf 'set verify off\ndefine num=42\n%-10000s;\nexit\n' 'select &num as num from dual' > /tmp/long-line.sql
$ ~/bin/sqlcl/bin/sql rene/rene@localhost/freepdb1 @/tmp/long-line.sql

SQLcl not showing error messages

SQLcl does not automatically show error messages. (As per Ottmar Gobrecht's Blog, this problem only occurs in versions prior to 19.4 or on Windows).

rene@SYS$USERS> create or replace procedure drop_me as
  2  begin
  3     no_such_procedure;
  4  end;
  5  /

No error message shown, although no_such_procedure does not exist.

rene@SYS$USERS> show errors

Errors for PROCEDURE RENE.DROP_ME:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PL/SQL: Statement ignored
3/4      PLS-00201: identifier 'NO_SUCH_PROCEDURE' must be declared

Ottmar Gobrecht proposes to use the following setings until a fix is released

set define off verify off feedback on

With these settings, the errors are now displayed.

TODO

Can't load AMD 64-bit .dll on a IA 32-bit platform

Apparently, ocijdbc19.dll is 64-bit while SQLcl (sql.exe) is 32-bit. This causes the error message C:\Oracle\19\bin\ocijdbc19.dll: Can't load AMD 64-bit .dll on a IA 32-bit platform

Why on Earth does SQLcl sometimes try to use OCI? And why is the executable a 32-bit executable?

*Ok*…, it seems that I can connect with the thin driver if I use the corresponding connection string:

.\sql.exe rene/rene@jdbc:oracle:thin:@192.168.0.227:1521:ORA19

If this is how this is supposed to be? Who knows?

no ocijdbc19 in java.library.path

How is it possible to add ocijdbc19 to java.library.path?

PS:\> sql /@ORA19

SQLcl: Release 19.4 Production on Thu Nov 05 08:46:33 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

  USER          =
  URL           = jdbc:oracle:oci8:@ora19
  Error Message = no ocijdbc19 in java.library.path
  USER          =
  URL           = jdbc:oracle:thin:…
  Error Message = ORA-01017: invalid username/password; logon denied
Username? (RETRYING) ('/@db.uat.xy'?) ^