Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

What's New in SQL*Plus?

New Features in SQL*Plus and iSQL*Plus 10.1

SQL*Plus Quick Start

What is SQL*Plus

Before Starting SQL*Plus or iSQL*Plus

Starting SQL*Plus Command-line

Starting SQL*Plus Windows GUI

Starting and Stopping the iSQL*Plus Application Server

Starting iSQL*Plus

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 Overview

What is SQL*Plus

SQL*Plus Command-line and Windows GUI Architecture

SQL*Plus Client

Oracle Database

iSQL*Plus Architecture

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

2 SQL*Plus User Interface

SQL*Plus Command-line User Interface

The Command-line Screen

Changing the Command-line Font and Font Size

To Change the Command-line Interface Font and Font Size

Using a Special Character in Windows

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

To Change the Windows GUI Font and Font Size

Using a Special Character

iSQL*Plus User Interface

iSQL*Plus Navigation

iSQL*Plus Login Screen

Connection Identifier:

iSQL*Plus DBA Login Screen

Connection Identifier:

iSQL*Plus Workspace

iSQL*Plus DBA Workspace

iSQL*Plus History Screen

iSQL*Plus Input Required Screen

Enter Value for sortcol

iSQL*Plus Preferences Screen

Interface Configuration

System Configuration

Script Formatting

Script Execution

Database Administration

Change Password

Preferences and Equivalent SET Commands

3 Configuring SQL*Plus

SQL*Plus and iSQL*Plus Environment Variables

SQL*Plus and iSQL*Plus Configuration

Default Site Profile Script

Modifying Your LOGIN File

Storing and Restoring SQL*Plus System Variables

Restoring the System Variables

Running the hlpbld.sql Script to Install Command-line Help

Running the helpdrop.sql Script to Remove 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

Grant Users the webDba Role

Revoke the webDba Role

Change User Passwords

Test iSQL*Plus DBA Access

Enabling SSL with iSQL*Plus

1. Generate Keys and Storage File

2. Load Root Certificate into Storage File

3. Request Certificate from CA

4. Import Certificate into Storage File

5. Configure iSQL*Plus to run in SSL mode

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

Using the Registry

SQLPATH Registry Entry

SQLPLUS_FONT Registry Entry

SQLPLUS_FONT_SIZE Registry Entry

4 Starting SQL*Plus

Login Username and Password

Changing your Password

Changing Your Password in iSQL*Plus

Retype new password:

Expired Password

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

Shortcuts to Starting Command-line SQL*Plus

Getting Command-line Help

Starting the Windows Graphical User Interface

Starting the GUI from the Windows Menu

Starting the GUI from the Windows Command Prompt

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

COMPATIBILITY Option

MARKUP Usage Notes

RESTRICT Option

Part II Using SQL*Plus

5 SQL*Plus Basics

Entering and Executing Commands

Executing Commands

Listing a Table Definition

Listing PL/SQL Definitions

Running SQL Commands

Understanding SQL Command Syntax

Dividing a SQL Command into Separate Lines

Ending a SQL Command

Running PL/SQL Blocks

Creating Stored Procedures

Running SQL*Plus Commands

Understanding SQL*Plus Command Syntax

Continuing a Long SQL*Plus Command on Additional Lines

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

6 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

Placing Comments in Scripts

Using the REMARK Command

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

System Variables and iSQL*Plus Preferences

Substitution Variables in iSQL*Plus

iSQL*Plus Input Required Screen

Enter Value for sortcol

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

7 Formatting SQL*Plus Reports

Formatting Columns

Changing Column Headings

Default Headings

Changing Default Headings

Formatting NUMBER Columns

Default Display

Changing the Default Display

Formatting Datatypes

Default Display

Changing the Default Display

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

Positioning Title Elements

Indenting a Title Element

Entering Long Titles

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

8 Generating HTML Reports from SQL*Plus

Creating Reports using Command-line SQL*Plus

Creating Reports

Suppressing the Display of SQL*Plus Commands in Reports

Creating Reports using iSQL*Plus

9 Tuning SQL*Plus

Tracing Statements

Controlling the Autotrace Report

Collecting Timing Statistics

Tracing Parallel and Distributed Queries

SQL*Plus Script Tuning

SET APPINFO OFF

SET LONGCHUNKSIZE

SET SERVEROUTPUT

SET TRIMOUT ON
SET TRIMSPOOL ON

10 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

11 Database Administration with SQL*Plus

Introduction to Database Startup and Shutdown

Database Startup

Database Shutdown

ARCHIVELOG Mode

Database Recovery

12 SQL*Plus Globalization Support

Configuring Globalization Support in Command-line SQL*Plus

SQL*Plus Client

Oracle Database

Configuring Multiple Language Support in iSQL*Plus

Application Server

NLS_LANG Environment Variable

Viewing NLS_LANG Settings

Setting NLS_LANG

Part III SQL*Plus Reference

13 SQL*Plus Command Reference

@@ (double "at" sign)

Predefined Variables

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 COM[PATIBILITY]{V7 | V8 | NATIVE}

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] [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]}

SQL*Plus Compatibility Matrix

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}

WHENEVER OSERROR

WHENEVER SQLERROR

14 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

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)

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)

D Commands Not Supported in iSQL*Plus

Index