Oracle SQL*Plus
Version 19c

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*Plus is the premier command line tool for doing just about every administrative task in Oracle that does not involve a RAC Cluster (use Server Control) and/or Data Guard (use the Data Guard Broker). If you need a GUI, what can I say, ... so I won't.R
 
General
HOST

Shell out to the operating system and exit the shell		 exit
host
SQL> host

# exit
Logon Parameters sqlplus <user_name>[/password][@<connect_identifier>] | / [AS SYSDBA | AS SYSOPER | /NOLOG
sqlplus system/manager@orabase AS SYSOPER /NOLOG
QUIT

Close SQL*Plus		 quit
SQL> host

# quit
Constants
Constant Usage Example
SQL.LNO Line Number SELECT COUNT(*)
FROM all_objects;

show lno
SQL.PNO Page Number SELECT object_name
FROM all_objects;

show pno
SQL.RELEASE Oracle Version show release
SQL.SQLCODE Current error code show sqlcode
SQL.USER Currently connected user show user
 
Arraysize
Array Size Demo ARRAYSIZE nnn
The default value of nnn is 15, which is too small for large data transfers. Try larger and larger values of nnn until response improvements become marginal.

CREATE TABLE t AS
SELECT *
FROM all_objects;

SELECT COUNT(*)
FROM t;

CREATE INDEX t_idx
ON t(object_id)
PCTFREE 0;

set autotrace traceonly

show arraysize

SELECT * FROM t;

SELECT * FROM t;

SELECT * FROM t;

set arraysize 10

SELECT * FROM t;

set arraysize 100

SELECT * FROM t;

set arraysize 250

SELECT * FROM t;
 
Configuration Settings
Save global settings in a glogin.sql file at /sqlplus/admin on the database server
set pagesize 45
set linesize 141
set long 1000000
col column_name format a30
col data_type format a20
col file_name format a60
col name format a30
col object_name format a30
col owner format a25
col segment_name format a30
col service_name format a30
col triggering_event format a35
col value format a30
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET DEFINE OFF
SET SERVEROUTPUT ON
SET HIST ON
define _editor=vi
Save local settings in a login.sql file at /sqlplus/admin in the client ORACLE_HOME
SQL> conn uwclass/uwclass
SQL> show linesize
SQL> set linesize 141
SQL> show linesize
SQL> STORE SET statefile REPLACE
SQL> EXIT

SQL> conn uwclass/uwclass
SQL> show linesize
SQL> @statefile
SQL> show linesize
 
Connect
CONN as SYS to ROOT CONN <logon> AS <SYSDBA | SYSOPER>
conn / as sysdba
CONN as SYS to PDB CONN <logon>@<service_name_tns_alias> AS <SYSDBA | SYSOPER>
conn sys@pdbdev AS SYSDBA
CONN as User CONN <logon> / <password> @ <instance>
conn uwclass/uwclass@orabase

conn uwclass@orabase
 
Describe
Describe a function desc <function_name>
CREATE OR REPLACE FUNCTION upperString (stringin VARCHAR2) RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN UPPER(stringin);
END upperString;
/

desc upperString
Describe a package desc <package_name>
CREATE OR REPLACE PACKAGE demopkg IS

PROCEDURE demoproc;
FUNCTION demofunc RETURN BOOLEAN;

END;
/

desc demopkg

CREATE OR REPLACE PACKAGE BODY demopkg IS

PROCEDURE demoproc IS
BEGIN
  NULL;
END;

FUNCTION demofunc RETURN BOOLEAN IS
BEGIN
  RETURN TRUE;
END;

END demopkg;
/

desc demopkg
Describe a procedure desc <procedure_name>
CREATE OR REPLACE PROCEDURE demoproc(numbin NUMBER, stringin IN OUT NOCOPY VARCHAR2, tfin OUT BOOLEAN)
AUTHID DEFINER IS
BEGIN
  NULL;
END demoproc;
/

desc demoproc
Describe a table desc <table_name>
CREATE TABLE demotable (
rid     NUMBER(2,2),
testcol VARCHAR2(20),
insdate DATE);

desc demotable
Describe an object table set describe depth all {linenum <ON | OFF>} {INDENT <ON | OFF>}
CREATE OR REPLACE TYPE rectangle_t AS OBJECT (
h  NUMBER,
w  NUMBER,
x  NUMBER,
y  NUMBER);
/

CREATE TABLE rectable (
rectangle_name VARCHAR2(20),
rectangle      rectangle_t);

desc rectable

set describe depth all

desc rectable

set describe depth all linenum on indent on

desc rectable
Describe a synonym desc <synonym_name>
CREATE SYNONYM demosyn FOR rectable;

desc demosyn
Describe a type set describe depth all {linenum <ON | OFF>} {INDENT <ON | OFF>}
desc <type_name>
desc xmltype

set describe depth all

desc xmltype

set describe depth all linenum on indent on

desc xmltype
Describe a view desc <view_name>
CREATE OR REPLACE VIEW demoview AS
SELECT *
FROM demotable;

desc demotable
 
Disconnect
Disconnect from the current session disconnect
conn uwclass/uwclass@pdbdev

disconnect
Disconnected from Oracle Database 18c Enterprise Edition Release 18.3.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
Display
CLEAR SCREEN cl[ear] scr
SELECT object_name, created
FROM all_objects
WHERE ROWNUM < 2;

clear scr
PAUSE

Enables control terminal scrolling when running reports. First, SET PAUSE text, and then SET PAUSE ON to make text to appear each time SQL*Plus pauses		 set pause <OFF | ON>
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause on

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause off
SERVEROUTPUT

Display Output From DBMS_OUTPUT.PUT_LINE built-in package		 set serveroutput <ON | OFF>
DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/

set serveroutput on

DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/
TIMING

Controls the display of timing statistics. ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.		 set timing <OFF | ON>
SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing on

SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing off
 
Edit / Editor
Define An Editor _editor
define _editor=vi
Edit The Last Command ed
SQL> SELECT dummy FROM dual;

SQL> ed
Change the Edit File Location set editfile <edit file location and file name>
set editfile /home/oracle/afiedt.buf
 
Error Handling
OS Errors whenever oserror <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER OSERROR EXIT
@c:\temp\nofile.sql
SQL Errors
See DBMS_REDEFINITION Demo to view usage of this feature		 whenever sqlerror <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER SQLERROR CONTINUE
SQL*Plus Erorr Logging (new in 11gR2) errorlogging < ON | OFF >
SQL> SET ERRORLOGGING ON
 
Escape
Defines the character used as the escape character esc[ape] <\ | c | ON | OFF>
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'

-- return to the default backslash (\) character

SET ESCAPE ON
Specifies a character to be escaped and not interpreted set escchar <@ | ? | % | $ | OFF>
SET ESCCHAR $

RUN LOADFILE$
 
Execute
Run a stored standalone or package procedure exec [<schema_name>.] [<package_name>.] <procedure_name>
CREATE OR REPLACE PROCEDURE demoexec IS
BEGIN
  dbms_output.put_line('*** Executed ***');
END demoexec;
/

set serveroutput on

exec demoexec
 
Formatting
Character Columns COL <column_name> FORMAT a<integer>
SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;

COL object_name FORMAT a30

SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;
Column Separators SET COLSEP <column separator>
set colsep ','

SELECT table_name, column_name, data_type
FROM user_tab_cols
WHERE rownum < 10;
HEAD

Turns the column display header on/off		 SET HEAD <OFF | ON>
SELECT table_name FROM all_tables;

set head off

SELECT table_name FROM all_tables;

set head on
LINESIZE

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line		 SET LINESIZE <integer>
SELECT text
FROM all_source
WHERE rownum < 21;

set linesize 121

SELECT text
FROM all_source
WHERE rownum < 21;
Money Columns COL <column_name> FORMAT $99,999
COL salary FORMAT $99,999
Numeric Columns
Element Example Description
9 9999 Number of significant digits returned
COL test FORMAT 99.99
SELECT 100/3 TEST FROM dual;

COL test FORMAT 99.9999
SELECT 100/3 TEST FROM dual;
0 0999
9990		 Display a leading zero or a value of zero in this position as 0
COL test FORMAT 099.999
SELECT 100/3 TEST FROM dual;
$ $9999 Prefixes with dollar sign
CREATE TABLE t (
test NUMBER(10,4));

INSERT INTO t VALUES (1234);
INSERT INTO t VALUES (-234);
INSERT INTO t VALUES (0);

COL test FORMAT $9999

SELECT * FROM t;
B B9999 Display a zero value as blank
COL test FORMAT B9999

SELECT * FROM t;
MI 9999MI Display "-" after a negative value
COL test FORMAT 9999MI

SELECT * FROM t;
S S9999 Display "+" for positive values and "-" for negative values
COL test FORMAT S9999

SELECT * FROM t;
PR 9999PR Displays a negative value in <angle brackets>
COL test FORMAT 9999PR

SELECT * FROM t;
D 99D99 Display the decimal character
COL test FORMAT 9999D99

SELECT * FROM t;
G 9G999 Display the group separator
COL test FORMAT 9G999

SELECT * FROM t;
C C999 Display the ISO currency symbol
COL test FORMAT C9999

SELECT * FROM t;
L L999 Display the local currency symbol
COL test FORMAT L9999

SELECT * FROM t;
, 9,999 Display a comma
COL test FORMAT 9,999

SELECT * FROM t;
. 99.99 Display a period
COL test FORMAT 9999.99

SELECT * FROM t;
V 999V99 Multiplies value by 10n, where n is number of "9"s after "V"
COL test FORMAT 9999V99

SELECT * FROM t;
EEEE 9.999EEEE Display value in scientific notation
COL test FORMAT 9999.99EEEE

SELECT * FROM t;
RN or rn RN Display upper or lowercase Roman numerals. Value can be an integer between 1 and 3999
COL test FORMATRN

SELECT * FROM t;
DATE DATE Format a NUMBER columns that represent Julian dates as MM/DD/YY
COL test FORMAT DATE

SELECT * FROM t;
 
NUMWIDTH

Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions		 SET NUMWIDTH <integer>
CREATE TABLE t (col NUMBER);

INSERT ITNO t VALUES (9999999999999999999999);

SELECT * FROM t;

set numwidth 25

SELECT * FROM t;
BREAK

Defines page breaks		 BREAK ON <column_name> [SKIP PAGE]
break on overload skip page

set pagesize 45
set linesize 161
col overload format a8

SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;
PAGESIZE

Sets the number of lines on each page of output		 SET PAGESIZE <integer>
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 45

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 0

SELECT object_name
FROM all_objects
WHERE rownum < 60;
TTITLE

Defines page title attributes		 ttitle {LEFT <text>} {CENTER <text>} {RIGHT <text>}
set pagesize 25

ttitle LEFT '01-Jan-2019' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO

SELECT * FROM dual;

ttitle LEFT '01-Jan-2019' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - 'Oracle Application Development'

SELECT object_name
FROM all_objects
WHERE rownum < 60;
WRAP

Controls line wrapping		 wrap <ON | OFF>
show wrap

SELECT * FROM dba_tables WHERE rownum < 11;

set wrap OFF

SELECT * FROM dba_tables WHERE rownum < 11;

set wrap ON

SELECT * FROM dba_tables WHERE rownum < 11;
 
Help
Display SQL*Plus Help help <command>
SQL> help index

SQL> help variable
 
History
Retain and maintain history of SQL*Plus commands hist[ory] [n | RUN | EDIT | DEL[ETE]] | [CLEAR | LIST]
SQL> show history
history is OFF
SQL> set history on
SQL> SELECT COUNT(*) FROM tab$;

  COUNT(*)
----------
      2150

SQL> SELECT COUNT(*) FROM obj$;

  COUNT(*)
----------
     72629

SQL> SELECT COUNT(*) FROM source$;

  COUNT(*)
----------
     12992

SQL> hist
  1 SELECT COUNT(*) FROM tab$;
  2 SELECT COUNT(*) FROM obj$;
  3 SELECT COUNT(*) FROM source$;


SQL> run 2
  1* SELECT COUNT(*) FROM source$

  COUNT(*)
----------
     12992
 
PASSWORD
Change the password of the current user session password
SQL> password
Changing password for UWCLASS
Old password:
New password:
Retype new password:
Password changed
 
RUN
Re-execute a statement of anonymous block. Obsolete and replaced by the forward slash run
set serveroutput on

BEGIN
  dbms_output.put_line('test');
END;
/

run

/
 
Run A Script
@ @ <path_and_script_name>
@/u01/app/oracle/product/18.3.0/dbhome_1/rdbms/admin/catplan.sql

-- The question mark "?" is an alias for $ORACLE_HOME

@?/rdbms/admin/catplan.sql
GET get <path_and_script_name>
get /u01/app/oracle/product/18.3.0/dbhome_1/rdbms/admin/catplan.sql

-- The question mark "?" is an alias for $ORACLE_HOME

get ?/rdbms/admin/catplan.sql
 
SAVE
Save the most recently executed SQL statement save <file_name>
SELECT tablespace_name, status
FROM all_tablespaces;

save ts

SQL> host

$ more ts

$ exit
 
SHOW
List all parameter settings sho[w] all
SQL> sho all
appinfo is OFF and set to "SQL*Plus"
arraysize 250
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colinvisible OFF
coljson OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define OFF
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
errorlogging is OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON for 6 or more rows SQL_ID OFF
flagger OFF
flush ON
fullcolname OFF
heading ON
headsep "|" (hex 7c)
history is ON and set to "100"
instance "local"
linesize 181
lno 51
loboffset 1
lobprefetch 0
logsource ""
long 1000000
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
markup CSV OFF DELIMITER , QUOTE ON
newpage 1
null ""
numformat ""
numwidth 10
pagesize 45
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1903000000
repfooter OFF and is NULL
repheader OFF and is NULL
rowlimit OFF
rowprefetch 1
securedcol is OFF
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 19.0.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
statementcache is 0
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool ON
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYS"
verify ON
wrap : lines will be wrapped
xmloptimizationcheck OFF
Show the current container Identifier sho[w] con_id
SQL> show con_id

CON_ID
------------------------------
     1

SQL> ALTER SESSION SET container=PDBDEV;

Session altered.

SQL> sho con_id

CON_ID
------------------------------
3
Show the current container name sho[w] con_name
SQL> sho con_name

CON_NAME
-----------
CDB$ROOT
Show the current session edition sho[w] edition
SQL> show edition

EDITION
------------------------------
ORA$BASE
Show the instance's PDBs visible from the current container sho[w] pdbs
SQL> show pdbs

    CON_ID CON_NAME         OPEN MODE  RESTRICTED
---------- ---------------- ---------- ----------
         2 PDB$SEED         READ ONLY  NO
         3 PDBDEV           READ WRITE NO
         5 PDBTEST          MOUNTED
 
Spooling
SPOOL: Create a spool file spo[ol] [path_and_file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
spool /home/oracle/spoolfiles/zzyzx.txt

SELECT table_name
FROM all_tables;

spo off

spo /home/oracle/spoolfiles/zzyzx.txt app

SELECT object_id
FROM user_objects

spool off

cd $HOME/spoolfiles

more zzyzx.txt
Termout termout <OFF | ON>
-- termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files.

abc.sql:
set termout off
select 'abc' from dual

-- and run it like this in sqlplus:
@abc.sql
Trimspool trimspool <OFF | ON >
spool /home/oracle/spoolfiles/zzyzx.txt

SELECT table_name
FROM all_tables
WHERE rownum < 11;

trimspool on

SELECT table_name
FROM all_tables
WHERE rownum < 11;

spool off
 
SQL> Prompt
Setting the SQL*Plus Prompt sqlprompt <value>

Predefined Variable Description
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available
_DATE Current date, or a user defined fixed string
_EDITOR Specifies the editor used by the EDIT command
_O_RELEASE Full release number of the installed Oracle Database
_O_VERSION Current version of the installed Oracle Database
_PRIVILEGE Privilege level of the current connection
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component
_USER User name used to make connection
SQL> set sqlprompt "_user'@'_connect_identifier>"
SYS@orabase>conn uwclass/uwclass
Connected.
UWCLASS@orabase>

SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER> '

SQL> set sqlprompt 'SQL>'
 
SQL*Plus Startup
Startup Parameters: Usage 1
Flags Description
-H Displays the SQL*Plus version and the usage help
-V Displays the SQL*Plus version
sqlplus -H | -V
Startup Parameters: Usage 2
Flags Description
-C<version> Sets the compatibility of affected commands to the version specified. The version has the form "x.y[.z]. For example -C 19.3.0 
-L Attempts to log on just once, instead of reprompting on error
-M<option>  
-R<level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2, or 3 with R3 being the most restrictive and disables all such commands
-S Sets the silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands
sqlplus [ [<option>] [<logon>] [start>] ]
 
Variable
Define Variable DEFINE [<variable_name>] [<value>]
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 1 20:36:41 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> DEFINE

SQL> DEFINE _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "ORABASE" (CHAR)

SQL> DEFINE _DATE
DEFINE _DATE = "04-JAN-2010 13:28:22" (CHAR)

SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)

SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options" (CHAR)

SQL> DEFINE _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)

SQL> DEFINE _USER
DEFINE _USER = "SYS" (CHAR)
Define variable use in PL/SQL DEFINE [<variable_name>] [<data_type>]
variable x VARCHAR2(20)

variable x

BEGIN
  :x := 'ABC';
END;
/
Undefine Variable undefine <variable_name>
SQL>UNDEFINE _USER
View Variable Value print <variable_name>
print x

Related Topics
Built-in Functions
Built-in Packages
SHOW
SQLcl
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