Render SQL*Plus output in HTML

by Bob Watkins | May 23, 2007 6:07:00 AM

Tags: Bob Watkins, HTML, Render SQL*Plus, markup, Oracle Tips Newsletter

Takeaway: Oracle's SQL*Plus query tool can create output as HTML tables in addition to plain text. The output format is controlled by the SET MARKUP command. Bob Watkins offers more details in this Oracle tip.

SQL*Plus has traditionally been thought of as a plain text SQL query tool. But since Oracle 8i, it has also had the capability to render its output using HTML.

One of SQL*Plus's environment settings, MARKUP, controls what kind of markup language (if any) to use for its output. By default, MARKUP defines HTML as the markup language, but markup itself is turned off. A set of HTML tags is predefined; all you have to do is turn markup on by typing:

SET MARKUP HTML ON

and the tags will be added to each output produced by SQL*Plus. For example, after activating the feature as above, you could type the following:

SPOOL deptlist.html

SELECT * FROM departments;

SPOOL OFF

and the result would be formatted as an HTML table ready to add to an intranet or other Web page. To create a complete HTML document, including the <HTML> and </HTML> tags and a CSS style sheet, type:

SET MARKUP HTML ON SPOOL ON

To turn the feature off again or exit the session, type:

SET MARKUP HTML OFF

SET MARKUP HTML OFF SPOOL OFF

If you don't like the way that SQL*Plus formats the output, no problem. You can also use the SET MARKUP command to replace the built-in formatting codes with your own. The HEAD, BODY, TABLE, and other options let you specify the HTML to generate.

For more information, consult the SQL*Plus User's Guide and Reference, Chapter 7, Generating HTML Reports from SQL*Plus.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.

3 comment(s)