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

6.2 Generating the Entire Page

Having SQL*Plus generate the entire HTML page is possible. SQL*Plus
generates a fairly decent-looking page by default, at least in Oracle
Database 10 g . The one problem
you'll likely encounter is that the kinds of page
headings you may be used to generating for plain-text reports do not
always translate well into headings for HTML reports.

6.2.1 Using SQL*Plus's Default Formatting

To generate a report as a complete HTML page using
SQL*Plus's default HTML formatting, add the command
SET MARKUP HTML ON SPOOL ON near the beginning of any report script.
Following is the beginning of Example 5-5 (in the
file ex5-5.sql ), with the SET MARKUP command
added as the second line:

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 61
--Setup page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "============================================================="
. . .

The SPOOL ON option to SET MARKUP causes SQL*Plus to write
<html>, <head>, and
<body> tags before writing data from the
query to the spool file, and to close those tags before closing the
file. Thus, the result from running the script is a complete web page
that you can load into a browser. SQL*Plus will write a series of
CSS-style definitions into the HTML heading between the
<head> and </head>
tags. The result, assuming you began with Example 5-5, is shown in Figure 6-2.

Figure 6-2. Page headers in HTML tables don't work well

The page heading in Figure 6-2 looks ugly,
doesn't it? To understand why that is, you need to
understand a bit about how SQL*Plus writes HTML page headings. Such
headings are written to a table separate from the table used for data
from the SELECT statement. This table has three columns: one for the
TTITLE content following the LEFT keyword; another for content
following the CENTER keyword; and the third column for content
following the RIGHT keyword.

This use of a table for page headings doesn't work
as well as you might think. That long, horizontal line of equal sign
(=) characters that you see in the figure is specified to be LEFT in
the TTITLE command. In a plain-text report, the line extends out
under the CENTER and RIGHT content. In an HTML report, however, the
line can't undercut the other content because it is
restricted to the leftmost column in the heading table. All those
characters must display, though, and the line ends up shoving the
CENTER and RIGHT content way off to the extreme right-hand side of
your browser window.

When generating reports for HTML, it's best to stick
with simple, uncomplicated page headings. In fact, I recommend
keeping everything LEFT. Use CSS to center any heading lines that you
wish centered. Don't mix alignments on the same
heading line. For example, avoid using LEFT and CENTER on the same
line. If you must mix two alignments, keep the different parts of
your heading text short, so text from one HTML table column
doesn't push other columns out of their proper
alignment.

Take care to follow any SKIP directive in a TTITLE immediately with
an alignment directive such as LEFT. If you fail to do that, SQL*Plus
seems to lose track of which cell subsequent text should fall into,
and that text will be lost.

Avoid SKIPping more than one line in a TTITLE. When you skip multiple
lines, SQL*Plus inserts blank rows into the table. This is a poor way
to generate vertical space in your output. Use CSS instead.

6.2.2 Taking Control of the Page Format

You don't have to rely on
SQL*Plus's default styles to format your HTML
reports. You hold the reins of power and can take almost complete
control. The script in Example 6-4 generates a
complete web page. Example 6-5 shows that page as
rendered using the CSS styles in Example 6-5.
There's a lot to explain in this script and in the
accompanying CSS stylesheet. I'll begin by
explaining the
SET MARKUP command:

SET MARKUP -
Begins the command.

HTML ON -
Enables HTML output.

HEAD '<title>Project Hours and Dollars Report</title> - <link href="/image/library/english/10067_/image/library/english/10067_ex6-5.css" rel="stylesheet" type="text/css"/> ' -
Specifies content to be written between
<head> and </head>.
This content replaces the default set of CSS styles that SQL*Plus
otherwise writes. I've wrapped this content within
single quotes, so I can write double quotes into the HTML file. The
<title> tag specifies a page title that most
browsers will display on their titlebar. The
<link> tag causes the browser displaying the
HTML page to format the page using the CSS styles defined in
/image/library/english/10067_/image/library/english/10067_ex6-5.css .

I prefer external stylesheets. However, you can take all the styles
from Example 6-5 and add them to the HEAD parameter
to have them written inline in the HTML document's
header.

BODY " -
Eliminates default attributes that SQL*Plus otherwise writes into the
<body> tag. Use CSS to format your HTML
body.

TABLE 'class="detail" ' -
Gives a class name to the HTML table created by SQL*Plus to hold the
rows of data in the report. This eliminates the default attributes
that SQL*Plus would otherwise write.

ENTMAP OFF -
Prevents SQL*Plus from translating < and
> characters in the script's
output stream to the HTML named character entities
< and >. My
approach here may be somewhat controversial, but it allows you to
place HTML tags into the page title and column headings, and to good
effect as you'll soon see.

SPOOL ON
Causes SQL*Plus to write <html>,
<head>, and <body>
tags before writing data from a query to a spool file and to close
those tags before closing the file.

I realize that all this advice is a lot to absorb. The key here is to
realize that I've specified a link to an external
stylesheet, and that I've enabled the script to
write HTML tags directly to the spool file. If you look at the
stylesheet in Example 6-5, you'll
see that the body margin is zero and that
I've specified the comic sans
ms font (not a business-like font, but I like it). The
zero margin eliminates any white border that would otherwise surround
my content.

Next, take a look at the page heading as specified by TTITLE:

TTITLE LEFT "<h1>The Fictional Company</h1>" SKIP 1 -
LEFT "<h2>Project Hours and Dollars Report</h2>" Only one "page" should be in this
report because PAGESIZE is set to 50000 and I know
there are fewer than 50,000 rows to be returned by the
report's query. The following two lines will be
written to the top of the HTML page, prior to the table containing
the report data:

<h1>The Fictional Company</h1>
<h2>Project Hours and Dollars Report</h2>

This is elementary HTML markup: a top-level heading followed by one
subheading. By being able to place HTML tags into a page heading, you
gain the ability to format that heading to use CSS styles. The
stylesheet in Example 6-5 specifies a zero top
margin for h1 and h2 to keep
them close together.

In an adventurous mood? Remove the second LEFT directive from the
TTITLE command and execute the script. In Oracle Database
10 g Release 1 at least, the resulting report
will be missing the h2 subhead. Always begin each
heading line in a TTITLE with an alignment directive.

To format column headings, I place HTML markup into the HEADING text
in my COLUMN commands:

COLUMN employee_name HEADING "<p class=left>Employee Name</p>" FORMAT A40 All I do here is to wrap each heading in a paragraph tag
(<p>) and assign it a class. In this case,
my classes are left and right,
and I use those styles to align text headings to the left and numeric
headings to the right. Otherwise, all headings end up centered over
their columns, which doesn't always look good.

When you place HTML markup in text column headings, take care to
format each column wide enough to accommodate its markup. I specified
FORMAT A40 for employee_name to
allow for up to 40 characters in the <p>
tag. Were I to specify FORMAT A9, my heading text
would be truncated to nine characters, with the result that the
incomplete tag <p class= would be written to my
HTML file. When generating HTML reports, don't worry
about using FORMAT to control the width of a column. Instead, use
FORMAT to ensure enough width for your entire heading, including any
HTML markup that you add.

I have not experienced truncation problems with headings of numeric
columns. Thus, I can use FORMAT 9,999 for
hours_logged without fear that my heading markup,
which is much longer than just five characters, will be truncated.

The script in Example 6-4 writes two tables into the
HTML file, one for the headings and the other for the data. Example 6-5 specifies the following two styles for these
tables:

table {background: black; color: white; width: 100%;}
table.detail {background: #eeeeee; color: black}

Example 6-4. SQL*Plus script to generate a complete web page

SET ECHO OFF
SET PAGESIZE 50000
SET MARKUP -
HTML ON -
HEAD '<title>Project Hours and Dollars Report</title> -
<link href="/image/library/english/10067_/image/library/english/10067_ex6-5.css" rel="stylesheet" type="text/css"/>' -
BODY " -
TABLE 'class="detail"' -
ENTMAP OFF -
SPOOL ON
--Set-up page headings and footings
TTITLE LEFT "<h1>The Fictional Company</h1>" SKIP 1 -
LEFT "<h2>Project Hours and Dollars Report</h2>"
--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|Charged</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 proj_hours_dollarsl
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

Example 6-5. CSS styles to format the output from Example 6-4

body {margin: 0; font-family: comic sans ms;
background: black;}
table {background: black; color: white; width: 100%;}
table.detail {background: #eeeeee; color: black}
p.left {text-align: left}
p.right {text-align: right}
th {padding-left: 5px; padding-right: 5px; text-decoration: underline}
td {padding-left: 5px; padding-right: 5px;}
h1 {margin-bottom: 0;}
h2 {margin-top: 0} There is no way to specify a class for the heading table, so I
control that table's format through the
table style, which sets the background and margin
such that the top of the page is white text on a black background
that fills the width of the browser window. There is only one other
table in the document, the table that holds the data, and for that
table I can specify a class, which gives me a
way to distinguish between the two tables. The
table.detail style inherits the 100% width from
table, and further specifies that the detail area
of the page is to be black on a light-gray background.

And there you have it: a credible-looking web page in Figure 6-3 that is generated entirely via SQL*Plus.

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

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی