Commands to Formating Report

This article explains how to format your query results to produce a finished report.

SQL*Plus COLUMN command,can be used to change the column headings and reformat the column data in your query results. SQL*Plus uses column or expression names as default column headings when displaying query results. Column names are often short and cryptic, however, and expressions can be hard to understand.
 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

To give the columns SALARY and LAST_NAME the headings MONTHLY SALARY and LAST NAME respectively, and to split the new headings onto two lines, enter

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

to set the underline to =======

SET UNDERLINE =

COLUMN column_name CLEAR

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

COLUMN column_name FORMAT model

Use format models to add commas, dollar signs, angle brackets (around negative values), and leading zeros to numbers in a given column. You can also round the values to a given number of decimal places, display minus signs to the right of negative values (instead of to the left), and display values in exponential notation.

To use more than one format model for a single column, combine the desired models in one COLUMN command
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

To set the width of the column LAST_NAME to four characters and rerun the current query, enter

COLUMN LAST_NAME FORMAT A4

To give the column COMMISSION_PCT the same display attributes you gave to SALARY, but to specify a different heading, enter the following command:

COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS

To Reset the formatting for the column

COLUMN column_name CLEAR

You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:

BREAK ON break_column SKIP n

To skip a page after every row, use

BREAK ON ROW SKIP PAGE

To show that SKIP PAGE has taken effect, create a TTITLE with a page number:
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO

If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column

The COMPUTE command has no effect without a corresponding BREAK command.

break on DEPARTMENT_ID page  nodup
          on JOB_ID skip 1 nodup

COMPUTE SUM OF SALARY ON DEPARTMENT_ID

You can also set a header and footer for each report. 

The REPHEADER command defines the report header; 

the REPFOOTER command defines the report footer.

A TTITLE, BTITLE, REPHEADER or REPFOOTER command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:

TTITLE position_clause(s) char_value position_clause(s) char_value ... BTITLE position_clause(s) char_value position_clause(s) char_value ... REPHEADER position_clause(s) char_value position_clause(s) char_value ... REPFOOTER position_clause(s) char_value position_clause(s) char_value ...

To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the line size to 70, enter the following commands:
SET PAGESIZE 66 
SET NEWPAGE 0 
SET LINESIZE 70

For detailed about Creating Oracle Reports : Click Here