SQL Plus - Formatting Reports

Card Puncher Data Processing

About

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

All below parameters can be configured in the login script.

Formatting

Headers

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

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

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 :

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.

Wrapping

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.

Page

The header is repeated for each pagesize line.

To suppress the pagination:

set pagesize 0 embedded on

Justification

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

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

How to

reset the formatting

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

Support

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

Reference





Discover More
Card Puncher Data Processing
(SQL Plus|SqlCl) (Oracle Database Console)

SQLPlus is the first client of the Oracle Database. On windows, there is two versions of SQLPlus : a GUI one (sqlplusw.exe) Officialy deprecated in the next release of Oracle.Documentation...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - (Pivot|Unpivot) Statement

Pivot statement in Oracle The column from the first select will be created with as prefix the value of the column in the for statement of the pivot statement. Unpivot can be used to see all column...
Card Puncher Data Processing
SQL Plus - Break

BREAK specifies where changes occur in a report and the formatting action to perform, such as: suppressing display of duplicate values for a given column skipping a line each time a given column value...
Card Puncher Data Processing
SQL Plus - Column Command

The column command: specifies display attributes for a given column, such as: text for the column heading alignment for the column heading format for NUMBER data wrapping of column data lists...
Card Puncher Data Processing
SQL Plus - Compute

COMPUTE in combination with the BREAK command, calculates and prints summary lines using various standard computations. The following summary function are available: SUM : Sum of the values in...
Card Puncher Data Processing
SQL Plus - Describe

Describe lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure. where: connect_identifier is a database link - connect identifier You...
Card Puncher Data Processing
SQL Plus - Tab System variable

Tab is a system variable that setermines how SQLPlus formats white space in terminal output. where: ON uses the TAB character. TAB settings are every eight characters. The default value for TAB...



Share this page:
Follow us:
Task Runner