Formatting Query Results, 6 of 6

Creating Web Reports

SQL*Plus enables you to generate either a complete standalone 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 standalone web pages. SQL*Plus generates complete HTML pages automatically encapsulated with <HTML> and <BODY> tags.

The retrieved data is written to in HTML by default, though you can optionally direct output to the HTML <PRE> tag so that it displays in a web browser exactly as it appears in SQL*Plus. See the SQLPLUS -MARKUP command in the "Starting SQL*Plus Using the SQLPLUS Command" section of Chapter 7 and the SET MARKUP command in the SET section of Chapter 8 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 in conjunction 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 complete HTML pages 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 Static Web Reports

You use the SET MARKUP command interactively during a SQL*Plus session to write HTML to a spool file. The output can be viewed in a web browser.

SET MARKUP only specifies that SQL*Plus output will be HTML encoded, it does not create or begin writing to an output file. You must use SET MARKUP HTML ON SPOOL ON to trigger the generation of the <HTML> and </HTML> tags when spool files are opened and closed. You then use the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it.

When creating a HTML file, it is important and convenient to specify a .html file extension which is the standard file extension 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 4-26 Creating a Standalone Web Report in an Interactive Session

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 DEPARTMENT_NAME HEADING 'DEPARTMENT'
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. This is a standalone web page 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 4-report.gif follows.

Text description of the illustration 4-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 of the -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.0.1">
</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 4-27 Creating a Standalone Web Report using the SQLPLUS command

Enter the following command at the operating system prompt:


SQLPLUS -S -M "HTML ON TABLE 'BORDER="2"'" HR/HR@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 departt.sql is a complete web page which in this case has been redirected to the file depart.html using the > operating system command. It could be sent to a web browser if SQL*Plus was called in a web server CGI script. See "Creating a Web Report from a CGI Script" 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 4-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 Dynamic Web Reports with CGI Scripts

The SQLPLUS -MARKUP command allows you to start a SQL*Plus session in Internet enabled 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 or an operating system command file. 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 command file (batch file), Java, JavaScript or a Perl file.

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

Example 4-28 Creating a Web 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 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 a form which prompts for your 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.


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

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

<!-- Change the URL here.  On Windows NT you may need to use
http://host.domain/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://host.domain/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:


# Copyright (c) Oracle Corporation 1999, 2001. 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 8.1.7 (or later) is

#   installed on your webserver, and the webserver is configured to

#   run CGI programs. The database may be on another machine, but

#   nust 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/901";  # 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 "\n";

  print "$plusexe:$connstr:$script:\n";

  print "\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 4-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 4-employ.gif follows.

Text description of the illustration 4-employ.gif

Suppressing the Display of SQL*Plus Commands in Web Reports

It is recommended that you use SILENT mode to start your SQL*Plus session. This ensures that only the results of your SQL query appear in the web browser.

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 command files. It suppresses the display of SQL*Plus commands and the SQL*Plus banner. Your HTML output shows only the data resulting from your SQL query.

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 4-2 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.