< previous page page_xi next page >
Page xi
Preface
Every day, computer professionals the world over wake up, travel to the office, sit down in front of a computer, and
begin another day working with that database called Oracle. Programmers write queries and stored procedures.
Database administrators monitor performance, make database changes, and perform other maintenance tasks.
Operations people may need to back up or recover a database. Analysts may need to explore the structure of a database
to answer the question What's out there? Testers may work on developing and loading test data. A wide variety of
people perform a wide variety of tasks, yet the vast majority of them are likely to have one thing in commonSQL*Plus.
SQL*Plus is the command-line interface to the Oracle database. It's a client-server application that allows you to enter
and execute SQL statements and PL/SQL blocks. One of the most common uses for SQL*Plus is as an ad hoc query
tool. You type in a SELECT statement, execute it, and see what results come back from the database. Programmers do
this all the time when developing queries and when experimenting with Oracle's built-in functions. Database
administrators sometimes issue queries against Oracle's data dictionary tables in order to see what objects are out there
in the database.
One important capability of SQL*Plus is its ability to format and paginate query results. You can enter a SELECT
statement, execute it, and have the results formatted so you can print them and produce a credible-looking report.
SQL*Plus implements a full range of formatting commands that allow you to add page headers and footers to your
reports. There are also commands that allow you to format the data displayed in the report. You can control column
headings, number formats, and column widths.
Another important capability of SQL*Plus, and one you should take advantage of if you don't already, is its ability to
run predefined SQL script files. A script file is

< previous page page_xi next page >
< previous page page_xii next page >
Page xii
analogous to a DOS BAT file, and is simply a text file that contains commands to execute. These commands may be
SQL statements, PL/SQL code blocks, or SQL*Plus commands. Scripts may be used to automate frequently-performed
tasks. One of the easiest things to do is to write a script to generate a report. You do this by placing all the formatting
commands and the SELECT query for the report into the script file. Then whenever you want the report, you just
execute the script. In addition to producing printed reports, scripts may also be used to automate routine tasks such as

creating a new user, or they may be used to display data on the screen. You might, for example, write a script to display
constraint definitions for a table, or perhaps to list the system privileges granted to one of your users.
SQL*Plus is also frequently used as a tool for loading stored code, such as a stored procedure, into the database. Years
ago it used to be that SQL*Plus was just about the only tool that could be used to load and compile a stored procedure,
trigger, or package. That situation has changed somewhat with the arrival of new GUIbased tools, but many people still
rely on the old workhorse SQL*Plus for this purpose.
A sometimes overlooked capability of SQL*Plus is its use as a data extraction tool. If you've been around Oracle for a
while, you are no doubt familiar with SQL*Loader. SQL*Loader is Oracle's general-purpose data load utility. Using it,
you can read data from a flat file and load it into one or more database tables. The strange thing is that Oracle does not
have a corresponding SQL*Unloader utility. When people want to extract data from Oracle into a flat file, such as a
commadelimited file, they often get frustrated when looking for a utility to do the job. SQL*Plus, it turns out, is a viable
choice for the task. It's a relatively simple matter to spool the results of a query to a file. It doesn't take much extra work
to format that output so that it is comma- or tab-delimited.
Finally, SQL*Plus gives you a reliable way to propagate database structure changes when you need to make the same
change to more than one database. If you have a table change that needs to be made on several similar databases, you
can write a SQL*Plus script to do the job. This script can easily be executed against each database, saving you the
trouble of making the same change several times over. This is a great convenience if you deal with clients in a variety of
locations, because you can send the script out to each client, where it can be executed by the staff against the database.
To many people, SQL*Plus, with its command-line interface, must seem increasingly like an anachronism. Graphical
user interfaces are prevalent everywhere, and often it seems as if computer users have forgotten how to do anything but
point and click with a mouse. You might ask, then, Why bother to learn SQL*Plus? Why bother with an ancient
command-line utility?These are fair questions.

