SQLcl Scripting
SQLcl Programming
show sqlformat
set sqlformat csv
set sqlformat ---> clear sqlformat
select * from employees fetch first 150 rows only;
show version
show jdbc
information hr.employees
help cd
help desc
help set sqlformat
help set ddl
HISTORY
HISTORY USAGE: View the history usage
HISTORY TIME: View the time spent executing each statement
HISTORY CLEAR: Clear the history
color 47
host "color 47"
host "COLOR XY"
-- ----------------------------------------------------
1): inline editor
Tab: File/SQL Completion
Up or Down: Recall Previous: Up or Down
Ctrl+R: Run buffer immediately
Ctrl+W: Go to the top of the buffer
Ctrl+S: Go to the bottom of the buffer
Ctrl+A: Go to the start of a line
Ctrl+E: Go to the end of a line
Ctrl+L: Clear Screen Ctrl + L
2): setting
autoprint
autorecovery
cmdsep
copytypecheck
describe
eschar
flagger
flush
fullcolname
logsource
loboffset
markup
recsep
recsepchar
shiftinout
sqlcase
sqlprefix
sqlterminator
tab
underline
xmloptimizationcheck
3): new commands
CTAS, DDL, Repeat, ALIAS, SCRIPT, FORMAT
HELP
ALIAS
APEX
BRIDGE
CD
CTAS
DDL
FORMAT
HISTORY
INFORMATION
LOAD
NET
REPEAT
SSHTUNNEL
TNSPING
eg:
SCRIPT xxx.js
eg:
alias current_time=select to_char(sysdate, 'HH24:MI') from dual;
eg:
alias get_emps=SELECT emp.empno, emp.ename, emp.sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.dname = :dept
ORDER BY 1;
eg usage: SQL> get_emps ACCOUNTING;
-- ---------------------------------------------------------------------------------------------
-- bridge: similar to sqlplus copy command
bridge hr_tabs as "jdbc:oracle:thin:hr/the_hr_password@0.0.0.0:1521/orcl"(select table_name from user_tables);
bridge insert into hr_tabs as "jdbc:oracle:thin:hr/the_hr_password@0.0.0.0:1521/orcl"(select table_name from user_tables);
-- ---------------------------------------------------------------------------------------------
cd my_os_directoy_name
-- exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false)
-- SET DDL STORAGE OFF
SQL> ctas emp emp_copy;
-- --------------------------------------------------------------------------------------------
clear screen
-- --------------------------------------------------------------------------------------------
ddl mytable
DDL MYOBJECT_NAME MY_OBJECT_TYPE
eg: DDL employee TABLE
DDL <OBJECT_NAME> SAVE <FILE_NAME>
-- exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false)
-- SET DDL STORAGE OFF
-- ----------------------------------------------------------------------------------------------
-- format: make sql look prettier
SQL> format
format file hr.sql hr_yuk.sql
SQL> format buffer
help
-- history: cycle through your 100 previous scripts/commands
history help
history [ | FULL | USAGE | TIME | HELP | CLEAR]
history full
history clear
info mytable
info mypackage.myfunction;
info+ mytable
load tablename filename.csv
load icc_wc_winners cricket/wc_winners.csv
-- login.sql: put all settings and alias in this file
repeat n m -- repeat to execute the previous sql command n times every m seconds
eg: repeat 3 10 -- repeat to execute the previous sql command 3 times every 10 seconds
-- ---------------------------------------------------------------------------------------
set ERRORLOGGING on
-- 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.
-- ---------------------------------------------------------------------------------------
set sqlformat ansiconsole
SET SQLFORMAT ANSICONSOLE
-- --------------------------------------------------------------------------------------
set sqlformat --> clear sqlformat
set sqlformat csv
set sqlformat xml
set sqlformat jason
set sqlformat default
set sqlformat ansiconsole
set sqlformat insert --> lists resuts as an insert statement
set sqlformat loader --> pipe-delimited
set sqlformat delimited --> same as csv
set sqlformat xml
set sqlformat html
set sqlformat fixed --> fixed width
set sqlformat text
set sqlformat json
set head off
-- ------------------------------------------------------------------------------------
set sqlprompt "@|blue _USER|@@@|green_CONNECT_IDENTIFIER|@@|blue >|@"
-- ------------------------------------------------------------------------------------
set sqlformat ansiconsole
-- '@|bg_red' || .. ||'@|'
-- '@|bg_green' || .. ||'@|'
-- '@|bg_yellow' || .. ||'@|'
-- -----------------------------
select '@|red,bold,underline This is red,bold,underline|@' "@|red Colors|@" from dual
union all
select '@|NEGATIVE_ON This is negative|@' from dual
union all
select '@|INTENSITY_FAINT This is faint|@' from dual
union all
select '@|INTENSITY_BOLD This is my bold|@' from dual
union all
select '@|ITALIC This is italic|@' from dual
union all
select '@|UNDERLINE This is underline|@' from dual
union all
select '@|BLINK_SLOW This is blink_slow|@' from dual
union all
select '@|BLINK_FAST This is blink_fast|@' from dual
union all
select '@|CONCEAL_ON This is conceal|@' from dual
union all
select '@|black This is black|@' from dual
union all
select '@|green This is green|@' from dual
union all
select '@|yellow This is yellow|@' from dual
union all
select '@|blue This is blue|@' from dual
union all
select '@|magenta This is magenta|@' from dual
union all
select '@|cyan This is cyan|@' from dual
union all
select '@|white This is white|@' from dual
union all
select '@|bg_red This is bg_red|@' from dual
union all
select '@|bg_black This is bg_black|@' from dual
union all
select '@|bg_green This is bg_green|@' from dual
union all
select '@|bg_yellow This is bg_yellow|@' from dual
union all
select '@|bg_blue This is bg_blue|@' from dual
union all
select '@|bg_magenta This is bg_magenta|@' from dual
union all
select '@|bg_cyan This is bg_cyan|@' from dual
union all
select '@|bg_white This is bg_white|@' from dual
union all
select '@|bg_red This is bg_red|@' from dual;
-- --------------------------------------------------------------------------------------
help show
show pdbs
show tns
|