Published: Wed 27 December 2006
By mafr
In tools .
tags: databases oracle
In the previous posting I gave an example on how to create an HTML report using Oracle's sqlplus tool. This time, we'll extend this example to demonstrate more of sqlplus' features.
Have a look at the following script:
--
-- Sample HTML report.
--
-- Usage: sqlplus LOGON @script OUTPUT_FILENAME
--
-- Activate HTML output and configure the generated markup.
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>My Report</title> -
<style type="text/css"> -
table { background: #eee; font-size: 80%; } -
th { background: #ccc; } -
td { padding: 0px; } -
</style>'
-- Dump results to the file that is given on the command line.
SPOOL &1
-- Only dump to file, not to the terminal.
SET TERMOUT OFF
-- Titles and formatting of columns.
COLUMN name HEADING 'Name'
COLUMN job HEADING 'Job Title'
COLUMN salary HEADING 'Salary' FORMAT $99,990
-- The query
--
SELECT e.last_name || ', ' || e.first_name AS name,
j.job_title AS job, 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
The first thing which catches the eye is the HEAD setting. Using SET MARKUP HEAD , you can specify a string that will appear inside the generated head element. It replaces the pre-configured title and stylesheet.
The &1 variable in the SPOOL &1 command isn't defined inside the script. The variables &1 , &2 etc. refer to command line arguments that you have to pass to the script when you execute it (see the "Usage" comment). In this case, I've made the output file name configurable.
Formatting options for the columns and their headers can be given using the COLUMN commands. In the example, better titles are set, and the salary is printed in a convenient notation. See the SQL*Plus User's Guide for all supported options.
In the query itself, the columns inside the SELECT clause are renamed to provide better titles. Additionally, the first and last name columns are concatenated to improve readability.