Go to main content
1/81
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for SQL*Plus
Changes in SQL*Plus Release 12.2.0.1.0
New Features
Desupported Features
SQL*Plus Quick Start
SQL*Plus Resources
SQL*Plus Overview
Who Can Use SQL*Plus
How Can I Learn SQL*Plus
How to Use the SQL*Plus Guide
SQL*Plus Command-line Architecture
SQL*Plus Client
Oracle Database
SQL*Plus Prerequisites
SQL*Plus Date Format
Starting SQL*Plus Command-line
About Starting SQL*Plus Instant Client
About Connecting to a Different Database
About Sample Schemas and SQL*Plus
Unlocking the Sample Tables
Running your first Query
About Exiting SQL*Plus
Part I SQL*Plus Getting Started
1
SQL*Plus User Interface
About 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
Default Site Profile Script
User Profile
Modifying Your LOGIN File
Storing and Restoring SQL*Plus System Variables
Restoring the System Variables
About Installing Command-line Help
Running the hlpbld.sql Script to Install Command-line Help
Running the helpdrop.sql Script to Remove Command-line Help
About Configuring Oracle Net Services
3
Starting SQL*Plus
Login Username and Password
Secure External Password Store
Expired Password
About Changing your Password
About Connecting to a Database
Net Service Name
Full Connection Identifier
Easy Connection Identifier
Connectionless Session with /NOLOG
About Starting SQL*Plus
About Starting Command-line SQL*Plus
About Getting Command-line Help
About Exiting SQL*Plus Command-line
SQL*Plus Program Syntax
Options
HELP Option
VERSION Option
COMPATIBILITY Option
LOGON Option
FAST Option
MARKUP Options
MARKUP Usage Notes
No Login Time Option
RESTRICT Option
SILENT Option
AC Option
Logon
Start
Part II Using SQL*Plus
4
SQL*Plus Basics
About Entering and Executing Commands
The SQL Buffer
About Executing Commands
About Listing a Table Definition
About Listing PL/SQL Definitions
Running SQL Commands
About Understanding SQL Command Syntax
About Dividing a SQL Command into Separate Lines
About Ending a SQL Command
About Running PL/SQL Blocks
About Creating Stored Procedures
Running SQL*Plus Commands
About Understanding SQL*Plus Command Syntax
About Continuing a Long SQL*Plus Command on Additional Lines
System Variables that Affect How Commands Run
About Stopping a Command while it is Running
About Running Operating System Commands
About Pausing the Display
About Saving Changes to the Database Automatically
About Interpreting Error Messages
5
Using Scripts in SQL*Plus
About Editing Scripts
Writing Scripts with a System Editor
About 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
About 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
About Exiting from a Script with a Return Code
Defining Substitution Variables
About 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
About 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
About Using Bind Variables
Creating Bind Variables
Referencing Bind Variables
Displaying Bind Variables
Executing an Input Bind
Using REFCURSOR Bind Variables
Fetching Iterative Results from a SELECT inside a PL/SQL Block
6
Formatting SQL*Plus Reports
About Formatting Columns
About Changing Column Headings
Default Headings
Changing Default Headings
About Formatting NUMBER Columns
Default Display
Changing the Default Display
About Formatting Datatypes
Default Display
Changing the Default Display
Copying Column Display Attributes
Listing and Resetting Column Display Attributes
About Suppressing and Restoring Column Display Attributes
Printing a Line of Characters after Wrapped Column Values
About 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
About Defining Page and Report Titles and Dimensions
Setting the Top and Bottom Titles and Headers and Footers
Positioning Title Elements
Indenting a Title Element
Entering Long Titles
Displaying System-Maintained Values in Titles
Listing, Suppressing, and Restoring Page Title Definitions
Displaying Column Values in Titles
About Displaying the Current Date in Titles
Setting Page Dimensions
About Storing and Printing Query Results
Creating a Flat File
Sending Results to a File
Sending Results to a Printer
7
Generating Reports from SQL*Plus
About Creating Reports using Command-line SQL*Plus
Creating HTML Reports
HTML Entities
Creating CSV Reports
About Suppressing the Display of SQL*Plus Commands in Reports
8
Tuning SQL*Plus
About Tracing Statements
Controlling the Autotrace Report
Execution Plan
Statistics
About Collecting Timing Statistics
Tracing Parallel and Distributed Queries
Execution Plan Output in Earlier Databases
About 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
About the PRODUCT_USER_PROFILE Table
About Creating the PUP Table
PUP Table Structure
Description and Use of PUP Columns
PUP Table Administration
Disabling SQL*Plus, SQL, and PL/SQL Commands
About Creating and Controlling Roles
About Disabling SET ROLE
About Disabling User Roles
About Disabling Commands with SQLPLUS -RESTRICT
About Program Argument Security
10
Database Administration with SQL*Plus
Overview
Introduction to Database Startup and Shutdown
Database Startup
PDB Startup
Database Shutdown
PDB Shutdown
Redo Log Files
ARCHIVELOG Mode
Database Recovery
11
SQL*Plus Globalization Support
About 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
HISTORY
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 COLINVI[SIBLE] [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 EXITC[OMMIT] {
ON
| OFF}
SET FEED[BACK] {
6
|
n
|
ON
| OFF | ONLY}
SET FLAGGER {
OFF
| ENTRY | INTERMED[IATE] | FULL}
SET FLU[SH] {
ON
| OFF}
SET HEA[DING] {
ON
| OFF}
SET HEADS[EP] { |
c
|
ON
| OFF}
SET HIST[ORY] {ON |
OFF
|
n
}
SET INSTANCE [
instance_path
|
LOCAL
]
SET LIN[ESIZE] {
80
|
n
}
SET LOBOF[FSET] {
1
|
n
}
SET LOBPREFETCH {
0
|
n
}
SET LOGSOURCE [
pathname
]
SET LONG {
80
|
n
}
SET LONGC[HUNKSIZE] {
80
|
n
}
SET MARK[UP]
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 ROWPREFETCH {
1
|
n
}
SET SECUREDCOL {
OFF
| ON} [UNAUTH[ORIZED]
text
] [UNK[NOWN]
text
]
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
]}
SQL*Plus Compatibility Matrix
SET SQLPRE[FIX] {
#
|
c
}
SET SQLP[ROMPT] {
SQL>
|
text
}
SET SQLT[ERMINATOR] {
;
|
c
|
ON
| OFF}
SET STATEMENTC[ACHE] {
0
|
n
}
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 XMLOPT[IMIZATIONCHECK] [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
About Controlling Treatment of the Destination Table
About Interpreting the Messages that COPY Displays
Specifying Another User's Table
About 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
About Choosing the SQL*Plus Instant Client to Install
Basic Instant Client
Lightweight Instant Client
Lightweight SQL*Plus Instant Client Error with Unsupported Character Set
About 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 12
c
Client Release Media
Installing SQL*Plus Instant Client on UNIX or Linux
Installing SQL*Plus Instant Client on Windows
About 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
About Connecting to a Database with SQL*Plus Instant Client
AS SYSDBA or AS SYSOPER Connections with SQL*Plus Instant Client
About Uninstalling Instant Client
Uninstalling SQL*Plus Instant Client
Uninstalling the Complete Instant Client
Index
Scripting on this page enhances content navigation, but does not change the content in any way.