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:
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
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.
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
|Marusia Churai||Corporate Web Site||20||,380.00|
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
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick