The COLUMN, ACCEPT, SET NUMBER, TTITLE, BTITLE, REPHEADER, and REPFOOTER commands allow you to control data formats using what is called a format specification. A format specification is a string of characters that tells SQL*Plus exactly how to format a number, date, or text string when it is displayed.
Table 1.4 shows the format elements that may be used when formatting numeric output.
Table 1-4. Numeric Format Elements
|
Format Element |
Function |
|---|---|
|
9 |
Represents a digit in the output. |
|
0 |
Marks the spot at which you want to begin displaying leading zeros. |
|
$ |
Includes a leading dollar sign in the output. |
|
, |
Places a comma in the output. |
|
. |
Marks the location of the decimal point. |
|
B |
Forces zero values to be displayed as blanks. |
|
MI |
Adds a trailing negative sign to a number and may be used only at the end of a format string. |
|
S |
Adds a + or - sign[a] to the number and may be used at either the beginning or end of a format string. |
|
PR |
Causes negative values to be displayed within angle brackets. For example, -123.99 will be displayed as <123.99>. |
|
D |
Marks the location of the decimal point. |
|
G |
Places a group separator (usually a comma) in the output. |
|
C |
Marks the place where you want the ISO currency indicator to appear. For U.S. dollars, this will be USD. |
|
L |
Marks the place where you want the local currency indicator to appear. For U.S. dollars, this will be the dollar sign character. |
|
V |
Displays scaled values. The number of digits to the right of the V indicates how many places to the right the decimal point is shifted before the number is displayed. |
|
EEEE |
Causes SQL*Plus to use scientific notation to display a value. You must use exactly four Es, and they must appear at the right end of the format string. |
|
RN |
Allows you to display a number using Roman numerals. An uppercase RN yields uppercase Roman numerals, while a lowercase rn yields lowercase Roman numerals. Numbers displayed as Roman numerals must be integers and must be between 1 and 3,999, inclusive. |
|
DATE |
Causes SQL*Plus to assume that the number represents a Julian date and to display it in MM/DD/YY format. |
[a] SQL*Plus always allows for a sign somewhere when you display a number. The default is for the sign to be positioned to the left of the number and to be displayed only when the number is negative. Positive numbers will have a blank space in the leftmost position. | |
Table 1.5 contains several examples illustrating the use of the various format elements.
Character strings are formatted using only one element. That element is “A”, and it is followed by a number specifying the column width in terms of characters. For example:
SQL> COLUMN a FORMAT A40 SQL> SELECT 'An apple a day keeps the doctor away.' A 2 FROM dual; A ---------------------------------------- An apple a day keeps the doctor away.
By default, longer text values are wrapped within the column. You can use the WORD_WRAPPED, WRAPPED, and TRUNCATED parameters of the COLUMN command to control whether and how wrapping occurs. For example:
SQL> COLUMN a FORMAT A18 WORD_WRAPPED SQL> SELECT 'An apple a day keeps the doctor away.' A 2 FROM dual; A ------------------ An apple a day keeps the doctor away.
When text columns wrap to multiple lines, SQL*Plus will print a blank line called a record separator following the record. Use SET RECSEP OFF to prevent that behavior.
When used with the ACCEPT command, a character format defines the maximum number of characters SQL*Plus will accept from the user.
The date format elements in Table 1.6 may be used with Oracle’s built-in TO_CHAR function to convert date values to character strings. For example:
SQL> SELECT TO_CHAR(SYSDATE, 2 'dd-Mon-yyyy hh:mi:ss PM') 3 FROM dual; TO_CHAR(SYSDATE,'DD-MON ----------------------- 13-Dec-1999 09:13:59 PM
When used with the ACCEPT command, a date format string requires the user to enter a date in the format specified.
Table 1-6. Date Format Elements
|
Format Element |
Function |
|---|---|
|
- / , . ; : |
Punctuation to be included in the output. |
|
‘text’ |
Quoted text to be reproduced in the output. |
|
AD or A.D. BC or B.C. |
An AD or BC indicator included with the date. |
|
AM or A.M. PM or P.M. |
AM or PM printed, whichever applies given the time in question. |
|
CC |
The century number. This will be 20 for years 1900 through 1999. |
|
SCC |
Same as CC, but negative for BC dates. |
|
D |
The number of the day of the week. This will be one through seven. |
|
DAY |
The full name of the day. |
|
DD |
The day of the month. |
|
DDD |
The day of the year. |
|
DY |
The abbreviated name of the day. |
|
HH |
The hour of the day on a 12-hour clock. |
|
HH12 |
The hour of the day on a 12-hour clock. |
|
HH24 |
The hour of the day on a 24-hour clock. |
|
IW |
The week of the year. This will be 1-53. |
|
IYYY |
The four-digit year. |
|
IYY |
The last three digits of the year number. |
|
IY |
The last two digits of the year number. |
|
I |
The last digit of the year number. |
|
J |
The Julian day. Day one is equivalent to Jan 1, 4712 BC. |
|
MI |
The minute. |
|
MM |
The month number. |
|
MON |
The three-letter month abbreviation. |
|
MONTH |
The month name, fully spelled out. |
|
Q |
The quarter of the year. Quarter one is Jan-Mar; quarter two is Apr-Jun; and so forth. |
|
RM |
The month number in Roman numerals. |
|
RR |
The last two digits of the year. |
|
RRRR |
The four-digit year. |
|
SS |
The second. |
|
SSSSS |
The number of seconds since midnight. |
|
WW |
The week of the year. |
|
W |
The week of the month. Week one starts on the first of the month; week two starts on the eighth of the month; and so forth. |
|
Y,YYY |
The four-digit year with a comma after the first digit. |
|
YEAR |
The year spelled out in words. |
|
SYEAR |
The year spelled out in words with a leading negative sign when the year is BC. |
|
YYYY |
The four-digit year. |
|
SYYYY |
The four-digit year with a leading negative sign when the year is BC. |
|
YYY |
The last three digits of the year number. |
|
YY |
The last two digits of the year number. |
|
Y |
The last digit of the year number. |
When you use a date format element that displays a text value, such as the name of a month, the case used for the format element drives the case used in the output. Table 1.7 shows examples of formatting dates.