SQL*Plus
Quick Reference

Release 8.0
Part No. A53718–01

Enabling the Information Age

SQL*Plus Quick Reference, Release 8.0
Part No. A53718–01
Copyright E 1997 Oracle Corporation
All rights reserved. Printed in the U.S.A.
Contributing Author: Frank Rovitto
Contributors: Larry Baer, Lisa Colston, Roland Kovacs, Karen Denchfield–Mas-
terson, Sanjeev Jhala, Alison Holloway, Christopher Jones, Anita Lam, Nimish
Mehta, Luan Nim, Bud Osterberg, Irene Paradisis, Richard Rendell, Farokh
Shapoorjee, Larry Stevens, Andre Touma
This software was not developed for use in any nuclear, aviation, mass
transit, medical, or other inherently dangerous applications. It is the
customer’s responsibility to take all appropriate measures to ensure the safe
use of such applications if the programs are used for such purposes.
This software/documentation contains proprietary information of Oracle
Corporation; it is provided under a license agreement containing restrictions on
use and disclosure and is also protected by copyright law. Reverse engineering
of the software is prohibited.
If this software/documentation is delivered to a U.S. Government Agency of
the Department of Defense, then it is delivered with Restricted Rights and the
following legend is applicable:
Restricted Rights Legend Use, duplication, or disclosure by the Government is
subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS
252.227–7013, Rights in Technical Data and Computer Software (October 1988).
Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.
If this software/documentation is delivered to a U.S. Government Agency not
within the Department of Defense, then it is delivered with “Restricted Rights”,
as defined in FAR 52.227–14, Rights in Data – General, including Alternate III
(June 1987).
The information in this document is subject to change without notice. If you
find any problems in the documentation, please report them to us in writing.
Oracle Corporation does not warrant that this document is error free.
Oracle, SQL* Forms, and SQL*Plus are registered trademarks, and PL/SQL is a
trademark of Oracle Corporation.
All other products or company names are used for identification purposes only,
and may be trademarks of their respective owners.
SQL*Plus Quick
Reference

T his Quick Reference shows the syntax for SQL*Plus commands.

For detailed information on each command, refer to the SQL*Plus
User’s Guide and Reference.

SQL*Plus Quick Reference 1

Conventions for Command Syntax
The following two tables describe the notation and conventions for
command syntax used in this Quick Reference.

Commands, Terms, and

Clauses

Feature Example Explanation

uppercase BTITLE Enter text exactly as spelled;
it need not be in uppercase.
lowercase italics column A clause value; substitute an
appropriate value.
words with specific c A single character.
meanings
char A CHAR value—a literal in
single quotes—or an
expression with a CHAR
value.
d or e A date or an expression with
a DATE value.
expr An unspecified expression.
m or n A number of an expression
with a NUMBER value.
text A CHAR constant with or
without single quotes.
variable A user variable (unless the
text specifies another
variable type).

Other words are explained where used if their meaning is not

explained by context.

2 SQL*Plus Quick Reference

Punctuation
Feature Example Explanation
vertical bar | Separates alternative syntax
elements that may be
optional or mandatory.
brackets [OFF|ON] One or more optional items.
If two items appear
separated by |, enter one of
the items separated by |. Do
not enter the brackets or |.
braces {OFF|ON} A choice of mandatory items;
enter one of the items
separated by |. Do not enter
the braces or |.
underlining {OFF|ON} A default value; if you enter
nothing, SQL*Plus assumes
the underlined value.
ellipsis n ... Preceding item(s) may be
repeated any number of
times.

Enter other punctuation marks (such as parentheses) where shown in

the command syntax.

Starting and Leaving SQL*Plus

Use the following commands to log in to and out of SQL*Plus.
SQLPLUS [[–S[ILENT]] [logon] [start]]|–|–?
Starts SQL*Plus from the operating system prompt.
logon Requires the following syntax:
username[/password][@database_spec]|
/|/NOLOG
start Requires the following syntax:
@file_name[.ext] [arg ...]

