Monday, 20 February 2017

REPORT GENERATION USING SQL*PLUS commands



*  What is a report?
*  Sample report
*  Report script
*  Break command
*  Compute command
*  Column command
*  Ttitle and Btitle commands
*  Spool command
*  Clear command
*  System variables
*  Displaying information using SHOW command

What is a report?
A report is information provided in a neat and understandable format. Report contains details as well as summary information. We have so far seen different clauses of SELECT command. But no clause allows you to display details as well as summary information.. For instance, GROUP BY can be used to get summary information but it cannot display details. Simple SELECT can display details but cannot display summary information.

SQL*PLUS is a tool that is used to send SQL command to Oracle Instance.
It is an environment in which you enter SQL commands.  It also provides some extra commands such as DESCRIBE, EDIT etc., which are called as SQL*PLUS commands. SQL*PLUS environment also provides a set of commands which can be used to generate report.

The following is a sample report that we are going to generate in this chapter. This report is generated with SQL*PLUS commands that are specifically meant for this purpose and SELECT command, which is used to retrieve data from database.

Sample Report
The following is the report to be generated. We will see in the remaining sections of this chapter how to generate this report.

The following are the various SQL*PLUS commands that are used to generate the following report. The report is used to display the details of payment made by students. The details are divided into groups based on batch and then course. The totals are displayed for each batch, each course and also for the entire report.
                                                                                                                                  Page:  1
                                                      Payments Report
                                                      Date of   Date of
COURS BATCH    ROLLNO Student Name         PHONE      Joining   Payment    AMOUNT
----- ----- --------- -------------------- ---------- --------- --------- -------
asp   b2            3 Andy Roberts         433554     11-JAN-01 13-JAN-01   2,000
                    3 Andy Roberts         433554     11-JAN-01 20-JAN-01   3,000
                    4 Malcom Marshall      653345     16-JAN-01 30-JAN-01   2,000
                    4 Malcom Marshall      653345     16-JAN-01 16-JAN-01   3,000
                    5 Vivan Richards       641238     16-JAN-01 16-JAN-01   5,000
      *****                                                               -------
      sum                                                                  15,000
 *****                                                                     -------
sum                                                                        15,000

    c     b3            6 Chirs Evert                     14-JAN-01 14-JAN-01   3,500
                    7 Ivan Lendal          431212     15-JAN-01 15-JAN-01   3,500
      *****                                                               -------
      sum                                                                   7,000
 *****                                                                     -------
 sum                                                                         7,000

java  b5       9 Richard Marx         876567     06-APR-01 07-APR-01   3,000
                   11 Jody Foster          234344     07-APR-01 10-APR-01   3,500
                   11 Jody Foster          234344     07-APR-01 07-APR-01   1,000
                  10 Tina Turner          565678     06-APR-01 10-APR-01   4,500
      *****                                                               -------
      sum                                                                  12,000
*****                                                                     -------
sum                                                                        12,000
ora   b1        1 George Micheal       488333     10-JAN-01 10-JAN-01   4,500
                    2 Micheal Douglas      334333     11-JAN-01 11-JAN-01   3,500
                    2 Micheal Douglas      334333     11-JAN-01 17-JAN-01   1,000
      *****                                                               -------
                                                                                  Page:  2
                                      Payments Report
                                                      Date of   Date of
COURS BATCH    ROLLNO Student Name         PHONE      Joining   Payment    AMOUNT
----- ----- --------- -------------------- ---------- --------- --------- -------
      sum                                                                   9,000

ora   b7       12 Louis Figo              535555     12-JUL-01 12-JUL-01   3,000
                   13 Marshall Brain       223222     13-JUL-01 13-JUL-01   2,500
                   13 Marshall Brain       223222     13-JUL-01 15-JUL-01   1,000
      *****                                                               -------
      sum                                                                   6,500
      *****                                                               -------
