Oracle:Control Commands

Control commands can be used to:

• Format reports
• Edit SQL commands
• Store and retrieve queries
• Get help

Some of these have been introduced in previous sections.

Formatting Reports

When saving the query results, you may wish to format them to make them more meaningful. For example:

• Change column headings
• Format number, character and date columns
• Copying, listing and resetting column display attributes
• Suppress duplicate values and inserting spaces in the output for clarity
• Setting page dimensions
• Placing titles at the top and bottom of reports (headers and footers)
• Display column values, or the current date, or page numbers in the titles
• Calculating and printing summary lines (totals, averages, minimums, etc.)
• Listing and removing spacing and summary line definitions
• Displaying column values and the current date or page number in your titles

Some of these commands have been introduced already, but the following sections will introduce some new formatting commands.

Some basic report formatting commands

The format of the output can be changed via various control commands.

Note, formatting commands do not need to be terminated by a semi-colon. The formatting commands are not part of the SQL standard and are an extra feature provided by the database vendor. Each database management system (DBMS) has its own set of formatting features, for example, the way Oracle formats a report will be different from FoxPro or Access. The extra features provided by Oracle are called SQL*Plus.

Column headings can be given more user-friendly names with the COLUMN command. This can be used to change the name of the column or reformat the column data in a query.

Changing Column Headings

SQL*Plus uses column or expression names (e.g., EMPNO, or COUNT(*)) as the default column name when outputting results. Column names can be short and cryptic and expressions can be hard to understand.

To define a more useful column heading, you need to use the HEADING clause of the FORMAT command.

The format is:

COLUMN column_name HEADING new_column_name

Note:

• If the column to be renamed is an expression, e.g., SUM(sal), then the column_name is substituted by the expression.
• If spaces are needed in the new name, enclose the name in either single or double quotes, e.g., "Student Name".
• The new heading is case sensitive, that is if you type it in lower/mixed case, it will appear exactly as typed, e.g., Student_Code.

To cancel an individual formatting command:

COLUMN column_name CLEAR

or to clear all column headings:

CLEAR COLUMNS

To see what column definitions are in action type:

columns

For example, to list information about employees who work for Blake:

COLUMN DEPTNO HEADING DEPARTMENT
TTITLE 'WILLIAM BLAKE || LIST OF EMPLOYEES'
SELECT ENAME, JOB, MGR, SAL, COMM, DEPTNO
FROM EMP WHERE MGR = 7698;

Notice the difference from normal. In a report the formatting commands should also be cancelled afterwards, otherwise they will remain active until you exit Oracle.

To cancel the formatting commands:

COLUMN DEPTNO CLEAR
TTITLE OFF

BTITLE can be used to display a title at the end of the report. It displays the title centred on a single line with no date or page number added.

To display the column heading over more than one line, use a vertical bar (|) where you want to begin a new line, for example:

COLUMN empno HEADING "Employee|Number"
COLUMN ename HEADING "Employee|Name"
SELECT empno, ename FROM EMP;
CLEAR COLUMNS

Changing Column Width

The displayed width of a VARCHAR2, LONG or DATE field can be changed in a report by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column. The structure for the FORMAT clause is:

COLUMN column_name FORMAT model

The column_name can also be a column alias for an expression.

Examples:

COLUMN ename FORMAT A15
COLUMN ename HEADING "Employee Name"
COLUMN sal FORMAT $9,999
COLUMN annual_sal FORMAT $99,999
SELECT empno, ename, sal, sal*12 AS annual_sal FROM EMP;
CLEAR COLUMNS

Formatting Number Columns

When working with numerical data, or calculated fields, for example, the SUM or COUNT of a particular field, the format model needs to reflect numerical data. A format model is a representation of the way you want the numbers in the column to appear, using 9's to represent the digits.

The format for numerical data is similar to before:

COLUMN column_name FORMAT model

The format model can be a combination of the following:

!Element	Example(s)	Description
9	9999	Determines the display width by the number of digits entered. Does not display leading zeros. Note, if you give fewer digits than the size of the number, only ####'s will be displayed!
0	0999	Displays leading zeroes.
	9990	Displays zero instead of a blank when a value is zero
