Find us on Google+ Google+

August 17, 2019

Oracle SQLcl commands

SQLcl - Oracle SQL Developer command line - commands

New free command line interface, by Oracle SQL Developer Team, for Oracle Database. And advanced version of SQL* Plus utility. SQLcl also introduces new commands and features which are missing from SQL*Plus.

sqlcl first released in 2016, and shipped by default with Oracle Database 12c Release 2.

sqlcl is not having any prerequisites, except Java (JDK/JRE). Installation is super easy. Just download sqlcl tool zip file (e.g. sqlcl-19.2.1.206.1649.zip) from oracle website and copy that tool to the Oracle database server and unzip.

SQLcl supports connections via EZConnect, TNS, LDAP, TWO_TASK, and more, and all without an Oracle client installed or configured.

Below are advantages of SQLcl (over SQL * PLUS):
  • TAB for auto completion of object names (like in any IDE)
  • Scrollback works, that is, UP ARROW & DOWN ARROW will work
  • Provides in-line editing, can navigate/edit any line in SQL prompt/buffer
  • HISTORY of commands/scripts (including previous sessions)
  • Can have aliases for frequently used commands
  • Automatic formatting   (no need to setup so many COLUMN FORMAT commands)
  • Database Administrators (DBAs) can get DDL easily
  • Can generate SQL query output in CSV, HTML, XML, JSON formats
  • Will display UTF characters
  • CTAS command would automatically generate the 'create table as ...' command
  • Supports SQL * Plus commands
SQLcl is also known as SDSQL (SQLDeveloper SQL), New SQL*Plus tool and SQL*Plus++




Best features of SQLCL

We can easily get SQL output in CSV format, with sqlcl (NO need to set SQL*Plus variables and spool it to CSV file).
SQLcl > SET SQLFORMAT CSV
SQLcl > SELECT * FROM emp WHERE deptno = 20;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20

Below sqlcl command also do the same.
SELECT /*csv*/ * FROM emp;

SQLcl help us to get INSERT statements so easily, for any record in database.
SQLcl > SET SQLFORMAT INSERT
SQLcl > SELECT * FROM emp WHERE deptno = 20;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH24.MI.SSXFF'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH24.MI.SSXFF'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH24.MI.SSXFF'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);

Below command in sqlcl prompt will do the same.
SELECT /*insert*/ * FROM emp;

SQLcl also handles login scripts (login.sql), like SQL Plus. If you would like to setup some SQLcl configuration/formatting parameters permanently, the easiest way is to create a file called login.sql in current directory, then call SQLcl from that directory.
$ cat login.sql
set lines 333 pages 44444 time on
col db_name new_value db_name
select name db_name from v$database;
SET SQLFORMAT ansiconsole
set sqlprompt "@|blue _USER|@@@|green &db_name|@@|red >|@ "




SQLCL [ [option] [logon | / NOLOG] [start] ]
SQLCL [  -H[ELP]  | -V[ERSION]  | [ [-C[OMPATIBILITY] x.y[.z]]]  [-L[OGON]]   [-NOLOGINTIME] [-R[ESTRICT] {1 | 2 | 3}] [-S[ILENT]] [-AC]]  [ {username[/password] [@connect_identifier]  | /}  [AS {SYSASM |SYSBACKUP |SYSDBA |SYSDG |SYSOPER |SYSRAC |SYSKM}]  [edition=value]  | / NOLOG] [   [start] ]
SQLCL -help
SQLCL -h
SQLCL -version

HELP | ? [topic]
help
help alias

DBA@DEVDB> help
For help on a topic type help topic 
List of Help topics available:

 /                @                @@               ACCEPT           ALIAS            APEX             APPEND           ARCHIVE_LOG      
