Oracle SQLcl , The Future of SQL*Plus !

06 Mar Oracle SQLcl , The Future of SQL*Plus !

In December of 2014 the team of Oracle SQLDeveloper released a beta version of an Oracle tool called SQLcl, which in a nutshell,  is the engine of SQL * Plus that belongs to SQLDeveloper, and the truth since I saw it, I fell in love with this little tool.

This tool can be downloaded from OTN, in the section of  “Early Adopters” of SQLDeveloper, which is a tool that is only 11Mb, which already has embedded a JRE, so this is ready to go once you unzip the downloaded file. According the SQLDeveloper team, is a tool that will be distributed with SQLDeveloper v4.1 and Oracle Database 12cR2.

Once you download the tool, you can connect with EZConnect as well as OCI or Thick clients. In this case, I’m going to connect to the HR schema on the pluggable database PDB1 data on port 1521.

Rene@iMac-de-Antunez [] /Users/Rene/Documents/Oracle/SQL Developer/sqlcl/bin
Rene $ pwd
/Users/Rene/Documents/Oracle/SQL Developer/sqlcl/bin
Rene@iMac-de-Antunez [] /Users/Rene/Documents/Oracle/SQL Developer/sqlcl/bin
Rene $ ls
sql sql.bat
Rene@iMac-de-Antunez [] /Users/Rene/Documents/Oracle/SQL Developer/sqlcl/bin
Rene $ sh ./sql hr/[email protected]:1521/PDB1
SQLcl: Release 4.1.0 Beta on Thu Mar 05 13:41:05 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL>

As you can see, many of the commands in SQL * Plus function as before, but this time it gives you a message that was not seen before : “Try the new information command: info employees“.

SQL> desc employees
Try the new information command: info employees
Name Null Type
-------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

Let’s try this command, and this is where you will fall in love with this tool , as before to get all this information, there were several querys, that if didn’t have saved off to the side, it was going to take several minutes to get all this information.

Note .- Sorry to post images, but my blog does not allow me to give the format that allows you to appreciate the great benefit of this tool

SQL> info employees

And if after that, you are not ready to install this tool, there is also an extended version of the command info

SQL> info+ employees;

And it does not stop here, before obtaining the DDL for an object you had to use the procedure dbms_metadata.get_ddl, now all you have to do is run the command DDL  object_name

SQL> DDL employees

CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
...
CREATE OR REPLACE EDITIONABLE TRIGGER "HR"."UPDATE_JOB_HISTORY"
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE

As you can see, although it is a small 11Mb tool, it is a very powerful weapon that comes to evolve SQL*Plus , and although there are certain things that are only possible through SQL*Plus, I can bet that from now you’re will be starting to use much SQLcl  than SQL*Plus.