sum                                                                        15,500
xml   b4       8 George Micheal       488333     01-MAR-01 01-MAR-01   2,000
                    8 George Micheal       488333     01-MAR-01 02-MAR-01   2,000
      *****                                                               -------
      sum                                                                   4,000
*****                                                                     -------
sum                                                                         4,000
                                                                               -------
sum                                                                        53,500
Report Script
The following is the report script.  The script is used to generate the report shown above. The following sections will explain the commands used in the script.

Type the script in any text editor under the name like payreport.sql and then run it from SQL prompt of SQL*Plus as follows.

SQL>>start c:\orabook\payreport.sql

rem ***********************************************
rem set break and compute settings
break on report on course skip page on batch skip 2
compute sum of amount on batch course report
set pagesize 24
set linesize 90
set feedback off
column amount  format 99,999
column name    format a20 heading 'Student Name'
column dj      heading 'Date of|Joining'
column dp      heading 'Date of|Payment'
ttitle  skip 1  right  'Page:' format 99 sql.pno  skip 1 center 'Payments Report'  skip 2

spool  payreport.lst

select c.ccode course, b.bcode batch, p.rollno, s.name name, phone, dj, dp, amount
from  batches b, students s, payments p, courses c
where  b.ccode = c.ccode and b.bcode = s.bcode and s.rollno = p.rollno
order by course, batch;

spool off
set feedback on
rem clear settings

clear compute
clear break
clear column
ttitle off

BREAK Command
Specifies how which column(s) the data selected by SELECT command is to be grouped (broken).  This is also used to specify what should be done when break is given.

BRE[AK] [ON expression [action]] . . .
 
Expression is the column on which the data is to be grouped or ROW or REPORT keyword.
 
Action specifies what action should be taken when break is issued. The following are the possible actions.
 
SKI[P]  n 
Prints specified number of empty lines.
SKI[P]  page
Issues a page break at break.
DUP[LICATE]
Prints the value of a break column in every selected row.
NODUP[LICATE]
Prints blanks instead of the value of a break column when the value is a duplicate of the column's value in the preceding row.
 

1.            Note: BREAK command alone displays the current break settings

The following BREAK command issues break whenever it detects a change in BCODE column and starts a new page.

break on bcode skip  page

2.            Note: It is important to give ORDER BY clause on the columns that are used in BREAK command.

When multiple columns are used in BREAK, SQL*PLUS issues break starting from rightmost column to leftmost.
break on country skip page on city skip 2
First it issues break on CITY and then issues break on COUNTRY. You need to makes sure the order is right.
BREAK command in script
The script above used the following break.
break on report on course skip page on batch skip 2
The above BREAK issues break on three different levels. First whenever there is a change in BATCH column, second whenever there is a change in COURSE column and finally at the end of the report.
It is important to know the order in which columns are to be given – most specific to most common.
For this break to work properly the ORDER BY clause of the SELECT must be given as follows:
order by  course, batch


COMPUTE Command
This is used to compute and print summary information.  The

COMP[UTE]  [function  [ LABEL  text] OF {column}...
           ON {column | REPORT | ROW} . . .]

FUNCTION is any of the functions listed in Table 1. 
If you give more than one function, use spaces to separate the functions.

FUNCTION
COMPUTES
APPLIES TO DATATYPES
AVG
Average of non-null values
NUMBER
COU[NT]
Count of non-null values
all types
MAX[IMUM]
Maximum value
NUMBER, CHAR, VARCHAR, VARCHAR2
MIN[IMUM]
Minimum value
NUMBER, CHAR, VARCHAR, VARCHAR2
NUM[BER]
Count of rows
all types
STD
Standard deviation of non-null values
NUMBER
SUM
Sum of non-null values
NUMBER
VAR[IANCE]
Variance of non-null values
NUMBER
Table 1:  List of functions used in COMPUTE.


Note: Every COMPUTE requires a corresponding BREAK.  COMPUTE displays summary information, only when there is a break on the given level.

