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

Creating Reports using Command-line SQL*Plus
Creating Reports using iSQL*Plus

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 <PRE> tag so that it displays in a web browser exactly as it appears in SQL*Plus. See the SQLPLUS MARKUP Options 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 file extension which are standard file extensions for HTML files. This enables you to easily identify the type of your output files, and also enables 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 7-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:

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:

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 10.2.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 7-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@Ora10g @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", prompts for the HR password, 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 Suppressing the Display of SQL*Plus Commands in Reports for information about calling SQL*Plus from a CGI script.

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

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.

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

iSQL*Plus prompts for undefined values for:

Username and password
Substitution variables
ACCEPT commands
RECOVER commands
CONNECT commands

iSQL*Plus pauses script execution and displays a Next Page button when a PAUSE command is executed. Select the Next Page button to continue script execution.

Dynamic report output can be displayed over multiple pages, or as a single page. The default is multiple page output with pages of 24 lines. Use the SET PAGESIZE and SET PAUSE commands in your scripts to set single or multiple page output for dynamic reports. For example:

To set dynamic report output to a single page, use:

SET PAUSE OFF

To set dynamic report output to multiple pages of 40 lines, use:

SET PAUSE ON

SET PAGESIZE 40

You can include username and password information in the request. However, for security reasons, it is recommended that you never include your password in plain text. You will always be prompted to enter your password 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.

The following examples use the EMP_DETAILS_VIEW view of the Human Resources (HR) sample schema. This schema contains personnel records for a fictitious company. It may be installed as part of the default Oracle Database installation using the Oracle Database Configuration Assistant.

For further information about the sample schemas included with Oracle Database, see the Oracle Database Sample Schemas guide.

Example 7-3 Creating a Dynamic Report

Create and save the following script to a file called script.sql on your Application 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/dynamic?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 Application Server. Save the HTML file on your Application Server.

Load the HTML file in your web browser and click "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 7-4 Creating a Dynamic Report with Parameters

Create and save the following script to a file called employee_name.sql on your Application 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>iSQL*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/dynamic">
<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 iSQL*Plus port number of your Application Server. Save the HTML file on your Application 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 7-5 Creating a Dynamic Script with Parameters and Login Details

Create and save the following script to a file called employee_id.sql on your Application 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/dynamic">
<INPUT TYPE="hidden" NAME="userid" VALUE="hr">
<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 iSQL*Plus port number of your Application Server, and hr with a valid userid. Save the HTML file on your Application 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, prompts for the password and displays the results in your web browser.

Example 7-6 Passing a Connect Identifier to a Dynamic Report

Create an HTML file which contains:

<HTML><HEAD><TITLE>iSQL*Plus Report</TITLE>
</HEAD><BODY> 
<H1><i>i</i>SQL*Plus Report</H1> 
<H2>Search for an Employee</H2> 
<FORM METHOD=get ACTION="http://machine_name.domain:port/isqlplus"> 
<p> 
<INPUT TYPE="hidden" NAME="userid" VALUE="hr@ora901"> 
<INPUT TYPE="hidden" NAME="script" 
VALUE="http://machine_name.domain:port/employee_id.sql"> 
Enter the employee's identification number: 
<INPUT TYPE="text" NAME="eid" SIZE="10" value="105"> 
<INPUT TYPE="submit" VALUE="Search"> 
</p> 
</FORM></BODY></HTML>

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

The connect identifier, ora91 in the example, needs to be included in the userid INPUT field. The connect identifier must be defined in the tnsnames.ora file of the iSQL*Plus Server you are using.