Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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 the database called Oracle. Programmers write queries
and store 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 common:
SQL*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 to see what objects
are available in the database. SQL*Plus is also frequently used as a
tool for loading stored code, such as a stored procedure, into 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 allowing 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*Plusand one you should
take advantage of if you don't alreadyis its
ability to run predefined SQL script files. A script file is
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 simply execute
the script. In addition to producing printed reports, scripts can
automate routine tasks such as creating a new user or displaying 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.

A sometimes overlooked capability of SQL*Plus is its use as a data
extraction tool. If you have 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 comma-delimited 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 for
them to execute against their own databases.

To many people, SQL*Plus, with its command-line interface, must seem
increasingly like an anachronism. Graphical user interfaces (GUIs)
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.

I have come to lean heavily on SQL*Plus because it is always there,
and it always works. Back when I used to work as a consultant, I
frequently visited clients and worked with databases on a variety of
platforms. One thing I could always count on, no matter where I went,
was that SQL*Plus would be available. Not only was SQL*Plus available
on the database administrator's PC, it was often
available on user PCs as well. No matter which machine I used at a
client site, I could almost always count on this venerable utility
being available, and because I knew it well, I could immediately be
productive.

Scripting and batch processing are two important reasons I use
SQL*Plus these days. Following proper procedures is important, and
SQL*Plus allows me, for example, to encapsulate the commands
necessary to add a new database user into one script, so each time I
create a new user, the creation process is done consistently.

Finally, one more reason I use SQL*Plus is speed. I type very fast
and with reasonable accuracy. 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 Oracle Enterprise Manager versus the time it
takes to load and start SQL*Plus.