Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

6.3 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
<table> 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
<div> 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 '<title>Project Hours and Dollars Report</title> -
<link href="/image/library/english/10067_ex6-7.css" rel="stylesheet" type="text/css"/>' -
BODY " -
TABLE 'class="detail"' -
ENTMAP OFF -
SPOOL ON
--Format the columns
COLUMN employee_name HEADING "<p class=left>Employee Name</p>" FORMAT A40
COLUMN project_name HEADING "<p class=left>Project Name</p>" FORMAT A40
COLUMN hours_logged HEADING "<p class=right>Hours</p>" FORMAT 9,999
COLUMN dollars_charged HEADING "<p class=right>Dollars Charged</p>" -
FORMAT $999,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_dollarsl
PROMPT <div class="top"> -
<h1>The Fictional Company</h1> -
<h2>Project Hours and Dollars Report</h2> -
</div> -
<div class="bottom">
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 </div>
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;}

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:

PROMPT <div class="top"> -
<h1>The Fictional Company</h1> -
<h2>Project Hours and Dollars Report</h2> -
</div> -
<div class="bottom">

I use one PROMPT command because the output from each PROMPT is
followed by a <br> tag. One unwanted
<br> is more than sufficient. The markup
here writes out a header (h1) and subheader
(h2), wraps those in a
<div> classified as
"top," and begins a new
<div> for the report detail. The detail
<div> 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 <div>.
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 <div> 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
<br> and <p> 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 <div> tags,
which gives better control over the look and feel of the resulting
web page.