BRIDGE           BTITLE           CD               CHANGE           CLEAR            COLUMN           COMPUTE          CONNECT          COPY             DDL              DEFINE           DEL              DESCRIBE         DISCONNECT       EDIT             EXECUTE          EXIT             FIND             GET              HISTORY          HOST             INFORMATION      INPUT            LIST             LOAD             NET              OERR           PAUSE            PRINT            PROMPT           QUIT             REMARK           REPEAT           RESERVED_WORDS   REST             RUN              SCRIPT           SET              SETERRORL        SHOW             SHUTDOWN         SODA             SPOOL            SSHTUNNEL        START            STORE            TIMING           TNSPING          TTITLE           UNDEFINE         VARIABLE         VAULT            WHENEVER         WHICH  

@{url | file_name[.ext]} [arg ...]
@@ { url | file_name[.ext] } [arg ...]

/ (slash)
Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
Reads a line of input and stores it in a given substitution variable.

ALIAS [name=SQL statement;| LOAD [filename]|SAVE [filename] | LIST [NAME] | DROP name | DESC name  Description String]
Alias is a command which used to save a SQL, PL/SQL or SQL*Plus script/command and assign it a shortcut command.
SQLcl > alias 
SQLcl > alias future=select sysdate + :days from dual; 
SQLcl > alias list
SQLcl > alias list future
SQLcl > future 12
SQLcl > ALIAS dl=select * from dual;
SQLcl > alias active_users=select sid,serial#,username from v$session where status='ACTIVE' and username is not null;

APEX [export application_id]
Lists Application Express Applications.

A[PPEND] text
Adds specified text to the end of the current line in the SQL buffer.

ARCHIVE LOG LIST
Displays information about archive/redo log files.

BRE[AK] [ON report_element [action [action]]] ...
Specifies where changes occur in a report and the formatting action to perform
BRE[AK] [ON {column | expression | ROW | REPORT} [action [[SKI[P] n | [ SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]] ]]] ...

BRIDGE  
Works like a database link to move between two connections/schemas.
BRIDGE department_new as "jdbc:oracle:thin:SYSTEM/ORACLE@172.33.242.33:1540/employees"(select * from department_new);
BRIDGE INSERT INTO department_new as "jdbc:oracle:thin:SYSTEM/ORACLE@172.33.242.33:1540/employees"(select * from department_new);

BTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
BTI[TLE] [ [ BOLD| CE[NTER]| COL n| FORMAT text| LE[FT]| R[IGHT] | S[KIP] [n] TAB ] | [text | variable] ...] | [ON | OFF]
Places and formats a title at the bottom of each report page, or lists the current BTITLE definition.

CD [directory]
Change directory at OS (Operation System) level
cd /home/oracle/satya/sql/

C[HANGE] sepchar old [sepchar [new [sepchar]]]
Changes first occurrence of old on the current line of the SQL buffer. 

CL[EAR] option ...
CL[EAR] [ BRE[AKS]| BUFF[ER]| COL[UMNS]| COMP[UTES]| CONTEXT| SCR[EEN]| SQL| TIMI[NG] ] 
Resets or erases the current value or setting for the specified option.
clear screen
cl scr
clear sql
clear columns

COL[UMN] [{column | expr} [option ...]]
Specifies display attributes for a given column.
col table_name format a30

COMP[UTE] [function [LAB[EL] text] ... OF {expr | column | alias} ...ON {expr | column | alias | REPORT | ROW} ...]
calculates and prints summary lines using various standard computations.

