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.TXTUsing 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.TXTUsing 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
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). |