Oracle SQL Developer Command Line (SQLcl)
Version 17.2

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose SQL Command Line is a command line tool intended not to replace SQL*Plus but to supplement it. There are things you can do with SQLcl that are incredibly easier to accomplish than the same task in any other command line Oracle product.
 
Installation
Create a directory under ORACLE_BASE named sqlcl

Download the installation .zip file from
http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html

Unzip the file

In Windows start it using the sqlcl.bat file

You may be required to also download Java if Java is not already installed in your operating environment.

 
Start sqlcl
Change directory to $ORACLE_BASE\sqlcl\bin

Execute the sql.bat file
 
Data Login
Username should be entered as <username>@<service_name>

Enter the password

Hit the [Enter] key
 
@
  @{url | file_name[.ext]} [arg ...]
 
 
@@
  @@ { url | file_name[.ext] } [arg ...]
 
 
/ (slash)
  / (slash)
 
 
ACCEPT
  ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
 
 
ALIAS
  ALIAS [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [<NAME>] | DROP <name> | DESC <name> <Description String>]
 
 
APEX
  APEX [export <application_id>]
TBD
 
APPEND
  A[PPEND] text
 
 
ARCHIVE LOG LIST
List archived redo logs ARCHIVE LOG LIST
 
 
BREAK
  BRE[AK] [ON report_element [action [action]]] ...
 
 
BRIDGE
Moves data between two connections/schemas. Also includes functionality to dynamically create Oracle tables which "fit" the data being received through JDBC. The following functionality is available:
 - Query tables in other connections
 - Query tables in multiple connections in the same statement
 - Insert data from one connection into another
 - Create a table and insert data into it from another connection		 BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>);
TBD
 
BTITLE
  BTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
 
 
CD
Change Directory CD [<directory>]
SQL> cd /u01/archive/scripts
 
CHANGE
  C[HANGE] sepchar old [sepchar [new [sepchar]]]
 
 
CLEAR
Clears the screen (identical to SQL*Plus) CL[EAR] option ...
SQL> cl scr
 
COLUMN
Format a column display (identical to SQL*Plus) COL[UMN] [{column | expr} [option ...]]
SQL> select table_name from user_tables where rownum = 1;

TABLE_NAME
-----------------------------------------------------------------------------------------------------
TARGET

SQL> col table_name format a30
SQL> select table_name from user_tables where rownum = 1;

TABLE_NAME
------------------------------
TARGET
 
Header
  COMP[UTE] [function [LAB[EL] text] ... OF {expr | column | alias} ...ON {expr | column | alias | REPORT | ROW} ...]
 
 
