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

March 7, 2003

by Ajay Gursahani

A view source on "test.html" will typically display the following:

<html>
<head>
<title>
  SQL*Plus Report
</title>
<meta name="generator" content="SQL*Plus 9.0.1">
</head>
<body>

  SQL> select * from test;
<br>
<p>
<table border="1" width="90%">
<tr><th>
  UNIQUE_ID
</th><th>
  NAME
</th><th>
  SALARY
</th></tr>
<tr><td align="right">
  1
</td><td>
  ANDY
</td><td align="right">
  4500
</td></tr>
<tr><td align="right">
  2
</td><td>
  ALAN
</td>
<td align="right">
  3500
</td></tr>
<tr><td align="right">
  3
</td><td>
  JACK
</td><td align="right">
  3600
</td></tr>
<tr><td align="right">
  4
</td><td>
  PETER
</td><td align="right">
  4000
</td></tr>
<tr><td align="right">
  5
</td><td>
  JOE
</td><td align="right">
  2900
</td></tr>
</table>
<p>

  SQL> spool off
<br>
</body>
</html>

However, you can generate an HTML file by executing the following script from the OS level, which will suppress the SQL commands from spooling.

test.sql

SELECT * FROM test;
EXIT

SQL> sqlplus -s -m "HTML ON" wbur/wbur @c:\test.sql>test.html

"-m" uses HTML Markup Options. It allows you to start SQL *Plus session in 'markup mode', rather than using SET MARKUP command interactively (as we did in above example)

"-s" uses silent mode

 

The output is redirected to "test.html". A sample output of "test.html" is as below;

test.html

UNIQUE_ID

NAME

SALARY

1

ANDY

4500

2

ALAN

3500

3

JACK

3600

4

PETER

4000

5

JOE

2900

Note that the SQL commands are no longer displayed as part of html.

Summary

The above article gives a basic understanding of how we can generate HTML reports using the MARKUP option in SQL *Plus. You can extend this exercise by writing CGI scripts that will generate web reports.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers