Sql*Plus User’s Guide and Reference

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

---

Creating Reports using Command-line SQL*Plus
7-2
SQL*Plus User's Guide and Reference
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  tag. Open a SQL*Plus session and enter the following:
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD " -
" -
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  and 
 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 ''||DEPARTMENT_
NAME||'' DEPARTMENT_NAME, CITY 
FROM EMP_DETAILS_VIEW 
WHERE SALARY>12000; 
Enter the SPOOL OFF command:
SPOOL OFF
The
SQL> SELECT '<A HREF="http://oracle.com/'
||DEPARTMENT_NAME||'.html">'||DEPARTMENT_NAME
||'</A>' DEPARTMENT_NAME, CITY
2 FROM EMP_DETAILS_VIEW
3* WHERE SALARY>12000

---

Creating Reports using Command-line SQL*Plus
7-4
SQL*Plus User's Guide and Reference

DEPARTMENT	CITY
Executive	Seattle
Executive	Seattle
Executive	Seattle
Sales	Oxford
Sales	Oxford
Marketing	Toronto

6 rows selected.
SQL> spool off