< previous page page_xii next page >
< previous page page_xiii next page >
Page xiii
I have come to lean heavily on SQL*Plus because it is always there, and it always works. In my work as a consultant, I
frequently visit clients and work with databases on a variety of platforms. Some sites have Enterprise Manager installed.
Some do not. Some Unix sites lean more heavily on the GUI version of Server Manager, and others actually do most of
their work at the command line. One thing I can always count on, no matter where I go, is that SQL*Plus will be

available. Not only is SQL*Plus available on the database administrator's PC, it is often available on user PCs as well.
No matter which machine I use at a client site, I can almost always count on this venerable utility being available, and
because I know it well, I can immediately be productive.
Scripting and batch processing are two other reasons I use SQL*Plus. Following proper procedures is important, and
SQL*Plus allows me to encapsulate the commands necessary to add a new database user into one script, so each time I
create a new user it is done consistently.
Finally, one more reason I use SQL*Plus is speed. I type very fast, and I type very accurately. Wonderful as many of
the modern GUI applications are, I can often perform a task more quickly using SQL*Plus. This is not always true, but
it is true often enough, especially when you consider the time it takes to load and start a GUI interface such as
Enterprise Manager versus the time it takes to load and start SQL*Plus.
Why I Wrote This Book
My motivation for writing this book stems from my early experiences learning about the Oracle database. Oracle's
documentation tends to be very narrowly focused, each manual discussing only those things strictly related to the
product being written about, and the manual for SQL*Plus is no exception. Oracle's manual will tell you about
SQL*Plus, but only about SQL*Plus. There is little information on how to use SQL*Plus in conjunction with other
Oracle products such as PL/SQL or SQL*Loader. There is also little information on using SQL*Plus to perform
common tasks like viewing a constraint definition or extracting data.
I remember clearly the frustration of working with three manuals spread out in front of me: the SQL manual, the
SQL*Plus manual, and the PL/SQL manual. I remember the frustration of frequently picking up the wrong manual
because I didn't understand clearly the relationship between these three products. Was DESCRIBE a SQL command?
How could I use a SQL*Plus variable in a PL/SQL script?
Even when I knew that something could be done with SQL*Plus, I frequently didn't find clear direction in the Oracle
manuals. The first time I wrote a script to extract data to a file, I spent quite a bit of time flipping back and forth in the
man-

< previous page page_xiii next page >
< previous page page_xiv next page >
Page xiv
ual and experimenting with various commands before I finally got the results I wanted. Things became even more
complicated when I began to write scripts and batch jobs with SQL*Plus. Suddenly I found myself wanting to branch,

and even to loop, from a SQL*Plus script. SQL*Plus doesn't officially provide this functionality, but there are some
tricks you can put to good use.
Finally, this is the book I want to give my clients and coworkers when they ask me how-to questions. Next time I'm
asked how to get data out of Oracle and into a comma-delimited file, I'll just refer them to Chapter 5. When I'm asked
about formatting a report, I'll refer them to Chapter 3, and when I'm asked how to write an IF statement in SQL*Plus,
I'll refer them to Chapter 7. Each of these chapters presents a solution to its respective problem, and leads you step by
step through the process of implementing that solution.
Objectives of This Book
The single, driving objective of this book is to help you squeeze every last possible ounce of productivity and usefulness
out of SQL*Plus. If you read this book cover to cover, at the end you will have learned:
Methodical approaches to common tasks such as generating a report and extracting data to a flat file
How to enter and edit commands in the SQL*Plus environment, with and without an external editor
How to write simple scripts using SQL*Plus
How to prompt for user input and use that input in SQL commands, PL/SQL blocks, and SQL*Plus commands
How to stretch the limits of what you can do in a SQL*Plus script by implementing pseudo-IF statements, and by using
one script to generate another
How the Product User Profile, an Oracle security feature, works with SQL*Plus
How to customize your SQL*Plus environment
How you can use SQL*Plus to view the definitions for database objects such as views, tables, constraints, triggers,
indexes, and synonyms
How to use SQL*Plus as a tuning aid to maximize the performance of your SQL queries
An additional objective of this book is to serve as a ready reference you can pull out when faced with a common task. If
you need to generate a report, open to the chapter on reports and follow the steps. Need to extract data? Open to the
chapter on data extraction, and again just follow the steps. Every effort has been made to organize the information
topically, so that all the information you need to

