Formatting Query Results
When the results of a query are displayed on the screen in SQL*Plus, Oracle uses default column titles and column data formats. There are several commands available in SQL*Plus that allow the user to format the data displayed by a query.
Basic Formatting
The following simple SELECT command:
select stocksymbol, stockname, currentprice from stock;
will result in the following display:
STO
STOCKNAME
CURRENTPRICE
--- -------------------- ------------
JFM Jetform
21.75
JDS JDS
Fitel
20.55
NTL NorTel
54.1
MLT Mitel
Corp.
14.3
CSN Cognos
27.5
Note that the column names from the database are used as the column headings which are truncated if the column is too narrow. The numeric data is not aligned on the decimal.
Adding a few simple commands will tidy up the output; these commands are entered before the query:
set
linesize 40
set headsep !
ttitle center
'Stock Table
Listing' skip 2
column stocksymbol heading 'Stock!Symbol'
format a7
column stockname heading 'Stock!Name'
format a20
column currentprice heading 'Current!Price
' format $999.99
The command: set linesize 40 sets the line width for the report; the default width is 80.
The command: set headsep ! specifies a character to be used as a separator for column headings. The default is |. It allows the user to create multi-line column headings.
The set long
{80|n} command sets the maximum
width displayed for values with a long data
type.
The ttitle command generates a report title displayed at the top of each report page. The command ttitle off clears the title.
The center option specifies the title is to be centered; left (justified) or right (justified) may also be specified. The actual title may be text (as shown in the example) or a variable; if text is used, it must be enclosed in single quotes. The skip option indicates how many lines are to be skipped after the title before the first detail line is printed.
The column command gives the display attributes for the column specified. Several options can be included with this command:
heading: specifies a column heading
format: specifies the display format for the column.
An indicates an alphanumeric column of width n
9 is used for a numeric field; the symbols $ , and . can be included in the format picture
The column command, without any parameters,
displays the current
settings for all columns. Column
stockname
will display the settings for the stockname
column. Column stocksymbol
clear will clear the settings for stocksymbol.
The output generated as a result of these commands is shown below:
Stock Table Listing
Stock
Stock
Current
Symbol Name
Price
------- -------------------- --------
COS
Corel
$3.16
CSN Cognos
$27.50
JDS JDS Fitel
$20.55
JFM Jetform
$21.75
MLT Mitel
Corp.
$14.30
NTL NorTel
$54.10
Using a Parameter in a Report
The following example generates a report from the view: foreign_keys. It will list the foreign keys for a table passed as a parameter:
set verify off
set headsep !
ttitle 'Foreign keys
for: ' &1 skip 2
column constraint_name heading 'Constraint!Name'
format a15
column column_name heading 'Column!Name'
format a15
column parent_table heading 'Parent!Table'
format a15
column delete_rule heading 'Delete!Rule'
format a10
select constraint_name,
column_name,
parent_table,
delete_rule
from foreign_keys where table_name
= '&1';
set verify on
If HOLDING is passed as a parameter on the command line, the following report will be produced:
Foreign keys for: HOLDING
Constraint
Column
Parent Delete
Name
Name
Table Rule
---------------
---------------
--------------- ----------
SYS_C0017675
PORTFOLIONO
PORTFOLIO NO ACTION
SYS_C0017676
STOCKSYMBOL
STOCK NO
ACTION
The parameter is used as part of the title and as part of the search condition in the SELECT statement.
The set verify command controls whether or not SQL*Plus
shows
substitution results when parameters are passed to a script file. Show
verify will display the setting for
verify. Show all will display the values for settings.
Grouping Data; Subtotals and Grand Totals
Grouping and subtotals will be demonstrated using the following query:
select portfoliono,
stocksymbol, numbershares,
to_char(purchasedate,'dd-Mon-yyyy')
temp_date, purchaseprice,
numbershares * purchaseprice
purchase_amount
from holding
order by portfoliono, stocksymbol;
This query displays data from the holding table.
The following format commands will generate a report, grouped by portfolio number. Subtotals and grand totals are generated for the PURCHASE_AMOUNT column.
set pagesize 50
set linesize 70
set headsep !
ttitle center 'Holding Table Listing' skip 2
column portfoliono heading 'Portfolio!Number' format a15
column stocksymbol heading 'Stock!Symbol' format a7
column temp_date heading 'Purchase!Date' format a12
column numbershares heading 'Current!Holdings' format 9,999
column purchaseprice heading 'Purchase!Price ' format $9,999.99
column purchase_amount heading 'Purchase!Amount ' format $999,999.99
break on report -
on portfoliono skip 2
compute sum label 'Total Amount' of purchase_amount on portfoliono
compute sum label 'Grand Total' of purchase_amount on reportbtitle 'Confidential'
The command: set pagesize n sets the number of lines per page; the default is 24.
The break command is used to specify a control break; on column-name gives the column for the break; on report specifies a report break that is used for displaying grand totals. The skip n option specifies how many lines are to be skipped between groups. Clear break will remove control breaks.
The compute command indicates that calculations are to be performed for each group; the group is indicated by the on column-name option. The functions - AVG, COUNT, MAX, MIN, NUMBER, STD, SUM, VARIANCE - can be used. The label option specifies the label to be attached to the value.
The first portion of the report generated by the above commands is
displayed
below:
Holding Table Listing
Portfolio Stock Current Purchase Purchase Purchase
Number Symbol Holdings Date Price Amount
--------------- ------- -------- ------------ ---------- ------------
P001 COS 200 07-Oct-1998 $3.50 $700.00
CSN 150 05-Oct-1998 $29.90 $4,485.00
CSN 250 06-Oct-1998 $29.75 $7,437.50
JFM 300 30-Sep-1998 $19.30 $5,790.00
JFM 300 01-Oct-1998 $19.55 $5,865.00
JFM 300 02-Oct-1998 $19.55 $5,865.00
NTL 250 30-Sep-1998 $56.40 $14,100.00
*************** ------------
Total Amount $44,242.50P004 MLT 200 13-Oct-1998 $12.20 $2,440.00
NTL 200 02-Oct-1998 $52.90 $10,580.00
*************** ------------
Total Amount $13,020.00
Clearing Settings
The CLEAR command can be used to clear report settings. Follow the keyword CLEAR by the setting to be cleared. For example, the command:
clear columns
will clear any column settings currently
in place.