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 11.2

SQL*Plus Quick Start

• SQL*Plus Resources
• SQL*Plus Overview
• SQL*Plus Prerequisites
• Starting SQL*Plus Command-line
• 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

• The Command-line Screen
• Changing the Command-line Font and Font Size

2 Configuring SQL*Plus

• SQL*Plus Environment Variables
  • SQLPATH Registry Entry
• SQL*Plus Configuration
  • Site Profile
  • User Profile
  • Storing and Restoring SQL*Plus System Variables
  • Installing Command-line Help
  • Configuring Oracle Net Services

3 Starting SQL*Plus

• Login Username and Password
  • Secure External Password Store
  • Expired Password
  • Changing your Password
• 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
• Exiting SQL*Plus Command-line
• 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
• 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

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

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
• 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 ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
  • SET ESC[APE] {\ | c | ON | OFF}
  • SET ESCCHAR {@ | ? | % | $ | OFF}
  • SET EXITC[OMMIT] {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 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 XMLOPT[IMIZATIONCHECK] [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
• 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 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 11g 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