< previous page page_xiv next page >
< previous page page_xv next page >
Page xv
accomplish a specific task is in one chapter. Where feasible, step-by-step instructons are provided so you don't need to

wade through a lot of theory in order to accomplish something.
Structure of This Book
This book is divided into elevan chapters and two appendixes. The chapters are task-oriented and show you how you
can use SQL*Plus to perform common tasks such as printing a report, or extracting data to a file. The appendixes
contain reference material, such as a list of all SQL*Plus commands.
Chapter 1, Introduction to SQL*Plus, goes into more detail about what SQL*Plus really is and why you should master
it. It gives you a taste of the many different tasks SQL*Plus can be used for. A short history of SQL*Plus is included, as
well as a description of the database used for many of the examples in this book, particularly the reporting examples.
Chapter 2, Interacting with SQL*Plus, shows you how to start SQL*Plus, enter, execute, and edit commands. This is
basic information you need to know.
Chapter 3, Generating Reports with SQL*Plus, explains the reporting features of SQL*Plus, and also presents a step-by-
step method for creating a report that has worked very well for me.
Chapter 4, Writing SQL*Plus Scripts, explains the basic scripting capabilities of SQL*Plus. Primarily, this chapter
shows how SQL*Plus substitution variables work, and how you can use them to your advantage. In addition, it covers
the subject of getting input from a user, and shows you how to control the output the user sees as the script is executed.
Chapter 5, Extracting and Loading Data, shows how you can use SQL*Plus to extract data from an Oracle database and
place it into a text file suitable for loading into another program such as a spreadsheet. This text file may be deimited,
by commas, for example, or it may consist of fixed-width columns. The chapter ends with an example showing how
SQL*Loader may be used to load that same data into another Oracle database.
Chapter 6, Exploring Your Database, shows how you can query Oracle's data dictionary tables in order to see the
structure of commonly-used database objects, such as tables and indexes.
Chapter 7, Advanced Scripting, builds on Chapter 4, but covers some advanced, and sometimes unusual, scripting
techniques. This chapter introduces bind variables, and explains how they are different from user variables. This chapter
also

< previous page page_xv next page >
< previous page page_xvi next page >
Page xvi
shows some creative techniques you can use to add some limited branching, and even some looping, to your SQL*Plus
scripts.

Chapter 8, Tuning and Timing, presents the SQL*Plus features that support the tuning of SQL statements. Also covered
in this chapter is Oracle's EXPLAIN PLAN command, which lets you get a look at the execution strategy that the
Oracle optimizer will user for any given SQL statement.
Chapter 9, The Product User Profile, introduces a security feature that a database administrator can use to limit what a
user can do with SQL*Plus. The product user profile allows you to turn off specific SQL and SQL*Plus commands for
one user or a group of users. It can also be used to limit a user's access to certain roles while connected via SQL*Plus.
Chapter 10, Administration with SQL*Plus, covers the new administrative commands that have been added to
SQL*Plus beginning with version 8.1 (available with Oracle8i). These commands are the same ones that the Server
Manager product implements. They have been added to SQL*Plus in preparation for Server Manager's demise.
Chapter 11, Customizing Your SQL*Plus Environment, shows a number of ways in which you may customize your
SQL*Plus environment. The site and user profiles are explained, as well as several registry settings that affect users on
Windows 95, 98, and NT.
Appendix A, SQL*Plus Command Reference, contains syntax diagrams for all SQL*Plus commands.
Appendiax B,SQL*Plus Format Elements, describes the various format elements that may be used to build up format
strings to be used with commands, such as COLUMN and ACCEPT, that format output or a accept user input.
Audience
I like to think there is something in this book for everyone: programmers, database administrators, and other technically
inclined Oracle users.If you work with Oracle on a daily basis, and if you use SQL*Plus in your work, then you need to
know what's in this book. If you are someone just learning Oracle, this book will provide you with a good understanding
of SQL*Plus, how it fits into the Oracle environment, and how you can use it productively. You know, if you've read
this far, that SQL*Plus is a multifaceted product, and can be used for a wide variety of tasks. You can benefit by
learning to get the most out of the tools you work with on a daily basis.

< previous page page_xvi next page >
< previous page page_xvii next page >
Page xvii
Obtaining the Scripts and Sample Data.
Many examples from the book, as well as SQL*Plus scripts to load the data used for the examples, can be download
from O'Reilly's web sit at http:// www.oreilly.com/catalog/orsqplus.
To load the data, download the file named SAMPLE_DATA.ZIP, unzip the files and follow the instructons given in

Chapter 1.
Conventions Used in This Book
The following conventions are used in this book:
Italic
Used for filenames, directory names, URLs, and occasional emphasis.
Constant width
Used for code examples.
Constant width bold
Used in examples that show interaction between SQL*Plus and a user. Commands typed by the user are shown in bold,
while output from SQL*Plus is shown in normal text.
Constant width italiac
In some code examples, indicates an element (e.g., a filename) you supply.
UPPERCASE
In code examples, indicates PL/SQL keywords. In text, indicates PL/SQL keywords, table names, and column names.
lowercase
In code examples, indicates user-defined item such as variables, parameters, ect.
Punctution
In code examples, enter exactly as shown.
Indentation
In code examples, helps to show structure (but is not required).
In code examples, a double hypen begins a single-line comment, which extends to the end of a line.
/*and*/
In code examples, these characters delimit a multiline comment, which can extend from one line to another.
In code examples and related discussions, a dot qualifies a reference by separating an object name from a
component name. In this book, do notation is

< previous page page_xvii next page >
< previous page page_xviii next page >
Page xviii
most often used in fully-qualified column names, which you will see written as table_name.field_name. The dot

separates the table name from the column name.
<> In syntax descriptions, angle brackets enclose the name of a syntactic element.
[] In syntax descriptions, square brackets enclose optional items.
{} In syntax descriptions, curly brackets enclose a set of items from which yu must choose only one.
¦ In syntax descriptions, a vertical bar separates the items enclosed in curly brackets, as in {VARACHAR2 ¦ DATE ¦
NUMBER}.
Indicates a tip, suggestion, or general note. For example, we'll tell
you if you need to use a particular Oracle version or if an operation requires
certain privileges.
Indicates a warning or caution. For example, we'll tell you if Oracle
does not behave as you'd expect or if a particular operation has a negative
impact on performance.
Which Platform and Version?
SQL*Plus changes frequently. Each new version brings with it new commands, and new options to use with old
commands. Most of the examples in this book use SQL*Plus 8.0. A few use SQL*Plus 2.3, and the ones in Chapter 10,
Administration with SQL*Plus, use Oracle8i and SQL *Plus 8.1. You should be able to apply the information in this
book toany reasonably current version of the product. If you have any doubts about whether or not a command will
work with the specific version of SQL*Plus you are using, check the SQL*Plus User's Guide and Reference manual for
that version.
A note is in order on operating systems. Throughout the book, references are made to the Windows operating system.
Whenever you see the term Windows used, please understand it to mean Windows NT, Windows 95, Windows 3.1,
Windows 3.11, Windows 98, or whatever other flavor of Windows might exist.

