Report Generation

FORMATING COMMAND (REPORT GENERATION)
            Sql * plus provides us with the several commands for formatting query results. They can be broadly classified as

COLUMN
            SQL*plus uses column names as default column headings while displaying query results. We can display our own column heading with the help of the column command.
Syntax:- Column <col name> heading <col-heading>
Ex:-     column eno heading EmployNo
Select * from vision;
Column na heading Name
Column sal heading Salary
Select * from vision;

Format
            When we display columns containing numeric values, we can either accept the default SQL*Plus display format or we can change it by issuing the following syntax
Syntax:- column col-name format model;
Formats


Element 

Example(s) 

Description 

9 

 
9999

Number of "9"s specifies number of significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. 

0 

 
0999
9990

Displays a leading zero or a value of zero in this position as a 0. 

$ 

 
$9999

Prefixes value with dollar sign. 

B 

 
B9999

Displays a zero value as blank, regardless of "0"s in the format model. 

MI 

 
9999MI

Displays "-" after a negative value. For a positive value, a trailing space is displayed. 

S 

 
S9999

Returns "+" for positive values and "-" for negative values in this position. 

PR 

 
9999PR

Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed. 

D 

 
99D99

Displays the decimal character in this position, separating the integral and fractional parts of a number. 

G 

 
9G999

Displays the group separator in this position. 

C 

 
C999

Displays the ISO currency symbol in this position. 

L 

 
L999

Displays the local currency symbol in this position. 

, (comma) 

 
9,999

Displays a comma in this position. 

. (period) 

 
99.99

Displays a period (decimal point) in this position, separating the integral and fractional parts of a number. 

V 

 
999V99

Multiplies value by 10n, where n is the number of "9's" after the "V." 

EEEE 

 
9.999EEEE

Displays value in scientific notation (format must contain exactly four "E's"). 

RN or rn 

 
RN

Displays upper- or lowercase Roman numerals. Value can be an integer between 1 and 3999. 

DATE 

 
DATE

Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian dates. 

Ex:-        column sal format $9,99,999.99
Select * from vision;
Column na format a3;
Select * from vision;

BREAK/COMPUTE
To organize rows of reports into subsets and to perform summation on group of rows, break and compute commands are used. The break command also suppresses duplicate values.
Syntax:- break on col-name skip n;
               Compute function of col-name…
Ex:-       break on sal skip 2
              Select eno,sal from vision where sal>2000;
              Break on report;
              Compute sum of sal on report;
              Select eno,sal from vision where sal>2000;

TTITLE
            Title commands Compressing ttitle and btitle are used to place top title and bottom title on each page.

 

Syntax:-  ttitle positionclause charValue PositionClause skip 1
Btitle  positionclause charValue PositionClause
Ttitle off
Btitle off
Skip n
Ttitle   à to display the title definition
Btitle    à to display the bottom title definition
Ex:- ttitle center ‘VisionComputers’ skip 1 left ‘pno:34374’
Btitle center ‘VUYYURU’
Ttitle off
Btitle off
Ttitle
btitle

PAGE DIMENSIONS
Newpage
            It is used to set the number of lines between the beginning of each page and the top line.
Syn:- set newpage<no of lines>[none]
Ex:-   set newpage  2

LINESIZE
Line size is used to print on a wide paper and align a title.
Syn:- set linesize<no of lines>
Ex:- set linesize 3
PAGESIZE
            It is used to set the number of lines on a page from the top line.
Syn:- set pagesize <no of lines>
Ex:-    set pagesize 10
Storing and Printing Query Result
Syn: 1) spool filename     à to store the data
2) spool off
3) spool out   à to print the file
4) @ filename  à to run the file

 

For
Online Classes

Contact Us: +919885348743