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 link – http://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/
Hi,
I am using SQL CL utility to run a script from file. i am launching sqlcl from power script and i wan to pass the yesterday date to script.
$Env:runDate = [DateTime]::Today.AddDays(-1).ToString(“dd-MMM-yyyy”), i can see its in Env variable of powrershell by doing “gci env:”.
when i launched sql.exe username/password@server:port/servicename, in sql command prompt i only see PATH variable is available from above powershell’s “gci env:” list, other environment variable is lost. could you please tell me a way if i set a variable in powershell or cmd, if i can access that in sql command prompt, which we get after running “sql.exe username/password@server:port/servicename”.
Thank you very much for great article on SQLcl.
– Satya