News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

SQLcl – SQL Command Line Interface Top Features

  • Posted by Gavin Soorma
  • On June 26, 2019
  • 0 Comments
  • 18c new feature, 19c new features, sql*plus, sqlcl

SQLcl or also known as SQL Command Interface was earlier available as a stand alone utility which we could download – it is now bundled in the Oracle 18c and Oracle 19c software (as well as 12c Release 2).

Think of SQLcl as a feature-rich combination of SQL*Plus and SQL Developer – all the helpful elements and cool utilities of the GUI available in a command line interface.

In-line editing, automatic SQL output formating, reuse commands and custom scripts with the ALIAS and REPEAT command, INFORMATION and INFO+ – these are just a few of the cool SQLcl features which will make you stop using SQL*Plus!

Let’s have a look at some SQLcl features (the HELP command provides us a lot of information about a command with examples of how to use them).
 
Note: we can launch SQLcl via the sql executable located in the $ORACLE_HOME/sqldeveloper directory.
 

[oracle@host02 admin]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/sqldeveloper/sqldeveloper/bin

[oracle@host02 bin]$ ./sql hr/hr@localhost:1521/pdb1.localdomain

SQLcl: Release 19.1 Production on Tue Jun 25 15:13:24 2019

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

 
SHOW TNS
 

SQL> show tns 
TNS Lookup locations
--------------------
1.  USER Home dir
    /home/oracle
2.  ORACLE_HOME
    /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

Location used:
-------------
	/u01/app/oracle/product/19.3.0/dbhome_1/network/admin

Available TNS Entries
---------------------
CDB1
LISTENER_CDB1
LISTENER_SH1
PDB1
SH1

 
INFO
 

SQL> info employees
TABLE: EMPLOYEES 
	 LAST ANALYZED:2019-06-25 14:30:58.0 
	 ROWS         :107 
	 SAMPLE SIZE  :107 
	 INMEMORY     :DISABLED 
	 COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference. 

