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

SET ECHO OFF SET PAGESIZE 50000 SET MARKUP - HTML ON - HEAD '

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 "

Hours

" 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

SPOOL OFF END

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;} div.top {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

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

PROMPT

-

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 div.top 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

and

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.