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
Please follow and like us:
Last Update: June 22, 2020
Total 0 Votes:
0
0