The following COMPUTE will display the subtotal and grand total of amount.
compute sum of amount on bcode report
For the above COMPUTE to function there must be a corresponding BREAK as follows.
break on bcode skip page on report
LABEL keyword specifies the text to be displayed as label for the computed value. The maximum length of the label is 500 characters.
compute sum label ‘Grand Total’ of amount on report

3.            NOTE: The label is truncated to the size of first column in the SELECT.

COMPUTE command in Script
The following compute command is used in the script to generate the report.
compute sum of amount on batch course report
It will display the sum of AMOUNT column at the end of each batch, course and at the end of report.

COLUMN Command
Specifies the display attributes of a column.  The following are the possible options:

*  Text for the column heading
*  Alignment of the column heading
*  Format of NUMBER type columns
*  Wrapping of text

COL[UMN] [{column|expr} [option ...]]

The following is the list of a few commonly used options of COLUMN command.
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
NUL[L] char
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

FORMAT
Specifies the display format of the column. 
The format is a string containing format characters. Format characters depend on the data type of the column.
For CHAR type the only available format is :
An
Where n is the number of locations used on the screen to display the value.  You can specify what action you want to take, when n is less than the length of the data. The valid options are:  

WRAPPED
Wraps the remaining string to next line.
WORD_WRAPPED
Same as WRAPPED but moves the entire word to next line.
TRUNCATED
Truncates the extra portion of the string.

To format DATE type column we have to use TO_CHAR function.
The following are the available options to format numeric columns.

Element
Example
Description
9
9999
Represents a digit.
0
0999
Displays leading zeroes.
$
$9999
Prefixes a dollar sign to a value.
B
B9999
Displays a zero value as blank.
MI
9999MI
Displays "-" after a negative value.
PR
9999PR
Displays a negative value in angle brackets.
comma
9,999
Displays a comma in the position indicated.
period
99.99
Aligns the decimal point in the position indicated.
V
999V99
Multiplies value by 10n, where n is the number of  “9s” after the “V”
EEEE
9.999EEEE
Displays in scientific notation.   There should be exactly EEEE.
Table 2: List of Formats for Numeric Data.

The following format will display FEE column in the given format.
column fee format 99,999.00

HEADING

Specifies the heading to be displayed. If no heading is given, the name of the column is displayed as heading.
If heading separator (|) is used then heading is split into multiple lines.
JUSTIFY   is used to align heading either to LEFT, CENTER or RIGHT.
column ccode heading  ‘Course|Code’ justify center
NULL
Substitutes null values with the given value in the display. If not given then null is displayed as blank.
COLUMN command may also be used to show display attributes of a particular column as follows:

SQL> column name
COLUMN   name ON
HEADING  'Student | Name ' headsep '|'
JUSTIFY center

COLUMN command in script
The following COLUMN commands in the script are used to format columns.

column amount  format 99,999
column name    format a20 heading 'Student Name'
column dj      heading 'Date of|Joining'
column dp      heading 'Date of|Payment'

AMOUNT column is formatted as 99,999.

NAME column is displayed with a width of 20 columns and heading is set to “Student Name”.

DJ’s heading is set to two lines heading where first line is “Date of” and second line is “Joining”. The same is the case with DP column.


TTITLE and BTITLE Commands
TTITLE specifies the text to be displayed   at the top of each   printed page. BTITILE displays text at the bottom of the printed page.

TTI[TLE]  [options  [text]] [ON | OFF]

BTI[TLE]  [options  [text]] [ON | OFF]


The following is the list of options:


Option
Meaning
BOLD
Prints text in bold print.
COL n
Prints title after n columns in the current line.
ENTER
Aligns title to center.
LEFT
Aligns title to left.
RIGHT
Aligns title to right.
SKIP   n
Skips n number of lines before printing title.


TTITLE center 'First line ' skip 1 center 'Second line'
Prints the text First Line in the first line and after skipping one line then displays text Second Line.
TTITLE left  ‘Payments Report’ right sql.pno
Prints the text Payments Report on the left and current page number on the right. SQL.PNO returns the current page number.  The other variables that you can use are:  SQL.USER – current username, SQL.RELEASE – current Oracle release number.

