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 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 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 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 GUI-based 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 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, 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.

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.