SQL*Plus Quick Reference 3

 {EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|
:BindVariable] [COMMIT|ROLLBACK]
Commits all pending changes, terminates SQL*Plus, and
returns control to the operating system.

Entering and Executing Commands

Use the following commands to execute and collect timing statistics on
SQL commands and PL/SQL blocks.
/ (slash)
Executes the SQL command or PL/SQL block currently
stored in the SQL buffer. Does not list the command.
EXEC[UTE] statement
Executes a single PL/SQL statement.
R[UN]
Lists and executes the SQL command or PL/SQL block
currently stored in the SQL buffer.
TIMI[NG] [START text|SHOW|STOP]
Records timing data for an elapsed period of time, lists the
current timer’s name and timing data, or lists the number
of active timers.
Use the following command to access the help system.
HELP [topic]
Accesses help on SQL*Plus commands and PL/SQL and
SQL statements.
Use the following command to execute host operating system
commands.
HO[ST] [command]
Executes a host operating system command without
leaving SQL*Plus.
Note: With some operating systems, you can use a “$” (VMS),
“!” (UNIX) or another character instead of HOST. See the
Oracle installation and user’s manual(s) provided for your
operating system for details.

4 SQL*Plus Quick Reference

Manipulating SQL, SQL*Plus, and PL/SQL Commands
Use the following commands to edit SQL commands and PL/SQL
blocks.
A[PPEND] text
Adds specified text to the end of the current line in the SQL
buffer. To separate text from the preceding characters with
a space, enter two spaces between APPEND and text. To
append text that ends with a semicolon, end the command
with two semicolons (SQL*Plus interprets a single
semicolon as a command terminator).
C[HANGE] sepchar old [sepchar [new [sepchar]]]
Changes text on the current line in the SQL buffer. You can
use any non-alphanumeric character such as “/” or “!” as a
sepchar. You can omit the space between CHANGE and the
first sepchar.
DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Deletes one or more lines of the buffer (“*” indicates the
current line). You can omit the space between DEL and n or
*, but not between DEL and LAST. Enter DEL with no
clauses to delete the current line of buffer.
I[NPUT] [text]
Adds one or more new lines of text after the current line in
the buffer.
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Lists one or more lines of the buffer (“*” indicates the
current line). You can omit the space between LIST and n or
*, but not between LIST and LAST. Enter LIST with no
clauses to list all lines.

SQL*Plus Quick Reference 5

 Use the following commands to create and modify command files.
@ file_name[.ext] [arg ...]
Runs the specified command file. Specified arguments are
substituted for &1, &2, etc.
@@ file_name[.ext]
Runs the specified nested command file.
ED[IT] [file_name[.ext]]
Invokes a host operating system text editor on the contents
of the specified file or on the contents of the SQL buffer. To
edit the buffer contents, omit the file name.
GET file_name[.ext] [LIS[T]|NOL[IST]]
Loads a host operating system file into the SQL buffer.
REM[ARK]
Begins a comment in a command file. The REMARK
command must appear at the beginning of a line, and the
comment ends at the end of the line (a line cannot contain
both a comment and a command). SQL*Plus does not
interpret the comment as a command.
SAV[E] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
Saves contents of the buffer into a host operating system
file (a command file).
STORE {SET} file_name[.ext] [CRE[ATE]|REP[LACE]|
APP[END]]
Saves the attributes of the current SQL*Plus environment in
a host operating system file (a command file).
STA[RT] file_name[.ext] [arg ...]
Executes the contents of the specified command file.
Specified arguments are substituted for &1, &2, etc.

6 SQL*Plus Quick Reference

WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|
n|variable|:BindVariable] [COMMIT|ROLLBACK]|
CONTINUE [COMMIT|ROLLBACK|NONE]}
Exits SQL*Plus if an operating system error occurs (such as
a file I/O error).
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|
n|variable|:BindVariable] [COMMIT|ROLLBACK]|
CONTINUE [COMMIT|ROLLBACK|NONE]}
Exits SQL*Plus if a SQL command or PL/SQL block
generates an error.
Use the following commands to write interactive commands.
ACC[EPT] variable [NUM[BER]|CHAR|DATE]
[FOR[MAT] format] [DEF[AULT] default]
[PROMPT text|NOPR[OMPT]] [HIDE]
Reads a line of input and stores it in a given user variable.
DEF[INE] [variable]|[variable = text]
Specifies a user variable and assigns it a CHAR value.
Alternatively, lists the value and variable type of a single
variable or all variables.
PAU[SE] [text]
Displays an empty line followed by a line containing text,
then waits for the user to press [Return]. Alternatively,
displays two empty lines and waits for the user’s response.
PROMPT [text]
Sends the specified message or a blank line to the user’s
screen.
UNDEF[INE] variable ...
Deletes given user variables that you defined either
explicitly (with the DEFINE command) or implicitly (with
an argument to the START command).
Use the following commands to create and display bind variables.
PRI[NT] [variable ...]
Displays the current values of bind variables.

SQL*Plus Quick Reference 7

 VAR[IABLE] [variable {NUMBER|CHAR|CHAR (n)|
NCHAR|NCHAR (n)|VARCHAR2 (n)|
NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR}]
Declares a bind variable which can then be referenced in
PL/SQL. If no arguments are supplied, VARIABLE lists all
declared bind variables.
Use the following symbols to create substitution variables and
parameters for use in command files.
&n Specifies a parameter in a command file you
run using the START command. START
substitutes values you list after the command
file name as follows: the first for &1, the
second for &2, etc.
&user_variable, Indicates a substitution variable in a SQL or
&&user_variable SQL*Plus command. SQL*Plus substitutes the
value of the specified user variable for each
substitution variable it encounters. If the user
variable is undefined, SQL*Plus prompts you
for a value each time an “&” variable is found,
and the first time an “&&” variable is found.
. (period) Terminates a substitution variable followed by
a character that would otherwise be part of the
variable name.

Formatting Query Results

Use the following commands to format, store and print your query
results.
ATTRIBUTE [type_name.attribute_name [option...]]
Specifies display attributes for a given column, or lists the
current display attributes for a single column or for all
columns; option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name|alias}
ON|OFF

8 SQL*Plus Quick Reference

BRE[AK] [ON report_element [action [action]]] ...
Specifies where and how formatting will change in a report
(for example, skipping a line each time a given column
value changes). Enter BREAK with no clauses to list the
current BREAK definition.
report_ Requires the following syntax:
element
{column|expr|ROW|REPORT}
action Requires the following syntax:
[SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|
DUP[LICATES]]
BTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
Places and formats the specified title at the bottom of each
report page, or lists the current BTITLE definition. See
TTITLE for additional information on valid printspec
clauses.
CL[EAR] option ...
Resets or erases the current value or setting for the
specified option; option represents one of the following
clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]

SQL*Plus Quick Reference 9

 COL[UMN] [{column|expr} [option ...]]
Specifies the display attributes for a given column, such as
text for the column heading, or formats for CHAR,
NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2
(NCHAR VARYING), LONG, CLOB, NCLOB and
NUMBER data; option represents one of the following
clauses:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
Enter COLUMN followed by column or expr and no other
clauses to list the current display attributes for only the
specified column or expression. Enter COLUMN with no
clauses to list all current column display attributes.
Enter FORMAT followed by the appropriate format
element to specify the display format for the column. To
change the width of a datatype or Trusted Oracle column
to n, use FORMAT An. (A stands for alphanumeric.)

10 SQL*Plus Quick Reference

To change the display format of a NUMBER column, use FORMAT
followed by one of the elements in the following table:

Element Example(s) Description

