Creating HTML Reports with Oracle’s SQL*Plus

Oracle’s sqlplus command line utility feels a bit antiquated for spoilt open source users. But with a bit of tweaking, you can generate nice database reports in HTML. Sure, the syntax is arcane, but if you’re used to Perl you can get used to anything.

The following basic example prints the top earners from the sample HR schema:

--
-- Sample HTML report.
--

-- Activate HTML output. "SPOOL ON" writes "html" and "head" tags.
SET MARKUP HTML ON SPOOL ON

-- Dump results to file.
SPOOL out.html

-- Only dump to file, not to the terminal.
SET TERMOUT OFF

-- The query
--
SELECT e.last_name, e.first_name, j.job_title, e.salary
FROM employees e, jobs j
WHERE e.job_id = j.job_id AND salary > 12000
ORDER BY e.salary DESC
/

-- Close file, which also closes the HTML tags.
SPOOL OFF

-- Back to non-HTML output
SET MARKUP HTML OFF

Save this script to a file (e.g. report_html.sql), log into the database and type this command:

    START report_html.sql

The @ sign is a synonym for START and the .sql extension is optional. Afterwards, there should be a file out.html in your working directory which contains the report.

The sqlplus manual has all the details on how to influence the layout of the generated reports. I’m also going to publish some more advanced examples here.

Advertisements
This entry was posted in tools and tagged , , . Bookmark the permalink.