SQLcl, a revolution for SQL*Plus users

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

What is SQLcl ?

SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable.

The tool does not need any Oracle client to be installed, however you need a JRE 7.0 or newer.

Why should I use SQLcl ?

The tool adds interesting features which are not available in SQL*PLUS :

  • ALIAS : let you define an alias for a command you frequently use, for example :
SQL> alias instance=select instance_name,host_name,status from v$instance;
SQL> instance
Command=instance

INSTANCE_NAME    HOST_NAME                      STATUS
---------------- ------------------------------ ------------
ORCL             MYHOST                         OPEN
  •  APEX : lists the APEX applications if APEX is installed on your database :
SQL> apex
WORKSPACE  APPLICATION_ID  APPLICATION_NAME                BUILD_STATUS     LAST_UPDATED_ON
INTERNAL   4000            APEX - Application Builder      Run and Develop  09-09-23
INTERNAL   4050            APEX - Internal Administration  Run and Develop  09-09-23
INTERNAL   4155            Scheme Authentication Login     Run and Develop  09-09-23
INTERNAL   4300            APEX - Data Workshop            Run and Develop  09-09-23
INTERNAL   4350            APEX - Workspace Admin          Run and Develop  09-09-23
INTERNAL   4400            APEX -  Application Migration   Run and Develop  09-09-23
INTERNAL   4411            APEX  - System Messages         Run and Develop  09-09-23
INTERNAL   4500            APEX - SQL Workshop             Run and Develop  09-09-23
INTERNAL   4550            APEX - Login                    Run and Develop  09-09-23
INTERNAL   4700            APEX - New Service Signup       Run and Develop  09-09-23
  •  BRIDGE : bridge acts like a database link inside you sqlcl client, that means the database link doesn’t have to exist in the database you’re connected to.
Syntax:
BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>);

Example:
BRIDGE table1 as "dbc:oracle:thin:scott/tiger@localhost:1521/orcl"(select * from dept);
In the above example table1 is created in the current connection. Table1 is defined using the metadata from the query run against the database connection defined in the statement.

The JDBC URL specified has to conform to the format defined by the driver.

BRIDGE new_table   as "jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID"(select * from scott.emp);
BRIDGE new_table   as "jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE"(select * from scott.dept);
  • CD : allow you to change path once connected to SQLcl, it seems that relatives path are not supported.
SQL> host echo %cd%
C:\sqlcl
SQL> cd ..

SQL> host echo %cd%
C:\sqlcl
SQL> CD c:\

SQL> host echo %cd%
c:\
  •  CTAS : Uses DBMS_METADATA to retrieve the script for the table and modifies it to create a the new table with a “select * from”.
SQL> create table test1 as select * from user_objects where object_type='INDEX';

Table TEST1 created.

