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

5.3 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 various SET commands, that are useful
when printing and formatting reports.

5.3.1 Step 1: Formulate the Query

The first step to designing a
report is to formulate the underlying SQL query. There is little
point in doing anything else until you have done this. The remaining
steps all involve formatting and presentation. If you
haven't defined your data, there is no reason to
worry about formatting.

For this chapter, let's look at developing a report
that answers the following questions:

To what projects is each employee assigned?

How many hours have been charged to each project?

What is the cost of those hours?

One way to satisfy these requirements would be to develop a report
based on the query in Example 5-1, which summarizes
the hours and dollars charged by employees to each of their projects.

Example 5-1. Summarizing hours and dollars by employee and project

SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) ,
SUM(ph.dollars_charged)
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY e.employee_id, e.employee_name,
p.project_id, p.project_name;

If you execute this query using SQL*Plus, here's
what the output will look like:

EMPLOYEE_NAME
----------------------------------------
PROJECT_NAME SUM(PH.HOURS_LOGGED)
---------------------------------------- --------------------
SUM(PH.DOLLARS_CHARGED)
-----------------------
Marusia Churai
Corporate Web Site 20
3380
Marusia Churai
Enterprise Resource Planning System 24
4056
EMPLOYEE_NAME
----------------------------------------
PROJECT_NAME SUM(PH.HOURS_LOGGED)
---------------------------------------- --------------------
SUM(PH.DOLLARS_CHARGED)
-----------------------
Marusia Churai
Accounting System Implementation 24
4056
Marusia Churai
Data Warehouse Maintenance 20
...

Looks ugly, doesn't it? I wouldn't
want to hand that to a client or my boss. It's a
start though. At least now you can see what the data looks like, and
you know what you have to work with.

5.3.2 Step 2: Format the Columns

Now that you have the data, you can
begin to work through the formatting process. Look again at the
listing produced in step 1. At least three things can be done to
improve the presentation of the data:

Get each record to fit on just one line.

Use better column headings.

Format the numbers.

The first thing that probably leaps out at you is the need to avoid
having report lines so long that they wrap around onto a second line
and become difficult to read. This is often a result of SQL*Plus
allowing for the maximum width in each column. Another cause is that
for calculated columns, the entire calculation is used for the column
heading. That can result in some long headings.

5.3.2.1 Column headings

Use the COLUMN command to format the data
returned from a SELECT statement. It allows you to specify heading,
width, and display formats. The following commands use
COLUMN's HEADING clause to specify more readable
column headings for our report:

COLUMN employee_name HEADING "Employee Name"
COLUMN project_name HEADING "Project Name"
COLUMN SUM(PH.HOURS_LOGGED) HEADING "Hours"
COLUMN SUM(PH.DOLLARS_CHARGED) HEADING "Dollars|Charged" You can refer to the calculated columns in the query by using their
calculations as their names. However, doing so is cumbersome, to say
the least, and requires you to keep the two copies of the calculation
in sync. There is a better way. You can give each calculated column
an alias and use that alias in the COLUMN commands. To give each
column an alias, the changes to the query look like the following:

SUM(PH.HOURS_LOGGED) hours_logged ,
SUM(PH.DOLLARS_CHARGED) dollars_charged The commands to format these two columns then become:

COLUMN hours_logged HEADING "Hours"
COLUMN dollars_charged HEADING "Dollars|Charged" The heading for the dollars_charged column has a
vertical bar separating the two words. This vertical bar tells
SQL*Plus to place the heading on two lines and allows you to use two
rather long words without the need for an excessive column width.

The vertical bar is the default heading separator character and may be changed with the SET HEADSEP command. See Appendix A for details.

5.3.2.2 Numeric display formats

Next, you can specify more readable display formats for the numeric
columns via the FORMAT clause. COLUMN commands are cumulative, so one
approach is to execute the COLUMN commands to set the headers
followed by some more COLUMN commands to set the display formats:

COLUMN employee_name HEADING "Employee Name"
COLUMN project_name HEADING "Project Name"
COLUMN hours_logged HEADING "Hours"
COLUMN dollars_charged HEADING "Dollars|Charged"
COLUMN hours_logged FORMAT 9,999
COLUMN dollars_charged FORMAT $999,999.99 This cumulative approach is handy in an interactive session because
you can continually refine your column formatting without having to
respecify formatting that you're already happy with.
In a script file, though, you're better off issuing
just one COLUMN command per column, followed by all formatting
options that you wish to specify for that column. This way, all
formatting for a given column is in one place:

COLUMN employee_name HEADING "Employee Name"
COLUMN project_name HEADING "Project Name"
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99

5.3.2.3 Text display formats

Finally, you can use FORMAT to specify a shorter length for the
employee_name and project_name
columns. The database allows each of those columns to contain up to
40 characters, but a visual inspection of the output shows that the
names are typically much shorter than that. The format clauses shown
next make these columns each 20 characters wide:

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED Normally, SQL*Plus will wrap longer values onto a second line. The
WORD_WRAPPED keyword keeps SQL*Plus from breaking a line in the
middle of a word.

WRAPPED and TRUNCATE are alternatives to WORD_WRAPPED. WRAPPED allows a longer value to break in the middle of a word and wrap to the next line. TRUNCATE does what it says; it throws away characters longer than the format specification allows.

5.3.2.4 Report output after formatting the columns

Example 5-2 pulls together all the formatting and
SQL changes discussed so far.

Example 5-2. Hours and dollars report with the columns nicely formatted

--Format the columns
COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Execute the query to generate the report.
SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) hours_logged,
SUM(ph.dollars_charged) dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY e.employee_id, e.employee_name,
p.project_id, p.project_name;
EXIT Here is what the output will look like:

Employee Name Project Name Hours Charged
-------------------- -------------------- ------ ------------
Marusia Churai Corporate Web Site 20 $3,380.00
Marusia Churai Enterprise Resource 24 $4,056.00
Planning System
Marusia Churai Accounting System 24 $4,056.00
Implementation
Marusia Churai Data Warehouse 20 $3,380.00
Maintenance This is a great improvement over step 1. The headings are more
readable. The numbers, particularly the dollar amounts, are formatted
better. Most records fit on one line, and when two lines are needed,
the data are wrapped in a much more readable format.

A
blank line has been inserted after every
record with a project name that wraps to a second line. That blank
line is a record separator , and
it's added by SQL*Plus every time a wrapped column
is output as part of a report. I suppose it is added to prevent
confusion because, in some circumstances, you might think that the
line containing the wrapped column data represented another record in
the report. I usually turn it off; this is the
command:
SET RECSEP OFF The next step is to add page headers and footers to the report.

5.3.3 Step 3: Add Page Headers and Footers

Page headers and footers may be added to your report through the use
of the TTITLE and BTITLE commands. TTITLE and BTITLE stand for
"top title" and
"bottom title," respectively.

5.3.3.1 The top title

TTITLE
commands
typically end up being a long string of directives interspersed with
text, and they often span multiple lines. Let's say
you want a page header that looked like this:

The Fictional Company
I.S. Department Project Hours and Dollars Report
============================================================= This heading is composed of the company name centered on the first
line, two blank lines, a fourth line containing the department name
and the report title, followed by a ruling line made up of equal sign
characters. You can begin to generate this heading with the following
TTITLE command:

TTITLE CENTER "The Fictional Company" The keyword CENTER is a directive telling SQL*Plus to center the text
that follows. In their documentation, Oracle sometimes uses the term
printspec to refer to such directives.

Always begin a TTITLE command with a directive such as LEFT, CENTER, or RIGHT. Failure to do this causes SQL*Plus to interpret the command as an old, now obsolete, and much less functional form of the command.

To get the two blank lines into the title, add a SKIP printspec as
follows:

TTITLE CENTER "The Fictional Company" SKIP 3 SKIP 3 tells SQL*Plus to skip forward three lines. This results in
two blank lines and causes the next report line to print as the third
line. To generate the fourth line of the title, containing the
department name and the report name, you again add on to the TTITLE
command:

TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" The text "I.S. Department" will
print flush left because it follows the LEFT printspec, and the
report title will print flush right because it follows the RIGHT
printspec. Both strings will print on the same line because there is
no intervening SKIP printspec. The last thing to do is to add the
final ruling line composed of equal sign characters, giving you this
final version of the TTITLE command:

TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "=============================================================" This is actually one long command. The hyphens at the end of the
first three lines are SQL*Plus command continuation characters. There
are 61 equal sign characters in the last line of the title.

You must use the SKIP directive to advance to a new line. If you want to advance just one line, use SKIP 1. SQL*Plus will not automatically advance for you. If you remove the two SKIP directives from the above TTITLE command, you will end up with a one-line title consisting entirely of equal signs.

5.3.3.2 The bottom title

The
BTITLE
command works the same way as TTITLE, except that it defines a footer
to appear at the bottom of each page of the report. As with TTITLE,
you should always begin a BTITLE command with a printspec such as
LEFT or RIGHT, as opposed to text or a variable name. If you want a
footer composed of a ruling line and a page number, you can use the
following BTITLE command:

BTITLE LEFT "=============================================================" -
SKIP 1 -
RIGHT " Page " FORMAT 999 SQL.PNO This BTITLE command introduces two features that
haven't been shown in previous examples. The first
is the FORMAT parameter, which in this case specifies a numeric
display format to use for all subsequent numeric values. The second
is the system variable SQL.PNO, which supplies the current page
number. Table 5-1 lists several values, maintained
automatically by SQL*Plus, that you can use in report headers and
footers.

The values in Table 5-1 have meaning only to
SQL*Plus and can be used only when defining headers and footers. They
cannot be used in SQL statements such as INSERT or SELECT.

5.3.3.3 Setting the line width

One final point to bring up
regarding page titles is that the directives RIGHT and CENTER operate
with respect to the current line width. The default line width, or
linesize as it is called in SQL*Plus, is 80
characters. So by default, a centered heading will be centered over
80 characters. A flush right heading will have its last character
printed in the 80th position. This presents a slight problem because
our report, using the column specifications given in step 2, is only
61 characters wide. The result will be a heading that overhangs the
right edge of the report by 19 characters, and that
won't appear centered over the data. You could
choose to live with that, or you could add this command to the
script: SET LINESIZE 61 Setting the linesize tells SQL*Plus to format the headings within a
61-character line. It also tells SQL*Plus to wrap or truncate any
lines longer than 61 characters, but the column specifications in
this report prevent anything like that from occurring.

The number of equal sign characters in the ruling line must match the linesize. Otherwise, the ruling line will be too short or too long. Either way, it will look tacky.

5.3.3.4 Report output with page titles

Example 5-3 shows our report generation script with
the addition of the TTITLE, BTITLE, and SET LINESIZE commands.

Example 5-3. Hours and dollars report with page titles added

--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 61
--Setup page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "============================================================="
BTITLE LEFT "=============================================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Execute the query to generate the report.
SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) hours_logged,
SUM(ph.dollars_charged) dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY e.employee_id, e.employee_name,
p.project_id, p.project_name;
EXIT Executing this script will produce the following output:

The Fictional Company
I.S. Department Project Hours and Dollars Report
=============================================================
Dollars
Employee Name Project Name Hours Charged
-------------------- -------------------- ------ ------------
Marusia Churai Corporate Web Site 20 $3,380.00
Marusia Churai Enterprise Resource 24 $4,056.00
Planning System
=============================================================
Page 1
The Fictional Company
I.S. Department Project Hours and Dollars Report
=============================================================
Dollars
Employee Name Project Name Hours Charged
-------------------- -------------------- ------ ------------
Marusia Churai Accounting System 24 $4,056.00
Implementation
=============================================================
Page 2
...

Only a few things are left to clean up before you can print this
report; one obvious improvement is to fix the pagination in order to
get more than 14 lines per page.

5.3.4 Step 4: Format the Page

Most of the work to produce this report
is behind you. Step 4 involves adjusting two SQL*Plus settings that
control page size and pagination. These two settings are:

pagesize
Controls the number of lines per page. SQL*Plus prints headings and
advances to a new page every pagesize lines.

newpage
Controls the size of the top margin, or tells SQL*Plus to use a
formfeed character to advance to a new page.

