SQL Plus. User's Guide and Reference

432 Pages·2001·3.45 MB·English

by  Watt S.

SQL*Plus
User’s Guide and Reference
Release 9.0.1
July 2001
Part No. A88827-02
SQL*Plus User’s Guide and Reference, Release 9.0.1
Part No. A88827-02
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Simon Watt
Contributors: Andrew Code, Alison Goggin, Alison Holloway, Christopher Jones, Anita Lam, Luan
Nim, Andrei Souleimanian, Christopher Tan, Ian Wu.
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 be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
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 Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle Net, Oracle HTTP Server, Oracle7, Oracle8, Oracle8i,
Oracle9i, PL/SQL, iSQL*Plus and SQL*Plus are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................... xi
Preface........................................................................................................................................................... xiii
Audience............................................................................................................................................... xiv
Organization......................................................................................................................................... xiv
Related Documentation...................................................................................................................... xvi
Conventions......................................................................................................................................... xvii
Documentation Accessibility.............................................................................................................. xx
Part I Understanding SQL*Plus
1 Introduction
Overview of SQL*Plus....................................................................................................................... 1-2
Basic Concepts............................................................................................................................... 1-2
Who Can Use SQL*Plus............................................................................................................... 1-2
Using this Guide................................................................................................................................. 1-3
Sample Tables................................................................................................................................ 1-3
What You Need to Run SQL*Plus................................................................................................... 1-4
Hardware and Software.............................................................................................................. 1-4
Information Specific to Your Operating System...................................................................... 1-5
Username and Password............................................................................................................. 1-5
Access to Sample Tables.............................................................................................................. 1-6
iii
2 Learning SQL*Plus Basics
Starting SQL*Plus............................................................................................................................... 2-2
Leaving SQL*Plus............................................................................................................................... 2-3
Entering and Executing Commands................................................................................................ 2-4
Running SQL Commands............................................................................................................ 2-6
Running PL/SQL Blocks........................................................................................................... 2-10
Running SQL*Plus Commands................................................................................................ 2-11
Variables that Affect Running Commands............................................................................. 2-13
Saving Changes to the Database Automatically.................................................................... 2-14
Stopping a Command while it is Running.............................................................................. 2-15
Collecting Timing Statistics on Commands You Run........................................................... 2-15
Running Host Operating System Commands........................................................................ 2-16
Getting Help...................................................................................................................................... 2-16
Listing a Table Definition.......................................................................................................... 2-17
Listing PL/SQL Definitions...................................................................................................... 2-18
Controlling the Display.............................................................................................................. 2-18
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-8
Saving Commands for Later Use..................................................................................................... 3-8
Storing Commands in Command Files..................................................................................... 3-8
Placing Comments in Command Files.................................................................................... 3-12
Retrieving Command Files........................................................................................................ 3-16
Running Command Files........................................................................................................... 3-17
Nesting Command Files............................................................................................................ 3-18
Modifying Command Files....................................................................................................... 3-19
Exiting from a Command File with a Return Code............................................................... 3-20
Setting Up Your SQL*Plus Environment................................................................................ 3-20
iv
Storing and Restoring SQL*Plus System Variables............................................................... 3-21
Writing Interactive Commands...................................................................................................... 3-22
Defining User Variables............................................................................................................. 3-22
Using Substitution Variables.................................................................................................... 3-23
Passing Parameters through the START Command............................................................. 3-29
Communicating with the User................................................................................................. 3-30
Using Bind Variables....................................................................................................................... 3-33
Creating Bind Variables............................................................................................................. 3-34
Referencing Bind Variables....................................................................................................... 3-34
Displaying Bind Variables......................................................................................................... 3-35
Using REFCURSOR Bind Variables......................................................................................... 3-35
Tracing Statements............................................................................................................................ 3-39
Controlling the Report............................................................................................................... 3-39
Execution Plan............................................................................................................................. 3-40
Statistics........................................................................................................................................ 3-41
Tracing Parallel and Distributed Queries............................................................................... 3-44
4 Formatting Query Results
Formatting Columns.......................................................................................................................... 4-2
Changing Column Headings...................................................................................................... 4-2
Formatting NUMBER Columns................................................................................................. 4-4
Formatting Datatypes.................................................................................................................. 4-6
Copying Column Display Attributes......................................................................................... 4-8
Listing and Resetting Column Display Attributes.................................................................. 4-9
Suppressing and Restoring Column Display Attributes........................................................ 4-9
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-15
Computing Summary Lines when a Break Column’s Value Changes............................... 4-16
Computing Summary Lines at the End of the Report........................................................... 4-19
Computing Multiple Summary Values and Lines................................................................. 4-20
v
Listing and Removing COMPUTE Definitions...................................................................... 4-22
Defining Page and Report Titles and Dimensions.................................................................... 4-22
Setting the Top and Bottom Titles and Headers and Footers.............................................. 4-22
Displaying the Page Number and other System-Maintained Values in Titles.................. 4-27
Listing, Suppressing, and Restoring Page Title Definitions................................................. 4-28
Displaying Column Values in Titles........................................................................................ 4-29
Displaying the Current Date in Titles...................................................................................... 4-30
Setting Page Dimensions........................................................................................................... 4-31
Storing and Printing Query Results.............................................................................................. 4-33
Sending Results to a File............................................................................................................ 4-34
Sending Results to a Printer...................................................................................................... 4-34
Creating Web Reports...................................................................................................................... 4-37
Creating Static Web Reports..................................................................................................... 4-37
Creating Dynamic Web Reports with CGI Scripts................................................................. 4-42
Suppressing the Display of SQL*Plus Commands in Web Reports.................................... 4-47
HTML Entities............................................................................................................................. 4-47
5 Database Administration
Overview.............................................................................................................................................. 5-2
Introduction to Database Startup and Shutdown........................................................................ 5-2
Database Startup........................................................................................................................... 5-2
Database Shutdown...................................................................................................................... 5-3
Redo Log Files..................................................................................................................................... 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-3
Connecting to a Remote Database from within SQL*Plus..................................................... 6-3
Connecting to a Remote Database as You Start SQL*Plus..................................................... 6-4
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
vi
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
Options........................................................................................................................................... 7-2
Logon.............................................................................................................................................. 7-9
Start............................................................................................................................................... 7-10
Setting Up the Site Profile......................................................................................................... 7-10
Setting Up the User Profile........................................................................................................ 7-10
Receiving a Return Code........................................................................................................... 7-10
Getting Help...................................................................................................................................... 7-12
8 Command Reference
SQL*Plus Command Summary....................................................................................................... 8-2
@ (“at” sign).......................................................................................................................................... 8-5
@@ (double “at” sign)......................................................................................................................... 8-7
/ (slash).................................................................................................................................................. 8-9
ACCEPT.............................................................................................................................................. 8-10
APPEND............................................................................................................................................. 8-12
ARCHIVE LOG................................................................................................................................. 8-13
ATTRIBUTE....................................................................................................................................... 8-16
BREAK................................................................................................................................................ 8-18
BTITLE................................................................................................................................................ 8-23
CHANGE............................................................................................................................................ 8-24
CLEAR................................................................................................................................................. 8-27
COLUMN........................................................................................................................................... 8-29
COMPUTE......................................................................................................................................... 8-40
CONNECT......................................................................................................................................... 8-46
COPY................................................................................................................................................... 8-48
DEFINE............................................................................................................................................... 8-52
DEL...................................................................................................................................................... 8-54
vii
DESCRIBE.......................................................................................................................................... 8-56
DISCONNECT.................................................................................................................................. 8-62
EDIT.................................................................................................................................................... 8-63
EXECUTE............................................................................................................................................ 8-65
EXIT..................................................................................................................................................... 8-66
GET...................................................................................................................................................... 8-68
HELP.................................................................................................................................................... 8-69
HOST................................................................................................................................................... 8-70
INPUT................................................................................................................................................. 8-72
LIST..................................................................................................................................................... 8-74
PASSWORD....................................................................................................................................... 8-76
PAUSE................................................................................................................................................. 8-77
PRINT.................................................................................................................................................. 8-78
PROMPT............................................................................................................................................. 8-79
RECOVER.......................................................................................................................................... 8-80
REMARK............................................................................................................................................ 8-86
REPFOOTER...................................................................................................................................... 8-87
REPHEADER..................................................................................................................................... 8-89
RUN..................................................................................................................................................... 8-93
SAVE.................................................................................................................................................... 8-94
SET....................................................................................................................................................... 8-96
SHOW............................................................................................................................................... 8-122
SHUTDOWN................................................................................................................................... 8-127
SPOOL.............................................................................................................................................. 8-129
START............................................................................................................................................... 8-130
STARTUP.......................................................................................................................................... 8-132
STORE............................................................................................................................................... 8-135
TIMING............................................................................................................................................ 8-136
TTITLE.............................................................................................................................................. 8-138
UNDEFINE....................................................................................................................................... 8-142
VARIABLE........................................................................................................................................ 8-143
WHENEVER OSERROR............................................................................................................... 8-150
WHENEVER SQLERROR............................................................................................................. 8-152
viii
A SQL*Plus Error Messages
B Release 9.0.1 Enhancements
C SQL*Plus Limits
D SQL Command List
E Security
F Obsolete SQL*Plus Commands
Glossary
Index
ix
x