Having SQL*Plus generate the entire HTML page is possible. SQL*Plus generates a fairly decent-looking page by default, at least in Oracle Database 10 g . The one problem you'll likely encounter is that the kinds of page headings you may be used to generating for plain-text reports do not always translate well into headings for HTML reports .

6.2.1 Using SQL*Plus's Default Formatting

To generate a report as a complete HTML page using SQL*Plus's default HTML formatting, add the command SET MARKUP HTML ON SPOOL ON near the beginning of any report script. Following is the beginning of Example 5-5 (in the file ex5-5.sql ), with the SET MARKUP command added as the second line:

SET ECHO OFF SET MARKUP HTML ON SPOOL ON --Setup pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equal signs used --for the ruling lines in the headers and footers. SET LINESIZE 61 --Setup page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "=============================================================" . . .

The SPOOL ON option to SET MARKUP causes SQL*Plus to write

,

, and

tags before writing data from the query to the spool file, and to close those tags before closing the file. Thus, the result from running the script is a complete web page that you can load into a browser. SQL*Plus will write a series of CSS-style definitions into the HTML heading between the and tags. The result, assuming you began with Example 5-5, is shown in Figure 6-2.

Figure 6-2. Page headers in HTML tables don't work well

The page heading in Figure 6-2 looks ugly, doesn't it? To understand why that is, you need to understand a bit about how SQL*Plus writes HTML page headings. Such headings are written to a table separate from the table used for data from the SELECT statement. This table has three columns : one for the TTITLE content following the LEFT keyword; another for content following the CENTER keyword; and the third column for content following the RIGHT keyword.

This use of a table for page headings doesn't work as well as you might think. That long, horizontal line of equal sign (=) characters that you see in the figure is specified to be LEFT in the TTITLE command. In a plain-text report, the line extends out under the CENTER and RIGHT content. In an HTML report, however, the line can't undercut the other content because it is restricted to the leftmost column in the heading table. All those characters must display, though, and the line ends up shoving the CENTER and RIGHT content way off to the extreme right-hand side of your browser window.

When generating reports for HTML, it's best to stick with simple, uncomplicated page headings. In fact, I recommend keeping everything LEFT. Use CSS to center any heading lines that you wish centered. Don't mix alignments on the same heading line. For example, avoid using LEFT and CENTER on the same line. If you must mix two alignments, keep the different parts of your heading text short, so text from one HTML table column doesn't push other columns out of their proper alignment.

Avoid SKIPping more than one line in a TTITLE. When you skip multiple lines, SQL*Plus inserts blank rows into the table. This is a poor way to generate vertical space in your output. Use CSS instead.

6.2.2 Taking Control of the Page Format

You don't have to rely on SQL*Plus's default styles to format your HTML reports. You hold the reins of power and can take almost complete control. The script in Example 6-4 generates a complete web page. Example 6-5 shows that page as rendered using the CSS styles in Example 6-5. There's a lot to explain in this script and in the accompanying CSS stylesheet. I'll begin by explaining the SET MARKUP command:

Project Hours and Dollars Report

SET MARKUP -

Begins the command.

HTML ON -

Enables HTML output.

HEAD ' -

' -

Specifies content to be written between

and . This content replaces the default set of CSS styles that SQL*Plus otherwise writes. I've wrapped this content within single quotes, so I can write double quotes into the HTML file. The tag specifies a page title that most browsers will display on their titlebar. The tag causes the browser displaying the HTML page to format the page using the CSS styles defined in ex6-5.css .

BODY "" -

Eliminates default attributes that SQL*Plus otherwise writes into the

tag. Use CSS to format your HTML body.

TABLE 'class="detail" ' -

Gives a class name to the HTML table created by SQL*Plus to hold the rows of data in the report. This eliminates the default attributes that SQL*Plus would otherwise write.

ENTMAP OFF -

Prevents SQL*Plus from translating < and > characters in the script's output stream to the HTML named character entities < and > . My approach here may be somewhat controversial , but it allows you to place HTML tags into the page title and column headings, and to good effect as you'll soon see.

SPOOL ON

Causes SQL*Plus to write

, , and tags before writing data from a query to a spool file and to close those tags before closing the file.

I realize that all this advice is a lot to absorb . The key here is to realize that I've specified a link to an external stylesheet, and that I've enabled the script to write HTML tags directly to the spool file. If you look at the stylesheet in Example 6-5, you'll see that the body margin is zero and that I've specified the comic sans ms font (not a business-like font, but I like it). The zero margin eliminates any white border that would otherwise surround my content.

Next, take a look at the page heading as specified by TTITLE:

TTITLE LEFT "

The Fictional Company

" SKIP 1 - LEFT "

Project Hours and Dollars Report

"

Only one "page" should be in this report because PAGESIZE is set to 50000 and I know there are fewer than 50,000 rows to be returned by the report's query. The following two lines will be written to the top of the HTML page, prior to the table containing the report data:

The Fictional Company

Project Hours and Dollars Report

This is elementary HTML markup: a top-level heading followed by one subheading . By being able to place HTML tags into a page heading, you gain the ability to format that heading to use CSS styles. The stylesheet in Example 6-5 specifies a zero top margin for h1 and h2 to keep them close together.

To format column headings, I place HTML markup into the HEADING text in my COLUMN commands:

COLUMN employee_name HEADING "

Employee Name

" FORMAT A40

All I do here is to wrap each heading in a paragraph tag (

) and assign it a class. In this case, my classes are left and right , and I use those styles to align text headings to the left and numeric headings to the right. Otherwise, all headings end up centered over their columns, which doesn't always look good.

When you place HTML markup in text column headings, take care to format each column wide enough to accommodate its markup. I specified FORMAT A40 for employee_name to allow for up to 40 characters in the

tag. Were I to specify FORMAT A9 , my heading text would be truncated to nine characters, with the result that the incomplete tag

would be written to my HTML file. When generating HTML reports, don't worry about using FORMAT to control the width of a column. Instead, use FORMAT to ensure enough width for your entire heading, including any HTML markup that you add.

The script in Example 6-4 writes two tables into the HTML file, one for the headings and the other for the data. Example 6-5 specifies the following two styles for these tables:

table {background: black; color: white; width: 100%;} table.detail {background: #eeeeee; color: black}

Example 6-4. SQL*Plus script to generate a complete web page

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

Project Hours and Dollars Report -' - BODY "" - TABLE 'class="detail"' - ENTMAP OFF - SPOOL ON --Set-up page headings and footings TTITLE LEFT "

The Fictional Company

" SKIP 1 - LEFT "

Project Hours and Dollars Report

" --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 "

DollarsCharged

" - 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 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; SPOOL OFF EXIT

Example 6-5. CSS styles to format the output from Example 6-4

body {margin: 0; font-family: comic sans ms; background: black;} table {background: black; color: white; width: 100%;} table.detail {background: #eeeeee; color: black} 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;} h1 {margin-bottom: 0;} h2 {margin-top: 0}

There is no way to specify a class for the heading table, so I control that table's format through the table style, which sets the background and margin such that the top of the page is white text on a black background that fills the width of the browser window. There is only one other table in the document, the table that holds the data, and for that table I can specify a class, which gives me a way to distinguish between the two tables. The table.detail style inherits the 100% width from table , and further specifies that the detail area of the page is to be black on a light-gray background.

And there you have it: a credible-looking web page in Figure 6-3 that is generated entirely via SQL*Plus.

Figure 6-3. Output from Example 6-4 rendered using styles from Example 6-5