Formatting Query Results

Use the following commands to format, store and print your query results.

ATTRIBUTE [type_name.attribute_name [option...]]

Specifies display attributes for a given column, or lists the current display attributes for a single column or for all columns; option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name|alias}
ON|OFF

BRE[AK] [ON report_element [action [action]]] ...

Specifies where and how formatting will change in a report (for example, skipping a line each time a given column value changes). Enter BREAK with no clauses to list the current BREAK definition.

Where report_element has the following syntax:

{column|expr|ROW|REPORT}

and where action has the following syntax:

[SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]

BTI[TLE] [printspec [text|variable] ...]|[ON|OFF]

Places and formats the specified title at the bottom of each report page, or lists the current BTITLE definition. See TITLE for additional information on valid printspec clauses.

CL[EAR] option ...

Resets or erases the current value or setting for the specified option; option represents one of the following clauses:

BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]

COL[UMN] [{column|expr} [option ...]]

Specifies the display attributes for a given column, such as text for the column heading, or formats for CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING), LONG, CLOB, NCLOB and NUMBER data; option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.

Enter COLUMN [{column |expr} FORMAT format] where the format element specifies the display format for the column.

To change the display format of a NUMBER column, use FORMAT followed by one of the elements in the following table:

Element	Example(s)	Description
9	9999	Number of "9"s specifies number of significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero.
0	0999 9990	Displays a leading zero or a value of zero in this position as a 0.
$	$9999	Prefixes value with dollar sign.
B	B9999	Displays a zero value as blank, regardless of "0"s in the format model.
MI	9999MI	Displays "-" after a negative value. For a positive value, a trailing space is displayed.
S	S9999	Returns "+" for positive values and "-" for negative values in this position.
PR	9999PR	Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed.
D	99D99	Displays the decimal character in this position, separating the integral and fractional parts of a number.
G	9G999	Displays the group separator in this position.
C	C999	Displays the ISO currency symbol in this position.
L	L999	Displays the local currency symbol in this position.
, (comma)	9,999	Displays a comma in this position.
. (period)	99.99	Displays a period (decimal point) in this position, separating the integral and fractional parts of a number.
V	999V99	Multiplies value by 10n, where n is number of "9"s after "V."
EEEE	9.999EEEE	Displays value in scientific notation (format must contain exactly four "E"s).
RN or rn	RN	Displays upper- or lowercase Roman numerals. Value can be an integer between 1 and 3999.
DATE	DATE	Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian dates.

COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]

Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. The following table lists valid functions. All functions except NUMBER apply to non-null values only.

Function	Computes	Applies to Datatypes
AVG	Average of non-null values	NUMBER
COU[NT]	Count of non-null values	All types
MAX[IMUM]	Maximum value	NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
MIN[IMUM]	Minimum value	NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
NUM[BER]	Count of rows	All types
STD	Standard deviation of non-null values	NUMBER
SUM	Sum of non-null values	NUMBER
VAR[IANCE]	Variance of non-null values	NUMBER

REPF[OOTER] [PAGE] [printspec [text|variable] ...] |[ON|OFF]

Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition. See REPHEADER for additional information on valid printspec clauses.

REPH[EADER] [PAGE] [printspec [text|variable] ...] |[ON|OFF]

Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition. Use one of the following clauses in place of printspec:

COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text

SPO[OL] [filename[.ext]|OFF|OUT]

Stores query results in an operating system file and, optionally, sends the file to a printer. OFF stops spooling. OUT stops spooling and sends the file to your host computer's standard (default) printer. Enter SPOOL with no clauses to list the current spooling status. If no file extension is given, the default extension, .lst, is used

TTI[TLE] [printspec [text|variable] ...]|[ON|OFF]

Places and formats a specified title at the top of each report page, or lists the current TTITLE definition. Use one of the following clauses in place of printspec:

COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text