TTITLE off

Will turn off display of top title.

TTITLE command in script
The following TTITLE command is used to display the top title for payments report.

ttitle  skip 1  right  'Page:' format 99 sql.pno  skip 1 center 'Payments Report'  skip 2

First string “Page:” is displayed followed by the page number of the current page. These two are right justified to the page. Then one line is skipped and then at the center of the line title “Payments Report” is displayed. Then two lines are skipped before the data of the page is displayed.


SPOOL Command
Stores the result of query into an operating system file.  It can be used to send the content of the file to printer.

Once spooling is turned on whatever that is displayed on the screen, a copy of that is copied into spool file.

SPO[OL ]    [ filename  |  OUT |  OFF ]


Filename
Specifies the name of the file into which output is to be stored. It begins spooling.
OFF        
Stops spooling.
OUT        
Stops spooling and sends the contents of the spool file to default printer.

The following SPOOL command sends subsequent output to file REPORT.LST.

SPOOL  report.lst
SPOOL command in the script
SPOOL command is used in the script to capture the output into file PAYREPORT.LST as follows.
spool payreport.lst
Then after the SELECT command SPOOL command is used again to stop capturing the output using:
spool off


CLEAR Command
Resets the current value of the specified option.

CLEAR option

Where option may be any of the following.

Option
What it clears?
BRE[AKS]
Break settings set by BREAK.
COMP[UTES]
Compute setting set by COMPUTE.
SCR[EEN]
Content of SQL*PLUS window.
COL[UMNS]
Column setting set by COLUMN.
BUFF[ER]
Text in the current buffer.

The following CLEAR command will rest all summary information specified by COMPUTE.
CLEAR COMPUTES
SET Variables
Set variables/system variables are used to configure SQL*PLUS environment. For instance, system variables can be used to set page pause, the length of page, size of line and so on.
SET command is used to change the value of system variables.

  SET system_variable value

The following is a list of a few system variables.  For the complete list of system variables and all the possible options, please see on-line help.
Some of the system variables can store more than one value. For instance, FEEDBACK stores a number and ON/OFF state.

System variable
Meaning
AUTOCOMMIT
If set to ON, automatically commits changes made by SQL command.
FEEDBACK
If set to ON, displays the number of rows selected by SELECT, if number of rows is >= the number specified by FEEDBACK variable.
LINESIZE
Specifies the number of characters that can be displayed in a single line.
PAGESIZE
Specifies the number of lines to be displayed in a single page.
NUMFORMAT
Specifies the default numeric format for numbers.
PAUSE
If set to ON, gives pause at the end of each page. Also contains the text to be displayed when pause is issued.
SERVEROUTPUT
If set to ON, enables the display of output by DBMS_OUTPUT package.
TERMOUT
If set to OFF, suppresses the display of output of start file.
Table 3: SET Variables.

The following SET commands will configure SQL*Plus for report.

set pagesize 50
set linesize  132
set feedback off
set pause off

Displaying Information Using SHOW Command
You can display the current values of system variables and  other options using  SHOW command.

SHO[W]  option

Where option may be any of the following.

Option
Meaning
System_variable
Any system variable.
ERR[ORS]
Compilation error of procedures, functions.
REL[EASE]
The current release of  Oracle.
TTI[TLE]   and    BTI[TLE]
Current setting of title.
USER
The name of the current user.
ALL
Displays the values of all show options.

For the complete list of options, please see on-line help.

To get the name of the current user, enter:

SQL> show user
user is "SATEESH"

To display the current value of PAGESIZE variable, enter:

SQL> show pagesize
pagesize 24

Summary

SQL*PLUS environment, apart from providing SQL commands, has its own set of commands called as SQL*PLUS commands. These commands are basically to generate report and to change the working environment.

SQL Interview Questions and Answers

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next...