Getting the Data into an HTML Table | Creating HTML Reports

Table of contents:

Project Hours and Dollars Report

SQL*Plus doesn't offer much in the way of formatting HTML output, and what little capability SQL*Plus does offer in this area, you're better off avoiding in favor of CSS. When writing a report as HTML, SQL*Plus places all the detail rows, and their column headings, into an HTML table. One approach to generating an HTML page with such data is to capture only that table and concatenate it with other HTML that you write yourself to generate a complete web page.

When generating HTML from SQL*Plus, keep things simple:

Pagination doesn't apply. All "pages" are written to one HTML file. Thus, you're better off thinking in terms of one, large page. To that end, set PAGESIZE to its maximum value of 50,000.
Line size is irrelevant. Each column value will be in its own table cell . Your browser, together with any CSS styles and/or table markup that you supply, will control the width of that table and its columns .
Don't worry about word-wrapping. Your browser will wrap table cell values as needed depending on how the table and its columns are sized . The COLUMN command's WORD_WRAPPED is meaningless in the context of generating HTML.

Example 6-1 shows a stripped-down version of the Project Hours and Dollars Report that you saw in Examples 5-1 through Example 5-5. This time, the report is output in HTML form. PAGESIZE is 50000 to ensure that only one set of column headings is generated.

Example 6-1. SQL*Plus script to generate an HTML table of report data

SET ECHO OFF

SET PAGESIZE 50000

SET MARKUP HTML ON TABLE ""



--Format the columns

COLUMN employee_name HEADING "Employee Name"

COLUMN project_name HEADING "Project Name"

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

Remember that these report scripts are designed to be invoked from the operating system command prompt, as in:

sqlplus


username


/


password


@ex6-1.sql

Avoid invoking them interactively from the SQL*Plus prompt, as you won't be starting with a clean slate each time with respect to the various SET, COLUMN, and TTITLE commands.

The key command in Example 6-1 is the SET MARKUP command:

SET MARKUP HTML ON TABLE ""

You can break down this command as follows :

SET MARKUP HTML ON

Causes SQL*Plus to write report output into an HTML table. All output is written in HTML, but the table is all we care about for this example.

TABLE ""

Specifies that no attributes are to be written into the opening table tag. If you don't specify otherwise , SQL*Plus writes some default attributes, such as width="90% ", but I recommend avoiding those in favor of using CSS to format your table.

Output from the script in Example 6-1 will be written to a file named middle.html . The file begins with a stray

tag, which SQL*Plus perhaps generates as a crude way of putting some space before the table. Whatever the reason for the tag, it's a bit of an annoyance when using CSS to format your output. The

tag is followed by an HTML table containing report data. Column headings are wrapped in tags in the first row of that table. The middle.html file then, begins as follows:

...

Each row of report data is written as a row in the HTML table. For example, here is the markup written to middle.html for the first data row:

...

...

The middle.html file is not a complete HTML page. Examples Example 6-2 and Example 6-3 show two additional files containing HTML markup, first.html and last.html . By wrapping the contents of middle.html within the contents of those other two files, you can produce a valid HTML page. On Linux/Unix systems, you can put all the pieces together with a cat command:

cat first.html middle.html last.html > proj_hours_dollars.html

On Windows systems, use the type command:

type first.html, middle.html, last.html > proj_hours_dollars.html

Example 6-2. The first.html file with markup to begin a page

Project Hours and Dollars Report

Example 6-3. The last.html file with markup to end a page

Employee Name	Project Name	Hours	Dollars Charged
Marusia Churai	Corporate Web Site	20	,380.00

If you download the example scripts for this book, first.html and last.html are named ex6-2.html and ex6-3.html respectively. This is so they conform to the same naming convention as all other examples.

Figure 6-1 shows the resulting page as rendered in a browser. It's not a fancy-looking page. I'm focusing on functionality in this first example and don't want to clutter it with all sorts of HTML and CSS markup. I hope you can see, however, the potential for creativity from your ability to control the content of first.html and last.html .

Figure 6-1. An HTML page with report content generated from SQL*Plus

figs/sqp2_0601.gif