The COLUMN command is used to format the data returned from a SELECT statement. In the next few sections, you will see how this command may be used to specify headings, widths, and display formats. The syntax for COLUMN looks like this:

COL[UMN] [column_name [ALI[AS] alias|
                      CLE[AR]|
                      FOLD_A[FTER]|
                      FOLD_B[EFORE]|
                      FOR[MAT] format_spec|
                      HEA[DING] heading_text|
                      JUS[TIFY] {LEFT|CENTER|CENTRE|RIGHT}|
                      LIKE source_column_name|
                      NEWL[INE]|
                      NEW_V[ALUE] user_variable|
                      NOPRI[NT]|
                      PRI[NT]|
                      NUL[L] null_text|
                      OLD_V[ALUE] user_variable|
                      ON|
                      OFF|
                      TRU[NCATED]...]]
                      WOR[D_WRAPPED]|
                      WRA[PPED]|

where:

Don’t be intimidated by all these parameters. The most important and complex ones are described in this chapter, complete with examples. Multiple parameters may be specified with one COLUMN command. COLUMN commands are cumulative. Issuing two COLUMN commands for the same column, but using different parameters, is the same as issuing one command with all the parameters.

The COLUMN command with the HEADING clause may be used to specify column headings that are more understandable by an end user. COLUMN commands are issued prior to the SELECT statement you want them to affect. SQL*Plus stores the headings and uses them when the results from the SELECT statement are displayed. The following COLUMN commands define more readable 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'

Notice that you can refer to the calculated columns in the query by using the calculation as the name. While possible, it 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.

Next you can specify more readable display formats for the numeric columns by adding a FORMAT clause to the COLUMN commands as shown here:

COLUMN hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999.99

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.

The script to produce the report now looks like this:

--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,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
 GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
          P.PROJECT_ID, P.PROJECT_NAME;

Here is what the output will look like:

Dollars
Employee Name        Project Name          Hours      Charged
-------------------- -------------------- ------ ------------
Jonathan Gennick     Corporate Web Site       20    $3,380.00
Jonathan Gennick     Year 2000 Fixes          24    $4,056.00
Jonathan Gennick     Accounting System        24    $4,056.00
                     Implementation

Jonathan Gennick     Data Warehouse           20    $3,380.00
                     Maintenance

Jonathan Gennick     TCP/IP                   28    $4,732.00
                     Implementation

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 is wrapped in a much more readable format.

Notice that 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 really represented another record in the report. I usually turn it off; the command to do that is:

SET RECSEP OFF

The next step is to add page headers and footers to the report.

The TTITLE command is used to define page headers. Here is the syntax:

TTI[TLE] [[OFF|ON]|
         COL x|
         S[KIP] x|
         TAB x|
         LE[FT]|
         CE[NTER]|
         R[IGHT]|
         BOLD|
         FOR[MAT] format_spec|
         text|

                              
                  variable...]

where:

You should always begin a TTITLE command with a parameter such as LEFT or RIGHT, as opposed to text or a variable name. Failure to do this causes SQL*Plus to interpret the command as an old, now obsolete, form of TTITLE. The old form is still acceptable for compatibility reasons, but is greatly limited in what it can do.

TTITLE commands typically end up being a long string of parameters, interspersed with text, and they often span multiple lines. Let’s say you wanted 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 referred to as a printspec , and tells SQL*Plus to center the text that follows. 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 line to print on that 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, while 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 very long command. The hyphens at the end of the first three lines are SQL*Plus command continuation characters. There are 61 equals-sign characters in the last line of the title.

The BTITLE command works exactly the same way as TTITLE, except that it defines a footer to appear at the bottom of each page of the report. The syntax looks like this:

BTI[TLE] [OFF|ON]|
         [COL x|
         S[KIP] x|
         TAB x|
         LE[FT]|
         CE[NTER]|
         R[IGHT]|
         BOLD|
         FOR[MAT] format_spec|
         text|
         variable...]

where:

As with TTITLE, you should always begin a BTITLE command with a parameter such as LEFT or RIGHT, as opposed to text or a variable name. If you wanted a footer composed of a ruling line and a page number, you could 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 use of the system variable SQL.PNO, which supplies the current page number. There are several values, maintained automatically by SQL*Plus, that you can use in report headers and footers. These are shown in Table 3.1.

These values 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.

One final point to bring up regarding page titles is that the printspecs LEFT, RIGHT, and CENTER all 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 this 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 ten 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 either wrap or truncate any lines longer than 61 characters, but the column specifications in this report prevent anything like that from occurring.

With the addition of the TTITLE, BTITLE, and SET LINESIZE commands, the script to generate the report will look like this:

--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,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
 GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
          P.PROJECT_ID, P.PROJECT_NAME;

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
-------------------- -------------------- ------ ------------
Jonathan Gennick     Corporate Web Site       20    $3,380.00
Jonathan Gennick     Year 2000 Fixes          24    $4,056.00
Jonathan Gennick     Accounting System        24    $4,056.00
                     Implementation

Jonathan Gennick     Data Warehouse           20    $3,380.00
                     Maintenance

Jonathan Gennick     TCP/IP                   28    $4,732.00
                     Implementation

Jenny Gennick        Corporate Web Site        9    $1,215.00
Jenny Gennick        Year 2000 Fixes           8    $1,080.00
=============================================================
                                                    Page    1

There are only a few things left to clean up before you can print this report; one obvious improvement is to fix the pagination in order to get more than 24 lines per 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 allow 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 how exactly how many lines you can print on one page.

The other issue to consider is the font size you will be using to print the report. I usually 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.

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 also 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.

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 exactly 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 in order to advance to a new page. The default value is 1. Setting it to causes SQL*Plus to output a formfeed character when it’s time to advance the page.

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

--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55

I usually make these settings just prior to the TTITLE and BTITLE commands, but you are free to put them anywhere you like so long as they precede the SELECT statement that generates the report.

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

SPOOL C:APROJ_HOURS_DOLLARS.LIS
SELECT E.EMPLOYEE_ID, 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 also echoed to this file. The second SPOOL command turns spooling off and closes the file.

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

SET FEEDBACK OFF

Turning feedback off gets rid of both the “50 rows selected.” and the “Commit complete.” messages, 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 still 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. On my PC, the simple report used as an example in this chapter runs in 13 seconds with the display on, but takes less than 1 second with the display off. You should definitely use this option on any large report.

I usually put the above two settings immediately prior to the SPOOL command, then turn them back on again after spooling has been turned off. For example:

SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL C:APROJ_HOURS_DOLLARS.LIS
...
SPOOL OFF
COMMIT;
SET TERMOUT ON
SET FEEDBACK ON

Do make sure that you set TERMOUT off prior to spooling the output; otherwise the SET TERMOUT OFF command will appear in your spool file.

After adding the SPOOL commands and the commands to turn feedback and terminal output off, the script file for the sample report looks like this:

--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

--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 C:APROJ_HOURS_DOLLARS.LIS
SELECT E.EMPLOYEE_NAME,
       P.PROJECT_NAME,
       SUM(PH.HOURS_LOGGED) hours_logged,
       SUM(PH.DOLLARS_CHARGED) dollars_charged
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
 GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
          P.PROJECT_ID, P.PROJECT_NAME;
COMMIT;
SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON

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> @c:jonathansql_plus_bookxd_ch_44_query_1_step_5.sql

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

After you run the script, the complete report will be in the PROJ_HOURS_DOLLARS.LIS file. To print that file, you must use whatever print command is appropriate for your operating system. On a Windows 95 or NT machine, assuming that LPT1 was mapped to a printer, you could use the following DOS command:

C:>COPY c:aproj_hours_dollars.lis LPT1:

A typical Unix print command would be:

lp proj_hours_dollars.lis

Another 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 12pt. 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, which, unfortunately, is 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.