Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

6.1 Getting the Data into an HTML Table

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 "Dollars|Charged" 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 middlel
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
middlel .
The file begins with a stray <p> 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 <p> tag is followed by an
HTML table containing report data. Column headings are wrapped in
<th> tags in the first row of that table.
The middlel file then, begins as follows:

<p>
<table>
<tr>
<th scope="col">
Employee Name
</th>
<th scope="col">
Project Name
</th>
<th scope="col">
Hours
</th>
<th scope="col">
Dollars
<br>
Charged
</th>
</tr>
...

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

...
<tr>
<td>
Marusia Churai
</td>
<td>
Corporate Web Site
</td>
<td align="right">
20
</td>
<td align="right">
$3,380.00
</td>
</tr>
...

The middlel file is not a complete HTML
page. Examples Example 6-2 and Example 6-3 show two additional files containing HTML
markup, firstl and
lastl . By wrapping the contents of
middlel 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 firstl middlel lastl > proj_hours_dollarsl On Windows systems, use the type command:

type firstl, middlel, lastl > proj_hours_dollarsl

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

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=WINDOWS-1252">
<title>Project Hours and Dollars Report</title>
<style type="text/css">
table {border-collapse: collapse; width: 100%;}
td {border: 2 solid black;}
th {border: 2 solid black;}
</style>
<body>
<h1>Project Hours and Dollars Report</h1>

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

</body>
</html>

If you download the example scripts for this book, firstl and lastl are named ex6-2l and ex6-3l 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
firstl and lastl .