SQL*Plus®
User’s Guide and Reference
Release 8.1.5
February, 1999
Part No. A66736-01
SQL*Plus User’s Guide and Reference, Release 8.1.5
Part No. A66736-01
Copyright © 1996, 1999, Oracle Corporation. All rights reserved.
Contributing Authors: Frank Rovitto
Contributors: Larry Baer, Lisa Colston, Roland Kovacs, Karen Denchfield-Masterson, Alison Holloway,
Sanjeev Jhala, Christopher Jones, Anita Lam, Nimish Mehta, Luan Nim, Bud Osterberg, Irene Paradisis,
Richard Rendell, Farokh Shapoorjee, Larry Stevens, Andre Touma
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other
inherently dangerous applications. It shall be the licensee’s responsibility to take all appropriate
fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the
Programs are used for such purposes, and Oracle disclaims liability for any damages caused by such
use of the Programs.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained 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. Except as may be expressly permitted in your license agreement
for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any
means, electronic or mechanical, for any purpose, without the express written permission of Oracle
Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs including documentation, shall

Conventions for Command Syntax
1-4
Sample Tables
1-5
What You Need to Run SQL*Plus
1-7
Hardware and Software
1-7
Information Specific to Your Operating System
1-7
Username and Password
1-7
Access to Sample Tables
1-8
2 Learning SQL*Plus Basics
Getting Started
2-2
Using the Keyboard
2-2
Starting SQL*Plus
2-3
iv
Shortcuts to Starting SQL*Plus
2-4
Leaving SQL*Plus
2-4
Entering and Executing Commands
2-5
Entering Commands
2-5

2-16
Listing a Table Definition
2-16
Listing PL/SQL Definitions
2-17
Controlling the Display
2-17
Interpreting Error Messages
2-18
3 Manipulating Commands
Editing Commands
3-2
Listing the Buffer Contents
3-3
Editing the Current Line
3-4
Adding a New Line
3-5
Appending Text to a Line
3-6
Deleting Lines
3-7
Editing Commands with a System Editor
3-7
Saving Commands for Later Use
3-8
Storing Commands in Command Files
3-8
v
Creating a Command File by Saving the Buffer Contents

3-18
Restoring the System Variables
3-18
Writing Interactive Commands
3-19
Defining User Variables
3-19
Using Substitution Variables
3-20
Where and How to Use Substitution Variables
3-21
Avoiding Unnecessary Prompts for Values
3-23
Restrictions
3-25
System Variables
3-25
Passing Parameters through the START Command
3-25
Communicating with the User
3-27
Prompting for and Accepting User Variable
3-27
Customizing Prompts for Substitution Variable
3-28
Sending a Message and Accepting [Return] as Input
3-30
Clearing the Screen
3-30
Using Bind Variables

4-8
Listing and Resetting Column Display Attributes
4-9
Suppressing and Restoring Column Display Attributes
4-10
Printing a Line of Characters after Wrapped Column Values
4-10
Clarifying Your Report with Spacing and Summary Lines
4-11
Suppressing Duplicate Values in Break Columns
4-12
Inserting Space when a Break Column’s Value Changes
4-13
Inserting Space after Every Row
4-14
Using Multiple Spacing Techniques
4-14
Listing and Removing Break Definitions
4-16
Computing Summary Lines when a Break Column’s Value Changes
4-16
Computing Summary Lines at the End of the Report
4-20
Computing Multiple Summary Values and Lines
4-21
Listing and Removing COMPUTE Definitions
4-22
Defining Page and Report Titles and Dimensions
4-23
Setting the Top and Bottom Titles and Headers and Footers

5-4
ARCHIVELOG Mode
5-4
Database Recovery
5-5
6 Accessing SQL Databases
Connecting to the Default Database
6-2
Connecting to a Remote Database
6-2
Connecting to a Remote Database from within SQL*Plus
6-3
Connecting to a Remote Database as You Start SQL*Plus
6-3
Copying Data from One Database to Another
6-4
Understanding COPY Command Syntax
6-5
Controlling Treatment of the Destination Table
6-6
Interpreting the Messages that COPY Displays
6-8
Specifying Another User’s Table
6-8
Copying Data between Tables on One Database
6-9
Part II Reference
7 Starting SQL*Plus and Getting Help
Starting SQL*Plus Using the SQLPLUS Command
7-2

CHANGE
8-27
CLEAR
8-30
COLUMN
8-32
COMPUTE
8-43
CONNECT
8-49
COPY
8-51
DEFINE
8-54
DEL
8-56
DESCRIBE
8-58
DISCONNECT
8-65
EDIT
8-66
EXECUTE
8-68
EXIT
8-69
GET
8-71
HELP
8-73

8-123
SHUTDOWN
8-127
SPOOL
8-129
START
8-130
STARTUP
8-132
STORE
8-136
TIMING
8-137
TTITLE
8-139
UNDEFINE
8-143
VARI AB LE
8-144
WHENEVER OSERROR
8-150
WHENEVER SQLERROR
8-152
A COPY Command Messages and Codes
B Release 8.1.5 Enhancements
C SQL*Plus Limits
D SQL Command List
E Security
F SQL*Plus Commands from Earlier Releases
Glossary

sample tables.
2
Manipulating
Commands
Also through examples, helps you learn to
edit commands, save them for later use, and
write interactive commands.
3
Formatting Query
Results
Explains how you can format columns,
clarify your report with spacing and
summary lines, define page dimensions and
titles, and store and print query results. Also
uses step-by-step examples.
4
Database
Administration
This chapter is intended for use by DBAs,
and covers the basic database
administration features in SQL*Plus.
5
Accessing
Databases
Tells you how to connect to default and
remote databases, and how to copy data
between databases and between tables on
the same database.
6
xiii

Appendix C
SQL Command List Provides a list of major SQL commands and
clauses.
Appendix D
Security Explains how to restrict access to certain
SQL*Plus and SQL commands.
Appendix E
SQL*Plus
Commands from
Earlier Releases
Provides information on SQL*Plus
commands from earlier releases.
Appendix F
Glossary Defines technical terms associated with
Oracle and SQL*Plus.
Glossary
xiv
Related Publications
Related documentation includes the following publications:
■
SQL*Plus Quick Reference
■
PL/SQL User’s Guide and Reference
■
Oracle8i SQL Reference
■
Oracle8i Concepts
■
Oracle8i Administrator’s Guide
■

Your Comments Are Welcome
Oracle Corporation values and appreciates your comments as an Oracle user and
reader of the manuals. As we write, revise, and evaluate, your opinions are the most
important input we receive. At the front of this manual is a form entitled "Send Us
Your Comments" that we encourage you to use to tell us both what you like and
what you dislike about this (or other) Oracle manuals. If the form is not at the front
of this manual, or if you would like to contact us, please use the following addresses
and phone numbers.
For documentation questions/comments, contact:
SQL*Plus Documentation Manager
Australian Product Development Center
Oracle Corporation Australia Pty Limited
324 St. Kilda Road
Melbourne VIC 3004
Australia
+61 3 9209 1600 (telephone)
+61 3 9690 0043 (fax)
(email)
For product questions/comments, contact:
SQL*Plus Product Manager
Australian Product Development Center
Oracle Corporation Australia Pty Limited
324 St. Kilda Road
Melbourne VIC 3004
Australia
+61 3 9209 1600 (telephone)
+61 3 9690 0043 (fax)
(email)
xvi
xvii


If you have problems with the software, please contact your local Oracle Support Services center.
xviii
Part I
Understanding SQL*Plus
This section provides an introduction to SQL*Plus. It provides an overview of how
to run SQL*Plus and demonstrates this with various examples.
The following chapters are covered in this section:
■
Introduction
■
Learning SQL*Plus Basics
■
Manipulating Commands
■
Formatting Query Results
■
Database Administration
■
Accessing SQL Databases
class="bi x0 y0 w1 h1"
Introduction 1-1
1
Introduction
This chapter introduces you to SQL*Plus, covering the following topics:
■
Overview of SQL*Plus
■
Using this Guide
■

retrieves information from one or more tables.
query results The data retrieved by a query.
report Query results formatted by you through SQL*Plus
commands.
Overview of SQL*Plus
Introduction 1-3
Who Can Use SQL*Plus
The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to
serve the needs of users with some database experience, yet straightforward enough
for new users who are just learning to work with Oracle.
The design of the SQL*Plus command language makes it easy to use. For example,
to give a column labelled ENAME in the database the clearer heading “Employee”,
you might enter the following command:
COLUMN ENAME HEADING EMPLOYEE
Similarly, to list the column definitions for a table called EMP, you might enter this
command:
DESCRIBE EMP
Other Ways of Working with Oracle
Oracle tools for Network Computing Architecture help developers to productively
and economically build, manage and deploy high-performance and robust
enterprise applications for Network Computing.
JDeveloper Suite a 3GL development tool for building component based,
server-centric applications in Java
Oracle Enterprise
Developer Suite
an integrated and flexible set of tools for building
enterprise-class database applications for client/server and
the web
Oracle Application
Server

Oracle Media
Objects
a development tool for object-oriented multimedia
applications
Oracle Mobile
Agents
a tool for applications using mobile and/or detached clients
Table 1–1 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.
Using this Guide

{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.
Table 1–1 Commands, Terms, and Clauses
Feature Example Explanation