The SET command is used to define values for each of these settings.
The values to use depend primarily on your output device, the paper
size being used, and the font size being used. Because SQL*Plus is
entirely character-oriented, these settings are defined in terms of
lines. The first question to ask, then, is how many lines will your
printer print on one page of paper.

5.3.4.1 How many lines on a page?

Years ago, before the advent of laser
printers with their multiplicity of typefaces, typestyles, and
typesizes (i.e., fonts), this was an easy question to answer. The
standard vertical spacing for printing was six lines per inch, with
eight lines per inch occasionally being used. Thus, an 11-inch-high
page would normally contain 66 lines. Most printers were pinfeed
printers taking fanfold paper, and would permit you to print right up
to the perforation, allowing you to use all 66 lines if you were
determined to do so.

Today's printers are much more complicated, yet most
will still print six lines per inch if you send them plain ASCII
text. However (and this is important), many printers today will not
allow you to print right up to the top and bottom edges of the paper.
This is especially true of laser printers, which almost always leave
a top and bottom margin. You may have to experiment a bit with your
printer to find out exactly how many lines you can print on one page.

I usually duck this issue entirely by setting PAGESIZE to a safely low setting, usually below 60 lines, and setting NEWPAGE to zero, which causes SQL*Plus to use a formfeed character to advance to a new page. My examples in this chapter use this approach.

The other issue to consider is the
font size you will be using to print
the report. I typically just send reports to a printer as plain ASCII
text, and that usually results in the use of a 12-point, monospaced
font, which prints at six lines per inch. Sometimes, however,
I'll load the file containing a report into an
editor, change the font size to something larger or smaller, and then
print the report. If you do that, you'll need to
experiment a bit to find out how many lines will fit on a page using
the new font size.

5.3.4.2 Setting the pagesize

You set the pagesize with the SQL*Plus command

SET
PAGESIZE as follows:

SET PAGESIZE 55 This tells SQL*Plus to print 55 lines per page. Those 55 lines
include the header and footer lines as well as the data. As it prints
your report, SQL*Plus keeps track of how many lines have been printed
on the current page. SQL*Plus knows how many lines make up the page
footer. When the number of remaining lines equals the number of lines
in your footer, SQL*Plus prints the footer and advances to the next
page. How SQL*Plus advances the page depends on the NEWPAGE setting.

5.3.4.3 Setting the page advance

There are two methods SQL*Plus can use to
advance the printer to a new page. The first method, and the one used
by default, is to print exactly the right number of lines needed to
fill one page. Having done that, the next line printed will start on
a new page. Using this method depends on knowing how many lines you
can fit on one page, and switching printers can sometimes cause your
report to break. One laser printer, for example, may have a slightly
larger top margin than another.

A more reliable method is to have SQL*Plus advance the page using the
formfeed character. The command to do this is:

SET NEWPAGE 0 The NEWPAGE setting tells SQL*Plus how many lines to print to advance
to a new page. The default value is 1. Setting

NEWPAGE
to 0 causes SQL*Plus to output a formfeed character when
it's time to advance the page.

If you set NEWPAGE to 0, do not set PAGESIZE to exactly match the number of lines you can physically print on your printer. Doing that may cause your output to consist of alternating detail pages and blank pages. That's because filling the physical page will itself advance your printer to a new page. The subsequent formfeed advances the page again, resulting in a skipped page. Instead, set PAGESIZE to at least one line less than what will fit on a physical page.

The examples in this chapter use a NEWPAGE setting of 0 and a
PAGESIZE of 55 lines, so you can add the following three lines to the
script file:

--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55 You are free to put these lines anywhere you like so long as they
precede the SELECT statement that generates the report. Example 5-4 shows them added to the beginning of the
script file.

Example 5-4. Hours and dollars report with pagination

--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 61
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "============================================================="
BTITLE LEFT "=============================================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Execute the query to generate the report.
SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) hours_logged,
SUM(ph.dollars_charged) dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY e.employee_id, e.employee_name,
p.project_id, p.project_name;
EXIT

5.3.5 Step 5: Print It

