SQL Plus - Formatting Reports

Text

Through the SQL*Plus COLUMN command with the heading options, you can change the column headings and reformat the column data in your query results.

COLUMN column_name HEADING column_heading

To produce a report from EMP_DETAILS_VIEW with new headings specified for LAST_NAME, SALARY, and COMMISSION_PCT, enter the following commands:

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

To split the new headings onto two lines, use a vertical bar (|) where you want to begin a new line :

COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'

Underline headings character

To change the character used by default (a dash) to underline headings to an equal sign and rerun the query, enter the following commands:

SET UNDERLINE '='

Character

To change the width of a character to n, use FORMAT An.

To set the width of the column LAST_NAME to four characters, enter

COLUMN LAST_NAME FORMAT A4

NUMBER

The default width of datatype columns is the width of the column in the database.

If you do not explicitly use FORMAT, then the default values are the system variables :

• NUMWIDTH for the column's width
• NUMFORMAT for the format

To display SALARY with a dollar sign, a comma, and the numeral zero instead of a blank for any zero values, enter the following command:

COLUMN SALARY FORMAT $99,990

Use a zero in your format model, as shown, when you use other formats such as a dollar sign and wish to display a zero in place of a blank for zero values.

Date

The default width and format of unformatted DATE columns is derived from the NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9.

The default format for the NLS territory, America, is DD-Mon-RR, and the default width is A9

LONG, CLOB, NCLOB or XMLType

The column width of a LONG, CLOB, NCLOB or XMLType defaults to the value of the system variables LONGCHUNKSIZE or LONG.

Global

linesize

Set linesize 160

The system variable WRAP controls all columns

Column

You can override the setting of WRAP for a given column through the WRAPPED, WORD_WRAPPED, and TRUNCATED clauses of the COLUMN command.

NCLOB or multibyte CLOB columns cannot be formatted with the WORD_WRAPPED option. COLUMN WRAPPED apply always.

Headers

The column heading is truncated regardless of the setting of WRAP or any COLUMN command clauses.

for one column

The format model will stay in effect until you enter a new one, reset the column's format with

COLUMN column_name CLEAR

or exit from SQL*Plus.

for all column

See clear