Oracle SQL*Plus

Our new edition of this highly popular book describes how to perform, in step-by-step fashion, all of the tasks that Oracle developers and DBAs want to perform with SQL*Plus-and maybe some they didn't realize they could perform. Readers will learn how to write and execute script files, generate ad hoc reports, extract data from the database, query the data dictionary tables, customize an SQL*Plus environment, and much more. This edition also includes an often-requested chapter on SQL itself, along with a complete introduction. It's an indispensable resource for readers new to SQL*Plus, a task-oriented learning tool for those already using it, and a quick reference for all SQL*Plus users.

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at jonathan@gennick.com, and you can learn more about him personally by visiting his website at http://gennick.com.

Preface

xi

1. Introduction to SQL*Plus

1

(19)

What Is SQL*Plus?

1

(6)

History of SQL*Plus

7

(3)

Why Master SQL*Plus?

10

(1)

Creating and Loading the Sample Tables

11

(9)

2. Command-Line SQL*Plus

20

(50)

Starting Command-Line SQL*Plus

20

(6)

Starting Windows SQL*Plus

26

(3)

Some Basic SQL*Plus Commands

29

(6)

Running SQL Queries

35

(3)

Working With PL/SQL

38

(5)

The SQL Buffer

43

(1)

Line Editing

43

(11)

Executing the Statement in the Buffer

54

(4)

Saving and Retrieving the Buffer

58

(2)

The EDIT Command

60

(6)

Executing a Script

66

(1)

The Working Directory

67

(3)

3. Browser-Based SQL*Plus

70

(19)

Starting iSQL*Plus

70

(8)

Executing SQL*Plus Commands

78

(2)

Running SQL Queries

80

(2)

Working with PL/SQL

82

(1)

Executing Scripts

83

(3)

iSQL*Plus Command History

86

(3)

4. A Lightning SQL Tutorial

89

(39)

Data Manipulation Statements

89

(9)

Transactions

98

(4)

The Concept of Null

102

(4)

Table Joins

106

(7)

Sorting Query Results

113

(1)

Grouping and Summarizing

114

(4)

Subqueries

118

(6)

Unions

124

(3)

To Learn More

127

(1)

5. Generating Reports with SQL*Plus

128

(29)

Following a Methodology

128

(1)

Saving Your Work

129

(1)

Designing a Simple Report

129

(15)

Master/Detail Reports

144

(13)

6. Creating HTML Reports

157

(17)

Getting the Data into an HTML Table

157

(4)

Generating the Entire Page

161

(6)

Another Approach to Headers

167

(3)

Master/Detail Reports in HTML

170

(4)

7. Advanced Reports

174

(32)

Totals and Subtotals

174

(13)

Getting the Current Date into a Header

187

(2)

Report Headers and Footers

189

(4)

Formatting Object Columns

193

(3)

Summary Reports

196

(4)

Taking Advantage of Unions

200

(6)

8. Writing SQL*Plus Scripts

206

(37)

Why Write Scripts?

206

(1)

Using Substitution Variables

207

(8)

Prompting for Values

215

(5)

Cleaning Up the Display

220

(2)

Executing a Script

222

(9)

Controlling Variable Substitution

231

(4)

Commenting Your Scripts

235

(3)

Resetting Your SQL*Plus Environment

238

(2)

Scripting Issues with iSQL*Plus

240

(3)

9. Extracting and Loading Data

243

(30)

Types of Output Files

244

(1)

Limitations of SQL*Plus

245

(1)

Extracting the Data

246

(11)

An Excel-Specific HTML Hack

257

(2)

Reloading the Data

259

(14)

10. Exploring Your Database

273

(35)

The DESCRIBE Command

273

(5)

Oracle's Data Dictionary Views

278

(2)

Tables

280

(6)

Table Constraints

286

(6)

Indexes

292

(1)

Triggers

293

(2)

Synonyms

295

(1)

Table Security

296

(1)

Scripting the Data Dictionary

297

(6)

Using SQL to Write SQL

303

(3)

The Master Key

306

(2)

11. Advanced Scripting

308

(50)

Bind Variables

309

(16)

Branching in SQL*Plus

325

(12)

Looping in SQL*Plus

337

(4)

Validating and Parsing User Input

341

(8)

Error Handling

349

(6)

Returning Values to Unix

355

(3)

12. Tuning and Timing

358

(43)

Using SQL*Plus Timers

358

(5)

Using EXPLAIN PLAN Statements

363

(16)

Using AUTOTRACE

379

(6)

Improving on EXPLAIN PLAN Results

385

(14)

Where to Find More Tuning Information

399

(2)

13. The Product User Profile

401

(17)

What Is the Product User Profile?

401

(7)

Using the Product User Profile

408

(10)

14. Customizing Your SQL*Plus Environment

418

(11)

SQL*Plus Settings You Can Control

418

(1)

The Site and User Profiles

419

(3)

Environment Variables That Affect SQL*Plus

422

(3)

Windows GUI SQL*Plus

425

(1)

iSQL*Plus User Preferences

426

(3)

A. SQL*Plus Command Reference

429

(96)

B. SQL*Plus Format Elements

525

(8)

Index

533

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Summary

Author Biography

Table of Contents

Supplemental Materials