Another Approach to Headers

That SQL*Plus writes all page headers into an HTML table bothers me. In the previous section's example, that table represents unwanted markup. The associated

tag also includes some unwanted attribute settings. One of those, width="90% ", can cause you some grief depending on what it is that you want to accomplish in the way of formatting. An alternative, helpful approach is to dispense with using TTITLE altogether and use the PROMPT command to write page headings directly into the HTML output stream.

Example 6-6 shows a new version of the Project Hours and Dollars Report that uses PROMPT to write page headings to the HTML stream. Figure 6-4 shows the result as rendered using the stylesheet in Example 6-7. This example introduces the concept of using

tags to format different sections of your HTML page.

Example 6-6. A script that uses PROMPT to write HTML page headings






Project Hours and Dollars Report


' - BODY "" - TABLE 'class="detail"' - ENTMAP OFF - SPOOL ON --Format the columns COLUMN employee_name HEADING "

Employee Name

" FORMAT A40 COLUMN project_name HEADING "

Project Name

" FORMAT A40 COLUMN hours_logged HEADING "


" FORMAT 9,999 COLUMN dollars_charged HEADING "

Dollars Charged

" - FORMAT 9,999.99 --Turn off feedback and set TERMOUT off to prevent the --report being scrolled to the screen. SET FEEDBACK OFF SET TERMOUT OFF --Execute the query to generate the report. SPOOL proj_hours_dollars.html PROMPT


The Fictional Company


Project Hours and Dollars Report



SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id GROUP BY e.employee_id, e.employee_name, p.project_id, p.project_name; PROMPT


Example 6-7. CSS styles to format the output from Example 6-6

body {margin: 0; font-family: comic sans ms; background: black;}

div {margin-left: 5px; margin-top: 5px; margin-right: 5px;} {background: white; color: black; 

 padding-bottom: 5px; text-align: center;}

div.bottom {background: #eeeeee; color: black;}

table.detail {position: relative; top: -1.5em;}

p.left {text-align: left;}

p.right {text-align: right;}

th {padding-left: 5px; padding-right: 5px; text-decoration: underline;}

td {padding-left: 5px; padding-right: 5px; 

 padding-top: 0; padding-bottom: 0;}

h1 {margin-bottom: 0;}

h2 {margin-top: 0; margin-bottom: 0;}


Figure 6-4. Output from Example 6-6 rendered using styles from Example 6-7


Getting consistently sized black borders around and between the page heading and report content is something I was unable to accomplish when those page headings were embedded within an HTML table. Your mileage may vary, but I found that the table and its 90% width got in the way of what I wanted to do.


Example 6-6 uses the following PROMPT command to write a good bit of markup to the HTML file:



The Fictional Company


Project Hours and Dollars Report

- -

I use one PROMPT command because the output from each PROMPT is followed by a
tag. One unwanted
is more than sufficient. The markup here writes out a header ( h1 ) and subheader ( h2 ), wraps those in a

classified as "top," and begins a new for the report detail. The detail is closed by another PROMPT command that immediately follows the SELECT statement.

The stylesheet in Example 6-7 centers the h1 and h2 content and makes it black text on a white background. All of this is accomplished through the style. A five-pixel margin is set for the left, top, and right side of each

. The black page background shows through that margin, giving the black borders that you see around and between the two sections of the page. The containing the report detail is set to display black text on a light-gray background. These settings give the basic form to the page.

One unusual aspect of table formatting in this example is that the table.detail style specifies a negative margin, placing the top of the report detail higher on the page than it would otherwise belong. The intent is to compensate for the unwanted

tags that SQL*Plus writes just in front of the detail table, and the value of - 1.5em is one that I found through experimentation. I think the results are pleasing, but you can omit that negative margin setting and live with a bit of extra blank space in front of the detail.

You might look at my approach in this section, that of using PROMPT to write out arbitrary markup, as a bit of a hack. And it is a bit of a hack. However, it's a hack that offends my sense of elegance less than the idea of placing my page headings into a table, and it enables the use of

tags, which gives better control over the look and feel of the resulting web page.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: