HTML support has actually been in SQL*Plus since Oracle8i Release 2, though I've pretty much ignored it over the years. Recently though, I decided to sit down, push the envelope a bit, and just see what I could do using the combination of SQL*Plus and HTML. I managed to surprise myself. You can generate some very creditable looking web reports using SQL*Plus.
SQL*Plus generates the entire HTML page.
SQL*Plus generates HTML only for the detail lines and column headings. You then wrap this detail output with HTML that you write yourself, in order to generate a complete, HTML page.
You can make either approach work, though I have a few issues with approach #1 because of the way that SQL*Plus writes TTITLE headings into HTML tables. For this article, I'm concentrating on approach #2.
One issue you bump up against when using SQL*Plus to generate HTML is the question of page size. Reports printed on paper tend to extend over many pages, and you usually want column titles printed at the top of each such page. SQL*Plus provides the PAGESIZE setting to control the number of lines per page.
50,000 lines is the largest PAGESIZE value that SQL*Plus supports. You can use SET PAGESIZE 0 to disable pagination completely, but then you don't get any column headings. By using SET PAGESIZE 50000, you pretty much ensure just one set of column headings, unless your report happens to generate more than 50,000 detail lines, in which case you're going to have a long HTML page indeed, and you might want to rethink what you're doing.
Note: You might want column headings to be repeated every so often throughtout your HTML report. That's ok. Nothing wrong with that. Simply set PAGESIZE to a value that you're comfortable with.
SET MARKUP HTML ON—Requests output in HTML form. Detail rows, and column headings, will be written to an HTML table.
The class lets me use CSS to format the detail table separately from any other tables I may have in my HTML document. I added cellspacing=0 only because I haven't found a reliable way to request that spacing via CSS.
ENTMAP OFF—Let's me embed HTML tags in my column headings. You'll see why I want to do this shortly.
<p class=left>...</p>—By wrapping my column heading in a <p> tag, and giving it a class, I can use CSS to control the heading's alignment. I can also use CSS to control other aspects of the way the heading is presented.
FORMAT A40—Headings for text columns are truncated at the column's maximum length. Here I specify a length of 40 characters, not out of concern for the data in the column, but rather to accomodate the extra, HTML markup in my heading definition.
My desire to embed HTML markup in my column headings is what drove my use of ENTMAP OFF in my SET MARKUP command.
The issue of a column's length is an interesting one. Because SQL*Plus writes column data to a table, you don't need to bother specifying a length using a COLUMN parameter such as A40. Your browser will simply resize the table in accordance with the data in the cells and the width to which you've set the browser window.
However, you do need to worry about accomodating any extra markup that you embed in your headings. Use A6 in my example above, and SQL*Plus will truncate my markup to: "<p cla", and that doesn't result in a valid tag. So in an interesting bit of turnabout, you specify column width to accomodate not the data, but its heading.
SQL*Plus takes care of left-aligning text data and right-aligning numeric data. Headings however, would be centered, but for my markup. The <p> tags that I add to the COLUMN commands work in conjunction with these two CSS styles to align column headings in accordance with their respective data types.
By the way, you don't seem to need to worry about truncating your numeric column headings. It isn't necessary to extend your numeric formats to accomodate the number of characters in their respective column headings. With text columns, you do need to worry about accomodating heading width, but not with numeric columns. I'm not entirely sure why that is, but I'm glad that it is.
--report being scrolled to the screen.
--Execute the query to generate the report.
The resulting spool file will begin with a blank paragraph, I don't know why.
At this point, you don't have an HTML page. You've only got a fragment of a page sitting in a file named middle.html.
The first, table.detail style simply sets the table width to 100% of the enclosing <div> size.
The second style formats the table detail, making the data rows appear in black text on a light-grey background.
The third style, this time for the <th> tag, but only in a table of the detail class, underlines the column headings.
Any other styles in the stylesheet that I haven't mentioned yet in this article are there to format those parts of the page not generated by SQL*Plus, and it's those I want to talk about next.
Ok, So Where's the Page?
top.html—Contains all the markup that should precede the table. In my case, this includes opening <html> and <body> tags, two opening <div> tags, and also all the <head> content, including a link to an external, CSS stylesheet.
bottom.html—Contains all the markup that should follow the table, including closing </div> tags, and also closing </body> and </html> tags.
The result, report.html, is a complete web page. You can apply the same technique to generate web reports from SQL*Plus that fit right in with the overall look-and-feel of your own site, or of any corporate intranet site.