Oracle SQL*Plus: The Definitive Guide

Advanced Report Formatting

You can add page breaks and line breaks to your reports with the BREAK command. BREAK is also commonly used to suppress repeating values in report columns. Take a look at the following script, which generates a detailed listing of all time charged to each project by each employee:

—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 77 —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, E.EMPLOYEE_NAME, P.PROJECT_ID, P.PROJECT_NAME, TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date, PH.HOURS_LOGGED, PH.DOLLARS_CHARGED ...