Oracle SQL*Plus: The Definitive Guide

Larger Cover

By Jonathan Gennick

Publisher: O'Reilly Media

Released: March 1999

Pages: 528

This book is the definitive guide to SQL*Plus. If you want to take best advantage of the power and flexibility of this popular Oracle tool, you need this book.

SQLPlus is an interactive query tool that is ubiquitous in the Oracle world. It is present in every Oracle installation and is available to almost every Oracle developer and database administrator. SQLPlus has been shipped with Oracle since at least version 6. It continues to be supported and enhanced with each new version of Oracle, including Oracle8 and Oracle8i. It is still the only widely available tool for writing SQL scripts. Despite this wide availability and usage, few developers and DBAs know how powerful SQL*Plus really is.

This book introduces SQLPlus, includes a quick reference to all of its syntax options, and, most important, provides chapters that describe, in step-by-step fashion, how to perform all of the tasks that Oracle developers and DBAs want to perform with SQLPlus -- and maybe some they didn't realize they COULD perform with SQLPlus. You will learn how to write and execute script files, generate ad hoc reports, extract data from the database, query the data dictionary tables, customize your SQLPlus environment, and use the SQL*Plus administrative features (new in Oracle8i).

This book is an indispensable resource for readers who are new to SQL*Plus, a task-oriented learning tool for those who are already using it, and a quick reference for every user.

A table of contents follows:

Preface
Introduction to SQLPlus
Interacting with SQLPlus
Generating Reports with SQLPlus
Writing SQLPlus Scripts
Extracting Data with SQLPlus
Exploring Your Database with SQLPlus
Advanced Scripting
Tuning and Timing
The Product User Profile
Administration with SQLPlus
Customizing Your SQLPlus Environment

Appendices

A. SQLPlus Command Reference
B. Connect Strings and the SQLPlus Command

Chapter 1 Introduction to SQL*Plus
1. What Is SQL*Plus?
2. History of SQL*Plus
3. Why Master SQL*Plus?
4. Creating and Loading the Sample Tables
Chapter 2 Interacting with SQL*Plus
1. Starting SQL*Plus
2. Some Basic SQL*Plus Commands
3. Running SQL Queries
4. Working with PL/SQL
5. The SQL Buffer
6. Line Editing
7. Executing the Statement in the Buffer
8. Saving and Retrieving the Buffer
9. The EDIT Command
10. Executing a Script
11. The Working Directory
Chapter 3 Generating Reports with SQL*Plus
1. Following a Methodology
2. Saving Your Work
3. Designing a Simple Report
4. Advanced Report Formatting
5. Totals and Subtotals
6. Other Reporting Topics
Chapter 4 Writing SQL*Plus Scripts
1. Why Write Scripts?
2. Using Substitution Variables
3. Prompting for Values
4. Cleaning Up the Display
5. Packaging Your Script
6. The DEFINE and UNDEFINE Commands
7. Controlling Variable Substitution
8. Commenting Your Scripts
Chapter 5 Extracting and Loading Data
1. Types of Output Files
2. Limitations of SQL*Plus
3. Extracting the Data
4. Reloading the Data
Chapter 6 Exploring Your Database
1. The DESCRIBE Command
2. Oracle’s Data Dictionary Views
3. Tables
4. Table Constraints
5. Indexes
6. Triggers
7. Synonyms
8. Table Security
9. Finding More Information
Chapter 7 Advanced Scripting
1. Bind Variables
2. Branching in SQL*Plus
3. Looping in SQL*Plus
4. Validating and Parsing User Input
5. Error Handling
Chapter 8 Tuning and Timing
1. Using SQL*Plus Timers
2. Using EXPLAIN PLAN
3. Using AUTOTRACE
4. Improving on EXPLAIN PLAN Results
5. Where to Find More Tuning Information
Chapter 9 The Product User Profile
1. What Is the Product User Profile?
2. Using the Product User Profile
Chapter 10 Administration with SQL*Plus
1. Connecting for Administrative Purposes
2. Starting and Stopping a Database
3. Looking at Your Database
4. Database Backup and Recovery
Chapter 11 Customizing Your SQL*Plus Environment
1. SQL*Plus Settings You Can Control
2. The Site and User Profiles
3. Environment Variable Settings
4. The SET Command
5. The SHOW Command

