2.9 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 onlyNEW_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 |
---|---|---|
|
9,999 |
Displays a comma in the specified position. |
|
99.99 |
Displays a period (decimal point) to separate the integral and fractional parts of a number. |
|
$9999 |
Displays a leading dollar sign. |
|
0999 9990 |
Displays leading or trailing zeros (0). |
|
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. |
|
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. |
|
C999 |
Displays the ISO currency symbol in the specified position. |
|
99D99 |
Displays the decimal character to separate the integral and fractional parts of a number. |
|
9.999EEEE |
Displays a value in scientific notation (format must contain exactly four "E"s). |
|
9G999 |
Displays the group separator in the specified positions in the integral part of a number. |
|
L999 |
Displays the local currency symbol in the specified position. |
|
9999MI |
Displays a trailing minus sign after a negative value. Displays a trailing space after a positive value. |
|
9999PR |
Displays a negative value in <angle brackets>. Displays a positive value with a leading and trailing space. |
|
RN rn |
Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and 3999. |
|
S9999 9999S |
Displays a leading minus or plus sign. Displays a trailing minus or plus sign. |
|
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. |
|
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