Report in oracle

By AMIT S VAISHNAV
main Image
Reports are designed in SQL *PLUS to display data other than table format and they are prepared using a select statement.

1. TTITLE – The top title of your report – Can span several lines, depending on how complex you make it.

Syntax – TTITLE [left]{text} [right]{text} [center]{text}

Here in above syntax [] represent optional value i.e. alignment of text, by default it is left alignment and {} represent compulsory value. i.e. text to be displayed.

2. BTITLE – The bottom title of your report.

Syntax – BTITLE [left]{text} [right]{text} [center]{text}

Here in above syntax [] represent optional value i.e. alignment of text, by default it is left alignment and {} represent compulsory value. i.e. text to be displayed.

3. COLUMN - Specify display attributes for each column.

Syntax – COLUMN {columnname}
[Format] {format instruction}
[Justify] {justification}
[Heading] {text}

Here in above syntax
Format option useful in applying editing to numeric fields, date masks to numeric fields, date masks to date fields, and specific lengths to variable length character fields.
Justify option overrides the SQL *PLUS default heading for the particular column.
Heading option overrides the SQL *PLUS default column alignment to the heading default.

4. BREAK ON - it is used to define page breaks based on changing column values and controls duplicate values.

Syntax – BREAK ON {column name}

Here in above syntax column name represent name of column of table.

5. COMPUTE - Defines summary calculations for a reports, tells what columns to summarize and over what range of records. A variety of aggregate function can be applied such as max, min, avg, sum, count etc.

Syntax – COMPUTE {function}

Here in above syntax function represent aggregate function such as max, min, avg, sum, count etc.

6. SQL.PNO – display current page number.

Syntax – SQL.PNO

7. SET PAUSE – makes screen display stop between pages of display.

Syntax – SET PAUSE

8. SET LINESIZE – Controls number of character per page.

Syntax – SET LINESIZE [number]

Here in above syntax number represent number of characters in a line.

9. SET PAGESIZE – Controls number of lines per page.

Syntax – SET PAGESIZE [number]

Here in above syntax number represent number of lines in a page.

To study report we take “employee” table as example.

Selecting employee table

Selecting employee table Selecting employee table

Example of report

Example of report Example of report Example of report

I hope this article will help you