Oracle Database - SQLPlus - Formatting SQL*Plus Reports

Through the SQL*Plus COLUMN command, you can change the column headings and reformat the column data in your query results. You can define a more useful column heading with the HEADING clause of the COLUMN command, in the following format:

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'

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 '='

Reset the column's format with

COLUMN column_name CLEAR

or exit from SQL*Plus.

If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.

The COLUMN command identifies the column you want to format and the model you want to use, as shown:

COLUMN column_name FORMAT model

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.

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.

The default width of datatype columns is the width of the column in the database. The column width of a LONG, BLOB, BFILE, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller.

Within the COLUMN command, identify the column you want to format and the model you want to use:

COLUMN column_name FORMAT model

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

COLUMN LAST_NAME FORMAT A4

• Formatting SQL*Plus Reports