SQLCL cheatsheet

Photo by Francesco Ungaro on Unsplash

https://docs.oracle.com/en/database/oracle/sql-developer-command-line/19.2/sqcug/oracle-sqlcl-users-guide.pdf

Basic commands

cd C:\Users\...\sqlcl\bin
sql -- start, you will be prompt for user@DB and pwd
exit

clear screen
-- see the existing commands
help
help show
show tns

-- generate ddl for a table
ddl TABLENAME
help set ddl -- show you the option

-- info is describe with additonal information
info XXX.TBL_NAME
info+ XXXX.TBL_NAME -- give you stats

-- see path and sql files in current folder
show sqlpath
! dir *.sql

Tips

• ctrl + R to run a query
• “/” to run the query in the buffer
• you can do stuff like: “ ! dir *.sql “
• ctrl + c to stop an ongoing query result (if you forgot to put “and rownum < 10”)
• Don’t copy paste from a file to the CL if you have tabs indent. You should convert to spaces first. Generally, don’t paste big queries
• You can start by select from XXX and then you have auto completion for the column names

History

-- give you last 100 queries - can be changed
history
-- put the query number 62 in the buffer
history 62
-- to check how to change the history config
help set history

Alias

-- alias for query or scripts
alias -- show existing aliases
alias test=select * from XXXX;
test
alias list test -- to see the query

alias cls=clear screen;

-- you can bind a variable to your alias. here 8 is the variable we select
alias tomorrow=select sysdate + :days from dual;
tomorrow 8

Save query result & save the buffer script in a file

-- save query result
SET SQLFORMAT csv
SPOOL 'C:/Users/.../sqlcl/bin/test.csv';
SELECT * FROM XXXX; -- type your query here
SPOOL off;
SET SQLFORMAT ansiconsole

-- save buffer in a file
save test.sql -- [create, replace, append]

Execute a query from a file

pwd
cd  /xxxxx/xxxxx/
@test.sql; -- not to forget the semi colon
-- @@ runs a statement in a specified script

Modifying a query with Edit, List, Change…

-- invoke the OS text editor to modify the query
Edit -- edit the query in the buffer
Edit history 70
Edit test.sql -- modify scripts

list -- show the previous script
list 3 -- show the line 3
list 1 last -- line 1 to last

change /10/15 -- replace 10 by 15 in the buffer
del 3 -- delete the line number 3 of the buffer
input where rownum < 5 -- add a final line to the buffer
append aaa -- append the last line with the specified text

Visual

set sqlformat ansiconsole
show sqlformat -- to see what exist
set sqlformat -- back to default
set sqlformat csv
SELECT /*csv*/ * FROM emp; -- give the same result
-- no column headers
set head off

-- number of printed lines that will fit on one page of output
set pagesize 50
-- turn off feddback
SET FEEDBACK OFF

Status Bar

bar at the bottom of screen. Add timing to see how long it takes for your queries to be executed

show statusbar
help set statusbar
set statusbar on
-- you can add or remove components
set statusbar add timing

Highlighting

-- add color to SQL
set highlighting
help set highlighting
set highlighting on
set highlighting keyword foreground green
set highlighting identifier foreground magenta
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment background white

Formatting

-- start by exporting the formatting preferences from sqldev then import the file
format rules format_xxx.xml -- import xml format rules
format buffer -- to apply the format to the buffer
format file input_file output_file

Vim or Emacs Editor

There is vi and emacs. You must define it with SET EDITOR. Then, you press escape to switch vi mode

Example:

• set status bar on to see if you are in vicmd or vi insert
• you write your query
• push escape to switch to vim cmd
• go to a line and press dd to remove a line
• press I to get back to vim insert

show keymap -- see all the shortcut and command
show editor
set editor emacs