Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. This was developed by the sql developer Team. It contains very useful features. We can say it is the advanced version of SQL* utility,

 

 

Installation:

Download the sqlcl tool from oracle website.

Download linkhttp://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

 

Copy that tool to the database server and unzip there.

-rw-r--r--   1 oracle   oinstall 19805375 Aug  7 09:37 sqlcl-17.2.0.184.1230-no-jre.zip
drwxr-xr-x   4 oracle   oinstall       4 Aug  7 09:38 sqlcl

To connect to database with sqlcl tool, go to sqlcl/bin location

cd /export/home/oracle/Utility/sqlcl/sqlcl/bin
 ./sql / as sysdba

SQLcl: Release 17.2.0 Production on Tue Aug 08 16:32:00 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

We can create one alias also:

alias sqlcl='/export/home/oracle/Utility/sqlcl/sqlcl/bin/sql / as sysdba'

# sqlcl

SQLcl: Release 17.2.0 Production on Tue Aug 08 16:33:50 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

If JAVA_HOME is not set properly, then you can hit unsupported minor version error , while connecting with sqlcl.

export JAVA_HOME= /jdk/jdk1.8.0_102         ( for solaris) 

USEFUL FEATURES OF SQLCL:

1. HELP:

This will display all the available commands and their usage:

HELP
HELP < COMMAND >

 

2. SHOW :

This Shows the value of a SQLcl system variable, or the current SQLcl environment.

SHOW
----

Shows the value of a SQLcl system variable, or the current
SQLcl environment. SHOW SGA requires a DBA privileged login.

SHO[W] option

where option represents one of the following terms or clauses:
     system_variable
     ALL
     BTI[TLE]
     CON_ID - Show the ID of the database connected
     CON_NAME - Show the current database connected
     CONNECTION - Show the current connection details
     EDITION  - Show the current enabled edition
     ENCODING - Show the encoding which is set for the client
     ENCODINGS - Show the available encodings for the client
     ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
        | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
     INSTANCE - Show the instance of the database that the client is connected to
     JAVA - Java JRE properties, including, java location, version and platform
     JDBC - Connection details including versions (of driver and database) and URL
     LNO
     NLS - Show NLS parameters set for the current session
     PARAMETERS [parameter_name]
     PDBS
     PNO
     RECYC[LEBIN] [original_name]
     REL[EASE]
     REPF[OOTER]
     REPH[EADER]
     SGA
     SPOO[L]
     SPPARAMETERS [parameter_name]
     SQLCODE
     TNS - Location of tnsnames.ora and list of aliases
     TTI[TLE]
     USER
     VERSION - Show the version of SQLcl

Examples:

3. HISTORY:

Setting this parameter to ON, will report all the sql commands in history, same as that of history command works at os level.

SET history ON — ( to enable history option)
SET history OFF — ( to disable history option)
SET history CLEAR — ( To clear the existing history )

4. REPEAT :

REPEAT command can be used to execute particular SQL query in the buffer for n number of times at particular interval.

SQL> REPEAT HELP
Usage: REPEAT   
         SQL is the sql from the current buffer
         Maximum sleep is 120s
         Maximum repeats are 2,147,483,647

example:

 

5.DDL:

DDL command is used to get the DDL of an object in databases, no need to use dbms.metadata.get_ddl utility.

DDL < OBJECT_NAME> < OBJECT_TYPE>

 

If you wish to save this DDL to a file, then SAVE keyword can be used.

 DDL <OBJECT_NAME> SAVE < FILE_NAME>

SQL>DDL TEST100 SAVE TEST.LOG
cat TEST.LOG

  CREATE TABLE "SYS"."TEST100"
   (    "USERNAME" VARCHAR2(128) NOT NULL ENABLE,
        "USER_ID" NUMBER NOT NULL ENABLE,
        "PASSWORD" VARCHAR2(4000),
        "ACCOUNT_STATUS" VARCHAR2(32) NOT NULL ENABLE,
        "LOCK_DATE" DATE,
        "EXPIRY_DATE" DATE,
        "DEFAULT_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
        "TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
        "LOCAL_TEMP_TABLESPACE" VARCHAR2(30),
        "CREATED" DATE NOT NULL ENABLE,
        "PROFILE" VARCHAR2(128) NOT NULL ENABLE,
        "INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(128),
        "EXTERNAL_NAME" VARCHAR2(4000),
        "PASSWORD_VERSIONS" VARCHAR2(17),
        "EDITIONS_ENABLED" VARCHAR2(1),
        "AUTHENTICATION_TYPE" VARCHAR2(8),
        "PROXY_ONLY_CONNECT" VARCHAR2(1),
        "COMMON" VARCHAR2(3),
        "LAST_LOGIN" TIMESTAMP (9) WITH TIME ZONE,
        "ORACLE_MAINTAINED" VARCHAR2(1),
        "INHERITED" VARCHAR2(3),
        "DEFAULT_COLLATION" VARCHAR2(100),
        "IMPLICIT" VARCHAR2(3),
        "ALL_SHARD" VARCHAR2(3)
   ) 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 "SYSTEM" ;
  
  

6.PASSWORD:

In the database, generally, we change the password of a user with alter user username identified password. The problem with this one is the password is displayed on the screen.

SQL> create user raj identified by oracle;

User RAJ created.

With PASSWORD command of sqlcl, we can alter password without displaying it. it will be marked as **.

PASSWORD
--------

Allows you to change a password without displaying it on an input device.

PASSW[ORD] [username]

 

7.INFORMATION:

INFORMATION command when used with an object_name, give details about the table structure and index, constraint information also.

INFORMATION <SCHEMA_NAME>.<OBJECT_NAME>

 

 

8. BRIDGE:

BRIDGE command works like a database link. i.e we can move data between databases without creating the database link. And also it supports LONG columns.

Creating a table NEW_TAB in local database from remote database (172.30.224.176:1540/D2RRESO)

SQL>  BRIDGE NEW_TAB as "jdbc:oracle:thin:SYSTEM/ORACLE@172.30.224.176:1540/D2RRESO"(select * from siebel.s_user);

Created table NEW_TAB and inserted 1,798 rows

For insert also:

SQL> BRIDGE INSERT INTO TEST_TAB2 as "jdbc:oracle:thin:SYSTEM/ORACLE@172.30.224.176:1540/D2RRESO"(select * from siebel.s_user);

Created table INSERT INTO NEW_TAB and inserted 1,798 rows

 

9. ERRORLOGGING:

If errorlogging is set ON, incase we are hitting any error, It will display the explanation of that error with cause and action like oerr tool.

10. CTAS:

If we want to create a table from another table from using CTAS( with all columns or few columns,), then CTAS will come in handy.

CTAS <EXISTING_TABLE>  <NEW_TABLE> , will generate the CTAS DDL.

And when we type EDIT, the DDL will open in an editor, where we can make changes to the table structure and save it. It can be executed to create the new table.

 

The more we explore this SQLCL utility, more we get its usefulness. So connect to sqlcl prompt and type HELP and try new commands.

I will update this article after going through other commands.

 

JeffSmith really worked a lot on this tool, He publishes regularly about the updates on this sqlcl utility.  http://www.thatjeffsmith.com/