SQL Plus. User’s Guide and Reference

SQL Plus User’s Guide and Reference is intended for business and technical end users and system administrators who perform the following tasks:■ Enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks■ Format, perform calculations on. store, print and create web output of query results■ List column definitions for any table■ Send messages to and accept responses from an end user■ Perform database administrationTo use this document, you need a basic understanding of the SQL database language. If you do not have any familiarity with this database tool, you should refer to the OracIe 9i SQL Reference. If you plan to use the PL/SQL database language in conjunction with SQL Plus, refer to the PL/SQL User s Guide and Reference for information on using PL/SQL.

---

Table of contents :
PDF Directory……Page 0
Starting SQL*Plus 2-3……Page 3
Writing Interactive Commands 3-19……Page 4
Defining Page and Report Titles and Dimensions 4-23……Page 5
Copying Data between Tables on One Database 6-8……Page 6
HELP 8-70……Page 7
WHENEVER SQLERROR 8-154……Page 8
F Obsolete SQL*Plus Commands……Page 9
Send Us Your Comments……Page 11
Preface……Page 13
1 Introduction……Page 21
Who Can Use SQL*Plus……Page 22
Other Ways of Working with Oracle……Page 23
Conventions for Command Syntax……Page 24
Sample Tables……Page 25
Hardware and Software……Page 26
Username and Password……Page 27
Access to Sample Tables……Page 28
2 Learning SQL*Plus Basics……Page 29
Using the Keyboard……Page 30
Starting SQL*Plus……Page 31
Leaving SQL*Plus……Page 32
Getting Help……Page 33
Running SQL Commands……Page 34
Understanding SQL Command Syntax……Page 35
The SQL Buffer……Page 37
Running PL/SQL Blocks……Page 38
Running SQL*Plus Commands……Page 39
Understanding SQL*Plus Command Syntax……Page 40
Saving Changes to the Database Automatically……Page 41
Running Host Operating System Commands……Page 43
Listing a Table Definition……Page 44
Interpreting Error Messages……Page 45
3 Manipulating Commands……Page 47
Editing Commands……Page 48
Listing the Buffer Contents……Page 49
Editing the Current Line……Page 50
Adding a New Line……Page 51
Appending Text to a Line……Page 52
Deleting Lines……Page 53
Storing Commands in Command Files……Page 54
Creating a Command File by Saving the Buffer Contents……Page 55
Creating a Command File by Using INPUT and SAVE……Page 56
Creating Command Files with a System Editor……Page 57
Using /*…*/……Page 58
Retrieving Command Files……Page 59
Running Command Files……Page 60
Running a Command File as You Start SQL*Plus……Page 61
Modifying Command Files……Page 62
Setting Up Your SQL*Plus Environment……Page 63
Storing and Restoring SQL*Plus System Variables……Page 64
Writing Interactive Commands……Page 65
Using Substitution Variables……Page 66
Where and How to Use Substitution Variables……Page 67
Avoiding Unnecessary Prompts for Values……Page 69
Passing Parameters through the START Command……Page 71
Prompting for and Accepting User Variable……Page 73
Customizing Prompts for Substitution Variable……Page 74
Sending a Message and Accepting [Return] as Input……Page 75
Creating Bind Variables……Page 76
Displaying Bind Variables……Page 77
Using REFCURSOR Bind Variables……Page 78
Controlling the Report……Page 81
Execution Plan……Page 82
Statistics……Page 83
Tracing Parallel and Distributed Queries……Page 85
4 Formatting Query Results……Page 89
Changing Column Headings……Page 90
Formatting NUMBER Columns……Page 92
Formatting Datatypes……Page 94
Copying Column Display Attributes……Page 96
Suppressing and Restoring Column Display Attributes……Page 97
Printing a Line of Characters after Wrapped Column Values……Page 98
Clarifying Your Report with Spacing and Summary Lines……Page 99
Suppressing Duplicate Values in Break Columns……Page 100
Inserting Space when a Break Column’s Value Changes……Page 101
Using Multiple Spacing Techniques……Page 102
Listing and Removing Break Definitions……Page 103
Computing Summary Lines when a Break Column’s Value Changes……Page 104
Computing Summary Lines at the End of the Report……Page 108
Computing Multiple Summary Values and Lines……Page 109
Listing and Removing COMPUTE Definitions……Page 110
Setting the Top and Bottom Titles and Headers and Footers……Page 111
Displaying the Page Number and other System-Maintained Values in Titles……Page 116
Listing, Suppressing, and Restoring Page Title Definitions……Page 117
Displaying Column Values in Titles……Page 118
Setting Page Dimensions……Page 120
Creating a Flat File……Page 123
Sending Results to a Printer……Page 124
Creating Web Reports Interactively……Page 126
Creating Embedded Web Reports……Page 130
Suppressing the Display of SQL*Plus Commands in Web Reports……Page 133
5 Database Administration……Page 135
Database Startup……Page 136
Database Shutdown……Page 137
ARCHIVELOG Mode……Page 138
Database Recovery……Page 139
6 Accessing SQL Databases……Page 141
Connecting to a Remote Database……Page 142
Connecting to a Remote Database as You Start SQL*Plus……Page 143
Understanding COPY Command Syntax……Page 144
Controlling Treatment of the Destination Table……Page 146
Copying Data between Tables on One Database……Page 148
7 Starting SQL*Plus and Getting Help……Page 153
Starting SQL*Plus Using the SQLPLUS Command……Page 154
Receiving a Return Code……Page 160
Getting Help……Page 162
8 Command Reference……Page 163
SQL*Plus Command Summary……Page 164
@ (“at” sign)……Page 167
@@ (double “at” sign)……Page 169
/ (slash)……Page 171
ACCEPT……Page 172
APPEND……Page 174
ARCHIVE LOG……Page 176
ATTRIBUTE……Page 180
BREAK……Page 182
BTITLE……Page 187
CHANGE……Page 188
CLEAR……Page 191
COLUMN……Page 193
COMPUTE……Page 203
CONNECT……Page 209
COPY……Page 211
DEFINE……Page 214
DEL……Page 216
DESCRIBE……Page 218
DISCONNECT……Page 225
EDIT……Page 226
EXECUTE……Page 228
EXIT……Page 229
GET……Page 231
HELP……Page 232
HOST……Page 233
INPUT……Page 234
LIST……Page 236
PASSWORD……Page 238
PAUSE……Page 239
PRINT……Page 240
PROMPT……Page 241
RECOVER……Page 243
REMARK……Page 249
REPFOOTER……Page 250
REPHEADER……Page 252
RUN……Page 256
SAVE……Page 257
SET……Page 259
SHOW……Page 286
SHUTDOWN……Page 291
SPOOL……Page 293
START……Page 294
STARTUP……Page 296
STORE……Page 300
TIMING……Page 301
TTITLE……Page 303
UNDEFINE……Page 307
VARIABLE……Page 308
WHENEVER OSERROR……Page 314
WHENEVER SQLERROR……Page 316
A COPY Command Messages and Codes……Page 319
SQL*Plus Release 8.1.6 Enhancements……Page 323
C SQL*Plus Limits……Page 325
D SQL Command List……Page 327
E Security……Page 329
Creating the Table……Page 330
Description and Use of Columns……Page 331
Disabling SQL*Plus, SQL, and PL/SQL Commands……Page 332
Disabling Roles……Page 335
SQLPLUS -RESTRICT……Page 336
F Obsolete SQL*Plus Commands……Page 339
SQL*Plus Obsolete Command Alternatives……Page 340
DOCUMENT……Page 341
SET BUFFER……Page 342
SET DOCUMENT……Page 343
SET SPACE……Page 344
TTITLE (old form)……Page 345
Glossary……Page 347
Index……Page 373