Generate HTML report using Oracle SQL Plus

SQL* Plus has good reporting commands and features. You can generate a report in text, HTML using SQL* Plus. This HTML report you can directly open in Microsoft Excel as well.

In this article let’s see how to generate HTML report using Oracle SQL* Plus.

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface… source www.oracle.com

To demonstrate, we will use Oracle-provided sample database schema with data. You can download it here.

Generate text report using SQL* Plus

You can use SQL* Plus to generate text reports which are either separate by TAB or any special character like ‘|’ PIPE or ‘,’ comma. It provides the built-in command to rename columns, store output in a physical file.

  • set pages  – sets the number of rows on each page of output
  • column – rename column name
  • spool – store script result in a flat file

Example 1: Generate text report using SQL plus

SET pages 2000
column department_id heading 'Department ID'
column department_name heading 'Department Name'
column manager_id heading 'Manager ID'
column location_id heading 'Location ID'
spool department_listing;
SELECT department_id
,department_name
,manager_id
,location_id
FROM departments;
spool OFF;
/

Generate HTML report using SQL* Plus

SQL* Plus allows to generate an HTML report using SQLPLUS -MARKUP “HTML ON” or SET MARKUP HTML ON SPOOL ON commands.

  • set markup html on spool on – specifies that output will be in HTML. You can specify any valid HTML or CSS syntax in the head section.
  • set markup html off – stops HTML formatting.

Example 2: Generate HTML report or Web Page using SQL plus

set pages 2000
set markup html on spool on preformat off entmap on -
head "<title>department report</title> -
<style type='text/css'> -
body { -
background: white; color: blue; padding: 10px 0x 0px 10px; } -
table,tr,td { width: 1%;} -
</style>" -
column department_id heading 'Department ID'
column department_name heading 'Department Name'
column manager_id heading 'Manager ID'
column location_id heading 'Location ID'
spool department_listing.html;
SELECT department_id
,department_name
,manager_id
,location_id
FROM departments;
spool off
set markup html off
/

 

Related Post

One Comment

Comments are closed.