< previous page page_xviii next page >
< previous page page_xix next page >
Page xix
How to Contact Us
We have tested and verified the information in this book to the best of our ability, but you may find that features have
changed (or even that we have made mistakes!). Please let us know about any errors you find, as well as your
suggestions for future editions, by wirting to:

O'Reilly & Associates, Inc.
101 Morris Street
Sebastopol, CA 95472
1-800-998-9938 (in the U.S. or Canada)
1-707-829-0515 (international/local)
1-707-829-0104 (FAX)
You can also send us messages electronically. To be put on the mailing list or request a catalog, send email to:

To ask technical questional ro comment on the book, send email to:

We have a web site for the book, where we'll list examples, errata, and any plans for future editions. You can access this
page at:
/>For more information about this book and others, see the O'Reilly web site:

Acknowledgments
The first book that I ever wrote consumed so much energy that I promised myself, and my family, that I would never
write another. That promise lasted about two months, when I became consumed with the idea of wirting the book you
are now reading. I owe a lot to my wife Donna, who understands my drive to write, and who bears a lot more of the load
than she should have to while I hole up in my office and work. This book has been my excuse for avoiding just about
every household chore imaginable, yet Donna had been very supportive during the endeavor.
My children have also contributed to the development of this book. If nothing else, they burst into my office on a daily
to distract me, and to remind me that there are more important things in life than typing on a computer all day. They
have also contributed time that I might otherwise have spent with them. My

< previous page page_xix next page >
< previous page page_xx next page >
Page xx
three-year-old son Jeff has grown up watching me write, and must think that all daddies come with a laptop computer
attached. To my daughter Jenny, I want to say that I have enjoyed all our late-night excursions together to the local
bookstore. I'm glad to see that you have developed the same love for books that I have. You may not always believe it,

but you are my pride and joy. I see a lot of myself in you, and I'm glad you ended up as my daughter.
My profound thanks also go out to John-Paul Navarro and David Dreyer, who read each chapter as it was written. David
Dreyer is an independent consultant residing near Detroit, Michigan, and specializes in PowerBuilder and Oracle
development. Dave is one of the most cerebral programmers that I know. Always calm and thoughtful, Dave has added
a great deal to this book with his insightful comments. John-Paul Navarro is a systems administrator for Argonne
National Laboratories in Argonne, Illinois. John-Paul and I go way back both as friends and colleagues, and I have no
end of respect for his technical abilities. He was always willing to discuss technical issues that came up during the
writing of this book, and for that I am in his debt.
Thanks to Alison Holloway, product manager for SQL*Plus. Home for Alison, and SQL*Plus, too, in case you ever
wondered, is in Melbourne, Australia. Alison helped dig up background information and history on SQL*Plus. She
provided information on new features being added to SQL*Plus for the upcoming Oracle8i release, and also provided
me with an early look at the documentation. Without her help, chapter 10 would not exist. In addition to all her other
help, Alison did a technical review of the entire book after it was written.
Krik Bradley, Ken Jacobs, Jay Rossiter, and Richard Rendell, all of Oracle Corporation, provided the historical
information about SQL*Plus in Chapter 1. It's always nice to know something of the tools one works with besides just
the raw technical details of how they are used. SQL*Plus has been around a long time, and Kirk, Ken, and Richard have
provided some interesting historical tidbits.
A number of technical reviewers read and commented on the final manuscript. These include David Kaufman of TUSC,
Dave Kreines, Eric Givler, Alison Holloway, and John-Paul Navarro. I am grateful to each of these people for giving
me an unbiased view of the manuscript, pointing out errors, and providing suggestions on ways to improve the clarity of
the text. This book is the better for their efforts.
Howard Vanderstow and Paul Cheney, both database administrators with the State of Michigan's Office of Technology
Resources, provided some welcome assistance in generating the examples for the HELP command in Chapter 2. They
also helped me research the location and structure of the database table containing the help text used by SQL*Plus.

< previous page page_xx next page >