Columns 
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No               Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes              First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No               Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No               Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes              Phone number of the employee; includes country
                                                      code and area code
 HIRE_DATE       DATE                No               Date when the employee started on this job. A not
                                                      null column.
 JOB_ID          VARCHAR2(10 BYTE)   No               Current job of the employee; foreign key to job_id
                                                      column of thejobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes              Monthly salary of the employee. Must be
                                                      greaterthan zero (enforced by constraint
                                                      emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes              Commission percentage of the employee; Only
                                                      employees in salesdepartment elgible for
                                                      commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes              Manager id of the employee; has same domain as
                                                      manager_id indepartments table. Foreign key to
                                                      employee_id column of employees table.(useful for
                                                      reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes              Department id where employee works; foreign key to
                                                      department_idcolumn of the departments table

Indexes
INDEX_NAME             UNIQUENESS   STATUS   FUNCIDX_STATUS   COLUMNS                 
HR.EMP_JOB_IX          NONUNIQUE    VALID                     JOB_ID                  
HR.EMP_NAME_IX         NONUNIQUE    VALID                     LAST_NAME, FIRST_NAME   
HR.EMP_EMAIL_UK        UNIQUE       VALID                     EMAIL                   
HR.EMP_EMP_ID_PK       UNIQUE       VALID                     EMPLOYEE_ID             
HR.EMP_MANAGER_IX      NONUNIQUE    VALID                     MANAGER_ID              
HR.EMP_DEPARTMENT_IX   NONUNIQUE    VALID                     DEPARTMENT_ID           


References
TABLE_NAME    CONSTRAINT_NAME   DELETE_RULE   STATUS    DEFERRABLE       VALIDATED   GENERATED   
DEPARTMENTS   DEPT_MGR_FK       NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   
EMPLOYEES     EMP_MANAGER_FK    NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   
JOB_HISTORY   JHIST_EMP_FK      NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   

 

INFO+ (note additional details like Histograms etc)
 

SQL> info+ hr.employees
TABLE: EMPLOYEES 
	 LAST ANALYZED:2019-06-25 14:30:58.0 
	 ROWS         :107 
	 SAMPLE SIZE  :107 
	 INMEMORY     :DISABLED 
	 COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference. 

Columns 
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*EMPLOYEE_ID     NUMBER(6,0)         No                   100                   206                   107            NONE       
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  Adam                  Winston               91             FREQUENCY  
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Abel                  Zlotkey               102            NONE       
 EMAIL           VARCHAR2(25 BYTE)   No                   ABANDA                WTAYLOR               107            NONE       
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  011.44.1343.329268    650.509.4876          107            NONE       
 HIRE_DATE       DATE                No                   2001.01.13.00.00.00   2008.04.21.00.00.00   98             NONE       
 JOB_ID          VARCHAR2(10 BYTE)   No                   AC_ACCOUNT            ST_MAN                19             FREQUENCY  
 SALARY          NUMBER(8,2)         Yes                  2100                  24000                 58             NONE       
 COMMISSION_PCT  NUMBER(2,2)         Yes                  .1                    .4                    7              NONE       
 MANAGER_ID      NUMBER(6,0)         Yes                  100                   205                   18             FREQUENCY  
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  10                    110                   11             FREQUENCY  

Indexes
INDEX_NAME             UNIQUENESS   STATUS   FUNCIDX_STATUS   COLUMNS                 
HR.EMP_JOB_IX          NONUNIQUE    VALID                     JOB_ID                  
HR.EMP_NAME_IX         NONUNIQUE    VALID                     LAST_NAME, FIRST_NAME   
HR.EMP_EMAIL_UK        UNIQUE       VALID                     EMAIL                   
HR.EMP_EMP_ID_PK       UNIQUE       VALID                     EMPLOYEE_ID             
HR.EMP_MANAGER_IX      NONUNIQUE    VALID                     MANAGER_ID              
HR.EMP_DEPARTMENT_IX   NONUNIQUE    VALID                     DEPARTMENT_ID           


References
TABLE_NAME    CONSTRAINT_NAME   DELETE_RULE   STATUS    DEFERRABLE       VALIDATED   GENERATED   
DEPARTMENTS   DEPT_MGR_FK       NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   
EMPLOYEES     EMP_MANAGER_FK    NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   
JOB_HISTORY   JHIST_EMP_FK      NO ACTION     ENABLED   NOT DEFERRABLE   VALIDATED   USER NAME   

 
DDL
 

SQL> ddl regions

  CREATE TABLE "HR"."REGIONS" 
   (	"REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE, 
	"REGION_NAME" VARCHAR2(25)
   ) 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 "SYSAUX" ;
  CREATE UNIQUE INDEX "HR"."REG_ID_PK" ON "HR"."REGIONS" ("REGION_ID") 
  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 "SYSAUX" ;
ALTER TABLE "HR"."REGIONS" ADD CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX "HR"."REG_ID_PK"  ENABLE;

 
CD
 

SQL> cd /home/oracle

SQL> host ls -l
total 3420
-rwxr-x--- 1 oracle oinstall 3490686 Jun  9 16:34 autoupgrade.jar
drwxr-xr-x 2 oracle oinstall       6 Jun  1 22:48 Desktop
drwxr-xr-x 2 oracle oinstall       6 Jun  1 22:48 Documents
drwxr-xr-x 2 oracle oinstall       6 Jun  1 22:48 Downloads

ALIAS and REPEAT

SQL> alias active_users=select sid,serial#,username from v$session where status='ACTIVE' and username is not null;

SQL> active_users

       SID    SERIAL#	USERNAME                                                                        
-----------  --------  ------------
        13      59956   SYS
       111      27507   HR
                                                                             
SQL> repeat 3 5

Running 1 of 3  @ 7:10:16.793 with a delay of 5s

       SID    SERIAL#	USERNAME                                                                        
-----------  --------  ------------
        13      59956   SYS
       111      27507   HR
                  

Running 2 of 3  @ 7:10:21.837 with a delay of 5s

      SID    SERIAL#	USERNAME                                                                        
-----------  --------  ------------
        13      59956   SYS
       111      27507   HR                                   


Running 3 of 3  @ 7:10:26.865 with a delay of 5s

      SID    SERIAL#	USERNAME                                                                        
-----------  --------  ------------
        13      59956   SYS
       111      27507   HR

 
SET SQLFORMAT ANSICONSOLE
 

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *