Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

top   prev   next  

Oracle SQLcl


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



top   prev   next  

Oracle SQLcl References


video 1  

video 2  

official download site  



top   prev   next  

Oracle SQL Developer

sql developer:
eg1:
set linesize 380
select /*CSV*/ * from employee;

select * from employees fetch first 150 rows only;



top   prev   next  

Oracle SQLPLUS

SET MARKUP CSV: the output is using the codepage of the CMD window. 
to change codepage, open CMD, and run CHCP 1252


top   prev   next  

Old SQLPLUS Solution

-- 1): for CSV
set head on
set colsep ,
sqlplus -s
set linesize 32767
set trimspool on
set trimout on


top   prev   next  

rlwrap

# sudo apt-get install rlwrap
# rlwrap sqlplus uid@db
more .sqlplus_history

rlwrap -if keywords.txt sqlplus uid@db
The -f switch points rlwrap to our file.
The -i switch tells rlwrap to ignore case when tab-completing.


top   prev   next  

home