SQL Plus User’s Guide and Reference

SQL*Plus User’s Guide and Reference, Release 10.1. Part No. B12170-01. Copyright й 1996, 2003 Oracle Corporation. All rights reserved. …

More PDF Content

SQL Plus User\’s Guide and Reference
Send Us Your Comments … xvii Preface… xix
Intended Audience … xx Documentation Accessibility … xx Structure … xxi Related Documents… xxiii Conventions… xxiv

What\’s New in SQL*Plus? … xxxi
New Features in SQL*Plus and iSQL*Plus 10.1 … xxxi

SQL*Plus Quick Start … xxxv
Resources … xxxvi What is SQL*Plus … xxxvi Before Starting SQL*Plus or iSQL*Plus … xxxvii Starting SQL*Plus Command-line… xxxvii Starting SQL*Plus Windows GUI… xxxviii Starting and Stopping the iSQL*Plus Application Server … xxxix Starting iSQL*Plus … xxxix Connecting to a Different Database … xl Sample Schemas and SQL*Plus … xl Running your first Query … xli Exiting SQL*Plus… xlii
SQL*Plus Getting Started

SQL*Plus Overview
What is SQL*Plus… SQL*Plus Command-line and Windows GUI Architecture … SQL*Plus Client… Oracle Database … iSQL*Plus Architecture … Web Browser … Application Server … Oracle Database … SQL*Plus Installation … SQL*Plus Date Format … Who Can Use SQL*Plus … How Can I Learn SQL*Plus … How to Use the SQL*Plus Guide … Oracle Database Sample Schemas and SQL*Plus … Unlocking the Sample Tables… 1-1 1-2 1-2 1-2 1-2 1-3 1-3 1-3 1-4 1-4 1-4 1-5 1-5 1-5 1-6

SQL*Plus User Interface
SQL*Plus Command-line User Interface … The Command-line Screen … Changing the Command-line Font and Font Size… Windows Graphical User Interface … Using the Mouse to Copy Text to the Command Prompt … Using Command Keys … Using the Windows GUI Menus … Changing the Windows GUI Font and Font Size… iSQL*Plus User Interface … iSQL*Plus Navigation … iSQL*Plus Login Screen … iSQL*Plus DBA Login Screen… iSQL*Plus Workspace … iSQL*Plus DBA Workspace… 2-1 2-1 2-2 2-3 2-3 2-4 2-4 2-8 2-10 2-10 2-12 2-13 2-16 2-17

iSQL*Plus History Screen … iSQL*Plus Input Required Screen … iSQL*Plus Preferences Screen … Preferences and Equivalent SET Commands …

Configuring SQL*Plus
SQL*Plus and iSQL*Plus Environment Variables… SQL*Plus and iSQL*Plus Configuration … Site Profile … User Profile … Storing and Restoring SQL*Plus System Variables … Installing Command-line Help… Configuring Oracle Net Services… iSQL*Plus Application Server Configuration … Changing the iSQL*Plus Application Server Port in Use … Testing if the iSQL*Plus Application Server is Running … Setting the Level of iSQL*Plus Logging … Setting the Session Time Out … Enabling or Disabling Restricted Database Access … Enabling iSQL*Plus DBA Access… Enabling SSL with iSQL*Plus … Enabling or Disabling iSQL*Plus or iSQL*Plus Help… Enabling User Defined HTML Markup … iSQL*Plus Web Browser Configuration … Session Integrity… Retained Session Settings … Windows Graphical User Interface Configuration … Setting Options and Values Using the Environment Dialog … Customizing Registry Entries that affect SQL*Plus on Windows… 3-1 3-4 3-6 3-7 3-8 3-10 3-12 3-12 3-12 3-14 3-15 3-15 3-16 3-17 3-21 3-24 3-24 3-25 3-25 3-26 3-26 3-26 3-28

Starting SQL*Plus
Login Username and Password … Changing your Password … Changing Your Password in iSQL*Plus … Expired Password… 4-1 4-2 4-2 4-4

Expired Password Screen in iSQL*Plus… Connecting to a Database … Net Service Name … Full Connection Identifier … Easy Connection Identifier … Connectionless Session with /NOLOG… Starting SQL*Plus … Starting Command-line SQL*Plus… Getting Command-line Help … Starting the Windows Graphical User Interface … Starting the iSQL*Plus Application Server … To Check the HTTP Port used by the iSQL*Plus Application Server … Stopping the iSQL*Plus Application Server … Running iSQL*Plus… Running iSQL*Plus as a DBA … Starting iSQL*Plus from a URL … Getting Help in iSQL*Plus… Exiting SQL*Plus … Exiting the Command-line User Interface … Exiting the Windows Graphical User Interface… Exiting the iSQL*Plus User Interface … SQLPLUS Program Syntax … Options … Logon … Start …

