SQL Plus - Formatting Reports

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

How to format SQLplus output (ie SQL Plus - (Report|SQL Output))

All below parameters can be configured in the login script.

Advertising

3 - Formatting

3.1 - Headers

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

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

3.2 - Column

The formatting of column is done through the help of the COLUMN function.

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

COLUMN column_name FORMAT model
Advertising

3.2.1 - 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

3.2.2 - 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 :

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.

3.2.3 - 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

Advertising

3.2.4 - 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.

3.3 - Wrapping

3.3.1 - Global

linesize

Set linesize 160

The system variable WRAP controls all columns

3.3.2 - 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.

3.3.3 - Headers

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

3.4 - Page

The header is repeated for each pagesize line.

To suppress the pagination:

SET pagesize 0 embedded ON

3.5 - Justification

Left justification is the default for datatypes. You can change it with the JUST option of the column command.

3.6 - Summary

First make a break on a column (A GROUP BY) and then defines your summary function with compute:

BREAK ON OWNER
COMPUTE SUM OF SIZE ON OWNER

4 - How to

4.1 - reset the formatting

4.1.1 - 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.

4.1.2 - for all column

See clear

5 - Support

5.1 - Bad Alignment

If you have formatting problem such as bad alignment, try the following:

  • add the following in your script:
clear column
set null NULL
  • Try to have the same number of lines for the headers description.
  • Try to set tab off

6 - Reference

lang/sqlplus/format.txt · Last modified: 2017/09/06 19:29 by gerardnico