Release 8.1.6
A75664-01
Library |
Product |
Contents |
Index |
| SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-01 |
|
Formatting Query Results, 6 of 6
You can output SQL*Plus HyperText Markup Language (HTML)
encoded reports to a file using the SET MARKUP and SPOOL commands, or with
the SQLPLUS -MARKUP command. The report content is written inside a 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.
You use the SET MARKUP command interactively during a SQL*Plus
session to write HTML to a spool file. The output can be viewed with a
web browser. The SQL*Plus screen display is unchanged as HTML encoding
is only written to the spool file.
SET MARKUP only specifies that SQL*Plus output will be HTML
encoded, it does not create or begin writing to an output file. You use
the SQL*Plus SPOOL command to create and name a spool file, and to begin
writing output to it. If you use SET MARKUP HTML ON SPOOL ON to enable
HTML encoded output and to enable the spooling of HTML output, then the
output written to the spool file is HTML encoded.
When creating a HTML encoded 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, .lst, is used.
You use SPOOL OFF, EXIT, or SPOOL filename to write
the final HTML tags to the spool file and then close it. In the SPOOL filename
case, a new HTML encoded spool file is created.
If you want to create a new output file which is not HTML
encoded, use EXIT or SPOOL OFF to close the existing HTML spool file, and
then turn off HTML output and spooling with SET MARKUP HTML OFF SPOOL OFF.
The next time you use the SQL*Plus SPOOL command to create a new spool
file in the current session, the output will be plain text, not HTML encoded.
You can use the SET MARKUP command to enable or disable HTML output as
required.
To create HTML output in an interactive SQL*Plus session, enter the appropriate SET MARKUP command. You can include an embedded style sheet, or any other valid text in the HTML <HEAD> tag.
SQL> SET MARKUP HTML ON SPOOL ON HEAD '<TITLE>Example</TITLE> <STYLE - > type="text/css"> <!-- BODY {background: red} --> </STYLE>'
|
Note: The minus sign ("-") at the end of the first line is the SQL*Plus continuation character. |
Enter the SQL*Plus SPOOL command and specify a spool file name:
SQL> SPOOL example.html
After the SPOOL command, anything entered or displayed on
standard output is also written to the spool file, example.html.
SET MARKUP HTML ON SPOOL ON tells SQL*Plus to enable the
HTML feature and write HTML tags to a spool file. The SPOOL command triggers
the writing of the <HTML>, <HEAD>, <BODY> and <PRE> tags to
the spool file. Subsequent commands and their output are then written to
the spool file until you close it.
Enter a SQL query:
SQL> SELECT EMPNO, ENAME FROM EMP;
Enter the SPOOL OFF command:
SQL> SPOOL OFF
The </PRE>, </BODY> and </HTML> tags are appended
to the spool file, example.html, before it is closed.
Open example.html in your web browser. It should appear as follows:
SQL> SELECT EMPNO, ENAME FROM EMP; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EMPNO ENAME ---------- ---------- 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. SQL> SPOOL OFF
Open example.html in a text editor. It should appear as follows:
<HTML> <HEAD> <TITLE> Example </TITLE> <STYLE type="text/css"> <!-- BODY {background: red} --> </STYLE> <META Name="generator" content="SQL*Plus 8.1.6"> </HEAD> <BODY > <PRE> SQL> SELECT EMPNO, ENAME FROM EMP; ENAME EMPNO ---------- ---------- SMITH 7369 ALLEN 7499 WARD 7521 JONES 7566 MARTIN 7654 BLAKE 7698 CLARK 7782 SCOTT 7788 KING 7839 TURNER 7844 ADAMS 7876 ENAME EMPNO ---------- ---------- JAMES 7900 FORD 7902 MILLER 7934 14 rows selected. SQL> SPOOL OFF </PRE></BODY></HTML>
Certain characters such as "<", ">" and "&" have 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.
| Character | HTML Entity | Meaning |
|---|---|---|
|
< |
< |
Start HTML tag label |
|
> |
> |
End HTML tag label |
|
& |
& |
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.
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 is HTML encoded and displayed directly
in a web browser. You can use any script language supported by your web
server which may include UNIX shell scripts, Windows command files (batch
files), Java, JavaScript or Perl files to name just some of the possibilities.
You can use this embedded approach to produce HTML reports
on the Internet using existing SQL*Plus scripts, unchanged. It provides
an easy way to provide dynamically-created, web-based reports.
To create a CGI script to run SQL*Plus, producing a report from an existing SQL script, and to view the results in a web browser, you first need to create a Shell script using a text editor. Your script can be written in Perl, Java or any other language supported by your web server. A Shell script is used in this example. Add the following text to your script and edit it to suit your configuration:
#!/bin/sh echo Content-type: text/html # Required blank line echo # Your ORACLE_HOME ORACLE_HOME=/vobs/oracle # Your ORACLE_SID ORACLE_SID=plus81 # Your Net8 TNSNAMES.ORA path if required for remote databases # TNS_ADMIN=/var/opt/oracle export TNS_ADMIN export ORACLE_HOME export ORACLE_SID # SQL*Plus executable path # and enable SQL*Plus markup feature and include Net8 service name # and SQL script path and script name $ORACLE_HOME/bin/sqlplus -M "html on" scott/tiger@connect_string @$ORACLE_ HOME/sqlplus/employee.sql
Save this script as sqlscript.sh in the cgi-bin
directory of your web server.
Create a SQL*Plus script in your text editor which contains:
SELECT EMPNO, ENAME FROM EMP; EXIT;
Save the script as employee.sql in $ORACLE_HOME/sqlplus.
Start your web browser and enter the URL appropriate for your web server. It should be similar to:
http://webserver.domain.com/cgi-bin/sqlscript.sh
The shell script sqlscript.sh runs, which in turn
starts SQL*Plus and runs the employee.sql script. The query results
are displayed directly in your web browser.
Your output should look something like this:
SQL*Plus: Release 8.1.6.0.0 - Production on Wed Sep 29 16:58:18 1999 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8I Server Release 8.1.6.0.0 - Production With the distributed, parallel query and Spatial Data options PL/SQL Release 8.1.6.0.0 - Production EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EMPNO ENAME ---------- ---------- 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. Disconnected from Oracle8i Server Release 8.1.6.0.0 - Production With the distributed, parallel query and Spatial Data options PL/SQL Release 8.1.6.0.0 - Production
It is recommended that you use SILENT mode to start your
SQL*Plus session to suppress the display of the SQL*Plus banner and SQL*Plus
commands. This ensures that only the results of your SQL query appear in
the web browser.
The SQLPLUS -SILENT option is very useful, particularly in
combination with -MARKUP when generating embedded SQL*Plus reports using
CGI scripts or operating system command files. SILENT suppresses the display
of SQL*Plus commands and the SQL*Plus banner. Your HTML output shows only
the data resulting from your SQL query.
For example, using SILENT, the output from example 4-27
should look something like this:
EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EMPNO ENAME ---------- ---------- 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected.
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|