CONN[ECT] [{logon| / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
CONN[ECT] [{ username[/password] [@connect_identifier]  | / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
Connects a given username to the Oracle Database.
connect ogg_owner/goldengate@proddb

CTAS table new_table
Used to get DDL to create new table from existing/mentioned table name.
CTAS - Create Table AS 
help ctas
ctas orders orders_backup

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
Copies data from a query to a table in the same or another database.

DDL [object_name [type] [SAVE filename]]
Generates the code to reconstruct the object listed.

help set ddl
set ddl
set ddl comment off
set ddl constraints on
set ddl storage off

show ddl


help DDL

ddl hr.employees
ddl emp
DDL TEST100 SAVE TEST.LOG

DEF[INE] [variable] | [variable = text]
Specifies a substitution variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables.

DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Deletes one or more lines of the SQL buffer.

DESC[RIBE] { [schema.]object[@connect_identifier] }
Lists the column definitions for a table, view or synonym, or the specifications for a function or procedure.
describe orders

DISC[ONNECT]
Commits pending changes to the database and logs the current user out of Oracle (but will not exit SQLcl)
disconnect;

ED[IT] [file_name[.ext]]
Invokes an OS (operating system) text editor on the contents of the specified file or SQL buffer.
ed

EXEC[UTE] statement
Executes a single PL/SQL statement or runs a stored procedure.

{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.
exit;

FIND [filename]
Searches the SQLPATH and its directories for the specified file name.

FORMAT [BUFFER | RULES filename | FILE input_file output_file]
Formats the script in the SQLcl Buffer
FORMAT BUFFER
FORMAT RULES filename
FORMAT FILE input_file output_file

GET [FILE] file_name[.ext] [LIST | NOLIST]
Loads a SQL statement or PL/SQL block from a file into the SQL buffer.

HISTORY HISTORY [index | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?]
To show previously ran SQLcl commands
SQLcl retains a history of the last 100 commands that were used (including previous sessions).
The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the total execution time for the statement. This will help DBAs to find out how much time the query took yesterday/last week.
SQLcl > set history
SQLcl > set history limit 500
SQLcl > set history on
SQLcl > set history off
SQLcl > set history clear
SQLcl > SET HISTORY BLACKLIST

SQLcl > history

SQLcl > history 6
SQLcl > history full
SQLcl > his usage
SQLcl > history script
SQLcl > history time
SQLcl > his clear
SQLcl > history fails

SQLcl > set nohistory

SQLcl > set nohistory SET, SHOW, HISTORY, CLEAR

SQLcl > show nohistory


HO[ST] [command]
Executes an operating system command without leaving SQLcl. 
host ls -tlr
! pwd

INFO[RMATION] { [schema.]object[@connect_identifier] }
Lists more detailed information about the column definitions for a table, view or synonym, or the specifications for a function or procedure.
info hr.employees
info+ hr.employees
information test_emp

I[NPUT] [text]
Adds one or more new lines of text after the current line in the SQL buffer.

L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

LOAD [schema.]table_name[@db_link] file_name
Loads a comma separated value (csv) file into a table. 

OERR facility error
Displays information about Oracle errors.

PASSW[ORD] [username]
Allows us to change a password without displaying it on an input device.

PAU[SE] [text]
Displays the specified text then waits for the user to press RETURN.

PRINT [variable ...]
Displays the current values of bind variables, or lists all bind variables.

PRO[MPT] [text]
Sends the specified message or a blank line to the user's screen.

{QUIT | EXIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.
quit;

REM[ARK]
Begins a comment in a script. 

REPEAT iterations sleep
Repeats the current SQL in the buffer at the specified times with sleep intervals.
repeat [number-of-repeats-max-2Billion] [sleep-duration-max-120seconds]
repeat help
repeat 10 1

REST [export [module_name | module_prefix] | modules | privileges | schemas]
REST 
Used to export Oracle REST Data Services 3.x services. 
REST export module_name 
REST modules
REST privileges
REST schemas

R[UN]
Lists and executes the most recently executed SQLcl command or PL/SQL block which is stored in the SQL buffer. 

SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves the contents of the SQL buffer in a script.

SCRIPT script file
Runs the SQLcl statements in the specified script.

SET system_variable value
Sets a system variable to alter the SQLcl environment settings for current session.
set pagesize 88

SET SQLFORMAT
Outputs reports in various formats. 
help set sqlformat
SET SQLFORMAT DEFAULT
SET SQLFORMAT ansiconsole
SET SQLFORMAT INSERT
SET SQLFORMAT CSV
SET SQLFORMAT html
SET SQLFORMAT xml
SET SQLFORMAT json
SET SQLFORMAT fixed
SET SQLFORMAT loader
SET SQLFORMAT delimited
SET SQLFORMAT delimited |^| ' '
SET SQLFORMAT DELIMITED ~del~ " "
show sqlformat

Below commands also works.
SELECT /*csv*/ * FROM emp;
SELECT /*html*/ * FROM emp;
SELECT /*xml*/ * FROM emp;
SELECT /*json*/ * FROM emp;
SELECT /*ansiconsole*/ * FROM emp;
SELECT /*insert*/ * FROM emp;
SELECT /*loader*/ * FROM emp;
SELECT /*fixed*/ * FROM emp;
SELECT /*delimited*/ * FROM emp;
SELECT /*text*/ * FROM emp;

System Variables and Environment Settings through the SET Command
autoprint
autorecovery
cmdsep
copytypecheck
describe
eschar
flagger
flush
fullcolname
logsource
loboffset
markup
recsep
recsepchar
shiftinout
sqlcase
sqlprefix
sqlterminator
tab
underline
xmloptimizationcheck

SET APPI[NFO]{ON | OFF | text}
SET ARRAY[SIZE] {15 | n}
SET AUTO[COMMIT] {ON | OFF | IMM[EDIATE] | n}
SET AUTOP[RINT] {ON | OFF}
SET AUTORECOVERY {ON | OFF]
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]}
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
SET CLEAR [ TOP | BOTTOM | SAME ]
SET CMDS[EP] {; | c | ON | OFF}
SET COLSEP {_ | text}
SET CON[CAT] {. | c | ON | OFF}
SET COPYC[OMMIT] {0 | n}
SET COPYTYPECHECK {ON | OFF}
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS | CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING | SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION | BODY | FORCE | INSERT | |INHERIT | RESET] {on|off} ] | OFF ]
SET DEF[INE] {& | c | ON | OFF}
SET ECHO {ON | OFF}
SET EDITF[ILE] file_name[.ext]
SET EMB[EDDED] {ON | OFF}
SET ENCODING
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
SET ESC[APE] {\ | c | ON | OFF}
SET ESCCHAR {@ | ? | % | $ | OFF}
SET EXITC[OMMIT] {ON | OFF}
SET [EXP[LAIN]] [STAT[ISTICS]]
SET FEED[BACK] {6 | n | ON | OFF}
SET FLU[SH] {ON | OFF}
SET HEA[DING] {ON | OFF}
SET HEADS[EP] { | | c | ON | OFF}
SET INSTANCE [instance_path | LOCAL]
SET LDAPCON
SET LIN[ESIZE] {80 | n}
SET LOBOF[FSET] {n | 1}
SET LOGSOURCE [pathname]
SET LONG {80 | n}
SET LONGC[HUNKSIZE] {80 | n}
SET NET {ON | OFF | READONLY}
SET NEWP[AGE] {1 | n | NONE}
SET NOVERWRITE {ON | OFF | WARN}
SET NULL text
SET NUMF[ORMAT] format
SET NUM[WIDTH] {10 | n}
SET PAGES[IZE] {14 | n}
SET PAU[SE] {ON | OFF | text}
SET RECSEP {WR[APPED] | EA[CH] | OFF}
SET RECSEPCHAR { | c}
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
SET SHOW[MODE] {ON | OFF}
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
SET SQLCO[NTINUE] { | text}
SET SQLFORMAT {csv | html | xml | json | ansiconsole | insert | loader | fixed | default}
SET SQLN[UMBER] {ON | OFF}
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
SET SQLPRE[FIX] {# | c}
SET SQLP[ROMPT] {SQL | text}
SET SQLT[ERMINATOR] {; | c | ON | OFF}
SET SUF[FIX] {SQL | text}
SET TAB {ON | OFF}
SET TERM[OUT] {ON | OFF}
SET TI[ME] {ON | OFF}
SET TIMI[NG] {ON | OFF}
SET TRIM[OUT] {ON | OFF}
SET TRIMS[POOL] {ON | OFF}
SET UND[ERLINE] {- | c | ON | OFF}
SET VER[IFY] {ON | OFF}
SET WRA[P] {ON | OFF}
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS | CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING | SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION | BODY | FORCE | INSERT | |INHERIT | RESET] {on|off} ] | OFF ]

