Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in SQL*Plus?

• New Features in SQL*Plus Release 10.2

SQL*Plus Quick Start

• SQL*Plus Resources
• SQL*Plus Overview
• SQL*Plus Prerequisites
• Starting SQL*Plus Command-line
• Starting SQL*Plus Windows GUI
• Starting iSQL*Plus
• Starting SQL*Plus Instant Client
• Connecting to a Different Database
• Sample Schemas and SQL*Plus
• Running your first Query
• Exiting SQL*Plus

Part I SQL*Plus Getting Started

1 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
  • iSQL*Plus History Screen
  • iSQL*Plus Input Required Screen
  • iSQL*Plus Preferences Screen
• Preferences and Equivalent SET Commands

2 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 Restricted Database Access
  • Enabling iSQL*Plus DBA Access
  • Enabling SSL with iSQL*Plus
  • Enabling iSQL*Plus or iSQL*Plus Help
  • Enabling Unauthorised Access Banner
  • Enabling URL Access
  • 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 Starting SQL*Plus

• Login Username and Password
• • Secure External Password Store
  • Expired Password
  • Expired Password Screen in iSQL*Plus
  • Changing your Password
  • Changing Your Password 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
  • Starting iSQL*Plus
  • Starting 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 Using SQL*Plus

4 SQL*Plus Basics

• Entering and Executing Commands
• • The SQL Buffer
  • Executing Commands
• Listing a Table Definition
• Listing PL/SQL Definitions
• Running SQL Commands
• • 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

5 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
  • 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

6 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
• 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

7 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 Tuning SQL*Plus

• Tracing Statements
• • Controlling the Autotrace Report
  • Execution Plan
  • Statistics
• Collecting Timing Statistics
• Tracing Parallel and Distributed Queries
• Execution Plan Output in Earlier Databases
• SQL*Plus Script Tuning
• • COLUMN NOPRINT
  • SET APPINFO OFF
  • SET ARRAYSIZE
  • SET DEFINE OFF
  • SET FLUSH OFF
  • SET LINESIZE
  • SET LONGCHUNKSIZE
  • SET PAGESIZE
  • SET SERVEROUTPUT
  • SET SQLPROMPT
  • SET TAB
  • SET TERMOUT
  • SET TRIMOUT ON SET TRIMSPOOL ON
  • UNDEFINE

9 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 Database Administration with SQL*Plus

• Overview
• Introduction to Database Startup and Shutdown
• • Database Startup
  • Database Shutdown
• Redo Log Files
• • ARCHIVELOG Mode
• Database Recovery

11 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

Part III SQL*Plus Reference

