< previous page page_57 next page >
Page 57
NOL[IST]
May be abbreviated to NOL, and tells SQL*Plus to load the file without listing it for you to see.
The following example shows how to retrieve the SQL statement that was saved in the previous section:
SQL> GET C:\A\HIGHEST_BILLED_EMP_PROJECTS
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee.employee_id = project_hours.employee_id
4 AND project_hours.project_id = project.project_id
5 AND employee_billing_rate in (
6 SELECT MAX (employee_billing_rate)
7 FROM employee
8 )
9* GROUP BY employee_name, project_name
Notice that the GET command automatically displays the contents of the retrieved file for you to see. This allows you to
confirm that you have loaded the correct statement. Once the statement has been loaded into the buffer, you may
execute it using either RUN or /, or you may use any of the editing commands to change it.
The GET command will load any text file into the buffer. You can
even load your AUTOEXEC.BAT file (for Windows users) into the buffer, edit
it, and save it again. This might be a useful thing to know if you are ever really
hard up for an editor.
While SQL*Plus will let you load any text file into the buffer, be aware that you cannot execute the buffer unless it
contains exactly one SQL statement or one PL/ SQL block. To be safe, the text file should terminate the statement (or
block) with a forward slash on a line by itself. See the previous section on the SAVE command for an example of this.
The Edit Command
You don't like line editing? SQL*Plus does not have a built-in full-screen editor, but it does have the EDIT command.
The SQL*Plus EDIT command allows you to invoke the text editor of your choice to use in editing SQL statements.
The specific editor invoked depends on the operating system, and on whether or not you've changed the default. The
default editor under Windows NT/95 is Notepad, while under Unix it is vi. You may, however, configure SQL*Plus to
use another editor of your choice. Do this by defining the user variable named _EDITOR to point to executable of the

editor you want to use.

< previous page page_57 next page >
< previous page page_58 next page >
Page 58
Invoking the Editor
You invoke the editor with the EDIT command. The syntax looks like this:
ED[IT] [filename]
where:
ED[IT]
May be abbreviated ED.
filename
Is an optional argument indicating a specific file you want to edit. The default extension is .SQL, but you may supply a
different extension if you like.
The typical use of the EDIT command is to edit a statement currently contained in the buffer. The following example
shows a query being entered and the editor being invoked:
SQL> SELECT project_name
2 FROM projects
3 WHERE project_id in
4 SELECT DISTINCT project_id
5 FROM project_hours)
SQL> EDIT
Wrote file afied.buf
Upon entering the EDIT command, the contents of the buffer are written to a file named AFIEDT.BUF, and the editor is
invoked. Figure 2-7 shows what your screen would now look like on a Windows 95 or NT system. On Unix systems,
the filename is lowercase, and will be afiedt.buf.
The filename AFIEDT.BUF is simply a work file used by SQL*Plus to hold your command while it is being edited. The
name is a throwback to the very early days of SQL*Plus when it was briefly known as AUFI, which stood for Advanced
User Friendly Interface.
SQL*Plus will not invoke the editor if the buffer is empty; instead you will see the message shown below:

SQL> EDIT
Nothing to save.
If you have an empty buffer and wish to enter a new query, you must type something, perhaps just the first line, into
SQL*Plus before using the EDIT command.
Another use for the EDIT command is to edit an existing text file. You can edit any text file you like, whether it
contains a query or not. The following EDIT command for example, lets you edit your Unix Korn shell profile:
EDIT.profile

< previous page page_58 next page >
< previous page page_59 next page >
Page 59
Figure 2-7.
Results of the EDIT command under Windows 95/NT
When you edit a file in this way, the contents of the file are not loaded into the buffer. This is just a convenient way for
you to edit a file without having to exit SQL*Plus first.
Formatting Your Command
Take another look at Figure 2-7. Pay attention to the way in which the SQL statement is terminated. Notice that there is
no trailing semicolon. Notice also that the statement is terminated by a forward slash on a line by itself. When you edit
the statement in the editor, do not attempt to terminate it with a semicolon. Semicolons only work as terminators when
you are executing a script file, or when you are typing a SQL statement in from the command prompt. Adding a
semicolon to the end of a statement being edited with the EDIT command will result in an invalid character error when
you try and execute it. The easiest thing to do is to leave the statement as it is, terminated by a forward slash on a line by
itself. Strictly speaking, though, the forward slash is optional, and may be omitted.
Although SQL statements do not require a trailing semicolon, a PL/SQL block does. That's because the trailing
semicolon is part of the PL/SQL syntax, but not part of the SQL syntax.