$	$9999	Prefixes a dollar sign to a value.(No £ signs!)
B	B9999	Display a zero value as a blank
MI	9999MI	Displays "-" after a negative value
PR	999PR	Displays a negative value in angle brackets
Comma	9,999	Displays a comma in the position indicated
Period	99.99	Aligns the decimal point in the position indicated
V	999V99	Multiples the value by 10n, where n is the number of "9's" after the "V"
EEEE	9.999EEEE	Displays in scientific notation (format must contain exactly four "E's"
DATE	Date	Displays value as a date in the MM/DD/YY format. Used to format a number that represents a Julian date.

A Julian date is the number of days elapsed since Jan 1, 4712 BC. Julian dates allow continuous dating from a common reference. You will find these formats useful for expressions involving COUNTs or SUMs.

For example type in the following and re-run the previous query:

COLUMN SAL FORMAT $9999.99
COLUMN COMM FORMAT $9999.99

Organising rows into groups: the BREAK command

The BREAK command can organise the rows of a report into groups and specify an action that a query is to take between groups.

To print a personnel report on the entire company with breaks where the value of DEPTNO changes and to skip a line in the report at each break:

BREAK ON DEPTNO SKIP1;
TTITLE 'ACME WIDGET PERSONNEL REPORT'
BTITLE 'COMPANY CONFIDENTIAL'
SELECT DEPTNO, ENAME, SAL
FROM EMP
ORDER BY DEPTNO;

A BREAK definition can be cleared by

CLEAR BREAK

Note that to make sensible use of the BREAK command the rows must be in a specific order. The order in which rows are displayed can be controlled by the ORDER BY command (see section 16).

Some useful BREAK formats

Generic Format Examples BREAK ON column name BREAK ON DEPTNO BREAK ON column name ON column name ON column name BREAK ON MGR ON DEPTNO BREAK ON ROW (Breaks whenever a row is selected) BREAK ON PAGE (Breaks between pages of a report) BREAK ON REPORT (Breaks at the end of the report) 1.5 Some useful actions at BREAK BREAK ON ....... SKIP (n) Skips N number of lines BREAK ON ........ PAGE Begins a new page

Computing subtotals at breaks

If you organise the rows of a report into groups with the BREAK command, you can make a query perform various computations on the rows in each group. The format of the COMPUTE command is:

COMPUTE function_name OF column_name, column_name ON break_column name

where the function name can be SUM, MIN, MAX, AVG, STD VAR, or COUNT.

For example,

BREAK ON DEPTNO SKIP1
COMPUTE SUM OF SAL ON DEPTNO
SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
ORDER BY DEPTNO;
CLEAR BREAK

Page size commands

The following commands alter the page size and width, which are useful for printed reports. The default values are shown first.

Function Action SET PAGESIZE {14|n} sets the number of lines from the top title to the end of the page. With 11 inch long paper, a value of 54 (plus a NEWPAGE value of 6) leaves one-inch margins above and below a report. Set PAGESIZE to 0 to suppress headings, page breaks, titles, the initial blank line, and other formatting information SET NEWPAGE {1|n} sets the number of blank lines printed between the beginning of each page and the top title. A value of 0 sends a formfeed between pages and clears the screen on most terminals. SET LINESIZE {80|n} sets the total number of characters displayed on a line before starting a new line and controls the position of centred and right-aligned text in TTITLE and BTITLE. The range is 1 to a system-dependent maximum.

For example,

SET PAGESIZE 65
SET NEWPAGE 1
SET LINESIZE 100

Will print 65 lines per page, 100 characters wide and will throw one new line at the top of a new page. For reports that will be viewed on the screen only, set the pagesize to about 40.

 Create a file which produces a report on Salesmen earning more than £3000. The report should be headed ‘Computing Salesmen Report’ and each page should have ‘For Your Eyes Only’ at the bottom. The output should be ordered by salary. The file should be stored in U:\salesman.sql. Save the output of the query into a file U:\salary.txt.