SQL Plus. User’s Guide and Reference

Watt S.

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 :
Contents……Page 3
Send Us Your Comments……Page 11
Preface……Page 13
Organization……Page 14
Related Documentation……Page 16
Conventions……Page 17
Documentation Accessibility……Page 20
Part I Understanding SQL*Plus……Page 21
1 Introduction……Page 23
Who Can Use SQL*Plus……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
Starting SQL*Plus……Page 30
Leaving SQL*Plus……Page 31
Entering and Executing Commands……Page 32
Running SQL Commands……Page 34
Running PL/SQL Blocks……Page 38
Running SQL*Plus Commands……Page 39
Variables that Affect Running Commands……Page 41
Saving Changes to the Database Automatically……Page 42
Collecting Timing Statistics on Commands You Run……Page 43
Getting Help……Page 44
Listing a Table Definition……Page 45
Interpreting Error Messages……Page 46
3 Manipulating Commands……Page 49
Editing Commands……Page 50
Listing the Buffer Contents……Page 51
Editing the Current Line……Page 52
Adding a New Line……Page 53
Appending Text to a Line……Page 54
Deleting Lines……Page 55
Storing Commands in Command Files……Page 56
Placing Comments in Command Files……Page 60
Retrieving Command Files……Page 64
Running Command Files……Page 65
Nesting Command Files……Page 66
Modifying Command Files……Page 67
Setting Up Your SQL*Plus Environment……Page 68
Storing and Restoring SQL*Plus System Variables……Page 69
Defining User Variables……Page 70
Using Substitution Variables……Page 71
Passing Parameters through the START Command……Page 77
Communicating with the User……Page 78
Using Bind Variables……Page 81
Referencing Bind Variables……Page 82
Using REFCURSOR Bind Variables……Page 83
Controlling the Report……Page 87
Execution Plan……Page 88
Statistics……Page 89
Tracing Parallel and Distributed Queries……Page 92
4 Formatting Query Results……Page 95
Changing Column Headings……Page 96
Formatting NUMBER Columns……Page 98
Formatting Datatypes……Page 100
Copying Column Display Attributes……Page 102
Suppressing and Restoring Column Display Attributes……Page 103
Printing a Line of Characters after Wrapped Column Values……Page 104
Clarifying Your Report with Spacing and Summary Lines……Page 105
Suppressing Duplicate Values in Break Columns……Page 106
Inserting Space when a Break Column’s Value Changes……Page 107
Using Multiple Spacing Techniques……Page 108
Listing and Removing Break Definitions……Page 109
Computing Summary Lines when a Break Column’s Value Changes……Page 110
Computing Summary Lines at the End of the Report……Page 113
Computing Multiple Summary Values and Lines……Page 114
Setting the Top and Bottom Titles and Headers and Footers……Page 116
Displaying the Page Number and other System-Maintained Values in Titles……Page 121
Listing, Suppressing, and Restoring Page Title Definitions……Page 122
Displaying Column Values in Titles……Page 123
Displaying the Current Date in Titles……Page 124
Setting Page Dimensions……Page 125
Storing and Printing Query Results……Page 127
Sending Results to a Printer……Page 128
Creating Static Web Reports……Page 131
Creating Dynamic Web Reports with CGI Scripts……Page 136
HTML Entities……Page 141
5 Database Administration……Page 143
Database Startup……Page 144
Database Shutdown……Page 145
ARCHIVELOG Mode……Page 146
Database Recovery……Page 147
6 Accessing SQL Databases……Page 149
Connecting to the Default Database……Page 150
Connecting to a Remote Database from within SQL*Plus……Page 151
Copying Data from One Database to Another……Page 152
Understanding COPY Command Syntax……Page 153
Controlling Treatment of the Destination Table……Page 154
Specifying Another User’s Table……Page 156
Copying Data between Tables on One Database……Page 157
Part II Reference……Page 159
7 Starting SQL*Plus and Getting Help……Page 161
Options……Page 162
Logon……Page 169
Receiving a Return Code……Page 170
Getting Help……Page 172
8 Command Reference……Page 173
SQL*Plus Command Summary……Page 174
@ (“at” sign)……Page 177
@@ (double “at” sign)……Page 179
/ (slash)……Page 181
ACCEPT……Page 182
APPEND……Page 184
ARCHIVE LOG……Page 185
ATTRIBUTE……Page 188
BREAK……Page 190
BTITLE……Page 195
CHANGE……Page 196
CLEAR……Page 199
COLUMN……Page 201
COMPUTE……Page 212
CONNECT……Page 218
COPY……Page 220
DEFINE……Page 224
DEL……Page 226
DESCRIBE……Page 228
DISCONNECT……Page 234
EDIT……Page 235
EXECUTE……Page 237
EXIT……Page 238
GET……Page 240
HELP……Page 241
HOST……Page 242
INPUT……Page 244
LIST……Page 246
PASSWORD……Page 248
PAUSE……Page 249
PRINT……Page 250
PROMPT……Page 251
RECOVER……Page 252
REMARK……Page 258
REPFOOTER……Page 259
REPHEADER……Page 261
RUN……Page 265
SAVE……Page 266
SET……Page 268
SHOW……Page 294
SHUTDOWN……Page 299
SPOOL……Page 301
START……Page 302
STARTUP……Page 304
STORE……Page 307
TIMING……Page 308
TTITLE……Page 310
UNDEFINE……Page 314
VARIABLE……Page 315
WHENEVER OSERROR……Page 322
WHENEVER SQLERROR……Page 324
A SQL*Plus Error Messages……Page 327
SQL*Plus Error Messages and Codes……Page 328
COPY Command Messages……Page 360
B Release 9.0.1 Enhancements……Page 363
SQL*Plus Release 9.0.1 Enhancements……Page 364
C SQL*Plus Limits……Page 367
D SQL Command List……Page 369
E Security……Page 371
PUP Table Structure……Page 372
Description and Use of PUP Columns……Page 373
Disabling SQL*Plus, SQL, and PL/SQL Commands……Page 374
Creating and Controlling Roles……Page 376
Disabling User Roles……Page 377
Disabling Commands with SQLPLUS -RESTRICT……Page 378
F Obsolete SQL*Plus Commands……Page 379
SQL*Plus Obsolete Command Alternatives……Page 380
DOCUMENT……Page 381
SET BUFFER……Page 382
SET DOCUMENT……Page 383
SET SPACE……Page 384
TTITLE (old form)……Page 385
Glossary……Page 387
Index……Page 413