SQL*Plus User's Guide and Reference
Release 8.1.6
A75664-01
Library
Product
Contents
Index
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
-- (comment delimiter), 3-13
- (hyphen)
continuing a long SQL*Plus command, 2-12,
8-1
- (hyphen) clause, 7-2
. (period), 2-10
/ (slash) command, 8-9
entered at buffer line-number prompt, 2-8,
8-9
entered at command prompt, 2-10,
8-9
executing current PL/SQL block, 2-10
executing current SQL command, 2-10
similar to RUN, 2-10,
8-9,
8-94
/ (slash), default logon, 7-7,
8-47
# Pound sign, 8-35
$ number format, 4-5
& (ampersand)
substitution variables, 3-20
* (asterisk)
in DEL command, 3-2,
8-54
in LIST command, 3-2,
8-74
/*...*/ (comment delimiters), 3-12
: (colon)
bind variables, 3-30
:BindVariable clause
EXIT command, 8-67
; (semicolon), 2-6
-? clause, 7-2
@ ("at" sign)
in CONNECT command, 6-3,
8-47
in COPY command, 6-5,
8-49
in SQLPLUS command, 3-15,
6-3,
7-2
@ ("at" sign) command, 3-15,
3-19,
8-5
arguments, 8-5
command file, 3-15,
8-5
passing parameters to a command file, 8-5
similar to START, 3-15,
8-6,
8-134
@@ (double "at" sign) command, 3-19,
8-7
command file, 8-7
similar to START, 8-7,
8-134
[Backspace] key, 2-2
[Cancel] key, 2-2,
2-15
[Interrupt] key, 2-2
[Pause] key, 2-2,
2-17
[Resume] key, 2-2
[Return] key, 2-2
_EDITOR, in EDIT command, 3-8,
8-64
~ Infinity sign, 8-35
-~ Negative infinity sign, 8-35
0, number format, 4-5
9, number format, 4-5
A
ABORT mode, 8-130
ACCEPT command, 3-27,
8-10
and DEFINE command, 8-52
CHAR clause, 8-10
customizing prompts for value, 3-28
DATE clause, 8-10
DEFAULT clause, 8-11
FORMAT clause, 8-10
HIDE clause, 8-11
NOPROMPT clause, 8-11
NUMBER clause, 3-29
PROMPT clause, 3-27,
8-11
Access, denying and granting, E-2
Account, GL-1,
GL-24
Alias, GL-1,
GL-23
ALIAS clause, 8-32
in ATTRIBUTE command, 8-18
Alignment, GL-1
ALL clause, 8-125
ALTER command
disabling, E-5
Ampersands (&)
in parameters, 3-25,
8-5,
8-133
substitution variables, 3-20
ANALYZE command
disabling, E-5
anonymous block, GL-1
APPEND clause
in COPY command, 6-6,
8-50
in SAVE command, 3-17,
8-95
APPEND command, 3-2,
3-6,
8-12
APPINFO variable, 8-98
ARCH background process, 8-15
ARCHIVE LOG
command, 5-4
mode, 5-4
ARCHIVE LOG command, 8-14
Archived redo log, GL-1
ARCHIVELOG, GL-1
Argument, GL-2
argument
in START command, 3-25,
8-133
Array processing, GL-2
ARRAYSIZE variable, 8-99
relationship to COPY command, 6-8,
8-51
ASCII, GL-2
Attribute
display characteristics, 8-18
ATTRIBUTE command, 8-18
ALIAS clause, 8-18
and CLEAR COLUMN command, 8-19
CLEAR clause, 8-19
clearing columns, 8-29,
8-32
controlling display characteristics, 8-19
entering multiple, 8-19
FORMAT clause, 8-19
LIKE clause, 8-19
listing all attributes' display characteristics, 8-18
listing an attribute's display characteristics, 8-18
OFF clause, 8-19
ON clause, 8-19
restoring column display attributes, 8-19
suppressing column display attributes, 8-19
AUDIT command
disabling, E-5
Autocommit, GL-2
AUTOCOMMIT variable, 2-14,
8-99
AUTOMATIC clause, 8-81
AUTOPRINT variable, 8-100
AUTORECOVERY variable, 8-100
AUTOTRACE variable, 3-35,
8-100
AVG function, 4-17
B
Background process, GL-2
background process
startup after abnormal termination, 8-130
Batch mode, 3-17,
8-68
BEGIN command, 2-10
disabling, E-6
Bind reference, GL-2
Bind variables, 3-30,
GL-2,
GL-25
creating, 8-147
displaying, 8-78
displaying automatically, 8-100,
8-148
in PL/SQL blocks, 8-148
in SQL statements, 8-148
in the COPY command, 8-148
REFCURSOR, 3-32
Bit, GL-3
Blank line
in PL/SQL blocks, 2-10
in SQL commands, 2-8
preserving in SQL commands, 8-111
Block, GL-3
Blocks, PL/SQL, 1-2
continuing, 2-10
editing in buffer, 3-2
editing with host system editor, 3-8,
8-64
entering and executing, 2-10
listing current in buffer, 3-3
run from SQL buffer, 2-10
saving current, 3-9,
8-95
setting character used to end, 8-101
stored in SQL buffer, 2-10
storing in command files, 3-8
timing statistics, 8-113
within SQL commands, 2-8
BLOCKTERMINATOR, 8-101,
8-111,
8-112
Body, GL-3
BODY clause, 8-107
BODY option, 7-3
BOLD clause, 8-91,
8-143
Break, GL-3
Break columns, 4-11,
8-20,
GL-3
inserting space when value changes, 4-13
specifying multiple, 4-14
suppressing duplicate values in, 4-12
BREAK command, 4-11,
8-20
and SQL ORDER BY clause, 4-11,
4-12,
4-14,
8-21
clearing BREAKS, 4-16
displaying column values in titles, 4-30
DUPLICATES clause, 8-23
inserting space after every row, 4-14
inserting space when break column changes, 4-13
listing current break definition, 4-16,
8-23
ON column clause, 4-12,
8-20
ON expr clause, 8-22
ON REPORT clause, 4-20,
8-22
ON ROW clause, 4-14,
8-22
printing "grand" and "sub" summaries, 4-20
printing summary lines at ends of reports, 4-20
removing definition, 8-29
SKIP clause, 4-14,
8-23
SKIP PAGE clause, 4-13,
4-14,
8-23
specifying multiple break columns, 4-14,
8-21
storing current date in variable for titles, 4-32
suppressing duplicate values, 4-12
used in conjunction with COMPUTE, 4-16,
8-20,
8-21,
8-22,
8-43
used in conjunction with SET COLSEP, 8-102
used to format a REFCURSOR variable, 8-149
Break definition
listing current, 4-16,
8-23
removing current, 4-16,
8-29
Break group, GL-3
Break hierarchy, GL-3
Break order, GL-3
Break report, GL-3
BREAKS clause, 4-16,
8-29
BTITLE clause, 8-126
BTITLE command, 4-23,
8-25,
GL-24
aligning title elements, 8-143
BOLD clause, 8-143
CENTER clause, 8-143
COL clause, 8-143
FORMAT clause, 8-144
indenting titles, 8-143
LEFT clause, 8-143
most often used clauses, 4-23
OFF clause, 8-143
old form, F-3
printing blank lines before bottom title, 4-26
referencing column value variable, 8-37
RIGHT clause, 8-143
SKIP clause, 8-143
suppressing current definition, 8-143
TAB clause, 8-143
TTITLE command, 8-25
Buffer, 2-9,
GL-4,
GL-21
appending text to a line in, 3-6,
8-12
delete a single line, 3-2
delete the current line, 3-2
delete the last line, 3-2
deleting a range of lines, 3-2,
8-54
deleting a single line, 8-54
deleting all lines, 3-2,
8-29,
8-54
deleting lines from, 3-7,
8-54
deleting the current line, 8-54
deleting the last line, 8-54
executing contents, 2-10,
8-9,
8-94
inserting new line in, 3-5,
8-72
listing a range of lines, 3-3,
8-74
listing a single line, 3-2,
8-74
listing all lines, 3-2,
8-74
listing contents, 3-3,
8-74
listing the current line, 3-2,
8-74
listing the last line, 3-3,
8-74
loading into host system editor, 3-8,
8-64
saving contents, 3-9,
8-95
BUFFER clause, 3-2,
3-10,
8-29
BUFFER variable, F-4
byte, GL-4
C
CANCEL clause, 8-83
CENTER clause, 4-26,
8-91,
8-143
CGI scripts, 4-42,
GL-6
CHANGE command, 3-2,
3-4,
8-26
CHAR clause, 8-10
VARIABLE command, 8-147
CHAR columns
changing format, 4-7,
8-33
default format, 4-6,
8-33
CHAR datatype, GL-4
Character, GL-4
Character string, GL-4
Clause, GL-4
CLEAR clause, 4-9,
8-32
in ATTRIBUTE command, 8-19
CLEAR command, 8-29
BREAKS clause, 4-16,
8-29
BUFFER clause, 3-2,
3-10,
8-29
COLUMNS clause, 4-9,
8-29
COMPUTES clause, 8-29
SCREEN clause, 3-30,
8-29
SQL clause, 8-30
TIMING clause, 8-30
Client, GL-4
CLOB clause
VARIABLE command, 8-148
CLOB columns
changing format, 4-7,
8-33
default format, 8-33
setting maximum width, 8-106
setting retrieval position, 8-106
setting retrieval size, 8-106
CLOB datatype, GL-4
CLOSECURSOR variable, F-2,
F-5
CMDSEP variable, 8-101
COL clause, 4-27,
8-91,
8-143
Colons (:)
bind variables, 3-30
COLSEP variable, 8-101
Column, GL-5
format, GL-24
width, GL-25
COLUMN command, 4-2,
8-31
ALIAS clause, 8-32
and BREAK command, 8-22
and DEFINE command, 8-52
CLEAR clause, 4-9,
8-32
DEFAULT clause, F-3
displaying column values in bottom titles, 4-31,
8-37
displaying column values in top titles, 4-30,
8-36
entering multiple, 8-37
FOLD_AFTER clause, 8-32
FOLD_BEFORE clause, 8-32
FORMAT clause, 4-5,
4-7,
8-33
formatting columns, 4-7
formatting NUMBER columns, 4-5,
8-33
HEADING clause, 4-2,
8-35
HEADSEP character, 8-35
JUSTIFY clause, 8-36
LIKE clause, 4-8,
8-36
listing a column's display attributes, 4-9,
8-31
listing all columns' display attributes, 8-31
listing all columnsdisplayattributes', 4-9
NEW_VALUE clause, 4-30,
4-32,
8-36
NEWLINE clause, 8-36
NOPRINT clause, 4-30,
8-36
NULL clause, 8-36
OFF clause, 4-9,
8-37
OLD_VALUE clause, 4-31,
8-37
ON clause, 4-9,
8-37
PRINT clause, 8-36
resetting a column to default display, 4-9,
8-32,
F-2
restoring column display attributes, 4-9,
8-37
storing current date in variable for titles, 4-32,
8-39
suppressing column display attributes, 4-9,
8-37
TRUNCATED clause, 4-8,
8-37
used to format a REFCURSOR variable, 8-149
WORD_WRAPPED clause, 4-8,
4-10,
8-37
WRAPPED clause, 4-8,
8-37
Column expression, GL-5
Column headings
aligning, 8-36
changing, 4-2,
8-35
changing character used to underline, 4-3,
8-113
changing to two or more words, 4-3,
8-35
displaying on more than one line, 4-3,
8-35
suppressing printing in a report, 8-104
when truncated, 8-33
when truncated for CHAR and LONG columns, 4-7
when truncated for DATE columns, 4-7
when truncated for NUMBER columns, 4-4
Column separator, 8-101,
F-2
Columns
assigning aliases, 8-32
computing summary lines, 4-16,
8-41
copying display attributes, 4-8,
8-19,
8-36
copying values between tables, 6-4,
6-8,
8-49
displaying values in bottom titles, 4-31,
8-37
displaying values in top titles, 4-30,
8-36
formatting CHAR, VARCHAR, LONG, and DATE, 8-33
formatting in reports, 4-2,
8-31
formatting MLSLABEL, RAW MLSLABEL, ROWLABEL, 8-33
formatting NUMBER, 4-5,
8-33
listing display attributes for all, 4-9,
8-31
listing display attributes for one, 4-9,
8-31
names in destination table when copying, 6-5,
8-50
printing line after values that overflow, 4-10,
8-109
resetting a column to default display, 4-9,
8-32,
F-2
resetting all columns to default display, 4-9,
8-29
restoring display attributes, 4-9,
8-19,
8-37
setting printing to off or on, 4-30,
8-36
starting new lines, 8-36
storing values in variables, 4-30,
8-36
suppressing display attributes, 4-9,
8-19,
8-37
truncating display for all when value overflows, 4-8,
8-113
truncating display for one when value overflows, 4-8,
8-37
wrapping display for all when value overflows, 4-7,
8-113
wrapping display for one when value overflows, 4-8,
8-37
wrapping whole words for one, 4-10
COLUMNS clause, 4-9,
8-29
Comma, number format, 4-5
Command, GL-5
Command file, GL-5
Command file extension, 8-95,
8-112,
8-139
Command files, 3-8
aborting and exiting with a return code, 3-17,
8-154,
8-156
allowing end-user input, 3-19
creating with a system editor, 3-11
creating with INPUT and SAVE, 3-10
creating with SAVE, 3-9,
8-95
editing with GET and SAVE, 3-16
editing with host system editor, 3-16,
8-64
in @ ("at" sign) command, 3-15,
8-5
in @@ (double "at" sign) command, 8-7
in EDIT command, 3-16,
8-64
in GET command, 3-13,
8-69
in SAVE command, 3-9,
3-10,
8-95
in SQLPLUS command, 3-15,
7-8
in START command, 3-14,
8-133
including comments in, 3-12,
8-87
including more than one PL/SQL block, 3-11
including more than one SQL command, 3-11
listing names with HOST command, 3-9
nesting, 3-16
passing parameters to, 3-25,
8-5,
8-133
registering, 8-98
retrieving, 3-13,
8-69
running, 3-14,
8-5,
8-133
running a series in sequence, 3-16
running as you start SQL*Plus, 3-15,
7-8
running in batch mode, 3-17,
8-68
running nested, 8-7
saving contents of buffer in, 3-9,
8-95
Command prompt, GL-5
host operating system, 2-3
SQL*Plus, 2-4
Commands, 1-2
case, 2-5
collecting timing statistics on, 2-15,
8-140
disabling, E-4
host, running from SQL*Plus, 2-15,
8-71
listing current in buffer, 8-74
re-enabling, E-5
spaces, 2-5
SQL
continuing on additional lines, 2-7
editing in buffer, 3-2
editing with host system editor, 3-8,
8-64
ending, 2-8
entering and executing, 2-6
entering without executing, 2-8
executing current, 2-10,
8-9,
8-94
following syntax, 2-7
list of major, D-1
listing current in buffer, 3-3
saving current, 3-9,
8-95
setting character used to end and run, 8-112
SQL*Plus
abbreviations, 2-11
command summary, 8-2
continuing on additional lines, 2-12,
8-1
editing at command prompt, 3-2
ending, 2-13,
8-1
entering and executing, 2-11
entering during SQL command entry, 8-112
obsolete command alternatives, F-2
stopping while running, 2-15
storing in command files, 3-8
syntax conventions, 1-4
tabs, 2-5
types of, 2-5
variables that affect running, 2-13
writing interactive, 3-19
Comments, GL-5,
GL-19
including in command files, 3-12,
8-87,
F-2
using -- to create, 3-13
using /*...*/ to create, 3-12
using REMARK to create, 3-12,
8-87,
F-2
Commit, GL-6
COMMIT clause, 8-67
WHENEVER OSERROR, 8-153
WHENEVER SQLERROR, 8-155
COMMIT command, 2-13
Common Gateway Interface, GL-6
Communications, GL-14
COMPATIBILITY clause
in LOGIN.SQL, 3-18
COMPATIBILITY variable, 8-102
Computation, GL-6
COMPUTE command, 4-11,
8-41
AVG function, 4-17
computing a summary on different columns, 4-21
COUNT function, 4-17
LABEL clause, 4-17,
4-20,
8-42
listing all definitions, 4-22,
8-43
MAXIMUM function, 4-17
maximum LABEL length, 8-42
MINIMUM function, 4-17
NUMBER function, 4-17
OF clause, 4-16
ON column clause, 4-16,
8-42
ON expr clause, 8-42
ON REPORT clause, 4-20,
8-42
ON ROW clause, 8-42
printing "grand" and "sub" summaries, 4-20
printing multiple summaries on same column, 4-21
printing summary lines at ends of reports, 4-20
printing summary lines on a break, 4-16
referencing a SELECT expression in OF, 8-42
referencing a SELECT expression in ON, 8-43
removing definitions, 4-22,
8-29
STD function, 4-17
SUM function, 4-17
used to format a REFCURSOR variable, 8-149
VARIANCE function, 4-17
Computed column, GL-6
COMPUTES clause, 8-29
CONCAT variable, 3-25,
8-102
Concurrency
instances, multiple, 8-136
Configuration, GL-6
Configuration files, GL-6
Connect, GL-6
CONNECT command
changing password, 8-76
net_service_name, 6-3
SYSDBA clause, 8-48
CONNECT command (SQL)
disabling, E-6
CONNECT command (SQL*Plus), 6-2,
6-3,
8-47
and @ ("at" sign), 6-3,
8-47
changing password, 8-47,
8-48
service name, 6-3
username/password, 6-2,
6-3,
6-4,
8-47
Connect string, GL-6
connect string, 6-3
Continuation character, 4-39
CONTINUE clause
WHENEVER OSERROR, 8-153
WHENEVER SQLERROR, 8-155
Continuing a long SQL*Plus command, 2-12,
8-1
Conventions, command syntax, 1-4
COPY command, 6-4,
8-49
and @ ("at" sign), 6-5,
8-49
and ARRAYSIZE variable, 6-8,
8-51
and COPYCOMMIT variable, 6-8,
8-51
and LONG variable, 6-8,
8-51
APPEND clause, 6-6,
8-50
copying data between databases, 6-4
copying data between tables on one database, 6-8
CREATE clause, 6-6,
8-50
creating a table, 6-6,
8-50
destination table, 6-5,
8-50
determining actions, 6-5
determining source rows and columns, 6-6,
8-50
disabling, E-5
FROM clause, 6-5,
8-49
INSERT clause, 6-6,
8-50
inserting data in a table, 6-6,
8-50
interpreting messages, 6-8
mandatory net service name, 8-49
naming the source table with SELECT, 6-6,
8-50
query, 6-6,
8-50
referring to another user's table, 6-8
REPLACE clause, 6-6,
8-50
replacing data in a table, 6-6,
8-50
sample command, 6-5,
6-6
service name, 6-5,
6-7,
6-8
specifying column names for destination, 8-50
specifying the data to copy, 6-6,
8-50
TO clause, 6-5,
8-49
username/password, 6-5,
6-7,
6-8,
8-49
USING clause, 6-6,
8-50
when a commit is performed, 8-51
Copy command
specifying column names for destination, 6-5
COPYCOMMIT variable, 8-102
relationship to COPY command, 6-8,
8-51
COPYTYPECHECK variable, 8-102
COUNT function, 4-17
CREATE clause
in COPY command, 6-6,
8-50
CREATE command
disabling, E-6
entering PL/SQL, 2-8
Creating flat files, 4-35
Creating the PRODUCT_USER_PROFILE table, E-2
Current line, GL-7
Cursor Variables, 8-148
D
Data Control Language, GL-8
Data Definition Language, GL-8
Data dictionary, GL-8
Data Manipulation Language, GL-8
Data security, GL-8
Database, GL-7
dismounted, GL-9
failure, GL-7
link, GL-7
local, GL-12
mounting, 8-136
object, GL-7
open, GL-15
opening, 8-136
schema, GL-20
server, GL-7
string, GL-8
transaction, GL-24
USERID, GL-24
username, GL-24
database
specification, GL-8
Database administrator, 1-7,
5-2,
GL-7
Database changes, saving automatically, 2-13,
8-99
DATABASE clause, 8-82
Database files
recovering, 8-81
Database name at startup, 8-135
Databases
and RECOVERY command, 8-81
connecting to default, 6-2,
8-47
connecting to remote, 6-3,
8-47
copying data between, 6-4,
8-49
copying data between tables on a single, 6-8
disconnecting without leaving SQL*Plus, 6-2,
8-63
mounting, 5-3
opening, 5-3
recovering, 5-5,
8-81
shutting down, 5-2,
5-3
starting, 5-2
DATAFILE clause, 8-83
Datatype, GL-9
LONG, GL-13
NCHAR, GL-14
NCLOB, GL-14
NUMBER, GL-14
NVARCHAR2, GL-15
RAW, GL-18
VARCHAR, GL-25
VARCHAR2, GL-25
DATE
datatype, GL-9
Julian, GL-12
DATE clause, 8-10
DATE columns
changing format, 4-7,
8-33,
8-40
default format, 4-6
Date, storing current in variable for titles, 4-32,
8-36,
8-39
DB2, 8-102
DBA, 5-2,
GL-7
DBA mode, 8-135
DBA privilege, 8-135
DBMS_APPLICATION_INFO package, 8-98
DCL, GL-8
DDL, GL-8
DECLARE command
disabling, E-6
DECLARE command (PL/SQL), 2-10
Default, GL-9
database, GL-9
DEFAULT clause, 8-11
DEFINE command, 3-20,
8-52,
GL-24
and host system editor, 3-8,
8-53
CHAR values, 8-52
substitution variables, 3-23,
8-52
DEFINE variable, 3-25,
8-102
DEL command, 3-2,
3-7,
8-54
using an asterisk, 3-2,
8-54
DELETE command
disabling, E-6
DEMOBLD, 1-8
DEMODROP, 1-8
DEPT table, 1-6
DESCRIBE command (SQL*Plus), 2-16,
8-56
net service name, 8-56
PL/SQL properties listed by, 8-57
table properties listed by, 8-56
Directory, GL-9
DISABLED keyword, disabling commands, E-4
Disabling
PL/SQL commands, E-6
SQL commands, E-4
SQL*Plus commands, E-4
DISCONNECT command, 6-2,
8-63
Dismounted database, GL-9
Display
format, GL-9
width, GL-10
DML, GL-8
DOCUMENT command
Obsolete commands
DOCUMENT, F-2,
F-3
REMARK as newer version of, F-4
DOCUMENT variable, F-2,
F-5
DROP command
disabling, E-6
DUAL table, GL-10
DUPLICATES clause, 8-23
E
ECHO variable, 3-15,
8-103
EDIT command, 3-8,
8-64
creating command files with, 3-11
defining _EDITOR, 3-8,
8-64
disabling, E-5
modifying command files, 3-16,
8-64
setting default file name, 8-103
EDITFILE variable, 8-103
Editor, GL-10
EMBEDDED variable, 8-103
EMP table, 1-6
Empty line, displaying, 8-77
End user, GL-10
Enhancement list, Release 8.1, B-1
ENTMAP, 7-4
ENTMAP clause, 4-42,
8-108
Error message, GL-10
Error messages, interpreting, 2-17
Errors, making line containing current, 3-4
Escape characters, definition of, 8-103
ESCAPE variable, 3-25,
8-103
Example
embedded CGI report, 4-42
interactive HTML report, 4-39
EXCLUSIVE clause, 8-135
EXECUTE command, 8-66
disabling, E-5
Executing
a CREATE command, 2-8
Execution statistics
including in report, 8-100
EXIT clause
WHENEVER OSERROR, 8-153
WHENEVER SQLERROR, 8-155
EXIT command, 2-4,
8-67
:BindVariable clause, 8-67
COMMIT clause, 8-67
disabling, E-5
FAILURE clause, 8-67
in a command file, 8-134
ROLLBACK clause, 8-67
use with SET MARKUP, 4-39
WARNING clause, 8-67
Exit, conditional, 8-153,
8-155
Expression, GL-10
Extension, 8-95,
8-112,
8-139
extension, GL-10
F
FAILURE clause, 8-67
FEEDBACK variable, 8-104
File, GL-10
File extension, 8-95,
8-112,
8-139
File extensions, 3-18
File names
in @ ("at" sign) command, 8-5
in @@ (double "at" sign) command, 8-7
in EDIT command, 8-64
in GET command, 8-69
in SAVE command, 3-9,
8-95
in SPOOL command, 4-36,
8-132
in SQLPLUS command, 7-8
in START command, 8-133
File type, GL-11
Filename, GL-11
Files
command files, 3-8
flat, 4-35
FLAGGER variable, 8-104
Flat file, 4-35
FLUSH variable, 8-104
FOLD_AFTER clause, 8-32
FOLD_BEFORE clause, 8-32
Footers
aligning elements, 8-91
displaying at bottom of page, 8-88
displaying system-maintained values, 8-90
formatting elements, 8-91
indenting, 8-91
listing current definition, 8-88
setting at the end of reports, 4-23
suppressing definition, 8-91
FORCE clause, 8-135
Form feed, GL-11
Format, GL-11
column, GL-24
model, GL-11
FORMAT clause, 8-10
in ATTRIBUTE command, 8-19
in COLUMN command, 4-5,
4-7,
8-33
in REPHEADER and REPFOOTER commands, 8-91
in TTITLE and BTITLE commands, 4-29,
8-144
Format models, number, 4-5,
8-35
Formfeed, to begin a new page, 4-33,
8-109
Forms, GL-15
Formula column, GL-11
FROM clause, 8-82
FROM clause (SQL*Plus), 6-5,
8-49
Function, GL-11
G
GET command, 3-13,
8-69
disabling, E-5
LIST clause, 8-69
modifying command files, 3-16
NOLIST clause, 8-69
retrieving command files, 3-13,
8-69
GLOGIN.SQL, 3-17,
3-36,
3-40,
7-8
See also
LOGIN.SQL
GRANT command, E-2
disabling, E-6
H
HEAD clause, 8-107
HEAD option, 7-3
Headers
aligning elements, 4-25
displaying at top of page, 8-90
displaying system-maintained values, 8-90
setting at the start of reports, 4-23
suppressing, 4-25
Heading, GL-11
HEADING clause, 4-2,
8-35
HEADING variable, 8-104
Headings
aligning elements, 8-91
column headings, 8-104
formatting elements, 8-91
indenting, 8-91
listing current definition, 8-92
suppressing definition, 8-91
HEADSEP variable, 8-105
use in COLUMN command, 4-3,
8-35
Help command, 8-70
Help, online, 2-5,
7-10,
8-70
HIDE clause, 8-11
HOST command, 2-16,
8-71
disabling, E-5
listing command file names with, 3-9
Host computer, GL-11
Host operating system
command prompt, 2-3
editor, 3-8,
8-64
file, loading into buffer, 8-69
running commands from SQL*Plus, 2-15,
8-71
HTML, 4-38,
4-42,
GL-12
entities, 4-42
spooling to file, 8-107
HTML clause, 8-106
HTML option, 7-3
Hyphen
continuing a long SQL*Plus command, 2-12,
8-1
I
IMMEDIATE mode, 8-130
Infinity sign (~), 8-35
Initialization parameters
displaying, 8-126
INIT.ORA file
Parameter file, 8-135
Input
accepting [Return], 3-29,
8-77
accepting values from the user, 3-27,
8-10
INPUT command, 3-2,
3-5,
8-72
entering several lines, 8-72
using with SAVE to create command files, 3-10
INSERT clause, 6-6,
8-50
INSERT command
disabling, E-6
Instance, GL-12
failure, GL-7
recovery, GL-12
INSTANCE variable, 8-105
Instances
multiple, sharing a database, 8-136
shutting down, 8-130
starting, 8-135
J
JDeveloper Suite, 1-3
Julian date, GL-12
Justification, GL-12
JUSTIFY clause, 8-36
K
Keyboard, significance of keys on, 2-2
Keys
[Backspace] key, 2-2
[Cancel] key, 2-2
[Interrupt] key, 2-2
[Pause] key, 2-2
[Resume] key, 2-2
[Return] key, 2-2
L
Label, GL-12
LABEL variable
SHOW command, F-2,
F-7
Labels
in COMPUTE command, 4-17,
8-42
Layout object
width, GL-25
LEFT clause, 4-26,
8-91,
8-143
LGWR, GL-13
LIKE clause, 4-8,
8-19,
8-36
Limits, SQL*Plus, C-1
Line numbers, for SQL commands, 2-6
Line wrapping, GL-25
Lines
adding at beginning of buffer, 8-72
adding at end of buffer, 8-72
adding new after current, 3-5,
8-72
appending text to, 3-6,
8-12
changing width, 4-33,
8-105
deleting all in buffer, 8-54
deleting from buffer, 3-7,
8-54
determining which is current, 3-4
editing current, 3-4
listing all in buffer, 3-2,
8-74
removing blanks at end, 8-113
LINESIZE variable, 4-25,
4-33,
8-105
LIST clause, 8-14,
8-69
LIST command, 3-2,
8-74
determining current line, 3-4,
8-74
making last line current, 3-4,
8-74
using an asterisk, 3-2,
8-74
LNO clause, 8-126
LOBOFFSET variable, 8-106
Local database, GL-12
LOCK TABLE command
disabling, E-6
Log in, GL-13
Log off, GL-13
Log on, GL-13
Log out, GL-13
Log Writer, GL-13
LOG_ARCHIVE_DEST parameter, 8-14
LOGFILE clause, 8-83
Logging off
conditionally, 8-153,
8-155
Oracle, 6-2,
8-63
SQL*Plus, 2-4,
8-67
Logging on
Oracle, 6-2,
6-3,
8-47
SQL*Plus, 2-4
LOGIN.SQL, 3-17,
7-8
including SET commands, 3-18
sample commands to include, 3-18
See also
GLOGIN.SQL
storing current date in variable for titles, 4-32
Logon string, GL-13
LONG columns
changing format, 4-7,
8-33
default format, 8-33
setting maximum width, 8-106
setting retrieval size, 8-106
LONG datatype, GL-13
LONG variable, 8-106
effect on COPY command, 6-8,
8-51
LONGCHUNKSIZE variable, 4-7,
8-33,
8-106
M
-MARKUP, 4-38,
7-2
SQLPLUS command clause, 7-3
MARKUP, 4-38,
GL-13
BODY clause, 8-107
ENTMAP clause, 8-108
example, 8-120
HEAD clause, 8-107
PREFORMAT clause, 8-108
SPOOL clause, 8-106
MARKUP clause, 4-38,
7-2,
8-106
-MARKUP option, 4-42
MAXDATA variable, F-2,
F-6
MAXIMUM function, 4-17
Media recovery, 8-136
Message, sending to screen, 3-27,
8-79
MINIMUM function, 4-17
MOUNT clause, 8-136
Mounted, GL-13
Mounting
shared databases, 8-136
Mounting a database, 8-136
Multi-threaded server, GL-14
N
NCHAR clause
VARIABLE command, 8-147
NCHAR columns
changing format, 4-7,
8-33
default format, 4-6,
8-33
NCHAR datatype, GL-14
NCLOB clause
VARIABLE command, 8-148
NCLOB columns
changing format, 4-7,
8-33
default format, 8-33
setting maximum width, 8-106
setting retrieval position, 8-106
setting retrieval size, 8-106
NCLOB datatype, GL-14
Negative infinity sign (-~), 8-35
Net service names
in DESCRIBE command, 8-56
net_service_name, 6-3
Net8, GL-14,
GL-21
Net8 protocol, 6-3
Network, GL-14
NEW_VALUE clause, 4-30,
8-36
storing current date in variable for titles, 4-32,
8-36,
8-39
NEWLINE clause, 8-36
NEWPAGE command, F-2,
F-4
NEWPAGE variable, 4-33,
8-109
NEXT clause, 8-15
NLS_DATE_FORMAT, 8-10,
8-40
NOAUDIT command
disabling, E-6
NOLIST clause, 8-69
NOLOG, 7-7
/NOLOG option, 7-7
NOMOUNT clause, 8-136
NONE clause
WHENEVER OSERROR, 8-153
WHENEVER SQLERROR, 8-155
NOPARALLEL clause, 8-85
NOPRINT clause, 4-17,
4-30,
8-36
NOPROMPT clause, 8-11
NORMAL mode, 8-130
NULL clause, 8-36
NULL value, GL-14
Null values
setting text displayed, 8-36,
8-109
NULL variable, 8-109
NUMBER clause, 3-29,
8-10
VARIABLE command, 8-147
NUMBER columns
changing format, 4-5,
8-33
default format, 4-4,
8-35
NUMBER datatype, GL-14
Number formats
$, 4-5
0, 4-5
9, 4-5
comma, 4-5
setting default, 8-109
NUMBER function, 4-17
NUMFORMAT clause
in LOGIN.SQL, 3-18
NUMFORMAT variable, 8-109
NUMWIDTH variable, 8-109
effect on NUMBER column format, 4-4,
8-35
NVARCHAR2 columns
changing format, 4-7,
8-33
default format, 4-6,
8-33
NVARCHAR2 datatype, GL-15
O
Object, GL-15
Object type, GL-15
Object-relational model, GL-15
Objects
describing, 8-103
Obsolete commands
BTITLE, F-3
COLUMN command DEFAULT clause, F-3
NEWPAGE, F-2,
F-4
SET command BUFFER variable, F-4
SET command CLOSECURSOR variable, F-2,
F-5
SET command DOCUMENT variable, F-2,
F-5
SET command MAXDATA variable, F-2,
F-6
SET command SCAN variable, F-2,
F-6
SET command SPACE variable, F-2,
F-6
SET command TRUNCATE variable, F-2,
F-7
SHOW command LABEL variable, F-2,
F-7
TTITLE command old form, F-7
OF clause, 4-16
OFF clause
in ATTRIBUTE command, 8-19
in COLUMN command, 4-9,
8-37
in REPFOOTER commands, 8-91
in REPHEADER commands, 8-91
in SPOOL command, 4-35,
8-132
in TTITLE and BTITLE commands, 4-29,
8-143
OLD_VALUE clause, 4-31,
8-37
ON clause
in ATTRIBUTE command, 8-19
in COLUMN command, 4-9,
8-37
in TTITLE and BTITLE commands, 4-30
ON column clause
in BREAK command, 4-12,
8-20
in COMPUTE command, 4-16,
8-42
ON expr clause
in BREAK command, 8-22
in COMPUTE command, 8-42
ON REPORT clause
in BREAK command, 4-20,
8-22
in COMPUTE command, 4-20,
8-42
ON ROW clause
in BREAK command, 4-14,
8-22
in COMPUTE command, 8-42
Online Help, 7-10
Online help, 2-5,
7-10,
8-70
Online redo log, GL-15
OPEN clause, 8-136
Open database, GL-15
Opening a database, 8-136
Operating system, GL-15
Oracle
USERID, GL-24
Oracle Application Server, 1-3
Oracle Designer, 1-3
Oracle Developer, 1-3
Oracle Discoverer, 1-3
Oracle Enterprise Developer Suite, 1-3
Oracle Express, 1-3
Oracle Forms, GL-15
Oracle Media Objects, 1-3
Oracle Mobile Agents, 1-3
Oracle Programmer, 1-3
Oracle RDBMS, GL-16
Oracle Reports, 1-3
Oracle Server, GL-16
Oracle Workflow, 1-3
ORDER BY clause
displaying column values in titles, 4-30
displaying values together in output, 4-11
OUT clause, 4-36,
8-132
Output, GL-16
formatting white space in, 8-112
pausing during display, 2-17,
8-109
query results, 1-2
Output file, GL-16
P
Packages, GL-16
Page, GL-16
PAGE clause, 8-90
Page number, including in titles, 4-15,
4-28
Pages
changing length, 4-33,
8-109
default dimensions, 4-32
matching dimensions to screen or paper size, 4-33
setting dimensions, 4-32
PAGESIZE variable, 2-7,
4-33,
8-109
PAGESIZEclause
in LOGIN.SQL, 3-18
PARALLEL clause, 8-136
Parallel server, GL-16
Parameter, GL-17
width, GL-25
Parameter file, GL-17
Parameter files (INIT.ORA files)
specifying alternate, 8-135
Parameters, 3-25,
8-5,
8-133
PARAMETERS clause, 8-126
Password, 1-7,
GL-17
changing with the PASSWORD command, 8-76
in CONNECT command, 6-2,
6-3,
8-47
in COPY command, 6-5,
6-7,
6-8
in SQLPLUS command, 2-3,
6-4,
7-7
PASSWORD command, 8-47,
8-76
disabling, E-5
Paths, creating
Installation and User's Guide, 8-51
PAUSE command, 3-29,
8-77
in LOGIN.SQL, 3-18
PAUSE variable, 2-17,
8-109
Performance
of SQL statements, 3-35
Performance, over dial-up lines, 8-113
Period (.)
terminating PL/SQL blocks, 2-10,
8-101
PLAN_TABLE table, 3-36
PL/SQL, 1-2,
2-10,
GL-17
blocks, PL/SQL, 2-10
executing, 8-66
formatting output in SQL*Plus, 8-148
listing definitions, 2-17
mode in SQL*Plus, 2-8
within SQL commands, 2-8
PLUSTRACE role, 3-36
PNO clause, 8-127
Pound sign (#), 8-35
PREFORMAT, 7-5
PREFORMAT clause, 8-108
Print
spooling, GL-21
PRINT clause, 8-36
PRINT command, 8-78
Printing
bind variables automatically, 8-100
REFCURSOR variables, 8-149
SPOOL command, 8-132
Procedure, GL-17
Process, GL-17
PRODUCT_USER_PROFILE table, E-2
Prompt, GL-18
PROMPT clause, 3-27,
8-11
PROMPT command, 3-27,
8-79
customizing prompts for value, 3-28
Prompts for value
bypassing with parameters, 3-25
customizing, 3-28
through ACCEPT, 3-27
through substitution variables, 3-21
PUPBLD.SQL, E-2
Q
Queries, 1-2
displaying number of records retrieved, 2-7,
8-104
in COPY command, 6-6,
8-50
Query, GL-18
results, GL-18,
GL-19
Query execution path
including in report, 8-100
Query results, 1-2
displaying on-screen, 2-6
sending to a printer, 4-36,
8-132
storing in a file, 4-36,
8-132
QUIT command, 8-67
See also
EXIT
R
RAW datatype, GL-18
RDBMS, GL-16,
GL-18,
GL-19
Record, GL-18,
GL-19
Record separators, printing, 4-10,
8-109
RECOVER
clause, 8-136
Recover, GL-18
RECOVER command, 8-81
AUTOMATIC clause, 8-81
CANCEL clause, 8-83
CONTINUE clause, 8-83
DATABASE clause, 8-82
DATAFILE clause, 8-83
FROM clause, 8-82
LOGFILE clause, 8-83
NOPARALLEL clause, 8-85
STANDBY DATABASE clause, 8-82
STANDBY DATAFILE clause, 8-83
STANDBY TABLESPACE clause, 8-83
TABLESPACE clause, 8-83
UNTIL CANCEL clause, 8-82
UNTIL CHANGE clause, 8-82
UNTIL CONTROLFILE clause, 8-83
UNTIL TIME clause, 8-82
USING BACKUP CONTROL FILE clause, 8-83
Recovery
RECOVER command, 8-81
RECOVERY command
and database recovery, 5-5
RECSEP variable, 4-10,
8-109
RECSEPCHAR variable, 4-10,
8-109
Redo log, GL-15,
GL-18
file, GL-18
Redo Log Files
ARCHIVE LOG command, 8-14
REFCURSOR bind variables, 3-32
in a stored function, 3-32
REFCURSOR clause
VARIABLE command, 8-148
Relational Database Management System, GL-18,
GL-19
RELEASE clause, 8-127
Remark, GL-19
REMARK command, 3-12,
8-87,
GL-19
Remote computer, GL-19
Remote database, GL-19
RENAME command
disabling, E-6
REPFOOTER clause, 8-127
REPFOOTER command, 4-23,
8-88
aligning footer elements, 8-91
BOLD clause, 8-91
CENTER clause, 8-91
COL clause, 8-91
FORMAT clause, 8-91
indenting report footers, 8-91
LEFT clause, 8-91
most often used clauses, 4-23
OFF clause, 8-91
RIGHT clause, 8-91
SKIP clause, 8-91
suppressing current definition, 8-91
TAB clause, 8-91
REPHEADER clause, 8-127
REPHEADER command, 4-23,
8-90
aligning header elements, 4-25
aligning heading elements, 8-91
BOLD clause, 8-91
CENTER clause, 8-91
COL clause, 8-91
FORMAT clause, 8-91
indenting headings, 8-91
LEFT clause, 8-91
most often used clauses, 4-23
OFF clause, 8-91
PAGE clause, 8-90
RIGHT clause, 8-91
SKIP clause, 8-91
suppressing current definition, 8-91
TAB clause, 8-91
REPLACE clause
in COPY command, 6-6,
8-50
in SAVE command, 3-16,
8-95
Report, GL-19
CGI scripts, 4-42
line wrapping, GL-25
subtotal, GL-22
summary, GL-22
summary line, GL-22
title, GL-24
Report breaks
BREAK command, 8-20
Report columns
Columns, 8-32
Report titles
Titles, 8-142,
F-2
Reports, 1-2
clarifying with spacing and summary lines, 4-11
creating bottom titles, 4-23,
8-25,
F-2
creating footers, 8-88
creating headers, 8-90
creating headers and footers, 4-23
creating master/detail, 4-30,
8-36,
8-37
creating top titles, 4-23,
8-142,
F-2
displaying, 8-100
embedded CGI example, 4-42
embedded using SQLPLUS -MARKUP, 4-42
formatting column headings, 4-2,
8-31
formatting columns, 4-4,
4-6,
8-31
interactive HTML example, 4-39
interactive using SET MARKUP HTML, 4-38
on the web, 4-38,
4-42
SILENT mode, 4-45
starting on a new page, 8-103
Reserved word, GL-19
-RESTRICT, 7-6,
E-9
RESTRICT, 7-6,
E-9
RESTRICT clause, 8-135
RETRY clause, 8-136
Return code, specifying, 3-17,
8-68,
8-156
REVOKE command, E-2
disabling, E-6
RIGHT clause, 4-26,
8-91,
8-143
Roles, E-8,
GL-19
disabling, E-7
re-enabling, E-7
Rollback, GL-19
ROLLBACK clause, 8-67
WHENEVER OSERROR, 8-153
WHENEVER SQLERROR, 8-155
Row, GL-18,
GL-19
Rows
performing computations on, 4-16,
8-41
setting number retrieved at one time, 8-99
setting the number after which COPY commits, 8-102
RUN command, 2-10,
8-94
disabling, E-5
executing current PL/SQL block, 2-10
executing current SQL command or PL/SQL block, 2-10
making last line current, 3-4
similar to / (slash) command, 2-10,
8-94
S
Sample tables, 1-5
access to, 1-8
DEMOBLD, 1-8
DEMODROP, 1-8
SAVE command, 3-9,
8-95
APPEND clause, 3-17,
8-95
CREATE clause, 8-95
disabling, E-5
modifying command files, 3-16
REPLACE clause, 3-16,
8-95
storing commands in command files, 3-9,
8-95
using with INPUT to create command files, 3-10
Saving environment attributes, 8-139
SCAN variable, F-2,
F-6
Schema, GL-20
SCREEN clause, 3-30,
8-29
Screen, clearing, 3-30,
8-29
Script, GL-21
CGI, 4-42,
GL-6
Search paths
Installation and User's Guide, 8-51
Security
changing password, 8-76
embedded web reports, 4-43
PRODUCT_USER_PROFILE table, E-2
RESTRICT, 7-6,
E-9
Security level, GL-20
Select, GL-20
SELECT command
and BREAK command, 4-11,
8-21,
8-22
and COLUMN command, 8-32
and COMPUTE command, 4-11
and COPY command, 6-6,
8-50
and DEFINE command, 8-52
and ORDER BY clause, 4-11
disabling, E-6
storing current date in variable for titles, 4-32
SELECT list, GL-20
SELECT statement, GL-20
formatting results, 3-32
Semicolon (;)
in PL/SQL blocks, 2-10
in SQL commands, 2-6,
2-8
in SQL*Plus commands, 2-13,
8-1
not needed when inputting a command file, 3-11
not stored in buffer, 3-3
Server, GL-16,
GL-20
SERVEROUTPUT variable, 8-110
Service Name
in COPY command, 6-8
Service name
in CONNECT command, 6-3
in COPY command, 6-5,
6-7
in SQLPLUS command, 6-3
Service names
in SQLPLUS command, 7-7
Session, GL-20
SET AUTOTRACE, 3-35
SET clause, 8-139
SET command, 2-13,
3-18,
4-38,
8-97,
GL-23
APPINFO variable, 8-98
ARRAYSIZE variable, 6-8,
8-99
AUTOCOMMIT variable, 2-14,
8-99
AUTOPRINT variable, 8-100,
8-148
AUTORECOVERY variable, 8-100
AUTOTRACE variable, 8-100
BLOCKTERMINATOR variable, 8-101
BUFFER variable, F-4
CLOSECURSOR variable, F-2,
F-5
CMDSEP variable, 8-101
COLSEP variable, 4-35,
8-101
COMPATIBILITY clause, 3-18
COMPATIBILITY variable, 8-102
CONCAT variable, 3-25,
8-102
COPYCOMMIT variable, 6-8,
8-102
COPYTYPECHECK variable, 8-102
DEFINE clause, 3-25
DEFINE variable, 8-102
DESCRIBE variable, 8-103
disabling, E-5
DOCUMENT variable, F-2,
F-5
ECHO variable, 3-15,
8-103
EDITFILE variable, 8-103
EMBEDDED variable, 8-103
ESCAPE variable, 3-25,
8-103
FEEDBACK variable, 8-104
FLAGGER variable, 8-104
FLUSH variable, 8-104
HEADING variable, 8-104
HEADSEP variable, 4-3,
8-105
INSTANCE variable, 8-105
LINESIZE variable, 4-25,
4-33,
8-105
LOBOFFSET variable, 8-106
LOGSOURCE variable, 8-106
LONG variable, 6-8,
8-106
LONGCHUNKSIZE variable, 8-106
MARKUP clause, 8-106
MARKUP Example, 8-120
MAXDATA variable, F-2,
F-6
NEWPAGE variable, 4-33,
8-109
NULL variable, 8-109
NUMFORMAT clause, 3-18
NUMFORMAT variable, 8-109
NUMWIDTH variable, 4-4,
8-35,
8-109
PAGESIZE clause, 3-18
PAGESIZE variable, 2-7,
4-33,
8-109
PAUSE clause, 3-18
PAUSE variable, 2-17,
8-109
RECSEP variable, 4-10,
8-109
RECSEPCHAR variable, 4-10,
8-109
SCAN variable, F-2,
F-6
SERVEROUTPUT variable, 8-110
SHIFTINOUT clause, 3-18
SHIFTINOUT variable, 8-110
SPACE variable, F-2,
F-6
SQLBLANKLINES variable, 8-111
SQLCASE variable, 8-111
SQLCONTINUE variable, 8-111
SQLNUMBER variable, 8-111
SQLPREFIX variable, 8-112
SQLPROMPT variable, 8-112
SQLTERMINATOR variable, 8-112
SUFFIX variable, 8-112
TAB variable, 8-112
TERMOUT variable, 4-32,
8-112
TIME clause, 3-18
TIME variable, 8-112
TIMING variable, 8-113
TRIMOUT variable, 8-113
TRIMSPOOL variable, 8-113
TRUNCATE variable, F-2,
F-7
UNDERLINE variable, 4-3,
8-113
used to format a REFCURSOR variable, 8-149
VERIFY clause, 3-21
VERIFY variable, 3-25,
8-113
WRAP variable, 4-7,
8-113
SET command variables
system variables, 2-13
SET MARKUP
BODY clause, 8-107
embedded CGI example, 4-42
ENTMAP clause, 4-42,
8-108
HEAD clause, 8-107
HTML, 8-106
interactive HTML example, 4-39
PREFORMAT clause, 8-108
See also
SPOOL command
SPOOL clause, 8-106
SET ROLE command
disabling, E-6
SET TRANSACTION command
disabling, E-6
SGA, GL-23
SGA clause, 8-127
Shared disk systems
mounting shared, 8-136
Shared mode
database, 8-136
SHIFTINOUT clause
in LOGIN.SQL, 3-18
SHIFTINOUT variable, 8-110
SHOW clause, 8-140
SHOW command, 2-13,
8-125,
GL-23
ALL clause, 8-125
BTITLE clause, 8-126
ERRORS clause, 8-126
LABEL variable, F-2,
F-7
listing current page dimensions, 4-34
LNO clause, 8-126
PNO clause, 8-127
RELEASE clause, 8-127
REPFOOTER clause, 8-127
REPHEADER clause, 8-127
SPOOL clause, 8-127
SQLCODE clause, 8-127
TTITLE clause, 8-127
USER clause, 8-127
SHOWMODE variable, 8-111
SHUTDOWN command, 8-130
ABORT, 8-130
IMMEDIATE, 8-130
NORMAL, 8-130
-SILENT option, 4-45,
7-6
Site Profile
GLOGIN.SQL, 7-8
LOGIN.SQL, 7-8
See also
User Profile
SKIP clause
in BREAK command, 4-13,
4-14,
8-23
in REPHEADER and REPFOOTER commands, 8-91
in TTITLE and BTITLE commands, 4-26,
8-143
used to place blank lines before bottom title, 4-26
SKIP PAGE clause, 4-13,
4-14,
8-23
Slash (/) command, 8-9
using with files loaded with GET command, 8-69
SPACE variable, F-2,
F-6
SPOOL, 7-4
SPOOL clause, 8-106,
8-127
SPOOL command, 4-34,
8-132,
GL-21
disabling, E-5
file name, 4-36,
8-132
OFF clause, 4-35,
8-132
OUT clause, 4-36,
8-132
to HTML file, 8-107
turning spooling off, 4-35,
8-132
use with SET MARKUP, 4-39
Spooling, GL-21
SQL, GL-21
buffer, GL-21
command, GL-21,
GL-22
query, GL-18
query results, GL-18
report, GL-19
rollback, GL-19
script, GL-21
select, GL-20
statement, GL-21,
GL-22
transaction, GL-24
SQL buffer, 2-9
SQL clause, 8-30
SQL commands, list of major, D-1
SQL database language, 1-2
SQL DML statements
reporting on, 8-100
SQL*Loader, GL-21
SQL*Net, GL-14,
GL-21
SQL*Plus, GL-21
basic concepts, 1-2
command prompt, 2-4
command summary, 8-2
database administration, 5-2
exiting, 2-4,
8-67
exiting conditionally, 8-153,
8-155
limits, C-1
LOGIN.SQL, 3-17
obsolete command alternatives, F-2
overview, 1-2
running commands in batch mode, 3-17,
8-68
setting up environment, 3-17
shortcuts to starting, 2-4
starting, 2-3,
7-2
substitution variable, GL-22
variable, GL-25
what you need to run, 1-6
who can use, 1-3
SQLBLANKLINES variable, 8-111
SQLCASE variable, 8-111
SQLCODE clause, 8-127
SHOW command, 8-127
SQLCONTINUE variable, 8-111
SQLNUMBER variable, 8-111
SQLPLUS
RESTRICT, 7-6,
E-9
SQLPLUS command, 2-3,
4-42,
7-2
- (hyphen) clause, 7-2
-? clause, 7-2
and @ ("at" sign), 3-15,
6-3,
7-2
and EXIT FAILURE, 7-8
BODY option, 7-3
connecting to a remote database, 6-3
display syntax, 7-2
ENTMAP option, 7-4
HEAD option, 7-3
HTML option, 7-3
-MARKUP clause, 7-3
-MARKUP option, 7-2
net_serrvice_name, 7-7
/NOLOG clause, 7-7
PREFORMAT option, 7-5
running command files, 3-15
service name, 6-3,
6-4,
7-7
-SILENT clause, 7-3,
7-6
-SILENT option, 4-45,
7-6
SPOOL option, 7-4
syntax, 7-2
unsuccessful connection, 7-8
username/password, 2-3,
7-7
SQL.PNO, referencing in report titles, 4-28
SQLPREFIX variable, 8-112
SQLPROMPT variable, 8-112
SQL.SQLCODE
using in EXIT command, 8-67
SQLTERMINATOR, 8-111
SQLTERMINATOR variable, 8-71,
8-112
STANDBY DATAFILE clause, 8-83
STANDBY TABLESPACE clause, 8-83
START clause, 8-15,
8-140
START command, 3-14,
8-133,
GL-24
arguments, 3-25,
8-133
command file, 3-14,
8-133
disabling, E-5
passing parameters to a command file, 3-25,
8-133
similar to @ ("at" sign) command, 3-15,
8-6,
8-134
similar to @@ (double "at" sign) command, 8-7,
8-134
Starting SQL*Plus, 2-3
shortcuts, 2-4
STARTUP command, 8-135
EXCLUSIVE clause, 8-135
FORCE clause, 8-135
MOUNT clause, 8-136
NOMOUNT clause, 8-136
OPEN clause, 8-136
PARALLEL clause, 8-136
PFILE clause, 8-135
RECOVER clause, 8-136
RESTRICT clause, 8-135
RETRY clause, 8-136
SHARED clause, 8-136
specifying a database, 8-136
Statement
SQL, GL-22
Statistics, 3-37
STD function, 4-17
STOP clause, 8-15,
8-140
STORE command, 3-18,
8-139
SET clause, 8-139
Stored functions, 3-32
Stored procedures
creating, 2-8
String, GL-22
Structured Query Language, GL-21
Substitution variables, 3-20,
GL-22,
GL-25
appending characters immediately after, 3-22
avoiding unnecessary prompts for value, 3-23
concatenation character, 8-102
DEFINE command, 3-23,
8-52
prefixing, 8-102,
F-2
restrictions, 3-25
single and double ampersands, 3-23
system variables used with, 3-25
undefined, 3-21
where and how to use, 3-21
Subtotal, GL-22
SUFFIX variable, 8-112
used with @ ("at" sign) command, 8-5
used with EDIT command, 8-64
used with GET command, 8-69
used with SAVE command, 8-95
used with START command, 8-133
SUM function, 4-17
Summary, GL-22
Summary lines, GL-22
computing and printing, 4-16,
8-41
computing and printing at ends of reports, 4-20
computing same type on different columns, 4-21
printing "grand" and "sub" summaries (totals), 4-20
printing multiple on same break column, 4-21
Syntax, GL-22
conventions, 1-4
COPY command, 6-4
Syntax rules
SQL commands, 2-7
SQL*Plus commands, 2-12
SYSDATE, 4-32
SYSDBA, GL-22
SYSDBA clause, 8-48
SYSOPER, GL-22
SYSOPER clause, 8-48
System administrator, GL-23
System editor, GL-23
System Global Area, GL-23
SYSTEM username, GL-23
System variables, 2-13,
8-114,
GL-23
changing current settings, 8-97
listing current settings, 2-13,
8-125
listing old and new values, 8-111
storing and restoring, 3-18
used with substitution variables, 3-25
System-maintained values
displaying in headers and footers, 8-90
displaying in titles, 4-28,
8-142
formatting in titles, 4-29
T
TAB clause, 8-91,
8-143
TAB variable, 8-112
Tables, 1-2,
GL-23
access to sample, 1-8
alias, GL-23
controlling destination when copying, 6-6,
8-50
copying values between, 6-4,
6-8,
8-49
DEPT, 1-5
EMP, 1-5
listing column definitions, 2-16,
8-56
referring to another user's when copying, 6-8
sample, 1-5
TABLESPACE clause, 8-83
Tablespaces
recovering, 8-81
TERMOUT variable, 8-112
storing current date in variable for titles, 4-32
using with SPOOL command, 8-132
Text, 8-107
adding to current line with APPEND, 3-6,
8-12
changing old to new with CHANGE, 3-4,
8-26
clearing from buffer, 3-2,
8-29
Text editor, GL-24
Text editor, host operating system, 3-8,
8-64
TIME clause
in LOGIN.SQL, 3-18
TIME variable, 8-112
Timer, GL-24
TIMING clause, 8-30
TIMING command, 2-15,
8-140
deleting all areas created by, 8-30
deleting current area, 8-140
SHOW clause, 8-140
START clause, 8-140
STOP clause, 8-140
TIMING variable, 8-113
Titles, GL-24
aligning elements, 4-25,
8-143
displaying at bottom of page, 4-23,
8-25,
F-2
displaying at top of page, 4-23,
8-142,
F-2
displaying column values, 4-30,
8-36,
8-37
displaying current date, 4-32,
8-36,
8-39
displaying page number, 4-28,
8-145
displaying system-maintained values, 4-28,
8-142
formatting elements, 8-144
formatting system-maintained values in, 4-29
indenting, 4-27,
8-143
listing current definition, 4-29,
8-25,
8-144
restoring definition, 4-30
setting at start or end of report, 4-23
setting lines from top of page to top title, 4-33,
8-109,
F-2
setting lines from top title to end of page, 8-109
setting top and bottom, 4-23,
8-25,
8-142,
F-2
spacing between last row and bottom title, 4-26
suppressing definition, 4-29,
8-143
TO clause, 6-5,
8-49
Tracing Statements, 3-35
for performance statistics, 3-37
for query execution path, 3-37
using a database link, 3-39
with parallel query option, 3-40
Transaction, GL-24
Transaction rollback, GL-19
TRIMOUT variable, 8-113
TRIMSPOOL variable, 8-113
Truncate, GL-24
TRUNCATE command
disabling, E-6
TRUNCATE variable, F-2,
F-7
TRUNCATED clause, 4-8,
8-37
TTITLE clause, 8-127
TTITLE command, 4-23,
8-142,
GL-24
aligning title elements, 4-25,
8-143
BOLD clause, 8-143
CENTER clause, 4-26,
8-143
COL clause, 4-27,
8-143
FORMAT clause, 4-29,
8-144
indenting titles, 4-27,
8-143
LEFT clause, 4-26,
8-143
listing current definition, 4-29,
8-144
most often used clauses, 4-23
OFF clause, 4-29,
8-143
old form, F-7
ON clause, 4-30
referencing column value variable, 4-30,
8-36
restoring current definition, 4-30
RIGHT clause, 4-26,
8-143
SKIP clause, 4-26,
8-143
suppressing current definition, 4-29,
8-143
TAB clause, 8-143
Tuning, 3-35
Type
column, GL-24
U
UNDEFINE command, 3-20,
8-146
and DEFINE command, 8-52
UNDERLINE variable, 4-3,
8-113
UNTIL CANCEL clause, 8-82
UNTIL CHANGE clause, 8-82
UNTIL CONTROLFILE clause, 8-83
UNTIL TIME clause, 8-82
UPDATE command
disabling, E-6
USER clause, 8-127
User Profile, 3-17
GLOGIN.SQL, 7-8
LOGIN.SQL, 7-8
See also
Site Profile
User variables, 3-20,
GL-24
defining, 3-20,
8-52
deleting, 3-20,
8-146
displaying in headers and footers, 8-90
displaying in titles, 8-142
in ACCEPT command, 3-27,
8-10
listing definition of one, 3-20,
8-52
listing definitions of all, 3-20,
8-52
USERID, GL-24
Username, 1-7,
GL-23,
GL-24
connecting under different, 6-2,
8-47
in CONNECT command, 6-2,
6-3,
8-47
in COPY command, 6-5,
6-7,
6-8
in SQLPLUS command, 2-3,
6-4,
7-7
USING BACKUP CONTROL FILE clause, 8-83
USING clause, 6-6,
8-50
V
V$SESSION virtual table, 8-98
V$SQLAREA virtual table, 8-99
VARCHAR columns
changing format, 4-7
default format, 4-6,
8-33
VARCHAR datatype, GL-25
VARCHAR2 clause
VARIABLE command, 8-147
VARCHAR2 columns
changing format, 4-7,
8-33
default format, 4-6
VARCHAR2 datatype, GL-25
VARIABLE command, 8-147
CHAR clause, 8-147
CLOB clause, 8-148
NCHAR clause, 8-147
NCLOB clause, 8-148
NUMBER clause, 8-147
REFCURSOR clause, 8-148
VARCHAR2 clause, 8-147
variable clause, 8-147
Variables, GL-22,
GL-23,
GL-25
bind variables, 3-30
substitution variables, 3-20
system variables, 2-13
user variables, 8-52
VARIANCE function, 4-17
VERIFY clause, 3-21
VERIFY variable, 3-25,
8-113
W
WARNING clause, 8-67
Web
outputting reports, 4-38
WHENEVER OSERROR command, 8-153
COMMIT clause, 8-153
CONTINUE clause, 8-153
EXIT clause, 8-153
NONE clause, 8-153
ROLLBACK clause, 8-153
WHENEVER SQLERROR command, 3-17,
8-155
COMMIT clause, 8-155
CONTINUE clause, 8-155
EXIT clause, 8-155
NONE clause, 8-155
ROLLBACK clause, 8-155
Width, GL-25
WORD_WRAPPED clause, 4-8,
4-10,
8-37
WRAP variable, 4-7,
8-113
WRAPPED clause, 4-8,
8-37
Wrapping, GL-25
Copyright © 1999 Oracle Corporation.
All Rights Reserved.
Library
Product
Contents