Formatting Columns

You use the COLUMN command to format the display of column headings and column data. The simplified syntax for the COLUMN command is as follows :

 COL[UMN] {  column   alias  } [  options  ]

where

column specifies the column name .
alias specifies the column alias to be formatted. In Chapter 2 you saw that you can rename a column using a column alias; you can then reference your alias in the COLUMN command.
options specifies one or more options to be used to format the column or alias.

There are a number of options you can use with the COLUM N command. The following table shows some of these options.

Option	Description
FOR[MAT] format	Sets the format for the display of the column or alias to that specified in the format string.
HEA[DING] heading	Sets the text for the heading of the column or alias to that specified in the heading string.
JUS[TIFY] [{ left center right }]	Aligns the column output to the left, center, or right.
WRA[PPED]	Wraps the end of a string onto the next line of output. This option may cause individual words to be split across multiple lines.
WOR[D_WRAPPED]	Similar to the WRAPPED option except that individual words aren t split across two lines.
CLE[AR]	Clears any formatting of columns (sets the formatting back to the default).

The format string in the previous table may take a number of formatting parameters. The parameters you specify depend on the data stored in your column:

If your column contains characters , you can use A x to format the characters, where x specifies the width for the characters. For example, A12 sets the width to 12 characters.
If your column contains numbers , you can use any of the number formats shown in Table 3-4 of Chapter 3. For example, $99.99 sets the format to a dollar sign, followed by two digits, the decimal point, and another two digits.
If your column contains a date, you can use any of the date formats shown in Table 4-2 of Chapter 4. For example, MM-DD-YYYY sets the format to a two-digit month ( MM ), a two-digit day ( DD ), and a four-digit year ( YYYY ).

Let s consider an example. You re going to format the output of a query that retrieves the product_id , name , description , and price columns from the products table. The display requirements, the format strings, and the COLUMN commands are shown in the following table.

Column	Display Requirement	Format	COLUMN Command
product_id	Two digits	99	COLUMN product_id FORMAT 99
name	Thirteen-character word-wrapped strings and change heading to PRODUCT_NAME	A13	COLUMN name HEADING PRODUCT_NAME FORMAT A13 WORD_WRAPPED
description	Thirteen-character word-wrapped strings	A13	COLUMN description FORMAT A13 WORD_WRAPPED
price	Dollar symbol, with two digits to the right of the decimal point and two digits to the left of the decimal point	$99.99	COLUMN price FORMAT $99.99

Enter the following COLUMN commands into SQL*Plus in preparation for executing a query against the products table:

 SQL>  COLUMN product_id FORMAT 99  SQL>  COLUMN name HEADING PRODUCT_NAME FORMAT A13 WORD_WRAPPED  SQL>  COLUMN description FORMAT A13 WORD_WRAPPED  SQL>  COLUMN price FORMAT .99

Next, run the following query to retrieve some rows from the products table. Notice the formatting of the columns in the output due to the previous COLUMN commands:

 SQL>  SELECT product_id, name, description, price  2  FROM products  3  WHERE product_id  <  6;  PRODUCT_ID PRODUCT_NAME DESCRIPTION PRICE ---------- ------------- ------------- --------  1 Modern A description .95  Science of modern  science  2 Chemistry Introduction .00  to Chemistry  3 Supernova A star .99  explodes  4 Tank War Action movie .95 PRODUCT_ID PRODUCT_NAME DESCRIPTION PRICE ---------- ------------- ------------- --------  about a  future war  5 Z Files Series on .99  mysterious  activities

This output is readable, but wouldn t it be nice if you could just display the headings once at the top? You can do that by setting the page size .