Previous | Table of Contents | Next

Page 87

enables the developer to easily format a heading, but it has some limitations that the column command corrects.

This command is used to change column headings. The benefits of the column command is increased formatting functionality. Table 4.2 contains some examples and descriptions of the various and available column format settings.

Table 4.2Column Format Setting

Example Description
column fk_department
heading `department'
Changes the column heading to
department.
column first_name heading `First
|Name'
Changes the column heading to First Name
and stacks the two words on top of
each other. The pipe (|) symbol indicates when to
begin the next row of the stack.
column last_name justify left Positions the heading on the left side.
Other options include center and right.
column last_name format a25 Changes the format of the column
to alphanumeric with a length of 25. This is a
useful command when your column
heading is larger than the column. It
eliminates the heading truncation that
will occur.
column wages format $99,999 Changes the format to a numeric format.
In this example, the values will begin with a
dollar sign ($) and use a comma to
designate thousands.
column taxes_owed like wages Formats the taxes_owed column the same
as the wages column.
column employment_date off Turns off the column options
without affecting the data.
column fk_department alias dept Creates a column alias called dept for
the fk_department column.
column birth_date newline Starts a new line before printing the
column value.
column fk_department new_val dpt Creates a variable called dpt to hold
the fk_department column's value for use in a
ttitle command.
column fk_department null `UNKNOWN' Specifies the text or value to be displayed
if the column has a null value.
                                         continues

Page 88

Table 4.2Continued

Example Description
column fk_department old_val odpt Creates a variable called odpt to hold
the fk_department column's value for use in a
btitle command.
column payroll_number noprint Turns the column's display off.
column payroll_number print Turns the column's display on.
column address trunc Truncates the value to the width of
the column's definition.
column classification wrapped Stacks the values of a column on multiple
lines if it does not fit the defined width of
the column. This option stacks at a letter, breaking apart a word.
column classification word_wrapped Stacks or places the values in a column on
multiple lines if it does not fit the defined
width of the column. This option stacks
at the end of a word. It will try not to break
apart a word.
column clear Eliminates the column definitions.

You can enter the column command into the buffer directly from the editor or from a command file. After the command has been entered, it remains until you issue the column clear command or a new column command for the column. The clear columns command eliminates all the column formats simultaneously. Whereas the column clear command only clears the specified column.

TIP
I always use this statement at the end of my command files to set the SQL*PLUS environment back to the default. This way I don't get any unexpected formats on subsequent queries.

The keyword column starts the command, and the name of the column to be formatted follows. Next comes the format settings. The command accepts as many of the settings as necessary. If you don't have enough room on the row to type all the settings, be certain to continue to the next line by placing the dash (-) symbol at the end of the row. You must place all the settings in one continuous command because subsequent column commands for the same column supersede each other.

Listing 4.3 demonstrates the use of several column commands. It gives custom headings to several columns: left justifies and stacks the last_name heading; right justifies the first_name heading; and center justifies, formats with a $, and prints the wages column on the next line.

Page 89

Listing 4.3L_04_03.TXT—Using the Column Command to Format Column Headings

SQL> ttitle off
SQL> btitle off
SQL> column last_name heading `LAST|NAME' justify left
SQL> column first_name justify right heading `FIRST NAME'
SQL> column wages heading `ANNUAL WAGES' justify center -
> format $99,999.99 newline
SQL> select last_name, first_name, wages
  2  from employee;
LAST
NAME                 FIRST NAME
--------------- ---------------
ANNUAL WAGES
------------
TAFT            WILLIAM
   $8,500.00

ROOSEVELT       THEODORE
   $8,000.00

ANTHONY         SUSAN
   $7,000.00

ROOSEVELT       ELEANOR
        $.00
.

19 records selected.

The new_val and old_val settings are interesting. The new_val setting creates a local variable and causes SQL*PLUS to populate this variable with the column value for each row selected. You can use this functionality to place column values into the report title. When the report starts a new page, this value is placed in the title. Placing a column value in the title makes a report look nice, especially when used with the page break command. The old_val has the same functionality except the variable is used in the report footing. Listing 4.4 illustrates the use of these settings by placing the first_name value in the title and the last_name in the footing.

Listing 4.4L_04_04.TXT—Using the New_Val and Old_Val Settings

SQL> ttitle off
SQL> btitle off
SQL> column last_name old_val ln
SQL> column first_name new_val fn
SQL> ttitle left fn center `EXAMPLE REPORT -
> DISPLAYING A NEW_VAL SETTING'
SQL> btitle left ln center `EXAMPLE REPORT -
> DISPLAYING AN OLD_VAL SETTING'
             		`									continues

Page 90

Listing 4.4 Continued

SQL> select last_name, first_name
  2  from employee
  3  where payroll_number = 29;

DWIGHT                      EXAMPLE REPORT  DISPLAYING A NEW_VAL SETTING
LAST
NAME                 FIRST NAME
--------------- ---------------
EISENHOWER      DWIGHT
EISENHOWER                  EXAMPLE REPORT  DISPLAYING AN OLD_VAL SETTING

Formatting Numbers

You can use special format settings to define numeric columns. The formats are placed in a column command. These format values are used in Oracle Forms and Reports in addition to SQL*PLUS. Table 4.3 lists these settings. The first column in the table is the format setting. The setting is placed in the column command. The second table column contains an example of a formatted value. The third column describes that format setting.

Table 4.3Number Formats

Format Display Description
999990 123521 The count of the nines and zeros determines the
maximum length that can be displayed by the column.
90 12 or 0 A zero displays if the value is zero.
99,999.99 12,345.34 Places a comma to designate thousands, and has a
two-digit decimal.
09999 01234 Displays the number with leading zeros.
$999 $123 Places a dollar sign at the beginning of the number.
B9999 1234 or blank If zero, displays appear blank.
99999MI 12345- or 12345 Places a minus sign after a negative number.
99999S 12345- or 12345 Same as 99999MI.
S99999 -12345 or 12345 Places a minus sign before a negative number.
RM LX Displays the number as a Roman numeral.
9999PR <1234> Displays negative numbers surrounded by arrows.
9.99EEEE 8.7656 19 Displays the number in scientific notation (must be
exactly four Es).

Previous | Table of Contents | Next