Appendix SQL*Plus Command Reference
1. SQLPLUS
2. Comment Delimiters (/*...*/)
3. Double Hyphen (- -)
4. At Sign (@)
5. Double At Sign (@@)
6. Forward Slash (/)
7. ACCEPT
8. APPEND
9. ARCHIVE LOG
10. ATTRIBUTE
11. BREAK
12. BTITLE
13. CHANGE
14. CLEAR
15. COPY
16. COLUMN
17. COMPUTE
18. CONNECT
19. DEFINE
20. DEL
21. DESCRIBE
22. DISCONNECT
23. EDIT
24. EXECUTE
25. EXIT
26. GET
27. HELP
28. HOST
29. INPUT
30. LIST
31. PASSWORD
32. PAUSE
33. PRINT
34. PROMPT
35. QUIT
36. RECOVER
37. REMARK
38. REPFOOTER
39. REPHEADER
40. RUN
41. SAVE
42. SET
43. SHOW
44. SHUTDOWN
45. SPOOL
46. START
47. STARTUP
48. STORE
49. TIMING
50. TTITLE
51. UNDEFINE
52. VARIABLE
53. WHENEVER
Appendix SQL*Plus Format Elements
1. Formatting Numbers
2. Formatting Character Strings
3. Formatting Dates
Colophon

Jonathan Gennick

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.

View Jonathan Gennick's full profile page.

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of Oracle SQLPlus: The Definitive Guide is a moving leaf (Phyllium giganteum), a large (about 10 cm in length) Malaysian leaf insect related to stick insects; together these constitute the order Phasmatida, derived from the Greek word for "ghost." These stunning insects imitate local foliage with intricate detail, down to the leaf-like veins on their legs and tattered brown edges to mimic dead leaves. Moving leaf insects feed on bramble and other plant material. Kept by many entymologists as exotic pets, they require high temperature and humidity. Ellie Fountain Maden was the production editor and project manager for Oracle SQLPlus: The Definitive Guide, and also copyedited the book. Quality assurance was provided by Madeleine Newell, Sarah Jane Shangraw, and Sheryl Avruch, who was also the production manager. Ellie Cutler proofread the book, and Seth Maislin wrote the index.

Edie Freedman designed the cover of this book, using a 19th-century engraving from the Dover Pictorial Archive. The cover layout was produced by Kathleen Wilson with Quark XPress 3.32 using the ITC Garamond font.

The inside layout was designed by Nancy Priest and implemented in FrameMaker 5.5 by Mike Sierra. The text and heading fonts are ITC Garamond Light and Garamond Book. The illustrations that appear in the book were created in Macromedia FreeHand 8 and Adobe Photoshop 5 by Robert Romano. This colophon was written by Nancy Kotary.

Description

Table of Contents

Product Details

About the Author

Colophon

Recommended for You

Recently Viewed

JavaScript Application Cookbook

By Jerry Bradenbaugh

October 1999

Print: $54.99

Your Child in the Hospital, 2nd Edition

A Practical Guide for Parents, 2nd Edition

By Nancy Keene, Rachel Prentice

March 1999

Print: $11.95

Internet Core Protocols: The Definitive Guide

Help for Network Administrators

By Eric Hall

February 2000

Ebook: $35.99

Print & Ebook: $49.49

Print: $44.99

Customer Reviews

REVIEW SNAPSHOT®

by PowerReviews

oreilly Oracle SQL*Plus: The Definitive Guide

5.0

(based on 5 reviews)

Reviews

Reviewed by 5 customers

Sort by

Displaying reviews 1-5

5/25/2009

5.0

Excellent series. Check for latest edition

By jdruin

from Undisclosed

Comments about oreilly Oracle SQL*Plus: The Definitive Guide:

This particular edition is out of date since it covered 8i, but the book has been updated with newer editions which are excellent.

5/21/2009

5.0

A Must Have""

By jdruin

from Undisclosed

Comments about oreilly Oracle SQL*Plus: The Definitive Guide:

As a frequent user of SQL Plus, this book is very handy. For many tasks we use Oracle SQL Developer but in many cases SQL Plus is an easier tool to use for scripting. However the scripting syntax and the abilities of SQL Plus can be difficult to fully utilize. This book does a great job of explaining what SQL Plus can do and how to use the tools. In additioning to covering querying techniques, the chapters on generating reports in a quick but structured fashion were useful. I ended up getting 3 more copies for my team as my copy kept wandering around the room.

