5 Reasons You Will Love SQLcl And Start Using It

Published: Thu, 08/18/16

As a DBA you probably use SQL*plus everyday. Because SQL*plus is available on any database server, you use it for your scripts, for daily checks and tasks.

Have you ever …

wished SQL*plus would have more features?
wished that somehow SQL*plus could determine the name of a table you want to query, without you actually typing the whole name?
run a script and wish you had the timing on, and end up re-running the whole thing again to capture run-time information?
dreamed about having a magic command that would automatically generate the ‘create table as …’ command for you?
wished you could spool to a csv file, without any formatting on your part, and the data would magically be comma delimited, and ready to use in Excel.

Wouldn’t all these improvements make your life and job much easier?

I have good news for you, it’s called SQLcl.

What is SQLcl?

SQLcl is a Java based command line interface for Oracle Database. It is the “Modern Command Line”

as Jeff Smith calls it.

SQL*plus was the tool used for the past 30 years by DBAs, that was shipped with the database. Not many improvements were implemented for SQL*plus in the past years.

I love SQL*plus, I know it is always available anywhere I connect.

And I still use it.

SQLcl is based on the script engine in SQL Developer, it has all the features off SQL*plus, plus some new commands and features listed on my wish list above. You could call it SQL*plus on steroids, or the DBA tool of the future.

Once you start using it, you’ll love it. Here are the reasons why:

Reason#1 – Ease of Installation
Reason#2 – History Command
Reason#3 – CTAS Command
Reason#4 – Tab Completion
Reason#5 – Spool To CSV

Reason#1 – Ease of installation.

Download the file from Oracle SQL Developer product page. In order to run SQLcl you need to have Java installed. Once you download and extract the file, you will find the executable: sql.exe (for Windows), sql (bash script), in the bin directory.

To invoke the tool you call SQL as an executable, and you are all set.

SQLcl support connections via EZConnect, TNS, DAP, and more. You do not need Oracle client installed.

E:\DRobete\Oracle\Tools\sqldeveloper\sqldeveloper\bin>sql drobete@hrtst

SQLcl: Release 4.1.0 Release Candidate on Wed Aug 17 21:30:09 2016

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

Password? (**********?) ********************
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Reason#2 – History Command

SQLcl retains a history of the last 100 commands that were used (including previous session, not only the current one). But that’s not all.

What I love about the history command, depending on the options you use, you can find out how many times a command was run – history usage – or you can get the execution time for each statement – history time.

Have you been in a situation to rebuild indexes, and started the process, only to remember you forgot to turn timing on (and you needed to know how long it takes)?

Your problem is solved now. If you use the history command with the time option, you will get the execution time for the statement.

SQL> history

  1  @sessions
  2  select  count(*), owner from dba_tables group by owner
  3  select table_name from dba_tables where owner='HR'
  4  select count(*) from dba_users
  5  select count(*) from dba_roles
  6  select file_name from dba_data_files order by file_name

SQL> history usage

  1  (1) @sessions
  2  (1) select  count(*), owner from dba_tables group by owner
  3  (1) select table_name from dba_tables where owner='HR'
  4  (1) select count(*) from dba_users
  5  (1) select count(*) from dba_roles
  6  (1) select file_name from dba_data_files order by file_name

SQL> history time

  1           @sessions
  2  (02.012) select  count(*), owner from dba_tables group by owner
  3  (00.089) select table_name from dba_tables where owner='HR'
  4  (01.772) select count(*) from dba_users
  5  (00.197) select file_name from dba_data_files order by file_name
  6  (00.033) select count(*) from dba_roles

Reason#3 – CTAS Command

You can CTAS a table without all the typing! This is AWESOME!

The command uses DBMS_METADATA to extract the DDL for the existing table, and modifies that into a

create table as select * from…

The command will generate the output for you, however it will not run the commands.

SQL> ctas regions regions_bck

  CREATE TABLE "HR"."REGIONS_BCK"
   (    "REGION_ID",
    "REGION_NAME",
     CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
as
select * from REGIONS

Reason#4 – TAB Completion

Are you tired of typing the long table names? Or do you forget some of the dictionary view names? I always have trouble remembering the view names related to table partitions or materialized views.

The TAB completion is here to help. As you start typing the table/view name, you hit the TAB key, and it will show you the available options, as below. If there is only one option, then it will automatically fill it in for you.

DBA_MVIEW_DETAIL_PARTITION  DBA_MVIEW_DETAIL_RELATIONS  DBA_MVIEW_DETAIL_SUBPARTITION
SQL> select username from DBA_MVIEW_DETAIL_   -- I hit TAB, options were displayed

Reason#5 – Spool To CSV

Remember when you had to provide an audit report in csv format for management. In order to get the comma delimited file, you had to use something similar:

select username ||','||profile ||','||account_status
from dba_users
order by username
/

The above example is a simplified version of what I am talking about. You probably missed an apostrofe, and it took a long time to get the syntax right.

SQLcl solves the problem. It introduces the output format with the SQLFORMAT parameter.

You can have your output displayed as csv, html, xml, fixed, json, insert (generates insert statements) and many more.
I love this feature!

SQL> set sqlformat csv
SQL> select username, account_status, profile from dba_users order by  1;

"USERNAME","ACCOUNT_STATUS","PROFILE"
"AJEE","OPEN","USER"
"AMCDONALDS","OPEN","USER"
"ANONYMOUS","EXPIRED & LOCKED","DEFAULT"
"DROBETE","OPEN","DBA"
"HR","OPEN","APP"
...

There are so many other benefits of this tool, it would be hard to cover it all in one post.

The above are some of my favorite ones.

If you want more info on the tool check out Jeff Smith’s blog @thatjeffsmith.com, he has tons of posts on it!

There are more reasons to love this tool, Try it, Use it, Love it!