**SQL*Plus Quick Reference**

This 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.

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 meanings	`c`	A single character.
	`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.

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 ...]`

{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.

**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.

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, and so on.

@@ 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, and so on.

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.

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, &&user_variable`	Indicates a substitution variable in a SQL or SQLPlus command. SQLPlus substitutes the value of the specified user variable for each substitution variable it encounters. If the user variable is undefined, SQLPlus 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

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_ element`	Requires the following syntax:
	`{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]

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.)

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 significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero.

0
0999 9990
Displays a leading zero or a value of zero 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 displayed.

S
S9999
Returns "+" for positive values and "-" for negative values in this position.

PR
9999PR
Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed.

D
99D99
Displays the decimal character in this position, separating the integral and fractional parts of a number.

G
9G999
Displays the group separator in this position.

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 fractional parts of a number.

V
999V99
Multiplies value by 10ⁿ, where n is the number of "9's" after the "V."

EEEE
9.999EEEE
Displays value in scientific notation (format must contain exactly four "E's").

RN or rn
RN
Displays upper- or lowercase Roman numerals. 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, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
`MIN[IMUM]`	Minimum value	NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
`NUM[BER]`	Count of rows	All types
`STD`	Standard deviation of non-null values	NUMBER
`SUM`	Sum of non-null values	NUMBER
`VAR[IANCE]`	Variance of non-null values	NUMBER

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

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.

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] 
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}

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 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

Library

Product

Contents

*Element*	*Example(s)*	*Description*
9	`9999`	Number of "9"s specifies number of significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero.
0	`0999 9990`	Displays a leading zero or a value of zero 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 displayed.
S	`S9999`	Returns "+" for positive values and "-" for negative values in this position.
PR	`9999PR`	Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed.
D	`99D99`	Displays the decimal character in this position, separating the integral and fractional parts of a number.
G	`9G999`	Displays the group separator in this position.
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 fractional parts of a number.
V	`999V99`	Multiplies value by 10ⁿ, where n is the number of "9's" after the "V."
EEEE	`9.999EEEE`	Displays value in scientific notation (format must contain exactly four "E's").
RN or rn	`RN`	Displays upper- or lowercase Roman numerals. 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.