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
Last Update: June 22, 2020