< previous page page_59 next page >
< previous page page_60 next page >
Page 60
Here are the rules to follow when editing the SQL buffer with an external text editor:

SQL statements must not end with a semicolon.
PL/SQL blocks do need to end with a semicolon.
Optionally, terminate the file with a forward slash on a line by itself.
Only one SQL statement or PL/SQL block is allowed.
Watch the semicolons. I'm so used to using a semicolon at the end of
my SQL statements that I sometimes include one when editing the buffer like
this. Don't make that same mistake, because your SQL statement won't run and
you will need to edit it again.
Getting Back to SQL*Plus
Once you are finished editing your statement, you need to exit the editor in order to return to SQL*Plus. If you are using
Notepad under Windows NT or 95, you do this by going to the File menu and choosing Exit.
Be sure to save the file before leaving the editor! In order for
SQL*Plus to see your changes, they must be written back to the work file. Most
editors, including Notepad, will remind you to save your changes before you
exit, but vi will not. You should explicitly save your changes unless you want to
throw them away.
When you exit the editor, control returns to SQL*Plus. The contents of the work file are loaded into the buffer and
displayed on the screen for you to see. You may then execute your revised statement by using either the RUN command
or the / command.
The work file (AFIEDT.BUF) is not deleted. Its contents remain
undisturbed until your next use of the EDIT command.

< previous page page_60 next page >
< previous page page_61 next page >
Page 61
Executing a Script
Most of this chapter has focused on what you need to know in order to enter a command directly into SQL*Plus and
have it executed. Another option available to you is to have SQL*Plus execute a script. A script is simply a text file that
contains one or more statements to execute. When SQL*Plus executes a script, the commands or statements in the file
are executed just as if you had typed them in directly from the keyboard. A script file can contain any combination of