Run the script file one more time and
look at the output on the screen. If everything looks good, you are
ready to print. To print a report, you need to have SQL*Plus write
the report to a file and then print that file. When people speak of
writing SQL*Plus output to a file, the term
spool is often used as a verb. You are said to
be spooling your output to a file. The SPOOL
command is used for this purpose, and you will need to use it twice,
once to turn spooling on and again to turn it off.

5.3.5.1 Spooling to a file

To send report output to a file, put SPOOL
commands immediately before and after the SQL query as shown here:

SPOOL proj_hours_dollars.lis
SELECT E.EMPLOYEE_NAME,
. . .
SPOOL OFF The first SPOOL command tells SQL*Plus to begin
echoing all
output to the specified file. After this command executes, everything
you see on the screen is echoed to this file. The second SPOOL
command turns spooling off and closes the file.

You may wish to add two other commands to the script file before
generating the report. The first is:

SET FEEDBACK OFF Turning feedback off gets rid of the "50 rows
selected" message, which you may have noticed at the
end of the report when you ran earlier versions of the script. The
second command you may want to add is:
SET TERMOUT OFF This command does what it says. It turns off output to the display
(terminal output) but allows the output to be written to a spool
file. Your report will run several orders of magnitude faster if
SQL*Plus doesn't have to deal with updating and
scrolling the display, especially true if you are running the Windows
GUI version of SQL*Plus. You should definitely use SET TERMOUT OFF
when spooling any large report. I usually put the above two settings
immediately prior to the SPOOL command. For example:

SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL $HOME/sqlplus/reports/proj_hours_dollars.lst
SELECT E.EMPLOYEE_NAME,
. . .
SPOOL OFF Do make sure that you set TERMOUT off prior to executing the SPOOL
command to spool the output; otherwise, the SET TERMOUT OFF command
will appear in your spool file.

5.3.5.2 The final script

Example 5-5 shows the script for our report after
adding the SPOOL commands and the commands to turn feedback and
terminal output off. Example 5-5 begins with the SET
ECHO OFF command , ensuring
that you aren't bothered by having to watch all the
remaining commands in the script scroll by on your terminal window.

Example 5-5. Hours and dollars report to be spooled to a file for printing

SET ECHO OFF
--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 61
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "============================================================="
BTITLE LEFT "=============================================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Turn off feedback and set TERMOUT off to prevent the
--report being scrolled to the screen.
SET FEEDBACK OFF
SET TERMOUT OFF
--Execute the query to generate the report.
SPOOL proj_hours_dollars.lst
SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) hours_logged,
SUM(ph.dollars_charged) dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY e.employee_id, e.employee_name,
p.project_id, p.project_name;
SPOOL OFF
EXIT

5.3.5.3 Executing the report

If
you've stored the script for the report in a text
file, you can execute that file from the SQL*Plus prompt like this:

SQL> @ex5-5 The @ character in front of a filename tells SQL*Plus to execute the
commands contained in that file.

5.3.5.4 Printing the file

After you run the script, the complete report will be in the
proj_hours_dollars.lst file. To print that file,
you must use whatever print command is appropriate for your operating
system. On a Windows machine, assuming that LPT1 is mapped to a
printer, you can use the following DOS command:

COPY c:\a\proj_hours_dollars.lis LPT1:

A typical Unix print command would be:

lp proj_hours_dollars.lis An alternative is to load the file into a word processor such as
Microsoft Word or Lotus Word Pro. These programs will interpret
formfeeds as page breaks when importing a text file, so your intended
pagination will be preserved. After you've imported
the file, select all the text and mark it as Courier New 12 point.
Then set the top and left margins to their minimum values; for laser
printers, half-inch margins usually work well. Next, set the right
and bottom margins to zero. Finally, print the report.

One final option, useful with Unix and Linux but not available in any
Windows version of SQL*Plus, is to use the SPOOL OUT command instead
of SPOOL OFF. SPOOL OUT closes the spool file and then prints that
file out to the default printer, saving you the extra step of
manually printing it. For whatever reason, Oracle has chosen not to
implement SPOOL OUT under Windows. It is, however, available under
Unix and
Linux.