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

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

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

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, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net