SHO[W] [option]
Shows the value of a SQLcl system variable, or the current SQLcl environment.
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
show all
show all+
show version
show connection
show tns
show no failures
show failures
SHOW ENCODING
SHOW ENCODINGS
show pdbs

SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle instance, optionally closing and dismounting a database.
shutdown immediate;

SODA
SODA allows schemaless application development using the JSON data model. 
SODA create collection_name
SODA list 
SODA get collection_name [-all | -f | -k | -klist] [{key | k1 k2 ... | qbe}]
SODA insert collection_name json_str | filename
SODA drop collection_name
SODA count collection_name [qbe]
SODA replace collection_name oldkey new_{str | doc}
SODA remove collection_name [-k | -klist | -f] {key | k1 k2 ...| qbe}

SPO[OL] [filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Stores query results in a file, or optionally sends the file to a printer.

SSHTUNNEL username@hostname -i identity_file [-L localPort:Remotehost:RemotePort]
Creates a tunnel using standard ssh options.

STA[RT] { url | file_name[.ext] } [arg ...]
Runs the SQLcl statements in the specified script.

STARTUP db_options | cdb_options | upgrade_options
STARTUP [FORCE] [RESTRICT] [PFILE=filename] [QUIET]  [ MOUNT [dbname] |  [ OPEN [ READ {ONLY | WRITE [RECOVER]} | RECOVER ] [dbname] ] | NOMOUNT ]
 | PLUGGABLE DATABASE pdbname  [FORCE] | [RESTRICT]  [ OPEN {open_pdb_options}]  |  [FORCE] | [RESTRICT]  [ OPEN { READ WRITE | READ ONLY }]
 | [PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
Starts an Oracle Database instance with several options, including mounting and opening a database.
startup;

STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves attributes of the current SQLcl environment in a file.

TIMI[NG]
Timing is only available as a switch

TNSPING address
determines whether the listener for a service on an Oracle Net network can be reached successfully.
tnsping devdb

TTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
TTI[TLE] [ [ BOLD| CE[NTER]| COL n| FORMAT text| LE[FT]| R[IGHT] | S[KIP] [n] TAB ] [text | variable] ...] | [ON | OFF]
Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.

UNDEF[INE] variable ...
Deletes one or more substitution variables that defined either explicitly (with the DEFINE command) or implicitly (with a START command argument).

VERSION
Displays the SQLcl version
version

WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE[COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQLcl by default) if an operating system error occurs.

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable  | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQLcl by default) if a SQL command or PL/SQL block generates an error.

WHICH filename
Searches the SQLPATH and its directories for the specified file name and prints the name of the first file matching the specified file name in the SQLPATH.

XQUERY xquery_statement
Used to run an XQuery from SQLcl.


To change/setup SQL prompt with user/database name and with different colors  and images :
set sqlprompt "_user'@'_connect_identifier "
set sqlprompt "_user'@'_connect_identifier 🍼 "
set sqlprompt "@|blue _USER|@@@|green _connect_identifier|@@|red |@ "
set sqlprompt "@|bold _USER|@@@|bg_cyan _CONNECT_IDENTIFIER|@@|blue 🍼 |@ "
set sqlprompt "@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue 🍺 |@ "
set sqlprompt "@|white 'I am '|@@|bold _USER|@@|white ' on '|@@|blue,bg_white _CONNECT_IDENTIFIER|@@|bg_cyan '  '|@"


Related Oracle Articles: 
Changing SQL prompt


No comments:

Post a Comment