Part II 5

Using SQL*Plus

SQL*Plus Basics
Entering and Executing Commands … The SQL Buffer… Executing Commands … Listing a Table Definition … Listing PL/SQL Definitions … Running SQL Commands … 5-2 5-3 5-3 5-4 5-5 5-5

Understanding SQL Command Syntax… Running PL/SQL Blocks… Creating Stored Procedures … Running SQL*Plus Commands … Understanding SQL*Plus Command Syntax … System Variables that Affect How Commands Run … Stopping a Command while it is Running … Running Operating System Commands … Pausing the Display … Saving Changes to the Database Automatically … Interpreting Error Messages …

Using Scripts in SQL*Plus
Editing Scripts … Writing Scripts with a System Editor … 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 … 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 … Exiting from a Script with a Return Code … Defining Substitution Variables … Using Predefined Variables … Using Substitution Variables … Where and How to Use Substitution Variables … Avoiding Unnecessary Prompts for Values … 6-2 6-2 6-3 6-4 6-5 6-7 6-8 6-9 6-9 6-10 6-10 6-10 6-11 6-13 6-14 6-15 6-15 6-16 6-16 6-17 6-17 6-20

vii

Restrictions … System Variables and iSQL*Plus Preferences… Substitution Variables in iSQL*Plus … iSQL*Plus Input Required Screen … Passing Parameters through the START Command … 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 … Using Bind Variables … Creating Bind Variables … Referencing Bind Variables … Displaying Bind Variables… Using REFCURSOR Bind Variables …

Formatting SQL*Plus Reports
Formatting Columns… Changing Column Headings … Formatting NUMBER Columns… Formatting Datatypes … Copying Column Display Attributes… Listing and Resetting Column Display Attributes … Suppressing and Restoring Column Display Attributes … Printing a Line of Characters after Wrapped Column Values … 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 … 7-1 7-1 7-4 7-5 7-9 7-9 7-10 7-10 7-12 7-13 7-14 7-15 7-15 7-16 7-17 7-21 7-22 7-23

Defining Page and Report Titles and Dimensions … Setting the Top and Bottom Titles and Headers and Footers … Displaying System-Maintained Values in Titles… Listing, Suppressing, and Restoring Page Title Definitions… Displaying Column Values in Titles … Displaying the Current Date in Titles… Setting Page Dimensions … Storing and Printing Query Results … Creating a Flat File… Sending Results to a File… Sending Results to a Printer …

Generating HTML Reports from SQL*Plus
Creating Reports using Command-line SQL*Plus… Creating Reports … Suppressing the Display of SQL*Plus Commands in Reports… HTML Entities… Creating Reports using iSQL*Plus … 8-1 8-2 8-6 8-7 8-8

Tuning SQL*Plus
Tracing Statements … Controlling the Autotrace Report … Execution Plan… Statistics… Collecting Timing Statistics … Tracing Parallel and Distributed Queries … SQL*Plus Script Tuning … COLUMN NOPRINT… SET APPINFO OFF… SET ARRAYSIZE … SET DEFINE OFF… SET FLUSH OFF … SET LINESIZE … SET LONGCHUNKSIZE … SET PAGESIZE … 9-1 9-2 9-4 9-4 9-8 9-8 9-11 9-12 9-12 9-12 9-12 9-12 9-13 9-13 9-13

SET SERVEROUTPUT … SET SQLPROMPT… SET TAB … SET TERMOUT … SET TRIMOUT ON SET TRIMSPOOL ON … UNDEFINE …

SQL*Plus Security
PRODUCT_USER_PROFILE Table … Creating the PUP Table… PUP Table Structure … Description and Use of PUP Columns… PUP Table Administration … Disabling SQL*Plus, SQL, and PL/SQL Commands … Creating and Controlling Roles … Disabling SET ROLE… Disabling User Roles … Disabling Commands with SQLPLUS -RESTRICT… Program Argument Security … iSQL*Plus Security … Enabling SSL with iSQL*Plus… Administration Privileges … Enabling DBA Access… Enabling or Disabling Restricted Database Access… Security Usage Notes … 10-1 10-2 10-2 10-3 10-4 10-4 10-7 10-7 10-8 10-9 10-10 10-10 10-11 10-11 10-11 10-12 10-12

Database Administration with SQL*Plus
Overview … Introduction to Database Startup and Shutdown … Database Startup … Database Shutdown… Redo Log Files … ARCHIVELOG Mode… Database Recovery … 11-1 11-2 11-2 11-3 11-4 11-4 11-5