Header
  CONN[ECT] [{<logon>| / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
SQL> conn uwclass/uwclass@pdbdevConnected.
 
CTAS
Generates the DDL for a full syntax Create Table As statement CTAS [source_table_name] [new_table_name];
SQL> ctas servers servers_copy;

CREATE TABLE "UWCLASS"."SERVERS_COPY"
( "SRVR_ID",
  "NETWORK_ID",
  "STATUS",
  "LATITUDE",
  "LONGITUDE",
  "NETADDRESS",
CONSTRAINT "PK_SERVERS" PRIMARY KEY ("SRVR_ID")
USING INDEX PCTFREE 0 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UWDATA" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UWDATA"
as
select * from SERVERS
 
COPY
  COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE | APPEND_BYTE | CREATE_BYTE | REPLACE_BYTE} destination_table[(column, column, column, ...)] USING query
 
 
DDL
Generates the code to reconstruct the object listed. Use the type option for materialized views. Use the SAVE option to save the DDL to a file. DDL [object_name [type] [SAVE filename]]
SQL> ddl pk_servers

CREATE UNIQUE INDEX "UWCLASS"."PK_SERVERS" ON "UWCLASS"."SERVERS" ("SRVR_ID")
PCTFREE 0 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UWDATA" ;
 
DEFINE
  DEF[INE] [variable] | [variable = text]
 
 
DEL
  DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
 
 
DESCRIBE
Describe an object (essentially identical to SQL*Plus) DESC[RIBE] {[schema.]object[@connect_identifier]}
SQL> desc servers
Name       Null?    Type
---------- -------- ------------
SRVR_ID    NOT NULL NUMBER(10)
NETWORK_ID          NUMBER(10)
STATUS              VARCHAR2(1)
LATITUDE            FLOAT(20)
LONGITUDE           FLOAT(20)
NETADDRESS          VARCHAR2(15)
 
DISCONNECT
  DISC[ONNECT]
 
 
EDIT
Will either load a file into the local editor (vi or notepad) or retrieve for editing the command line buffer just as in SQL*Plus ED[IT] [file_name[.ext]]
SQL> ed
 
EXECUTE
  EXEC[UTE] statement
 
 
EXIT
Exit the SQLcl program EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
SQL> exit;
 
FIND
Searches the SQLPATH and its directories for the specified file name FIND [<filename>]
TBD
 
FORMAT
  FORMAT [BUFFER | RULES <filename> | FILE <input_file> <output_file>]
 
 
GET
  GET [FILE] file_name[.ext] [LIST | NOLIST]
 
 
HELP
SQLcl command and syntax help HELP | ? [topic]
SQL> help history
HISTORY
---------
history [<index> | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?]

SQL>history full
  1  select 1 from dual;
  2  select 2
  >  from dual;
  3  select 3 from dual
  >  where 1=1;

SQL>history usage
  1  (2) select 1 from dual;
  2  (11) select 2 from dual;
  3  (2) select 3 from dual where 1=1;

SQL>history script
 select 1 from dual;
 select 2 from dual;
 select 3 from dual where 1=1;

SQL>history 3
  1  select 3 from dual
  2* where 1=1;

SQL>his time
  1           clear
  2           cl bre
  3  (00.201) select 1 from dual
 
HISTORY
Manages and displays the SQLcl history HISTORY [index | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?]
SQL> history clear
History Cleared
SQL> history
History empty
SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2560

SQL> select * from dual;

D
-
X

SQL> history full
1 select count(*) from dba_tables;

2 select * from dual;
 
HOST
Shells out to the O/S command line identical to SQL*Plus HO[ST] [command]
SQL> host
Microsoft Windows [Version 10.0.15063]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\temp>exit
 
INFORMATION
  INFO[RMATION] {[schema.]object[@connect_identifier]}
SQL> information servers
TABLE: SERVERS
LAST ANALYZED:2017-02-20 16:22:45.0
ROWS :141
SAMPLE SIZE :141
INMEMORY :DISABLED
COMMENTS :

Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*SRVR_ID NUMBER(10,0) No
NETWORK_ID NUMBER(10,0) Yes
STATUS VARCHAR2(1 BYTE) Yes
LATITUDE FLOAT Yes
LONGITUDE FLOAT Yes
NETADDRESS VARCHAR2(15 BYTE) Yes

Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
UWCLASS.PK_SERVERS UNIQUE VALID SRVR_ID
 
INPUT
  I[NPUT] [text]
 
 
LIST
  L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
 
 
LOAD
  LOAD [schema.]table_name[@db_link] file_name
 
 
OERR
  OERR <facility> <error>
 
 
PASSWORD
  PASSW[ORD] [username]
 
 
PAUSE
  PAU[SE] [text]
 
 
Header
  PRINT [variable ...]
 
 
Header
  PRO[MPT] [text]
 
 
QUIT
Quit the SQLcl program QUIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
SQL> quit;
 
REMARK
  REM[ARK]
 
 
REPEAT
  REPEAT <iterations> <sleep>
 
 
REPFOOTER
  REPFOOTER
 
 
REPHEADER
  REPHEADER
 
 
REST
  REST [export [<module_name> | <module_prefix>] | modules | privileges | schemas]
 
 
RUN
  R[UN]
 
 
SAVE
  SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
 
 
SCRIPT
  SCRIPT <script file>
 
 
SET
Define interface display options SET <option> <value>
SQL> set linesize 161
SQL> set pagesize 45
 
SET SQLFORMAT
Defines query output in a specified format SET SQLFORMAT {csv | html | xml | json | ansiconsole | insert | loader | fixed | default}
SQL> set sqlformat csv
SQL> SELECT *
  2  FROM servers
  3  WHERE rownum < 3;
"SRVR_ID","NETWORK_ID","STATUS","LATITUDE","LONGITUDE","NETADDRESS"
1,1028,"Y",32.9806,-117.2567,"172.020.130.002"
2,1028,"Y",32.6956,-117.1261,"172.020.130.018"

SQL> set sqlformat loader
SQL> SELECT *
  2  FROM servers
  3  WHERE rownum < 3;
1|1028|"Y"|32.9806|-117.2567|"172.020.130.002"|
2|1028|"Y"|32.6956|-117.1261|"172.020.130.018"|
Defines query output with the specified delimiter and enclosure SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure>
SQL> set sqlformat delimited |
SQL> select *
  2  from servers
  3  where rownum < 3;
"SRVR_ID"|"NETWORK_ID"|"STATUS"|"LATITUDE"|"LONGITUDE"|"NETADDRESS"
1|1028|"Y"|32.9806|-117.2567|"172.020.130.002"
2|1028|"Y"|32.6956|-117.1261|"172.020.130.018"
 
SHOW
Displays the client encoding SHO[W] [option]
SQL> show encoding
Encoding:windows-1252
 
SHUTDOWN
Shuts-down a database
We recommend using SQL*Plus, Server Control, or OEM for this		 SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
SQL> shutdown immediate;
 
SODA
  SODA
 
 
SPOOL
  SPO[OL] [filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
SQL> spool c:\temp\listing.txt
SQL> /

PRO LINE_NUMBER CUST ORDER_DAT DELIVERED
--- ----------- ---- --------- ---------
737        1948 SAL  16-DEC-21 26-JUL-27
           1949 ILC  17-DEC-21 28-JUL-27
           1950 SWA  18-DEC-21 30-JUL-27
           1951 NWO  19-DEC-21 01-AUG-27
           1952 USAF 20-DEC-21 03-AUG-27
           1953 AAL  21-DEC-21 05-AUG-27
           1954 DAL  22-DEC-21 07-AUG-27
           1955 SAL  23-DEC-21 09-AUG-27
           1956 ILC  24-DEC-21 11-AUG-27
           1957 SWA  25-DEC-21 13-AUG-27
           1958 NWO  26-DEC-21 15-AUG-27
           1959 USAF 27-DEC-21 17-AUG-27
           1960 AAL  28-DEC-21 19-AUG-27
           1961 DAL  29-DEC-21 21-AUG-27
           1962 SAL  30-DEC-21 23-AUG-27
           1963 ILC  31-DEC-21 25-AUG-27
           1964 SWA  01-JAN-22 27-AUG-27
           1965 NWO  02-JAN-22 29-AUG-27
           1966 USAF 03-JAN-22 31-AUG-27
           1967 AAL  04-JAN-22 02-SEP-27

20 rows selected.

SQL> spool off
 
SSHTUNNEL
  SSHTUNNEL <username>@<hostname> -i <identity_file> [-L localPort:Remotehost:RemotePort]
TBD
 
START
  STA[RT] { url | file_name[.ext] } [arg ...]
 
 
STARTUP
Starts a database instance or PDB
We recommend using the proper tool for this: SQL*Plus, Server Control, or OEM		 STARTUP db_options | cdb_options | upgrade_options
SQL> startup;
 
STORE
Allows schemaless application development using the JSON data model STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
TBD
 
TNSPING
Runs the TNSPING for the specified TNSNAMES alias TNSPING <address>
SQL> tnsping pdbdev
ping:-2ms
 
TITLE
  TTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
 
 
UNDEFINE
  UNDEF[INE] variable ...
 
 
VERSION
Displays the SQLcl version VERSION
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 17.2.0.184.0917
 
WHENEVER OSERROR
  WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE[COMMIT | ROLLBACK | NONE]}
 
 
WHENEVER SQLERROR
  WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
 
 
WHICH
  WHICH <filename>
TBD
 
XQUERY
  XQUERY xquery_statement
 

Related Topics
Built-in Functions
Built-in Packages
SQL*Plus
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx