8
Generating HTML Reports from SQL*Plus

This chapter explains how to generate a HTML report containing your query results. This chapter covers the following topics:

Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the HR sample schema described in Chapter 1, "SQL*Plus Overview".

Creating Reports using Command-line SQL*Plus

In addition to plain text output, the SQL*Plus command-line interface enables you to generate either a complete web page, or HTML output which can be embedded in a web page. You can use SQLPLUS -MARKUP "HTML ON" or SET MARKUP HTML ON SPOOL ON to produce complete HTML pages automatically encapsulated with <HTML> and <BODY> tags.

By default, data retrieved with MARKUP HTML ON is output in HTML, though you can optionally direct output to the HTML tag so that it displays in a web browser exactly as it appears in SQL*Plus. See the SQLPLUS -MARKUP command in the "SQL*Plus Command Summary" section and the SET MARKUP command for more information about these commands.

SQLPLUS -MARKUP "HTML ON" is useful when embedding SQL*Plus in program scripts. On starting, it outputs the HTML and BODY tags before executing any commands. All subsequent output is in HTML until SQL*Plus terminates.

The -SILENT and -RESTRICT command-line options may be effectively used with -MARKUP to suppress the display of SQL*Plus prompt and banner information, and to restrict the use of some commands.

SET MARKUP HTML ON SPOOL ON generates an HTML page for each subsequently spooled file. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits.

You can use SET MARKUP HTML ON SPOOL OFF to generate HTML output suitable for embedding in an existing web page. HTML output generated this way has no <HTML> or <BODY> tags.

Creating Reports

During a SQL*Plus session, use the SET MARKUP command interactively to write HTML to a spool file. You can view the output in a web browser.

SET MARKUP HTML ON SPOOL ON only specifies that SQL*Plus output will be HTML encoded, it does not create or begin writing to an output file. You must use the SQL*Plus SPOOL command to start generation of a spool file. This file then has HTML tags including <HTML> and </HTML>.

When creating a HTML file, it is important and convenient to specify a .html or .htm">/EM> file extension which are standard file extensions for HTML files. This allows you to easily identify the type of your output files, and also allows web browsers to identify and correctly display your HTML files. If no extension is specified, the default SQL*Plus file extension is used.

You use SPOOL OFF or EXIT to append final HTML tags to the spool file and then close it. If you enter another SPOOL filename command, the current spool file is closed as for SPOOL OFF or EXIT, and a new HTML spool file with the specified name is created.

You can use the SET MARKUP command to enable or disable HTML output as required.

Example 8-1 Creating a Report Interactively

You can create HTML output in an interactive SQL*Plus session using the SET MARKUP command. You can include an embedded style sheet, or any other valid text in the HTML <HEAD> tag. Open a SQL*Plus session and enter the following:



SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Department Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"

You use the COLUMN command to control column output. The following COLUMN commands create new heading names for the SQL query output. The first command also turns off entity mapping for the DEPARTMENT_NAME column to allow HTML hyperlinks to be correctly created in this column of the output data:



COLUMN DEPARTMENT_NAME HEADING 'DEPARTMENT' ENTMAP OFF
COLUMN CITY HEADING 'CITY'

SET MARKUP HTML ON SPOOL ON enables SQL*Plus to write HTML to a spool file. The following SPOOL command triggers the writing of the <HTML> and <BODY> tags to the named file:



SPOOL report.html

After the SPOOL command, anything entered or displayed on standard output is written to the spool file, report.html.

Enter a SQL query:

Keyboard icon
SELECT '<A HREF="http://oracle.com/'||DEPARTMENT_NAME||'.html">'||DEPARTMENT_NAME||'</A>' DEPARTMENT_NAME, CITY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
Enter the SPOOL OFF command:



SPOOL OFF

The </BODY> and </HTML> tags are appended to the spool file, report.html, before it is closed.

The output from report.sql is a file, report.html, that can be loaded into a web browser. Open report.html in your web browser. It should appear something like the following:

Text description of the illustration report.gif

In this example, the prompts and query text have not been suppressed. Depending on how you invoke a script, you can use SET ECHO OFF or command-line -SILENT options to do this.

The SQL*Plus commands in this example contain several items of usage worth noting:

The hyphen used to continue lines in long SQL*Plus commands.
The TABLE option to set table WIDTH and BORDER attributes.
The COLUMN command to set ENTMAP OFF for the DEPARTMENT_NAME column to enable the correct formation of HTML hyperlinks. This makes sure that any HTML special characters such as quotes and angle brackets are not replaced by their equivalent entities, ", &, < and >.
The use of quotes and concatenation characters in the SELECT statement to create hyperlinks by concatenating string and variable elements.

View the report.html source in your web browser, or in a text editor to see that the table cells for the Department column contain fully formed hyperlinks as shown:

<html>
<head>
<TITLE>Department Report</TITLE>  <STYLE type="text/css">  <!-- BODY 
{background: #FFFFC6} -->  </STYLE>
<meta name="generator" content="SQL*Plus 9.2.0.1.0">
</head>
<body TEXT="#FF00Ff">
SQL&gt; SELECT '&lt;A HREF=&quot;http://oracle.com/'||DEPARTMENT_
NAME||'.html&quot;&gt;'||DEPARTMENT_NAME||'&lt;/A&gt;' DEPARTMENT_NAME, CITY
<br>
  2  FROM EMP_DETAILS_VIEW
<br>
  3* WHERE SALARY&gt;12000
<br>
<p>
<table WIDTH="90%" BORDER="5">
<tr><th>DEPARTMENT</th><th>CITY</th></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Sales.html">Sales</A></td>
<td>Oxford</td></tr>
<tr><td><A HREF="http://oracle.com/Sales.html">Sales</A></td>
<td>Oxford</td></tr>
<tr><td><A HREF="http://oracle.com/Marketing.html">Marketing</A></td>
<td>Toronto</td></tr>
</table>
<p>

6 rows selected.<br>


SQL&gt; spool off
<br>
</body>
</html>

Example 8-2 Creating a Report using the SQLPLUS Command

Enter the following command at the operating system prompt:



SQLPLUS -S -M "HTML ON TABLE 'BORDER="2"'" HR/your_password@Ora9i 
@depart.sql>depart.html

where depart.sql contains:

SELECT DEPARTMENT_NAME, CITY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
EXIT

This example starts SQL*Plus with user "HR", sets HTML ON, sets a BORDER attribute for TABLE, and runs the script depart.sql. The output from depart.sql is a web page which, in this case, has been redirected to the file depart.html using the ">" operating system redirect command (it may be different on your operating system). It could be sent to a web browser if SQL*Plus was called in a web server CGI script. See "Creating Reports with CGI Scripts" for information about calling SQL*Plus from a CGI script.

Start your web browser and enter the appropriate URL to open depart.html:

Text description of the illustration dept.gif

The SQLPLUS command in this example contains three layers of nested quotes. From the inside out, these are:

"2" is a quoted HTML attribute value for BORDER.
'BORDER="2"' is the quoted text argument for the TABLE option.
"HTML ON TABLE 'BORDER="2"'" is the quoted argument for the -MARKUP option.

The nesting of quotes may be different in some operating systems or program scripting languages.

Creating Reports with CGI Scripts

The SQLPLUS -MARKUP command allows you to start a SQL*Plus session in 'markup mode', rather than using the SET MARKUP command interactively. This allows you to run a SQL*Plus session embedded inside a Common Gateway Interface (CGI) script. A file created in this way can be displayed directly in a web browser. You can call SQL*Plus using any script language which is supported by your web server, such as a UNIX shell script, a Windows batch file, Java, JavaScript or a Perl script.

You can use this embedded approach to produce web pages that use existing SQL*Plus scripts without change. It provides an easy way to provide dynamically-created, web-based reports.

Example 8-3 Creating a Report from a CGI Script

You can use a CGI script to run SQL*Plus, and so produce a web report from a SQL script. There are three main elements required:

A web page to call and pass parameters to the CGI script.
A CGI script to gather the input and run SQL*Plus.
The SQL script to be run by the SQL*Plus session.

Web Page for CGI Example

In this example, the web page is an HTML form, with fields for a username and password, a database connection string and the name of the SQL script to run.

Note:

You need to carefully consider security on your server before embedding login information in a script file, or using a CGI script to prompt for login information and pass it into the SQLPLUS command.

Consider setting initial conditions rather than assuming default values. For example, explicitly set ENTMAP ON even thought its default is ON.

Create an HTML file containing the following script and save it as plus.html.



<html>
<head><title>SQL*Plus CGI Report</title></head>
<body bgcolor="#ffffff">

<h1>SQL*Plus CGI Report</h1>

<!-- Change the URL here.  On Windows NT you may need to use
http://machine_name.domain:port/cgi-bin/perl?plus.pl if your web server is 
not configured to identify the script as a Perl program -->

<form method=post action="http://machine_name.domain:port/cgi-bin/plus.pl">
<table border=0 summary="">

<tr>
  <td>Username:</td>
  <td><input type="text" name="username" size="10" align="left"></td>
</tr>
<tr>
  <td>Password:</td>
  <td><input type="password" name="password" size="10" align="left"></td>
</tr>
<tr>
  <td>Connect string: </td>
  <td><input type="text" name="db" size="10" align="left"></td>
</tr>
<tr>
  <td>Report to run: </td>
  <td><input type="text" name="script" value="employee.sql" size=40></td>
</tr>
<tr>
  <td><input type="submit" value="Run it">&nbsp;<input type="reset" 
value="Reset Form"></td>
  <td>&nbsp;</td>
</tr>
</table>
</form>
</body>
</html>

Perl Script for CGI Example

In this example, the CGI script is a Perl script, but it could be a shell script, a Java class or any other language supported by your web server. Create the following Perl CGI script and save it as plus.pl in the cgi-bin directory of your web server:



#! /usr/local/bin/perl -w
# Copyright (c) Oracle Corporation 1999, 2002. All Rights Reserved.
# NAME
#   plus.pl
# DESCRIPTION
#   This is a demonstration program to run a SQL*Plus report via CGI.
#   It is provided as is with no warranty implied or expressed.
#   Users are strongly recommended to understand error handling and
#   security issues before implementing CGI scripts.
#
# NOTES
#   This demostration requires that SQL*Plus 9.2.0 (or later) is
#   installed on your web server, and the web server is configured to
#   run CGI programs. The database may be on another machine, but
#   must have Oracle Net access configured.
#
#   This demonstration consists of three files:
#      plus.html      - Sample HTML form that you open in your web
#                       browser. It calls plus.pl to run employee.sql
#      plus.pl        - Sample CGI program to run SQL*Plus
#      employee.sql   - Sample SQL script to generate a report from 
#                       the HR sample schema.
#   These scripts need to be customized for your site.
#
# INSTALLATION INSTRUCTIONS:
#      1. Put plus.pl (this file) in the cgi-bin directory of your
#      web server and edit the environment variable section at the top
#      of the file.  Make the program executable, for example on UNIX,
#          chmod +x plus.pl
#      You may need to customize the top line of this script to point
#      to the Perl installation on your machine, and in the syntax 
#      required for your operating system.
#      2. Put employee.sql in the cgi-bin directory too.
#      3. Put plus.html in a directory you can access from the web.
#      Edit plus.html to change the form URL to that of your web server.
#      4. Open plus.html in your browser and enter the fields. As
#      this demonstation uses the view, EMP_DETAILS_VIEW, from the HR
#      sample schema, enter the associated username, HR, and password.
#      If your database is not the default, or is on another machine, 
#      enter a valid network alias, or full connection identifier in 
#      the Connect Identifier field, otherwise leave it blank. If 
#      employee.sql is in your cgi-bin directory,  you will probably 
#      not need to specify a path, otherwise specify a machine path 
#      and filename.

$debug = 0;   # Set this to 1 to see the form fields values entered.
# !!! Customize these environment variables and the executable name.
# !!! On Windows use "$ENV{'ORACLE_HOME'}\\bin\\sqlplus" for the executable.

# Set up the SQL*Plus environment
$ENV{'ORACLE_SID'} = "Ora9i";         # Your SID goes here
$ENV{'ORACLE_HOME'} = "/oracle";  # Your Oracle Home directory goes here
# $ENV{'TNS_ADMIN'} = "/var/opt/oracle";
$plusexe = "$ENV{'ORACLE_HOME'}/bin/sqlplus";

# Extract parameters and values from data entered through web browser
$i=<>;
@in = split(/[&;]/,$i);
foreach $i (0 .. $#in)
{ ($key,$val) = split(/=/,$in[$i],2);
# Change encoding to machine character set
   $key =~ s/%([A-Fa-f0-9]{2})/pack("c",hex($1))/ge;
   $val =~ s/%([A-Fa-f0-9]{2})/pack("c",hex($1))/ge;
# Store the value
   $in{"$key"} = $val;
}

# Construct the connection string from values passed in
$connstr = $in{'username'}."/".$in{'password'};
$connstr = $connstr."@".$in{'db'} if ($in{'db'});

# Construct the SQL script to be run
$script = "@".$in{'script'};

# Force output to be flushed
$| = 1;

# Print mime type
print "Content-Type: text/html\n\n";

if ($debug)
{ print "<html><body>\n";
  print "$plusexe:$connstr:$script:\n";
  print "</body></html>\n";
  exit;
}

# Call SQL*Plus with the parameters entered from the web browser
system ("$plusexe -r 3 -s -m \"html on\" $connstr $script");
exit;

SQL Script for CGI Example

Create the following SQL*Plus script in a text editor and save it as employee.sql in the cgi-bin directory of your web server:



SELECT LAST_NAME, CITY, SALARY 
FROM EMP_DETAILS_VIEW;
EXIT;

Start your web browser and enter the appropriate URL to open plus.html:

Text description of the illustration plus.gif

Click Run It to execute the shell script plus.pl, which in turn starts SQL*Plus and runs the employee.sql script. The query results are displayed directly in your web browser:

Text description of the illustration employ.gif

Suppressing the Display of SQL*Plus Commands in Reports

The SQLPLUS -SILENT option is particularly useful when used in combination with -MARKUP to generate embedded SQL*Plus reports using CGI scripts or operating system scripts. It suppresses the display of SQL*Plus commands and the SQL*Plus banner. The HTML output shows only the data resulting from your SQL query.

You can also use SET ECHO OFF to suppress the display of each command in a script that is executed with the START command.

HTML Entities

Certain characters, <, >, " and & have a predefined meaning in HTML. In the previous example, you may have noticed that the > character was replaced by > as soon as you entered the SET MARKUP HTML ON command. To enable these characters to be displayed in your web browser, HTML provides character entities to use instead.

Table 8-1 Equivalent HTML Entities

Character	HTML Entity	Meaning
<	<	Start HTML tag label
>	>	End HTML tag label
"	"	Double quote
&	&	Ampersand

The web browser displays the > character, but the actual text in the HTML encoded file is the HTML entity, >. The SET MARKUP option, ENTMAP, controls the substitution of HTML entities. ENTMAP is set ON by default. It ensures that the characters <, >, " and & are always replaced by the HTML entities representing these characters. This prevents web browsers from misinterpreting these characters when they occur in your SQL*Plus commands, or in data resulting from your query.

You can set ENTMAP at a global level with SET MARKUP HTML ENTMAP ON, or at a column level with COLUMN column_name ENTMAP ON.

Creating Reports using iSQL*Plus

You can create dynamic reports, and pass variables to scripts by sending iSQL*Plus a request to run a script from a URL. The script is uploaded using the HTTP POST protocol and must be available through HTTP, HTTPS or FTP. iSQL*Plus executes the script, using any HTML form field values as parameters, and returns the results in a new web browser window.

You are not prompted for undefined variables. You should take care that there are no variables that have not been defined in your script, or explicitly passed as parameters.

You can also include username and password information in the request. You should carefully consider the security implications of including usernames and passwords in HTML files. If you do not include a username or password, iSQL*Plus prompts you to enter login information when you run the script.

If you want to use the SET MARKUP command to change the HEAD or BODY options for a report, put the SET MARKUP command before the first command that generates output.

Example 8-4 Creating a Dynamic Report

Create and save the following script to a file called script.sql on your Oracle HTTP Server.

SET PAGESIZE 200
SELECT *
FROM EMP_DETAILS_VIEW
ORDER BY LAST_NAME, EMPLOYEE_ID
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<A HREF="http://machine_name.domain:port/isqlplus?script=http://machine_
name.domain:port/script.sql">

Run Employee Report</A>
</BODY>
</HTML>

Replace machine_name.domain with the host and domain names, and replace port with the port number of your Oracle HTTP Server. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser and click on "Run Employee Report". iSQL*Plus requests your username and password. Log in to iSQL*Plus. iSQL*Plus executes the script and displays the results in your web browser.

Example 8-5 Creating a Dynamic Report with Parameters

Create and save the following script to a file called employee_name.sql on your Oracle HTTP Server.

SET VERIFY OFF
SET PAGESIZE 200
SET FEEDBACK OFF
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee(s) with Last Name like &last_name%</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE UPPER(last_name) LIKE UPPER('&last_name%')
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE><em>i</em>SQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<H2>Query by Last Name</H2>
<FORM METHOD=get ACTION="http://machine_name.domain:port/isqlplus">
<INPUT TYPE="hidden" NAME="script" VALUE="http://machine_
name.domain:port/employee_name.sql">
Enter last name of employee: <INPUT TYPE="text" NAME="last_name" SIZE="20">
<INPUT TYPE="submit" VALUE="Run Report">
</FORM>
</BODY>
</HTML>

The name of the INPUT TYPE should be the same as either a column or substitution variable in your script, for example

<INPUT TYPE="text" NAME="last_name" SIZE="20">

maps to the substitution variable &last_name in the employee_name.sql script.

Replace machine_name.domain with the host and domain names, and port with the port number of your Oracle HTTP Server. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. iSQL*Plus executes the script and displays the results in your web browser.

Example 8-6 Creating a Dynamic Script with Parameters and Login Details

Create and save the following script to a file called employee_id.sql on your Oracle HTTP Server.

SET VERIFY OFF
SET PAGESIZE 200
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee Number &eid</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = &eid
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<H2>Query by Employee ID</H2>
<FORM METHOD=get ACTION="http://machine_name.domain:port/isqlplus">
<INPUT TYPE="hidden" NAME="userid" VALUE="hr/your_password">
<INPUT TYPE="hidden" NAME="script" VALUE="http://machine_
name.domain:port/employee_id.sql">
Enter employee identification number: <INPUT TYPE="text" NAME="eid" SIZE="10">
<INPUT TYPE="submit" VALUE="Run Report">
</FORM>
</BODY>
</HTML>

Replace machine_name.domain with the host and domain names, port with the port number of your Oracle HTTP Server, and hr/your_password with a valid userid and password. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser. Enter an employee identification number in the text field, for example, "105". Click the Run Report button. iSQL*Plus executes the script and displays the results in your web browser.

8 Generating HTML Reports from SQL*Plus

Creating Reports using Command-line SQL*Plus

Creating Reports

Example 8-1 Creating a Report Interactively

Example 8-2 Creating a Report using the SQLPLUS Command

Creating Reports with CGI Scripts

Example 8-3 Creating a Report from a CGI Script

Web Page for CGI Example

Perl Script for CGI Example

SQL Script for CGI Example

Suppressing the Display of SQL*Plus Commands in Reports

HTML Entities

Table 8-1 Equivalent HTML Entities

Creating Reports using iSQL*Plus

Example 8-4 Creating a Dynamic Report

Example 8-5 Creating a Dynamic Report with Parameters

Example 8-6 Creating a Dynamic Script with Parameters and Login Details

8
Generating HTML Reports from SQL*Plus