Skip Headers
SQL*Plus® User's Guide and Reference
Release 11.1
Part Number B31189-01
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
List of Examples
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in SQL*Plus?
New Features in SQL*Plus Release 11.1
SQL*Plus Quick Start
SQL*Plus Resources
SQL*Plus Overview
SQL*Plus Prerequisites
Starting SQL*Plus Command-line
Starting SQL*Plus Instant Client
Connecting to a Different Database
Sample Schemas and SQL*Plus
Running your first Query
Exiting SQL*Plus
Part I SQL*Plus Getting Started
1
SQL*Plus User Interface
The Command-line Screen
Changing the Command-line Font and Font Size
2
Configuring SQL*Plus
SQL*Plus Environment Variables
SQLPATH Registry Entry
SQL*Plus Configuration
Site Profile
User Profile
Storing and Restoring SQL*Plus System Variables
Installing Command-line Help
Configuring Oracle Net Services
3
Starting SQL*Plus
Login Username and Password
Secure External Password Store
Expired Password
Changing your Password
Connecting to a Database
Net Service Name
Full Connection Identifier
Easy Connection Identifier
Connectionless Session with /NOLOG
Starting SQL*Plus
Starting Command-line SQL*Plus
Getting Command-line Help
Exiting SQL*Plus Command-line
SQLPLUS Program Syntax
Options
Logon
Start
Part II Using SQL*Plus
4
SQL*Plus Basics
Entering and Executing Commands
The SQL Buffer
Executing Commands
Listing a Table Definition
Listing PL/SQL Definitions
Running SQL Commands
Understanding SQL Command Syntax
Running PL/SQL Blocks
Creating Stored Procedures
Running SQL*Plus Commands
Understanding SQL*Plus Command Syntax
System Variables that Affect How Commands Run
Stopping a Command while it is Running
Running Operating System Commands
Pausing the Display
Saving Changes to the Database Automatically
Interpreting Error Messages
5
Using Scripts in SQL*Plus
Editing Scripts
Writing Scripts with a System Editor
Editing Scripts in SQL*Plus Command-Line
Listing the Buffer Contents
Editing the Current Line
Appending Text to a Line
Adding a New Line
Deleting Lines
Placing Comments in Scripts
Using the REMARK Command
Using /*...*/
Using - -
Notes on Placing Comments
Running Scripts
Running a Script as You Start SQL*Plus
Nesting Scripts
Exiting from a Script with a Return Code
Defining Substitution Variables
Using Predefined Variables
Using Substitution Variables
Where and How to Use Substitution Variables
Avoiding Unnecessary Prompts for Values
Restrictions
System Variables
Passing Parameters through the START Command
Communicating with the User
Receiving a Substitution Variable Value
Customizing Prompts for Substitution Variable
Sending a Message and Accepting Return as Input
Clearing the Screen
Using Bind Variables
Creating Bind Variables
Referencing Bind Variables
Displaying Bind Variables
Using REFCURSOR Bind Variables
6
Formatting SQL*Plus Reports
Formatting Columns
Changing Column Headings
Formatting NUMBER Columns
Formatting Datatypes
Copying Column Display Attributes
Listing and Resetting Column Display Attributes
Suppressing and Restoring Column Display Attributes
Printing a Line of Characters after Wrapped Column Values
Clarifying Your Report with Spacing and Summary Lines
Suppressing Duplicate Values in Break Columns
Inserting Space when a Break Column's Value Changes
Inserting Space after Every Row
Using Multiple Spacing Techniques
Listing and Removing Break Definitions
Computing Summary Lines when a Break Column's Value Changes
Computing Summary Lines at the End of the Report
Computing Multiple Summary Values and Lines
Listing and Removing COMPUTE Definitions
Defining Page and Report Titles and Dimensions
Setting the Top and Bottom Titles and Headers and Footers
Displaying System-Maintained Values in Titles
Listing, Suppressing, and Restoring Page Title Definitions
Displaying Column Values in Titles
Displaying the Current Date in Titles
Setting Page Dimensions
Storing and Printing Query Results
Creating a Flat File
Sending Results to a File
Sending Results to a Printer
7
Generating HTML Reports from SQL*Plus
Creating Reports using Command-line SQL*Plus
Creating Reports
Suppressing the Display of SQL*Plus Commands in Reports
HTML Entities
8
Tuning SQL*Plus
Tracing Statements
Controlling the Autotrace Report
Execution Plan
Statistics
Collecting Timing Statistics
Tracing Parallel and Distributed Queries
Execution Plan Output in Earlier Databases
SQL*Plus Script Tuning
COLUMN NOPRINT
SET APPINFO OFF
SET ARRAYSIZE
SET DEFINE OFF
SET FLUSH OFF
SET LINESIZE
SET LONGCHUNKSIZE
SET PAGESIZE
SET SERVEROUTPUT
SET SQLPROMPT
SET TAB
SET TERMOUT
SET TRIMOUT ON SET TRIMSPOOL ON
UNDEFINE
9
SQL*Plus Security
PRODUCT_USER_PROFILE Table
Creating the PUP Table
PUP Table Structure
Description and Use of PUP Columns
PUP Table Administration
Disabling SQL*Plus, SQL, and PL/SQL Commands
Creating and Controlling Roles
Disabling SET ROLE
Disabling User Roles
Disabling Commands with SQLPLUS -RESTRICT
Program Argument Security
10
Database Administration with SQL*Plus
Overview
Introduction to Database Startup and Shutdown
Database Startup
Database Shutdown
Redo Log Files
ARCHIVELOG Mode
Database Recovery
11
SQL*Plus Globalization Support
Configuring Globalization Support in Command-line SQL*Plus
SQL*Plus Client
Oracle Database
NLS_LANG Environment Variable
Viewing NLS_LANG Settings
Setting NLS_LANG
Part III SQL*Plus Reference
12
SQL*Plus Command Reference
SQL*Plus Command Summary
@ (at sign)
@@ (double at sign)
/ (slash)
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
Predefined Variables
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INPUT
LIST
PASSWORD
PAUSE
PRINT
PROMPT
RECOVER
REMARK
REPFOOTER
REPHEADER
RUN
SAVE
SET
SET System Variable Summary
SET APPI[NFO]{ON |
OFF
|
text
}
SET ARRAY[SIZE] {
15
|
n
}
SET AUTO[COMMIT]{ON |
OFF
| IMM[EDIATE] |
n
}
SET AUTOP[RINT] {ON |
OFF
}
SET AUTORECOVERY [ON |
OFF
]
SET AUTOT[RACE] {ON |
OFF
| TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET BLO[CKTERMINATOR] {. |
c
|
ON
| OFF}
SET CMDS[EP] {; |
c
| ON |
OFF
}
SET COLSEP { |
text
}
SET CON[CAT] {. |
c
|
ON
| OFF}
SET COPYC[OMMIT] {
0
|
n
}
SET COPYTYPECHECK {
ON
| OFF}
SET DEF[INE] {
&
|
c
|
ON
| OFF}
SET DESCRIBE [DEPTH {
1
|
n
| ALL}] [LINENUM {ON |
OFF
}] [INDENT {ON |
OFF
}]
SET ECHO {ON |
OFF
}
SET EDITF[ILE]
file_name
[.
ext
]
SET EMB[EDDED] {ON |
OFF
}
SET ERRORL[OGGING] {ON |
OFF
} [TABLE [
schema.
]
tablename
] [TRUNCATE] [IDENTIFIER
identifier
]
SET ESC[APE] {
\
|
c
| ON |
OFF
}
SET ESCCHAR {@ | ? | % | $ |
OFF
}
SET FEED[BACK] {
6
|
n
|
ON
| OFF}
SET FLAGGER {
OFF
| ENTRY | INTERMED[IATE] | FULL}
SET FLU[SH] {
ON
| OFF}
SET HEA[DING] {
ON
| OFF}
SET HEADS[EP] { |
c
|
ON
| OFF}
SET INSTANCE [
instance_path
|
LOCAL
]
SET LIN[ESIZE] {
80
|
n
}
SET LOBOF[FSET] {
1
|
n
}
SET LOGSOURCE [
pathname
]
SET LONG {
80
|
n
}
SET LONGC[HUNKSIZE] {
80
|
n
}
SET MARK[UP] HTML [ON |
OFF
] [HEAD
text
] [BODY
text
] [TABLE
text
] [ENTMAP {
ON
| OFF}] [SPOOL {ON |
OFF
}] [PRE[FORMAT] {ON |
OFF
}]
SET NEWP[AGE] {
1
|
n
| NONE}
SET NULL
text
SET NUMF[ORMAT]
format
SET NUM[WIDTH] {
10
|
n
}
SET PAGES[IZE] {
14
|
n
}
SET PAU[SE] {ON |
OFF
|
text
}
SET RECSEP {
WR[APPED]
| EA[CH] | OFF}
SET RECSEPCHAR { |
c
}
SET SERVEROUT[PUT] {ON | OFF} [SIZE {
n
|
UNL[IMITED]
}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHIFT[INOUT] {VIS[IBLE] |
INV[ISIBLE]
}
SET SHOW[MODE] {ON |
OFF
}
SET SQLBL[ANKLINES] {ON |
OFF
}
SET SQLC[ASE] {
MIX[ED]
| LO[WER] | UP[PER]}
SET SQLCO[NTINUE] {
>
|
text
}
SET SQLN[UMBER] {
ON
| OFF}
SET SQLPLUSCOMPAT[IBILITY] {
x
.
y
[.
z
]}
SET SQLPRE[FIX] {
#
|
c
}
SET SQLP[ROMPT] {
SQL>
|
text
}
SET SQLT[ERMINATOR] {
;
|
c
|
ON
| OFF}
SET SUF[FIX] {
SQL
|
text
}
SET TAB {
ON
| OFF}
SET TERM[OUT] {
ON
| OFF}
SET TI[ME] {ON |
OFF
}
SET TIMI[NG] {ON |
OFF
}
SET TRIM[OUT] {
ON
| OFF}
SET TRIMS[POOL] {ON |
OFF
}
SET UND[ERLINE] {
-
|
c
|
ON
| OFF}
SET VER[IFY] {
ON
| OFF}
SET WRA[P] {
ON
| OFF}
SET XQUERY BASEURI {
text
}
SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}
SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}
SET XQUERY CONTEXT {
text
}
SHOW
SHUTDOWN
SPOOL
START
STARTUP
STORE
TIMING
TTITLE
UNDEFINE
VARIABLE
WHENEVER OSERROR
WHENEVER SQLERROR
XQUERY
13
SQL*Plus Error Messages
SQL*Plus Error Messages
COPY Command Messages
Part IV SQL*Plus Appendixes
A
SQL*Plus Limits
B
SQL*Plus COPY Command
COPY Command Syntax
Terms
Usage
Examples
Copying Data from One Database to Another
Understanding COPY Command Syntax
Controlling Treatment of the Destination Table
Interpreting the Messages that COPY Displays
Specifying Another User's Table
Copying Data between Tables on One Database
C
Obsolete SQL*Plus Commands
SQL*Plus Obsolete Command Alternatives
BTI[TLE]
text
(obsolete old form)
COL[UMN] {
column
|
expr
} DEF[AULT] (obsolete)
DOC[UMENT] (obsolete)
NEWPAGE [1|n] (obsolete)
SET BUF[FER] {
buffer
|SQL} (obsolete)
SET COM[PATIBILITY]{V7 | V8 |
NATIVE
} (obsolete)
SET CLOSECUR[SOR] {ON|OFF} (obsolete)
SET DOC[UMENT] {ON|OFF} (obsolete)
SET MAXD[ATA]
n
(obsolete)
SET SCAN {ON|OFF} (obsolete)
SET SPACE {1|
n
} (obsolete)
SET TRU[NCATE] {ON|OFF} (obsolete)
TTI[TLE]
text
(obsolete old form)
D
SQL*Plus Instant Client
Choosing the SQL*Plus Instant Client to Install
Basic Instant Client
Lightweight Instant Client
Installing SQL*Plus Instant Client by Downloading from OTN
Installing SQL*Plus Instant Client from Linux RPM Packages
Installing SQL*Plus Instant Client from the UNIX or Windows Zip Files
List of Files Required for SQL*Plus Instant Client
Installing SQL*Plus Instant Client from the 11
g
Client Release Media
Installing SQL*Plus Instant Client on UNIX or Linux
Installing SQL*Plus Instant Client on Windows
Configuring SQL*Plus Instant Client
Configuring SQL*Plus Instant Client on Linux (from RPMs)
Configuring SQL*Plus Instant Client on Linux (from Client Media or Zip File) and UNIX
Configuring SQL*Plus Instant Client on Windows
Connecting to a Database with SQL*Plus Instant Client
AS SYSDBA or AS SYSOPER Connections with SQL*Plus Instant Client
Uninstalling Instant Client
Uninstalling SQL*Plus Instant Client
Uninstalling the Complete Instant Client
Index
