Formatting Query Results

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

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

Specifies where changes occur in a report and the formatting action to perform, such as:

suppressing the display of duplicate values for a given column
skipping a line each time a given column value changes
printing computed figures each time a given column value changes or at the end of the report

Enter BREAK with no clauses to list the current BREAK definition.

Where report_element has the following syntax:

{column | expression | 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 a title at the bottom of each report page, or lists the current BTITLE definition. Use one of the following clauses in place of printspec:

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

CL[EAR] option ...

Resets or erases the current value or setting for the specified option.

Where option represents one of the following clauses:

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

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

Specifies display attributes for a given column, such as:

text for the column heading
alignment for the column heading
format for NUMBER data
wrapping of column data

Also lists the current display attributes for a single column or for all columns.

Where option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
ENTMAP {ON | OFF}
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | 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]

Note:

Currently only NEW_V[ALUE] variable syntax is supported.

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	Examples	Description
`,` (comma)	9,999	Displays a comma in the specified position.
`.` (period)	99.99	Displays a period (decimal point) to separate the integral and fractional parts of a number.
`$`	$9999	Displays a leading dollar sign.
`0`	0999 9990	Displays leading or trailing zeros (0).
`9`	9999	Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeros. A zero (0) is displayed for a value of zero.
`B`	B9999	Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model.
`C`	C999	Displays the ISO currency symbol in the specified position.
`D`	99D99	Displays the decimal character to separate the integral and fractional parts of a number.
`EEEE`	9.999EEEE	Displays a value in scientific notation (format must contain exactly four "E"s).
`G`	9G999	Displays the group separator in the specified positions in the integral part of a number.
`L`	L999	Displays the local currency symbol in the specified position.
`MI`	9999MI	Displays a trailing minus sign after a negative value. Displays a trailing space after a positive value.
`PR`	9999PR	Displays a negative value in <angle brackets>. Displays a positive value with a leading and trailing space.
`RN rn`	RN rn	Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and 3999.
`S`	S9999 9999S	Displays a leading minus or plus sign. Displays a trailing minus or plus sign.
`TM`	TM	Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E.
`U`	U9999	Displays the dual currency symbol in the specified position.

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

In combination with the BREAK command, calculates and prints summary lines using various standard computations. It also lists all COMPUTE definitions. The following table lists valid functions. All functions except NUMBER apply to non-null values only. COMPUTE functions are always executed in the following sequence AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE.

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

SET SQLFORMAT {csv | html | xml | json | ansiconsole | insert | loader | fixed | default}

Outputs reports in various formats. The ansiconsole option formats and resizes data according to the column widths, for easier readability. The json option returns a query in JSON format.

SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure> allows you to set a custom delimited format.

SET SQLFORMAT JSON-FORMATTED returns a query in well formatted JSON output.

SPO[OL] [filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Stores query results in a file, or optionally sends the file to a printer. OFF stops spooling. OUT stops spooling and sends the file to your computer's default printer. Enter SPOOL with no clauses to list the current spooling status. If no file extension is given, the default extension, .lst or .lis, 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. The old form of TTITLE is used if only a single word or a string in quotes follows the TTITLE command.

Where printspec represents one or more of the following clauses:

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