Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Master/Detail Reports

You can add page breaks and line breaks to your reports with the BREAK command. BREAK is commonly used to suppress repeating values in report columns, which commonly occur in master/detail reports. Take a look at the script in Example 5-6, which generates a detailed listing of all the time charged to each project by each employee.

Example 5-6. Detailed listing of time charged to projects

SET ECHO OFF --Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 76 --Set up page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Detail" SKIP 1 - LEFT "========================================" - "====================================" - SKIP 2 BTITLE LEFT "========================================" - "====================================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_id HEADING "Emp ID" FORMAT 9999 COLUMN employee_name HEADING "Employee Name" FORMAT A16 WORD_WRAPPED COLUMN project_id HEADING "Proj ID" FORMAT 9999 COLUMN project_name HEADING "Project Name" FORMAT A12 WORD_WRAPPED COLUMN time_log_date HEADING "Date" FORMAT A11 COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99 --Execute the query to generate the report. SELECT e.employee_id, ...