Contents

Title and Copyright Information

Preface

Send Us Your Comments

Part I Understanding SQL*Plus

1 Introduction

Overview of SQL*Plus

Basic Concepts

Who Can Use SQL*Plus

Other Ways of Working with Oracle

Using this Guide

Conventions for Command Syntax

Sample Tables

What You Need to Run SQL*Plus

Hardware and Software

Information Specific to Your Operating System

Username and Password

Access to Sample Tables

2 Learning SQL*Plus Basics

Getting Started

Using the Keyboard

Starting SQL*Plus

Shortcuts to Starting SQL*Plus

Leaving SQL*Plus

Entering and Executing Commands

Entering Commands

Getting Help

Executing Commands

Running SQL Commands

Understanding SQL Command Syntax

The SQL Buffer

Executing the Current SQL Command or PL/SQL Block from
the Command Prompt

Running PL/SQL Blocks

Running SQL*Plus Commands

Understanding SQL*Plus Command Syntax

Variables that Affect Running Commands

Saving Changes to the Database Automatically

Stopping a Command while it is Running

Collecting Timing Statistics on Commands You Run

Running Host Operating System Commands

Getting Help

Listing a Table Definition

Listing PL/SQL Definitions

Controlling the Display

Interpreting Error Messages

3 Manipulating Commands

Editing Commands

Listing the Buffer Contents

Editing the Current Line

Adding a New Line

Appending Text to a Line

Deleting Lines

Editing Commands with a System Editor

Saving Commands for Later Use

Storing Commands in Command Files

Creating a Command File by Saving the Buffer Contents

Creating a Command File by Using INPUT and SAVE

Creating Command Files with a System Editor

Placing Comments in Command Files

Using the REMARK Command

Using /*...*/

Using - -

Retrieving Command Files

Running Command Files

Running a Command File as You Start SQL*Plus

Nesting Command Files

Modifying Command Files

Exiting from a Command File with a Return Code

Setting Up Your SQL*Plus Environment

Modifying Your LOGIN File

Storing and Restoring SQL*Plus System Variables

Restoring the System Variables

Writing Interactive Commands

Defining User 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

Prompting for and Accepting User Variable

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

Tracing Statements

Controlling the Report

Execution Plan

Statistics

Tracing Parallel and Distributed Queries

4 Formatting Query Results

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 the Page Number and other 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

Sending Results to a File

Storing and Printing Query Results

Creating a Flat File

Sending Results to a Printer

5 Database Administration

Overview

Introduction to Database Startup and Shutdown

Database Startup

Database Shutdown

Redo Log Files

ARCHIVELOG Mode

Database Recovery

6 Accessing SQL Databases

Connecting to the Default Database

Connecting to a Remote Database

Connecting to a Remote Database from within SQL*Plus

Connecting to a Remote Database as You Start SQL*Plus

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

Part II Reference

7 Starting SQL*Plus and Getting Help

Starting SQL*Plus Using the SQLPLUS Command

Setting Up the Site Profile

Setting Up the User Profile

Receiving a Return Code

Getting Help

8 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

DEL

DESCRIBE

DISCONNECT

EDIT

EXECUTE

EXIT

GET

HELP

HOST

INPUT

LIST

PASSWORD

PAUSE

PRINT

PROMPT

RECOVER

REMARK

REPFOOTER

REPHEADER

RUN

SAVE

SET

SHOW

SHUTDOWN

SPOOL

START

STARTUP

STORE

TIMING

TTITLE

UNDEFINE

VARIABLE

WHENEVER OSERROR

WHENEVER SQLERROR

A COPY Command Messages and Codes

B Release 8.1.5 Enhancements

C SQL*Plus Limits

D SQL Command List

E Security

F SQL*Plus Commands from Earlier Releases

Glossary

Index