5/13/2006

5.0

Great overview

By Sam

from Undisclosed

Comments about oreilly Oracle SQL*Plus: The Definitive Guide:

Very well organized, good examples, good writing. I was able to get out of it what I needed in a weekend. Nice job.

3/15/2001

5.0

Oracle SQL*Plus: The Definitive Guide Review

By Sharon Close

from Undisclosed

Comments about oreilly Oracle SQL*Plus: The Definitive Guide:

Thank you for a superb introduction to SQLPLUS. I can definitely be described as a novice - I attended an Oracle sql for end users course two years ago, but found it a difficult subject to grasp as I had no experience of using sql (and sharing a pc with another trainee did not help!). I thought I was doomed never to use it again.

I went into a shop two days ago looking for SQL for Dummies, but discovered your far more useful book and have suddenly discovered I can do it after all! I have impressed myself with my new found ability to write scripts (with lots of joins) and correctly format reports - a major breakthrough for a new consultant in the marketplace.

Thank you once again - this is the best value for money in the marketplace.

8/9/2000

5.0

Oracle SQL*Plus: The Definitive Guide Review

By K Vainstein

from Undisclosed

Comments about oreilly Oracle SQL*Plus: The Definitive Guide:

Just like the cover says - Definitive. My one complaint is that command-line arguments aren't in the book's index, although they're used in a few of the example scripts. (Call them &&1, &&2, etc. Here's a good approach - at start of script, have something like

PRO Inputs:

PRO 1: schema name

PRO 2: column name

, and then user will get an explanation of what args were expected. If the command-line args weren't entered, SQL*Plus will give another opportunity to enter them, as in

Enter value for 1:

, so forth. Looks nice.)

The Ukrainian names are literary, not historical (off-the-wall correction of an earlier reader comment).

Truly superior technical text.

Displaying reviews 1-5

Save a Tree - Go Digital what is this?

Chapter 1 Introduction to SQL*Plus

What Is SQL*Plus?

History of SQL*Plus

Why Master SQL*Plus?

Creating and Loading the Sample Tables

Chapter 2 Interacting with SQL*Plus

Starting SQL*Plus

Some Basic SQL*Plus Commands

Running SQL Queries

Working with PL/SQL

The SQL Buffer

Line Editing

Executing the Statement in the Buffer

Saving and Retrieving the Buffer

The EDIT Command

Executing a Script

The Working Directory

Chapter 3 Generating Reports with SQL*Plus

Following a Methodology

Saving Your Work

Designing a Simple Report

Advanced Report Formatting

Totals and Subtotals

Other Reporting Topics

Chapter 4 Writing SQL*Plus Scripts

Why Write Scripts?

Using Substitution Variables

Prompting for Values

Cleaning Up the Display

Packaging Your Script

The DEFINE and UNDEFINE Commands

Controlling Variable Substitution

Commenting Your Scripts

Chapter 5 Extracting and Loading Data

Types of Output Files

Limitations of SQL*Plus

Extracting the Data

Reloading the Data

Chapter 6 Exploring Your Database

The DESCRIBE Command

Oracle’s Data Dictionary Views

Tables

Table Constraints

Indexes

Triggers

Synonyms

Table Security

Finding More Information

Chapter 7 Advanced Scripting

Bind Variables

Branching in SQL*Plus

Looping in SQL*Plus

Validating and Parsing User Input

Error Handling

Chapter 8 Tuning and Timing

Using SQL*Plus Timers

Using EXPLAIN PLAN

Using AUTOTRACE

Improving on EXPLAIN PLAN Results

Where to Find More Tuning Information

Chapter 9 The Product User Profile

What Is the Product User Profile?

Using the Product User Profile

Chapter 10 Administration with SQL*Plus

Connecting for Administrative Purposes

Starting and Stopping a Database

Looking at Your Database

Database Backup and Recovery

Chapter 11 Customizing Your SQL*Plus Environment

SQL*Plus Settings You Can Control

The Site and User Profiles

Environment Variable Settings

The SET Command

The SHOW Command

Appendix SQL*Plus Command Reference

SQLPLUS

Comment Delimiters (/*...*/)

Double Hyphen (- -)

At Sign (@)

Double At Sign (@@)

Comment Delimiters (/.../)