SQL*Plus Globalization Support
Configuring Globalization Support in Command-line SQL*Plus … SQL*Plus Client … Oracle Database … Configuring Multiple Language Support in iSQL*Plus … Web Browser … Application Server… NLS_LANG Environment Variable … Viewing NLS_LANG Settings … Setting NLS_LANG … 12-2 12-2 12-2 12-2 12-2 12-3 12-3 12-4 12-5

Part III 13

SQL*Plus Reference

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 … 13-2 13-6 13-8 13-10 13-11 13-13 13-14 13-17 13-19 13-24 13-26 13-29 13-31 13-42 13-48 13-50 13-51 13-53 13-57 13-59

DISCONNECT … 13-66 EDIT … 13-67 EXECUTE … 13-69 EXIT … 13-70 GET … 13-72 HELP … 13-74 HOST … 13-75 INPUT … 13-77 LIST … 13-79 PASSWORD … 13-81 PAUSE … 13-82 PRINT … 13-83 PROMPT … 13-84 RECOVER … 13-85 REMARK … 13-94 REPFOOTER … 13-95 REPHEADER … 13-97 RUN … 13-100 SAVE … 13-101 SET … 13-103 SET System Variable Summary … 13-104 SET APPI[NFO]… 13-107 SET ARRAY[SIZE] … 13-109 SET AUTO[COMMIT] … 13-109 SET AUTOP[RINT]… 13-109 SET AUTORECOVERY… 13-110 SET AUTOT[RACE] … 13-110 SET BLO[CKTERMINATOR] … 13-111 SET CMDS[EP] … 13-111 SET COLSEP … 13-112 SET COM[PATIBILITY]… 13-113 SET CON[CAT] … 13-114 SET COPYC[OMMIT] … 13-114 SET COPYTYPECHECK … 13-114 SET DEF[INE]… 13-114

SET DESCRIBE… SET ECHO … SET EDITF[ILE]… SET EMB[EDDED]… SET ESC[APE] … SET FEED[BACK] … SET FLAGGER … SET FLU[SH] … SET HEA[DING]… SET HEADS[EP] … SET INSTANCE … SET LIN[ESIZE] … SET LOBOF[FSET] … SET LOGSOURCE … SET LONG … SET LONGC[HUNKSIZE] … SET MARK[UP] … SET NEWP[AGE]… SET NULL… SET NUMF[ORMAT] … SET NUM[WIDTH] … SET PAGES[IZE]… SET PAU[SE] … SET RECSEP … SET RECSEPCHAR … SET SERVEROUT[PUT]… SET SHIFT[INOUT]… SET SHOW[MODE] … SET SQLBL[ANKLINES] … SET SQLC[ASE] … SET SQLCO[NTINUE]… SET SQLN[UMBER] … SET SQLPLUSCOMPAT[IBILITY] … SET SQLPRE[FIX] … SET SQLP[ROMPT]…

13-115 13-116 13-116 13-116 13-117 13-117 13-118 13-118 13-118 13-119 13-119 13-120 13-120 13-121 13-121 13-121 13-122 13-123 13-124 13-124 13-124 13-124 13-125 13-125 13-125 13-126 13-128 13-128 13-129 13-129 13-130 13-130 13-130 13-132 13-132

SET SQLT[ERMINATOR]… SET SUF[FIX] … SET TAB … SET TERM[OUT]… SET TI[ME]… SET TIMI[NG] … SET TRIM[OUT]… SET TRIMS[POOL] … SET UND[ERLINE] … SET VER[IFY] … SET WRA[P] … SHOW … SHUTDOWN … SPOOL … START … STARTUP … STORE … TIMING … TTITLE … UNDEFINE … VARIABLE … WHENEVER OSERROR … WHENEVER SQLERROR …

SQL*Plus Error Messages
SQL*Plus Error Messages … 14-1 iSQL*Plus Error Messages … 14-45 COPY Command Messages … 14-54

Part IV SQL*Plus Appendixes A B SQL*Plus Limits SQL*Plus COPY Command
COPY Command Syntax … B-1 Copying Data from One Database to Another … B-4 Copying Data between Tables on One Database … B-9

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 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) … SHO[W] LABEL (obsolete) … TTI[TLE] text (obsolete old form) … C-1 C-2 C-2 C-3 C-3 C-3 C-4 C-4 C-4 C-4 C-4 C-5 C-5 C-5

Download SQL Plus User's Guide and Reference pdf from download.oracle.com, 552 pages, 7337.43KB.
Related Books

Leave a Reply