Learning Objectives – after this chapter you will be able to:
- Explain why SQL*Plus report commands are needed (details + summaries together).
- Write a report script using BREAK, COMPUTE, COLUMN, TTITLE/BTITLE and SPOOL.
- Reset settings with CLEAR and configure SQL*Plus with SET system variables.
- Display settings with SHOW.
What is a Report?
A report presents information in a neat, understandable format containing details and summary information. A plain SELECT shows details but no summaries; GROUP BY shows summaries but no details. SQL*Plus – the environment used to send SQL to the Oracle instance – provides extra commands to produce such reports.
The sample Payments Report lists each payment (course, batch, roll number, student name, phone, date of joining, date of payment, amount), grouped by course and batch, with totals for each batch, each course and the whole report, a page number and a centred title.
The Report Script
payreport.sql (from the chapter)
rem save as payreport.sql and run: SQL> start c:\orabook\payreport.sql 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 clear compute clear break clear column ttitle off
BREAK and COMPUTE
BRE[AK] [ON {column | ROW | REPORT} [action]] ... action: SKI[P] n | SKI[P] PAGE | DUP[LICATE] | NODUP[LICATE] COMP[UTE] [function [LABEL text] ... OF column ... ON {column | REPORT | ROW} ...]- BREAK groups the selected rows: when the value of a break column changes, the action is performed (skip lines, new page). NODUPLICATE (default) prints blanks for repeated values.
BREAKalone shows current settings. - The ORDER BY must be on the break columns. Breaks are issued from the rightmost column to the leftmost, so list them from the most common to the most specific as in the script (report → course → batch).
- COMPUTE calculates and prints summary values – functions AVG, COUNT, MAXIMUM, MINIMUM, NUMBER (count of rows), STD, SUM and VARIANCE.
- Every COMPUTE needs a corresponding BREAK at that level; COMPUTE prints only when that break occurs.
- LABEL sets the text shown for the computed value (up to 500 characters, truncated to the width of the first column):
compute sum label 'Grand Total' of amount on report.
COLUMN Command
COL[UMN] {column | expr} [FOR[MAT] format] [HEA[DING] text] [JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}] [NUL[L] text] [WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]]- Character columns use the format
An(n = display width); longer values are WRAPPED, WORD_WRAPPED or TRUNCATED. - DATE columns are formatted with TO_CHAR in the query.
- HEADING sets the column heading; the separator | splits it over several lines (
'Date of|Joining'). JUSTIFY aligns the heading. NULL substitutes text for null values. COLUMN namealone displays the current attributes of that column.
| Element | Example | Meaning |
|---|---|---|
| 9 | 9999 | A digit |
| 0 | 0999 | Leading zeroes |
| $ | $9999 | Dollar sign prefix |
| B | B9999 | Zero shown as blank |
| MI / PR | 9999MI / 9999PR | “-” after a negative value / negative value in angle brackets |
| , and . | 99,999.00 | Comma and decimal point positions (12345 → 12,345.00) |
| V | 999V99 | Multiply by 10n (n = 9s after V) |
| EEEE | 9.999EEEE | Scientific notation |
TTITLE, BTITLE, SPOOL and CLEAR
- TTITLE prints text at the top and BTITLE at the bottom of each page. Options: BOLD, COL n, CENTER, LEFT, RIGHT, SKIP n.
TTITLE OFFturns it off. - Variables for titles: SQL.PNO (current page number), SQL.USER (current user), SQL.RELEASE (Oracle release).
- SPOOL filename copies everything displayed into a file; SPOOL OFF stops; SPOOL OUT stops and sends the file to the default printer.
- CLEAR resets settings: BREAKS, COMPUTES, SCREEN, COLUMNS, BUFFER.
ttitle center 'First line' skip 1 center 'Second line' ttitle left 'Payments Report' right sql.pno ttitle off
SET System Variables and SHOW
| Variable | Meaning |
|---|---|
| AUTOCOMMIT | ON – commits every SQL command automatically |
| FEEDBACK | Shows the number of rows selected when it is ≥ the given number |
| LINESIZE | Characters per line |
| PAGESIZE | Lines per page |
| NUMFORMAT | Default number format |
| PAUSE | Pause at the end of each page (with optional text) |
| SERVEROUTPUT | ON – displays output of the DBMS_OUTPUT package (used in PL/SQL) |
| TERMOUT | OFF – suppresses output of a start file |
Configuring SQL*Plus and displaying settings
set pagesize 50 set linesize 132 set feedback off set pause off show user -- user is "SRIKANTH" show pagesize show errors -- compilation errors of procedures/functions show all
Key Takeaways
- BREAK groups output; COMPUTE prints totals and needs a matching BREAK; ORDER BY must match the breaks.
- COLUMN formats headings and values; TTITLE/BTITLE add page titles (SQL.PNO = page number).
- SPOOL saves output to a file; CLEAR resets settings; SET/SHOW configure and display the environment.
Chapter 14 Notes (PDF)
ch14.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 14 notes (PDF). Click a question to see the answer.
Answer : A report is information presented in a neat and understandable format containing both details and summary information. No single SELECT clause does both – GROUP BY gives summaries but not details and a simple SELECT gives details but not summaries – so SQL*Plus provides commands to generate reports.
Answer : BREAK specifies the column(s) on which the selected data is grouped (broken) and what to do when a break occurs. Actions: SKIP n (print n empty lines), SKIP PAGE (page break), DUPLICATE (print the break column value in every row) and NODUPLICATE (print blanks for duplicate values). BREAK alone displays the current settings.
Answer : BREAK detects a change in the value of a column, so rows must be ordered on the break columns (e.g. ORDER BY course, batch). When multiple columns are given, SQL*Plus issues breaks from the rightmost column to the leftmost – in BREAK ON report ON course SKIP PAGE ON batch SKIP 2 it breaks on batch, then course, then at the end of the report.
Answer : COMPUTE computes and prints summary information. Its functions are AVG, COUNT, MAXIMUM, MINIMUM, NUMBER (count of rows), STD (standard deviation), SUM and VARIANCE. For example, COMPUTE SUM OF amount ON batch course report prints the sum of AMOUNT at the end of each batch, each course and the report.
Answer : Every COMPUTE requires a corresponding BREAK. COMPUTE displays summary information only when there is a break on the given level; e.g. COMPUTE SUM OF amount ON bcode REPORT needs BREAK ON bcode ... ON REPORT.
Answer : LABEL specifies the text displayed as the label of the computed value, e.g. COMPUTE SUM LABEL 'Grand Total' OF amount ON REPORT. The label can be up to 500 characters but is truncated to the size of the first column in the SELECT.
Answer : The text of the column heading (HEADING), alignment of the heading (JUSTIFY LEFT/CENTER/RIGHT), the format of the column (FORMAT), wrapping of text (WRAPPED, WORD_WRAPPED, TRUNCATED) and the text substituted for null values (NULL).
Answer : Character columns use the format An, where n is the display width (longer values are wrapped, word-wrapped or truncated). DATE columns must be formatted with the TO_CHAR function in the query. Numeric columns use format elements such as 9, 0, $, B, MI, PR, comma, period, V and EEEE, e.g. FORMAT 99,999.00.
Answer : The format 99,999.00 displays 12345 as 12,345.00. In HEADING, the separator | splits the heading over multiple lines, e.g. COLUMN dj HEADING 'Date of|Joining' prints "Date of" on the first line and "Joining" on the second.
Answer : TTITLE prints text at the top of each printed page and BTITLE at the bottom. Options include BOLD, COL n, CENTER, LEFT, RIGHT and SKIP n. For example, TTITLE CENTER 'Sales Report' prints the title centred at the top of each page; TTITLE OFF turns the top title off.
Answer : SQL.PNO returns the current page number, SQL.USER the current username and SQL.RELEASE the current Oracle release number – e.g. TTITLE LEFT 'Payments Report' RIGHT SQL.PNO.
Answer : SPOOL stores the output displayed on the screen into an operating system file, which can also be printed. SPOOL filename starts spooling into the file, SPOOL OFF stops spooling, and SPOOL OUT stops spooling and sends the file to the default printer.
Answer : CLEAR resets the specified option: BREAKS (break settings), COMPUTES (compute settings), SCREEN (content of the SQL*Plus window), COLUMNS (column settings) and BUFFER (text in the current buffer).
Answer : AUTOCOMMIT ON commits changes made by each SQL command automatically; FEEDBACK displays the number of rows selected when it is at least the specified number; LINESIZE sets the number of characters per line and PAGESIZE the number of lines per page (together the dimension of the report page); SERVEROUTPUT ON enables display of output from the DBMS_OUTPUT package. They are changed with SET.
Answer : SHOW displays the current values of system variables and other options: e.g. SHOW USER (name of the current user), SHOW PAGESIZE, SHOW ERRORS (compilation errors of procedures and functions), SHOW RELEASE, SHOW TTITLE and SHOW ALL.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). SQL*Plus User’s Guide and Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/