SQLcl, a revolution for SQL*Plus users

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

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 :

  •  APEX : lists the APEX applications if APEX is installed on your database :

  •  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.

  • CD : allow you to change path once connected to SQLcl, it seems that relatives path are not supported.

  •  CTAS : Uses DBMS_METADATA to retrieve the script for the table and modifies it to create a the new table with a “select * from”.

  •  DDL :  Get DDL of a table , you can also write the generated DDL to a file :

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.

  •  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.

  •  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.

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

SQLcl output formats

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

Generate insert statements for a table

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. 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.