valid SQL*Plus commands, SQL statements, or PL/SQL blocks.
The START command is used to execute a script. Here is the syntax to use:
START filename [arg1 arg2 arg3]
where:
STA[RT]
May be abbreviated to STA.
filename
Is the name of the script file you want to execute. The default extension is .SQL.
arg1 arg2 arg3
Represent any command-line arguments you want to pass to the script file. These are delimited by spaces, and you may
specify as many as are needed by the script. Arguments containing spaces should be enclosed in either single or double
quotes.
Let's say you had a file named DESCRIBE_ALL.SQL, and it contained the following SQL*Plus commands:
DESCRIBE employee
DESCRIBE project
DESCRIBE project_hours
You could execute this file with the START command as shown here:
SQL> START C:\JONATHSN\SQL_PLUS_BOOK\XB_CH_2\DESCRIBE_ALL
Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER
EMPLOYEE_NAME VARCHAR(40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER

NAME Null? Type

PROJECT_ID NOT NULL NUMBER
PROJECT_NAME VARCHAR2(40)


< previous page page_61 next page >
< previous page page_62 next page >
Page 62
PROJECT_BUDGET NUMBER

Name Null? Type

PROJECT_ID NOT NULL NUMBER
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER
DOLLARS_CHARGED NUMBER
In the previous example, SQL*Plus has executed all three DESCRIBE commands contained in the DESCRIBE_ALL.
SQL file. The output of each command is sent to the screen, but by default SQL*Plus does not echo the individual
commands to the screen.
More commonly, the @ command is used instead of the START command. Both commands do exactly the same thing,
but @ is much shorter to type.
You can do a lot with command files. They are handy for running reports, extracting data, creating new database users,
or performing any other complex task that you need to repeat on a periodic basis. Much of this book centers around the
concept of writing SQL*Plus scripts to automate these types of routine tasks. You will begin to see scripts used
beginning in Chapter 3, Generating Reports with SQL*Plus, where you will learn how to write scripts to take advantage
of SQL*Plus's reporting functionality. Chapter 4 delves into the subject of scripting even more deeply.
The Working Directory.
Whenever you work with files in SQL*Plus, it's important to understand the concept of a working directory. The
working directory is simply the directory that is used whenever you specify a filename without also including a path.
The working directory is also where SQL*Plus writes temporary files, such as the AFIEDT. BUF file created when you
invoke the EDIT command.
If you work under an operating system like Unix, you are already familiar with the concept of a current working
directory. You also very likely know how to move between directories, and are familiar with commands such as pwd

that tell you what your current directory is. The working directory simply happens to be whatever directory you are in
when you invoke SQL*Plus.
Users of the Microsoft Windows operating system tend to be a bit insulated from the concept of a current directory.
SQL*Plus is typically invoked under Windows by clicking an icon, and the user often does not think of himself as being
in any particular directory when this is done. In spite of this, some directory will be cur-

< previous page page_62 next page >
< previous page page_63 next page >
Page 63
rent when you run SQL*Plus under Windows. Take a look at Figure 2-8. It shows the properties for the SQL*Plus menu
item under Windows 95.
Figure 2-8.
The SQL*Plus shortcut properties under Windows 95
Notice the Start in setting shown in Figure 2-8. It's set to the C:\ORAWIN95\BIN directory, the same directory in which
the executable sits. This is the default setting used when installing Oracle on any version of Windows. The implication
is that whenever you run SQL*Plus under Windows and use the SAVE command without specifying a path for the file,
the file will be created in the Oracle BIN directory. The same is true when you use the SPOOL command to send report
output to a file. If you don't specify a path, the report output will go to the BIN directory. Also, when you use the EDIT
command, the AFIEDT.BUF temporary file will be created in this directory.
It's best, if you are using SQL*Plus under Windows, always to specify a path when you save a query, or report output,
to a file. If you save a file, and later can't seem to find it, the Oracle BIN directory is the first place you should look.
You can change the default directory if you like. One way would be to edit the shortcut properties and change the Start
in setting to some other directory. This would be a permanent change, and is probably more trouble than it is worth.
Another, more useful, technique is to use the File → Open dialog to navigate to the directory you want to be current,
and then press the Cancel button. Figure 2-9

< previous page page_63 next page >
< previous page page_64 next page >
Page 64
shows this dialog in preparation for making the C:\JONATHAN\SQL_SCRIPTS directory the current working directory.

Figure 2-9.
The SQL*Plusfile open dialog
Once you navigate to the directory you want as the current directory, you just press the Cancel button. It's not necessary
to actually open a file. This use of the file open dialog is undocumented, but it's a very handy technique for quickly
changing your working directory under Windows.

< previous page page_64 next page >
< previous page page_65 next page >
Page 65
3
Generating Reports with SQL*Plus
In this chapter:
Following a Methodology
Saving Your Work
Designing a Simple Report
Advanced Report Formatting
Totals and Subtotals
Other Reporting Topics

SQL*Plus is a very effective reporting tool. You can count on having SQL*Plus available in any Oracle environment,
and it gets the job done well with a minimum of fuss. This chapter shows you how to leverage the power of SQL*Plus
to produce reports, and presents a report development methodology that works well in most situations.
Following a Methodology
Designing a report is a development project, albeit a very small one. As with any development project, it helps to have a
methodology, or sequence of steps,to follow that will lead you successfully through the process of designing a report
with a minimum of fuss and backtracking. For example, when designing a report, it makes no sense to worry about
formatting the data until you have first decided what data to include.
This chapter presents a micro-methodology for developing reports with SQL*Plus that has worked very well for me. It
divides the design process for a simple report into the following steps:
1. Formulate the query.

2. Format the columns.
3. Add page headers and footers.
4. Format the page.
5. Print it.

< previous page page_65 next page >
< previous page page_66 next page >
Page 66
Dividing the design process into discrete, logical steps reduces both the amount and the complexity of the information
you must keep in your head at any given time.
For more advanced reports, such as a master-detail report, there are some additional tasks you may want to perform.
These include:
Add page and line breaks
Add totals and subtotals
Try to work through report design in a methodical manner. Often there is a temptation just to start typing in commands.
Resist this temptation!
Saving Your Work
Consider saving all the commands to produce a report in a script file. I can't remember ever producing an ad-hoc report
that was truly generated just once. Often there are formatting issues I don't see until after the first printing. Other times a
client will look at a report and immediately think of one or two things to add. By saving your work in a file, you will be
prepared when the request for that same report, or one similar to it, comes again.
The only time I ever print a report by typing the commands interactively into SQL*Plus is when the query is very
simple and I don't care how the output is formatted. In such a case, if I'm in a hurry, I may simply spool off the output
from a SELECT statement, print it, and live with the incorrect pagination, lack of titles, and lack of column formatting.
Saving your work in a file also protects you from simple mistakes and minor catastrophes. If you've typed 20 formatting
commands into SQL*Plus and your computer locks up, you've lost your work. You'll have to restart your computer and
then attempt to remember all those commands. If you are working on a long SQL query, and that query exists only in
the SQL buffer, a simple thing like typing COMMIT will clobber itsomething I've done many times myself. Save
yourself some work by saving your work in a file.
Designing a Simple Report

The first part of this chapter will lead you through the five steps involved in generating a simple, columnar report. This
report will be complete with page headings, page footings, and column headings. In addition, you will learn about
several settings, controlled by the SET command, that are useful when printing and formatting reports.

< previous page page_66 next page >