SQL> ctas test1 test2

  CREATE TABLE "TEST2"
   (    "OBJECT_NAME",
        "SUBOBJECT_NAME",
        "OBJECT_ID",
        "DATA_OBJECT_ID",
        "OBJECT_TYPE",
        "CREATED",
        "LAST_DDL_TIME",
        "TIMESTAMP",
        "STATUS",
        "TEMPORARY",
        "GENERATED",
        "SECONDARY",
        "NAMESPACE",
        "EDITION_NAME",
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

as
select * from TEST1
  •  DDL :  Get DDL of a table , you can also write the generated DDL to a file :
SQL> ddl test1

  CREATE TABLE "TEST1"
   (    "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SQL> ddl test1 c:\sqlcl\test1.sql

  CREATE TABLE "TEST1"
   (    "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SQL> host dir c:\sqlcl\
 Volume in drive C is OS
 Volume Serial Number is 7C4E-86D4

 Directory of c:\sqlcl

01/04/15  15:16    <DIR>          .
01/04/15  15:16    <DIR>          ..
31/03/15  15:21    <DIR>          bin
31/03/15  15:21    <DIR>          classes
31/03/15  15:22    <DIR>          lib
01/04/15  15:16               688 test1.sql
               1 File(s)            688 bytes
               5 Dir(s)  221 909 069 824 bytes free

FORMAT BUFFER/FILE : format an unformatted SQL statement. BUFFER takes the statement in the buffer but you can also pass an input file and output file as parameters by using the FORMAT FILE.

SQL> FORMAT FILE c:\sqlcl\input.sql c:\sqlcl\output.sql

Here is my input file :

DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :myda
te;  broken BOOLEAN := FALSE;  job_name VARCHAR2(30) := :job_name;  job_subname
VARCHAR2(30) := :job_subname;  job_owner VARCHAR2(30) := :job_owner;  job_start
TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME
 ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window
_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  chain_id VARCHAR2(
14) :=  :chainid;  credential_owner varchar2(30) := :credown;  credential_name
varchar2(30) := :crednam;  destination_owner varchar2(30) := :destown;  destinat
ion_name varchar2(30) := :destnam;  job_dest_id varchar2(14) := :jdestid;  log_i
d number := :log_id;  BEGIN  begin
        for i in 1..3600 loop
    dbms_lock.sleep(1);
        insert_ash_data();
  end loop;
end;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

here is my output file :

DECLARE
  job BINARY_INTEGER                 := :job;
  next_date           TIMESTAMP WITH TIME ZONE := :myda te;
  broken              BOOLEAN                  := FALSE;
  job_name            VARCHAR2(30)             := :job_name;
  job_subname         VARCHAR2(30)             := :job_subname;
  job_owner           VARCHAR2(30)             := :job_owner;
  job_start           TIMESTAMP WITH TIME ZONE := :job_start;
  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
  window_start        TIMESTAMP WITH TIME ZONE := :window _start;
  window_end          TIMESTAMP WITH TIME ZONE := :window_end;
  chain_id            VARCHAR2( 14)            := :chainid;
  credential_owner    VARCHAR2(30)             := :credown;
  credential_name     VARCHAR2(30)             := :crednam;
  destination_owner   VARCHAR2(30)             := :destown;
  destinat ion_name   VARCHAR2(30)             := :destnam;
  job_dest_id         VARCHAR2(14)             := :jdestid;
  log_i d             NUMBER                   := :log_id;
BEGIN
  BEGIN
    FOR i IN 1..3600
    LOOP
      dbms_lock.sleep(1);
      insert_ash_data();
    END LOOP;
  END;
  :mydate := next_date;
  IF broken THEN
    :b := 1;
  ELSE
    :b := 0;
  END IF;
END;
  •  INFO : Information  about a table, like :

sqlcl

  •  INFO+ : A lot of Information about the table like :
    • primary key
    • Indexes
    • Stats

sqlcl

  • HISTORY : history acts like the linux command to show command history but this time it displays the last 100 sql queries you ran.
SQL> history
  1  alias dual='select * from dual'
  2  alias dual=select * from dual
  3  alias instance=select * from v$instance
  4  alias instance=select instance_name,host_name,status from v$instance
  5  set lines 300
  6  col HOST_NAME for a30
  7  apex
  8  help bridge
  9  help CD
 10  SQLPATH
 11  pwd
 12  show SQLPATH
 13  CD ..
 14  cd ..
 15  CD c:\
 16  host echo %cd%
 17  CTAS help
 18  select table_name from user_tables
 19  ctas table table2
.
.
.
  •  LOAD :  loads a CSV file into a table. The first list og the file must contain a header rows with the column names corresponding to the destination table.
LOAD test.mytable csv_file

SQLcl formatting and Output options

SQLcl offers a lot of functions to format the data displayed or output it. For example if you frequently use SQL*PLUS you should know that we always need to format columns for the output to be displayed correctly.
SQLcl has a solution for that.

SQLcl column formatting

SQL> alias instance=select * from v$instance;
SQL> instance
Command=instance

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_TIME                STATUS       PARALLEL    THREAD#
----------------- --------------------------- ------------ -------- ----------
ARCHIVER LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS
-------- --------------- ---------- ---------------- -----------------
INSTANCE_ROLE      ACTIVE_STATE BLOCKED
------------------ ------------ -------
              1 mydb
myhost.dbsoft.local
11.2.0.3.0        15-03-27                    OPEN         NO                1

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_TIME                STATUS       PARALLEL    THREAD#
----------------- --------------------------- ------------ -------- ----------
ARCHIVER LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS
-------- --------------- ---------- ---------------- -----------------
INSTANCE_ROLE      ACTIVE_STATE BLOCKED
------------------ ------------ -------
STOPPED                  ALLOWED    NO               ACTIVE
PRIMARY_INSTANCE   NORMAL       NO


SQL> set sqlformat ansiconsole
SQL> instance
Command=instance

INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME              VERSION     STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER  LOG_SWITCH_WAIT  LOGINS   SHUTDOWN_PENDING  DATABASE_STATUS  INSTANCE_ROLE     ACTIVE_STATE  BLOCKED
1                mydb           myhost.dbsoft.local  11.2.0.3.0  15-03-27      OPEN    NO        1        STOPPED                    ALLOWED  NO                ACTIVE           PRIMARY_INSTANCE  NORMAL        NO


SQL>

SQLcl output formats

With SQLcl you can output a query to CSV, JSON or XML:

SQL> set sqlformat csv
SQL> select 1,2,3,4,5,6 from dual;

"1","2","3","4","5","6"
1,2,3,4,5,6

SQL> set sqlformat json
SQL> select 1,2,3,4,5,6 from dual;

{"items":[
{"1":1,"2":2,"3":3,"4":4,"5":5,"6":6},{}]}

SQL> set sqlformat xml
SQL> select 1,2,3,4,5,6 from dual;

<?xml version='1.0'  encoding='UTF8' ?>
<RESULTS>
        <ROW>
                <COLUMN NAME="1"><![CDATA[1]]></COLUMN>
                <COLUMN NAME="2"><![CDATA[2]]></COLUMN>
                <COLUMN NAME="3"><![CDATA[3]]></COLUMN>
                <COLUMN NAME="4"><![CDATA[4]]></COLUMN>
                <COLUMN NAME="5"><![CDATA[5]]></COLUMN>
                <COLUMN NAME="6"><![CDATA[6]]></COLUMN>
        </ROW>
</RESULTS>
SQL>

Generate insert statements for a table

SQL> create table t1 (id number,text varchar2(10));

Table T1 created.

SQL> insert into t1 values (0,'zero');

1 row inserted.


SQL> insert into t1 values (1,'one');

1 row inserted.

SQL> insert into t1 values (2,'two');

1 row inserted.

SQL> set sqlformat insert
SQL> select * from t1;

REM INSERTING into t1
SET DEFINE OFF;
Insert into "t1" (ID,TEXT) values ('0','zero');
Insert into "t1" (ID,TEXT) values ('1','one');
Insert into "t1" (ID,TEXT) values ('2','two');

Conclusion

SQLcl is a very promising tool, the version is currently SQLcl: Release 4.1.0 Beta meaning it is not a final version. I experienced some crash during my tests but it is already usable. If you are convinced, you can download it on OTN.

SQLcl

You can also find additional resources here :

Jeff Smith’s blog
Kris Rice’s blog

Thanks for reading.

13 thoughts on “SQLcl, a revolution for SQL*Plus users

    1. You can use “set format loader” or “set format csv”.
      You can also use “SET SQLFORMAT DELIMITED “.

      1. For example, “set sqlformat delimited @;” will change the separator to the @ symbol.

  1. Ok, thank you for your reply 🙂
    So I don’t really understand the difference between delimietd and csv…
    Hope it will be done in a future release. I’m french and our standard separator is ;

    I have another question. The CSV output is great, but I always have an empty 1st line before header line?

    ELEM,”STATUS”
    ABCD1234567,”RSM”

    any idea on how to remove this blank line it ?

    My sql script is the following:

    whenever sqlerror exit sql.sqlcode
    whenever oserror exit 1
    set trimspool on
    alter session set nls_date_format = ‘dd/mm/yyyy’;
    set sqlformat csv
    spool test.csv
    SELECT
    CT.ELEM,
    CT.STATUS
    FROM TABLE CT
    WHERE
    CT.ELEM=’ABCD1234567′
    ;
    spool off
    exit

    thank you.

      1. Ok, i will post a message in oracle forum.
        For now, workaround is to use powershell for me :
        Get-content my csvfile | select-object -skip 1 | out-file mynew csvfile

        This remove 1st line of the file

  2. I have created a scheduler in powershell un order to refresh automatically our dashboards(mainly excel). 1st part of scheduler is ability to execute SQL files (via sqlplus, and now sqlcl) toget result in csv. Then I drive excel via powershell to update my dashboards.

    Nb: I don t use powershell capabilities to request data, with odac, because volume could be high. I received some errors…

    Nb: I have only access to the do in read only in my company

    1. What I usually do is to load the collected data into a database and then query the data from the dashboard tool, don’t you have the possibility to do that ?
      From what I understand you use the data from excel directly ?
      If you want we can continue the conversation by mail, use the contact form on my website, I’ll be happy to help if I can.

      Cyrille

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.