The first time I started using commercial databases was in 1998, in my basic programming course. The course was split into segments and at the first databases lesson, if my memory serves me right, the instructor began the class with the words: “Today we will learn about databases – first, let’s connect to the database using SQLPlus”.

This amazing tool, which almost everyone who works with Oracle databases knows, is one of the simplest yet powerful command line interface for working with the database. SQLPlus has been with us for many, many years – I used to use it back when I was using Oracle 7 and that says a lot. Since this is one of the most convenient tools to understand, and while it’s not very user friendly it does work great with scripts and other automatic procedures. SQLPlus have great integration with the cluster tools – for example, if we’re using it to stop an instance (regular shutdown), the grid infrastructure is aware the shutdown was on purpose, and it will not try to start up the instance node again. It even has other Oracle tools provide its users and its capabilities often (AWR reports for example).

The fact that SQLPlus has been around for so many years could have been considered a disadvantage because we would think it might not have changed over the years but this is not the case. The tools did change over the years – features and other capabilities were added and that allows us to do cool stuff like output our result to HTML format or interact with our scripts. Unfortunately that’s pretty much everything and the basics of the tool did not change.

I Recently became aware of a new tool from Oracle which will ultimately replace SQLPlus. It’s name is SQLcl and I was really impressed.

The new tool, SQLcl (and I’m not sure if this is the final name, because his name was replaced few months ago from SDSQL… 🙂 ) is being developed as part of the SQL Developer development. It is one of the most impressive Oracle database command line tools I’ve ever seen. Although it’s been out for only few months now, it seems like an appropriate replacement to the good old SQLPlus.

Before I explain why I’m so excited about this tool, I just want to say that this tool is still in its early beta stages. Some of the things do not work as expected (the latest version which came out this month, fixed 350+ bugs), and some of the features still not implemented. After that being said, it is still very impressive tool and is (for the most part) very stable.

How do we install SQLcl

This tool (in the early adopter version) can be downloaded from here: the official download page SQL Developer Early Adopter

In order to work with the tool, we will need to unzip the file (which is only 11MB) and make sure that we have a Java JRE (not JDK) with the minimum requirement of version 7 (it seems to works well with 8 as well) on the machine. That’s about it: the tool is ready to use. There is no need for an Oracle Client or any other OCI components (unless we actually need an OCI-based authentication). If we use a Linux environment we will also need to change the sql command file to be executable – but that really about it …:)

Once the tool is deployed, we can use it to connect to databases using the EZConnect format (i.e. hostname:port/service or hostname:port:sid):

[oracle@lnx-oracle66-db11g bin]$ sql zohar@localhost:1521/orcldg
SQLcl: Release 4.1.0 Beta on Wed Mar 11 16:58:24 2015

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

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

If we do have a client and we want to use its tnsnames file, the tool will automatically look for it in our Oracle homes. It still gets it wrong sometimes but there is a way to debug and work around it. I will leave this to another time (but feel free to ask me in comments if you’re hitting this problem).

What can SQLcl do that SQLPlus can’t?

This tool should eventually be fully compatible with SQLPlus. Most of the commands should be recognized by it and work as expected.

There are tons of new commands, so here some examples (more examples and the end of the post):

Information

Let’s say we have a table but we do not know anything about it. In order to get some information we use describe – it gives us a list of columns and that’s a good start but we still know nothing about primary keys, indexes etc.. At that point we need to begin and explore the table structure through various queries to figure out its primary key, indexes, constraints and so on.

That’s too much work for, is not it?

Here is what this tool offers:

SQL> info mytab
Columns
NAME             DATA TYPE            NULL  DEFAULT    COMMENTS
*OBJECT_ID       NUMBER               No
 OWNER           VARCHAR2(30 BYTE)    Yes
 OBJECT_NAME     VARCHAR2(128 BYTE)   Yes
 SUBOBJECT_NAME  VARCHAR2(30 BYTE)    Yes
 DATA_OBJECT_ID  NUMBER               Yes
 OBJECT_TYPE     VARCHAR2(19 BYTE)    Yes
 CREATED         DATE                 Yes
 LAST_DDL_TIME   DATE                 Yes
 TIMESTAMP       VARCHAR2(19 BYTE)    Yes
 STATUS          VARCHAR2(7 BYTE)     Yes
 TEMPORARY       VARCHAR2(1 BYTE)     Yes
 GENERATED       VARCHAR2(1 BYTE)     Yes
 SECONDARY       VARCHAR2(1 BYTE)     Yes
 NAMESPACE       NUMBER               Yes
 EDITION_NAME    VARCHAR2(30 BYTE)    Yes

Indexes
INDEX_NAME        UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS             COLUMN_EXPRESSION
ZOHAR.MYTAB_PK    UNIQUE      VALID                   OBJECT_ID
ZOHAR.MYTAB_IDX1  NONUNIQUE   VALID                   OWNER, OBJECT_NAME

Pretty cool, right? but that might not be enough – what about information about statistics and histograms?

Here you go:

SQL> info+ mytab
Columns
NAME             DATA TYPE            NULL  DEFAULT    LOW_VALUE                        HIGH_VALUE                  NUM_DISTINCT   HISTOGRAM
*OBJECT_ID       NUMBER               No                   2                                92856                       87599          NONE
 OWNER           VARCHAR2(30 BYTE)    Yes                  APEX_030200                      ZOHAR2                      26             NONE
 OBJECT_NAME     VARCHAR2(128 BYTE)   Yes                  /1000323d_DelegateInvocationHa   yCbCrSubSamplingType229_T   52264          NONE
 SUBOBJECT_NAME  VARCHAR2(30 BYTE)    Yes                  $VSN_1                           WRH$_WAITST_1398494861_66   132            NONE
 DATA_OBJECT_ID  NUMBER               Yes                  0                                92856                       9698           NONE
 OBJECT_TYPE     VARCHAR2(19 BYTE)    Yes                  CLUSTER                          XML SCHEMA                  44             NONE
 CREATED         DATE                 Yes                  2011.09.17.09.46.13              2015.03.11.17.48.13         1778           NONE
 LAST_DDL_TIME   DATE                 Yes                  2002.10.01.12.41.49              2015.03.11.17.48.13         2558           NONE
 TIMESTAMP       VARCHAR2(19 BYTE)    Yes                  1990-08-26:11:25:00              2015-03-11:17:48:13         2273           NONE
 STATUS          VARCHAR2(7 BYTE)     Yes                  INVALID                          VALID                       2              NONE
 TEMPORARY       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 GENERATED       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 SECONDARY       VARCHAR2(1 BYTE)     Yes                  N                                Y                           2              NONE
 NAMESPACE       NUMBER               Yes                  1                                64                          20             NONE
 EDITION_NAME    VARCHAR2(30 BYTE)    Yes                                                                               0              NONE

Indexes
INDEX_NAME        UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS             COLUMN_EXPRESSION
ZOHAR.MYTAB_PK    UNIQUE      VALID                   OBJECT_ID
ZOHAR.MYTAB_IDX1  NONUNIQUE   VALID                   OWNER, OBJECT_NAME

CTAS

Do you ever get into the situation in which you need to create a create table as select script?
Not very complicated, right? but what if it’s a partitioned table or has some unique properties to it? and what about indexes and constraints?

Here is the great solution by this tool. Using the simple command CTAS with the source and target table names and it generates a script that does everything for us!

SQL> ctas t t2

  CREATE TABLE "ZOHAR"."T2"
   (    "DUMMY"
   ) SEGMENT CREATION IMMEDIATE
  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 T
SQL>

DDL

Creating table as select is cool, but what if we just want to extract the table structure to script?
We can use DBMS_METADATA but if you ever used it, you would know that it has the tendency to break some of the lines in the middle thus not creating a valid script.

The DDL command extract the full command – including table columns, default values, comments, indexes, constraints, partitions and storage parameters.

