Quick Tip – Oracle SQL*Plus: HTML Output

Recently, I posted about SQL*Plus, a database querying tool from Oracle. Like I mentioned there, the tool is rudimentary and has some basic file handling capabilities. It has a command, SPOOL, to write the console output to a file. Traditionally, you only spooled the output to a text file. Some releases ago (I believe in 8i), Oracle included the option to spool the output in HTML format. Useful when you are dumping data from tables (have you tried dumping table data in plain text file format??) and for generating simple reports.

To generate HTML output from SQL*Plus, all you have to do is add markup option either on command line or inside the script. By the nature of it, it is only useful when you are spooling the output. Just add below to the script before spooling: and the output will be spooled to the file mentioned  in HTML format.

SQL

SET MARKUP HTM ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>My TABLE Listing</TITLE> -
<STYLE type='text/css'> -<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"

…
spool dump_table.html

…

SPOOL off

That’s it. Just be aware that the output file will be larger than the usual text file spool. By the way, the hyphen “-” at the end of each line is the line continuation character in SQL*Plus. So, it’s really one line command with several options.

Alternatively, you can specify the HTML option on the commandline as shown below:

HTML

sqlplus -S -M "HTML ON" <user>/<password>@<SID> @dump_table.sql > dump_table.html

The option -M “HTML ON” is equivalent to MARKUP HTML ON in the script above. The option, -S is for silent, which turns off spurious output from SQL*Plus.

See here for more about creating HTML reports using SQL*Plus.