Web Reports from SQL *Plus in Oracle 8i/9i


by Ajay Gursahani

The
SQL *Plus command-line interface enables you to generate a complete HTML output
which can be embedded in a web page.

SQL
*Plus provides you with a command, SET MARKUP HTML ON SPOOL ON, which is used
to produce HTML pages automatically. You can view these pages using any web
browser.

The SET MARKUP HTML ON SPOOL ON only specifies that the
SQL *Plus output will be HTML encoded; it does not create or begin writing to
an output file till you issue SPOOL <filename>. The file will then have
HTML tags including <HTML> and </HTML>

You
have to use SPOOL OFF to close the spool file and issue SET MARKUP HTML OFF to
disable HTML output.

Example:


SQL> desc test

Name Null? Type
———————————– ——– —————–
UNIQUE_ID NUMBER
NAME VARCHAR2(25)
SALARY NUMBER

SQL> SELECT * FROM TEST;

UNIQUE_ID NAME SALARY
———- ————————- ———-
1 ANDY 4500
2 ALAN 3500
3 JACK 3600
4 PETER 4000
5 JOE 2900

Issue
the following commands at SQL Prompt


SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL c:test.html
SELECT * FROM test;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO OFF

The
above commands will generate an HTML file “test.html” which you can view using
a web browser. A sample output is as below:

SQL> SELECT * FROM test;

UNIQUE_ID

NAME

SALARY

1

ANDY

4500

2

ALAN

3500

3

JACK

3600

4

PETER

4000

5

JOE

2900

SQL> spool off

Please note that, the SQL query (SELECT * FROM test;) and
SPOOL OFF are also a part of “test.html”.

Latest Articles