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

Chapter 5. Generating Reports with SQL*Plus

SQL*Plus is an effective reporting tool. You can count on having SQL*Plus available in any Oracle environment, and it gets the job done well with a minimum of fuss. SQL*Plus was originally a plain-text reporting tool, but HTML support was added in Oracle8i Database Release 2. This HTML support makes SQL*Plus a handy tool for generating web reports for corporate Intranets, and I describe that support next in Chapter 6.

Tip

iSQL*Plus is good for ad hoc query execution but not so good for polished report generation because you can't spool iSQL*Plus output to a file for later printing.

Following a Methodology

Designing a report is a development project, albeit a small one. As with any development project, it helps to have a methodology, or sequence of steps, to follow that will lead you successfully through the process of designing a report with a minimum of fuss and backtracking. For example, when designing a report, it makes no sense to worry about formatting the data until you have first decided what data to include.

This chapter presents a micro-methodology for developing reports with SQL*Plus that has worked well for me. It divides the design process for a simple report into the following steps:

1. Formulate the query.

2. Format the columns.

3. Add page headers and footers.

4. Format the page.

5. Print it.

Dividing the design process into discrete, logical steps reduces the amount and the complexity of the information you must keep in your head at any given ...