5 Reasons You Will Love SQLcl And Start Using It
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.
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 220.127.116.11.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, and the above 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!
If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!