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.
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:
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.
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Submit an article or tip