12 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
• DISCONNECT
• EDIT
• EXECUTE
• EXIT
• GET
• HELP
• HOST
• INPUT
• LIST
• PASSWORD
• PAUSE
• PRINT
• PROMPT
• RECOVER
• REMARK
• REPFOOTER
• REPHEADER
• RUN
• SAVE
• SET
• SET System Variable Summary
• • SET APPI[NFO]{ON | OFF | text}
  • SET ARRAY[SIZE] {15 | n}
  • SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
  • SET AUTOP[RINT] {ON | OFF}
  • SET AUTORECOVERY [ON | OFF]
  • SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
  • SET BLO[CKTERMINATOR] {. | c | ON | OFF}
  • SET CMDS[EP] {; | c | ON | OFF}
  • SET COLSEP { | text}
  • SET CON[CAT] {. | c | ON | OFF}
  • SET COPYC[OMMIT] {0 | n}
  • SET COPYTYPECHECK {ON | OFF}
  • SET DEF[INE] {& | c | ON | OFF}
  • SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
  • SET ECHO {ON | OFF}
  • SET EDITF[ILE] file_name[.ext]
  • SET EMB[EDDED] {ON | OFF}
  • SET ESC[APE] {\ | c | ON | OFF}
  • SET FEED[BACK] {6 | n | ON | OFF}
  • SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
  • SET FLU[SH] {ON | OFF}
  • SET HEA[DING] {ON | OFF}
  • SET HEADS[EP] { | | c | ON | OFF}
  • SET INSTANCE [instance_path | LOCAL]
  • SET LIN[ESIZE] {80 | n} SET LIN[ESIZE] {150 | n} in iSQL*Plus
  • SET LOBOF[FSET] {1 | n}
  • SET LOGSOURCE [pathname]
  • SET LONG {80 | n}
  • SET LONGC[HUNKSIZE] {80 | n}
  • SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
  • SET NEWP[AGE] {1 | n | NONE}
  • SET NULL text
  • SET NUMF[ORMAT] format
  • SET NUM[WIDTH] {10 | n}
  • SET PAGES[IZE] {14 | n}
  • SET PAU[SE] {ON | OFF | text}
  • SET RECSEP {WR[APPED] | EA[CH] | OFF}
  • SET RECSEPCHAR { | c}
  • SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
  • SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
  • SET SHOW[MODE] {ON | OFF}
  • SET SQLBL[ANKLINES] {ON | OFF}
  • SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
  • SET SQLCO[NTINUE] {> | text}
  • SET SQLN[UMBER] {ON | OFF}
  • SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
  • SET SQLPRE[FIX] {# | c}
  • SET SQLP[ROMPT] {SQL> | text}
  • SET SQLT[ERMINATOR] {; | c | ON | OFF}
  • SET SUF[FIX] {SQL | text}
  • SET TAB {ON | OFF}
  • SET TERM[OUT] {ON | OFF}
  • SET TI[ME] {ON | OFF}
  • SET TIMI[NG] {ON | OFF}
  • SET TRIM[OUT] {ON | OFF}
  • SET TRIMS[POOL] {ON | OFF}
  • SET UND[ERLINE] {- | c | ON | OFF}
  • SET VER[IFY] {ON | OFF}
  • SET WRA[P] {ON | OFF}
  • SET XQUERY BASEURI {text}
  • SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}
  • SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}
  • SET XQUERY CONTEXT {text}
• SHOW
• SHUTDOWN
• SPOOL
• START
• STARTUP
• STORE
• TIMING
• TTITLE
• UNDEFINE
• VARIABLE
• WHENEVER OSERROR
• WHENEVER SQLERROR
• XQUERY

13 SQL*Plus Error Messages

• SQL*Plus Error Messages
• iSQL*Plus Error Messages
• COPY Command Messages

Part IV SQL*Plus Appendixes

A SQL*Plus Limits

B SQL*Plus COPY Command

• COPY Command Syntax
• • Terms
  • Usage
  • Examples
• Copying Data from One Database to Another
• • Understanding COPY Command Syntax
  • Controlling Treatment of the Destination Table
  • Interpreting the Messages that COPY Displays
  • Specifying Another User's Table
• Copying Data between Tables on One Database

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 COM[PATIBILITY]{V7 | V8 | NATIVE} (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)
• TTI[TLE] text (obsolete old form)

D Commands Not Supported in iSQL*Plus

E SQL*Plus Instant Client

• Choosing the SQL*Plus Instant Client to Install
• • Basic Instant Client
  • Lightweight Instant Client
• Installing SQL*Plus Instant Client by Downloading from OTN
• • Installing SQL*Plus Instant Client from Linux RPM Packages
  • Installing SQL*Plus Instant Client from the UNIX or Windows Zip Files
  • List of Files Required for SQL*Plus Instant Client
• Installing SQL*Plus Instant Client from the 10g Client Release Media
• • Installing SQL*Plus Instant Client on UNIX or Linux
  • Installing SQL*Plus Instant Client on Windows
• Configuring SQL*Plus Instant Client
• • Configuring SQL*Plus Instant Client on Linux (from RPMs)
  • Configuring SQL*Plus Instant Client on Linux (from Client Media or Zip File) and UNIX
  • Configuring SQL*Plus Instant Client on Windows
• Connecting to a Database with SQL*Plus Instant Client
• AS SYSDBA or AS SYSOPER Connections with SQL*Plus Instant Client
• Uninstalling Instant Client
• • Uninstalling SQL*Plus Instant Client
  • Uninstalling the Complete Instant Client

Index