Generating Reports in Oracle

sql * plus allows the user the flexibility for formatting the results in the form of reports.

It uses sql to retrieve information from the oracle data base and lets the user create polished,well formatted reports.
//Commands used in generation of reports
1. REM
SQLPlus ignores anything on a line that begins with the letters REM. It is used to add comments,
documnetation and explanations etc to a FILE.

2. SET HEAD SEP
SET HEAD SEP is used for head seperator .SQLPLUS will indicate to break a page title
or a column heading that no longer than one line.

3. TTILE AND BTITLE
TTITLE is used to set header for each page and BTITLE is used to set the footer for each page.

4. COLUMN
Column allows us to change the heading and format of any column in a select statement.

5. BREAKON <Column- name> SKIP page <n>
breakon to skip n lines every time the item is changed. breakon command and order by clause must be used together.

6. computesum
compute sum command used to calculate the sum for a particular column and always works with
breakon command.

7. set linesize
set Line size determines the max no of characters that appear in a single line.

8. set pagesize
set page size command sets the total no of lines PLSQL will place on each page including 
ttitle,btitle,column headingsand blank lines it prints.

9. set NewPage
set new page print blank lines before the top line of each page of the reports.

10. list<n>
list command list the buffer contents depending on n.

11. set pause on 
set pause on command is used to view the contents page by page.

12. set pause off
set pause off command is used to disable the pause  on command.

13. spool <file-name>  all the information displayed on the screen is displayed in the specified file
After setting all the above formatting commands if we execute the select query then those commands
will be applied to sql query and will generate a report.

14. spool off : will stop spooling.

15. spool out : close the listed file and prints the file to the system printer.

To reset all the above statements ,execute the following
16. ttitle off;
17. btitle off;


//Sample Example:
ttitle 'Deepak writes a good blog having Account report | Account report';
btitle 'Day begins with hello, good morning';
column Name heading 'Name';
column ProductType heading 'Product Type';
column Status heading 'Status';
break on Status;
spool Account_report.doc;
select Name, ProductType,Status from Account where ProductType='PLATINUM';
spool off;
spool out;
ttitle off;
btitle off;
column Name clear;
column ProductType clear;
column Status clear;
clear breaks;
------------------------------End-----------------------