9 9999 Number of “9”s specifies number of sig-
nificant digits returned. Blanks are dis-
played for leading zeroes. A zero (0) is
displayed for a value of zero.
0 0999 Displays a leading zero or a value of zero
9990 in this position as a 0.
$ $9999 Prefixes value with dollar sign.
B B9999 Displays a zero value as blank, regardless
of “0”s in the format model.
MI 9999MI Displays “–” after a negative value. For a
positive value, a trailing space is dis-
played.
S S9999 Returns “+” for positive values and “–”
for negative values in this position.
PR 9999PR Displays a negative value in <angle brack-
ets>. For a positive value, a leading and
trailing space is displayed.
D 99D99 Displays the decimal character in this
position, separating the integral and frac-
tional parts of a number.
G 9G999 Displays the group separator in this posi-
tion.
C C999 Displays the ISO currency symbol in this
position.
L L999 Displays the local currency symbol in this
position.
, (comma) 9,999 Displays a comma in this position.
. (period) 99.99 Displays a period (decimal point) in this
position, separating the integral and frac-
tional parts of a number.
V 999V99 Multiplies value by 10 n, where n is the
number of “9’s” after the “V.”
EEEE 9.999EEEE Displays value in scientific notation (for-
mat must contain exactly four “E’s”).

SQL*Plus Quick Reference 11

 Element Example(s) Description
RN or rn RN Displays upper- or lowercase Roman nu-
merals. Value can be an integer between 1
and 3999.
DATE DATE Displays value as a date in MM/DD/YY
format; used to format NUMBER columns
that represent Julian dates.

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, on subsets of selected rows. Or,
lists all COMPUTE definitions. The following table lists
valid functions. All functions except NUMBER apply to
non-null values only.
Function Computes Applies to Datatypes
AVG Average of non-null values NUMBER
COU[NT] Count of non-null values All types
MAX[IMUM] Maximum value NUMBER, CHAR,
NCHAR, VAR-
CHAR2 (VAR-
CHAR), NVAR-
CHAR2 (NCHAR
VARYING)
MIN[IMUM] Minimum value NUMBER, CHAR,
NCHAR, VAR-
CHAR2 (VAR-
CHAR), NVAR-
CHAR2 (NCHAR
VARYING)
NUM[BER] Count of rows All types
STD Standard deviation of non- NUMBER
null values
SUM Sum of non-null values NUMBER
VAR[IANCE] Variance of non-null values NUMBER

12 SQL*Plus Quick Reference

REPF[OOTER] [PAGE] [printspec [text|variable] ...] |
[OFF|ON]
Places and formats a specified report footer at the bottom
of each report, or lists the current REPFOOTER definition.
See REPHEADER for additional information on valid
printspec clauses.
REPH[EADER] [PAGE] [printspec [text|variable] ...] |
[OFF|ON]
Places and formats a specified report header at the top of
each report, or lists the current REPHEADER definition.
Use one of the following clauses in place of printspec:
COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text
SPO[OL] [filename[.ext]|OFF|OUT]
Stores query results in an operating system file and,
optionally, sends the file to a printer. OFF stops spooling.
OUT stops spooling and sends the file to your host
computer’s standard (default) printer. Enter SPOOL with
no clauses to list the current spooling status.
TTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
Places and formats a specified title at the top of each report
page, or lists the current TTITLE definition. Use one of the
following clauses in place of printspec:
COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text

SQL*Plus Quick Reference 13

Accessing Databases
Use the following commands to access and copy data between tables
on different databases.
CONN[ECT] [username[/password][@database_spec]|/]
Connects a given username to Oracle. If you omit
database_spec, connects you to the default database. If you
omit username and/or password, SQL*Plus prompts for
them. CONNECT followed by a slash (/) connects you
using a default (ops$) logon.
DISC[ONNECT]
Commits pending changes to the database and logs the
current username off Oracle, but does not exit SQL*Plus.
COPY {FROM username[/password]@database_spec|
TO username[/password]@database_spec|
FROM username[/password]@database_spec
TO username[/password]@database_spec}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column ...)]
USING query
Copies data from one Oracle database to a table in another.
APPEND, CREATE, INSERT, or REPLACE specifies how
COPY treats the existing copy of the destination table (if it
exists). USING query identifies the source table and
determines which rows and columns COPY copies from it.
PASSW[ORD] [username]
Allows you to change password without echoing the
password on an input device.