A small anecdote: I have a customer who has tables with thousands of partition and is using out of line LOBS (secure files) columns. When we tried to generate the DDL command to create one of the tables for Oracle support, we crushed the GUI tools. The DBMS_METADATA didn’t produce a valid command and we didn’t have a proper solution to the problem. Using this tool we were able to extract the DDL and got a 1.4 MB create table command script.

SQL> ddl mytab

  CREATE TABLE "ZOHAR"."MYTAB"
   (    "OWNER" VARCHAR2(30),
        "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),
         CONSTRAINT "MYTAB_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  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"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  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"

  CREATE INDEX "ZOHAR"."MYTAB_IDX1" ON "ZOHAR"."MYTAB" ("OWNER", "OBJECT_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  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"

Command Alias

Most of the command line DBAs I know keep a pile of ready-to-go scripts which they run a lot. Examples for such scripts can be query for a list of connected session or blocking locks. Wouldn’t it be nice if we could have given a said script a name (somewhat like using a view) and run it on anytime we wanted without referencing our scripts?

We should note that we don’t use views here – we’re just keeping the query (or multiple queries or even SQLPlus like formats) in an alias configuration and run them on demand:

SQL> alias dual=select * from dual;
SQL> dual
Command=dual

DUMMY
-----
X

We can also alias scripts with parameters we set by the user:

SQL> alias dual2=select :parameter from dual;
SQL> dual2 2
Command=dual2

:PARAMETER
--------------------------------
2

Bridge

Suppose we have a query which needs to retrieve data from multiple database – for example the status of the data guard or golden gate between sites.

In some cases (such as the data guard example) we will not want to create a database between those databases, but we would still like to pull the data from all the databases simultaneously.
The solution we use today is to produce an OS script which connect to each database separately, run the queries on multiple databases and keeps the results. Once it returns all the results we need to analyze it, parse it and get the data we need from the output.

The solution given by SQLcl is using the bridge command. The bridge command allows us to transform our client to a bridge between the two databases. The tool will then connect (in parallel) to several databases and will allow us to pull the results via a single ocmmand. The tool uses jdbc to create the connection and works currently only between Oracle databases only.

Another example bridge use is to transfer data between databases which we can’t or don’t want to have a constant connection between (i.e.: production and development environments). When using the bridge command we can create an “insert table as select” command from the two servers without a DB link.

SQL> create table zohar2.t as select 'zohar2' from dual;

Table ZOHAR2.T created.

SQL> bridge zohar_test as "jdbc:oracle:thin:zohar2/zohar2@localhost:1521:orcldg"(select * from zohar2.t);
Table zohar_test : insert succeeded
SQL> select * From zohar_test;

A
------
zohar2

SQL> select * from dual where dummy = (select a from zohar_test);

no rows selected

Summary

If you got here and you’re still not excited about the tool as much as I am, I guess one of us might be in the wrong line of business… 🙂
What I’ve listed here is just a few examples in a nutshell. I didn’t even got to mention the fact that the tool knows how to produce outputs in different formats (such as CSV, JSON or insert commands) in a single command, that it gives us a real command history (including using the up and down arrows to scroll back commands to old), that it knows how to output color output, that it has the auto-complete by using tab button feature, that it shows error messages with their full description from the Oracle documentation, or that it has XQuery queries capabilities.

So to make a long story short, this is a very cool tool. It appears that its developers had taken into consideration all the rants DBAs had on the SQLPlus for the last 10 years or so and tried to solve it with this tool. In my opinion this tool is pretty stable for its basic uses, but it might take some time to make it perfect. I added it to my favorite tools and so far it saved me a lot of time in my everyday work (but it did crush on me a few times). If you choose to try it out, please remember that some of the things still don’t work properly, or not fully implemented. This is still an early adopters versions and still doesn’t even have a place where we can report its bugs.

Bottom line: I see a bright future for this tool – I suggest you get to know it as it might be one of the more interesting tools Oracle had produced for the everyday database activities in years… 🙂

You can read more about this tool on ThatJeffSmith blog and on Kris’ blog.