One use for the venerable SQL*Plus is to generate simple, character-based reports. You may have used it yourself for that purpose. But did you know that SQL*Plus can also generate HTML?
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.
In this article, I'm going to walk you through my process for generating an HTML report from the results of this query:
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;
The Basic Approach
When generating an HTML report using SQL*Plus, you must decide between the following two options:
- 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.
Setting the Page Size
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.
When it comes to HTML reports, I tend to think in terms of a report being a single, HTML page. It may be a long page, but it's one page. To ensure that I get only one set of column headings at the top of that one page, I've been using the following setting:
SET PAGESIZE 50000
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.
Specifying HTML Output
Use the SET MARKUP command to request HTML output from SQL*Plus. For the example in this article, I use the following command and options:
SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF
The effects of this particular command are as follows:
SET MARKUP HTML ON—Requests output in HTML form. Detail rows, and column headings, will be written to an HTML table.
TABLE “class=detail cellspacing=0”—Specifies content to be written into the HTML <table> tag, which in this case will be written as:
<table class=detail cellspacing=0>
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.
My script now consists of two lines:
SET PAGESIZE 50000
SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF
Creating Column Headings
You probably know that you use SQL*Plus’ COLUMN command to define the text to use in column headings. For example:
COLUMN employee_name HEADING "Employee Name"
One problem you'll run into is that, in HTML, such headings are written into a table cell, and by default the contents of such cells are centered. Thus, the default behavior is for all headings to be centered over their respective columns. Such centering doesn't always look so good. I prefer my text column labels to be aligned left. To that end, I use the following COLUMN command for employee_name:
COLUMN employee_name HEADING "<p class=left>Employee Name</p>" FORMAT A40
This command adds the following two elements:
<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.
Following is a new version of my script, showing the four COLUMN commands for my report:
SET PAGESIZE 50000
SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF
--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</p>" -
FORMAT $999,999.99
Notice the class names I give the different headings. I use class=left for text columns, and class=right for numeric columns. In my CSS stylesheet for this report, I define the following two styles:
p.left {text-align: left;} p.right {text-align: right;}
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.
Spooling the Output
All that's left is execute the query and spool the output. There's nothing new about doing those things. Following is the complete, report script:
SET PAGESIZE 50000
SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF
--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</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 middle.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
What Do You Get?
The resulting spool file will begin with a blank paragraph, I don't know why. Next, SQL*Plus will begin a table for the column headings and report detail. Notice the contents of the <table> tag reflect what I specified in the SET MARKUP command's TABLE parameter:
<table class=detail cellspacing=0>
The table will begin with a row of column headings, which are marked up using HTML's <th> tags. You can see in these headings, the <p> tags from my COLUMN commands:
<tr>
<th scope="col">
<p class=left>Employee Name</p>
</th>
<th scope="col">
<p class=left>Project Name</p>
</th>
<th scope="col">
<p class=right>Hours</p>
</th>
<th scope="col">
<p class=right>Dollars</p>
</th>
</tr>
Next will be the rows of data, written into the same HTML table, but using <td> tags. Here's the first row:
<tr>
<td>
Marusia Churai
</td>
<td>
Corporate Web Site
</td>
<td align="right">
20
</td>
<td align="right">
$3,380.00
</td>
</tr>
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.
Formatting the Table
My stylesheet contains the following styles to format the query results in the table:
table.detail {width: 100%;}
table.detail td {padding-left: 5px; padding-right: 5px;
background: #eeeeee; color: black;}
table.detail th {padding-left: 5px; padding-right: 5px;
text-decoration: underline;}
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?
To make it easy to embed the table generated by SQL*Plus into a full-blown web page, I created the following two files:
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.
Generating a complete HTML page then, is as simple as concatenating all three files together:
cat top.html middle.html bottom.html > report.html
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.
Published on Jul 1, 2004