14 SQL*Plus Quick Reference

Miscellaneous
DESC[RIBE] [schema.]object[@database_link_name]
Lists the column definitions for the specified table, view, or
synonym or the specifications for the specified function or
procedure.
SET system_variable value
Sets a system variable to alter the SQL*Plus environment
for your current session, such as setting the display width
for NUMBER data or the number of lines per page. Enter a
system variable followed by a value as shown below:
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {20|n}
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}
AUTOP[RINT] {OFF|ON}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]]
[STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|OFF|ON}
COLSEP {_|text)
COM[PATIBILITY] {V7|V8|NATIVE]
CON[CAT] {.|c|OFF|ON}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {OFF|ON}
DEF[INE] {’&’|c|OFF|ON}
ECHO {OFF|ON}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {OFF|ON}
ESC[APE] {\|c|OFF|ON}
FEED[BACK] {6|n|OFF|ON}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}
FLU[SH] {OFF|ON}
HEA[DING] {OFF|ON}
HEADS[EP] {||c|OFF|ON}
LIN[ESIZE] [80|n]

SQL*Plus Quick Reference 15

 LOBOF[FSET] {n|1}
LONG {80|n}
LONGC[HUNKSIZE] [80|n]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {OFF|ON|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR { |c}
SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT]
{WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
SHOW[MODE] {OFF|ON}
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {OFF|ON}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|OFF|ON}
SUF[FIX] {SQL|text}
TAB {OFF|ON}
TERM[OUT] {OFF|ON}
TI[ME] {OFF|ON}
TIMI[NG] {OFF|ON}
TRIM[OUT] {OFF|ON}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {–|c|ON|OFF}
VER[IFY] {OFF|ON}
WRA[P] {OFF|ON}

16 SQL*Plus Quick Reference

SHO[W] [option]
Lists the value of a SQL*Plus system variable. Use one of
the following terms or clauses in place of option:
system_variable
ALL
APPI[NFO]
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|
PACKAGE BODY|TRIGGER|VIEW|TYPE|
TYPE BODY} [schema.]name]
LABEL
LNO
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SPOO[L]
SQLCODE
TTI[TLE]
USER
Enter any system variable set by the SET command in place
of system_variable.

SQL*Plus Quick Reference 17

SQL Command List
SQL commands were formerly documented in SQL*Plus
documentation. You should now refer to the Oracle8 Server SQL
Language Reference Manual for full documentation of these commands
and clauses. Below is a list of major SQL commands:

ALTER LOCK TABLE

ANALYZE NOAUDIT
AUDIT RENAME
COMMENT REVOKE
COMMIT ROLLBACK
CREATE SAVEPOINT
DELETE SELECT
DROP SET ROLE
EXPLAIN SET TRANSACTION
GRANT TRUNCATE
INSERT UPDATE

18 SQL*Plus Quick Reference

Notes

SQL*Plus Quick Reference 19

Notes

20 SQL*Plus Quick Reference

Reader’s Comment Form

Name of Document: SQL*Plus
Quick Reference

Part No. A53718–01

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness
of this publication. Your input is an important part of the information used for revision.
• Did you find any errors?
• Is the information clearly presented?
• Do you need more information? If so, where?
• Are the examples correct? Do you need more examples?
• What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the topic, chapter,
and page number below:

Please send or email your comments to:

SQL*Plus Documentation Manager
Oracle Systems Australia Pty. Ltd
324 St. Kilda Road
Melbourne VIC 3004 Australia
+61 3 9209 1600 (telephone) +61 3 9690 0043 (fax)
[email protected]
If you would like a reply, please give your name, address, and telephone number below:

Thank you for helping us improve our documentation.