htmloracle-databasesqlplus

How to write multi-line headers in html output file of oracle sqlplus?

The output I need (I need to add multi-line headers): The table as desired, with the first table header (TOTAL) spanning two rows, one column and the rest spanning several columns, one row

The output I am getting: Oversized table headers that seem to not be connected to the data table itself, the TOTAL header missing entirely

My Query:

-- Enable SQL*Plus HTML Markup
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL "C:\Grocery_data.html"

-- Define HTML Styling
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<title>Application Count</title> -
<style> -
body {background: #ffffff; font-family: Arial, sans-serif;} -
table {width: 100%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text-align: center;} -
th, td {padding: 8px; border: 2px solid #ddd; text-align: center; white-space: nowrap;} -
th {background-color: #c90421; color: #ffffff; font-weight: bold;} -
td {min-width: 50px;} -
tr:nth-child(even) {background-color: #f2f2f2;} -
</style>"

-- Inject custom HTML table header
SET MARKUP HTML OFF
PROMPT <table>
PROMPT <thead>
PROMPT <tr>
PROMPT <th colspan="4" style="width:460px;">Fruits</th>
PROMPT <th colspan="3" style="width:350px;">Vegetables</th>
PROMPT <th colspan="2" style="width:300px;">Desserts</th>
PROMPT </tr>
PROMPT </thead>
PROMPT <tbody>
SET MARKUP HTML ON

COLUMN BANANA_DATA HEADING 'Banana'
COLUMN APPLE_DATA HEADING 'Apple'
COLUMN MANGO_DATA HEADING 'Mango'
COLUMN CHERRY_DATA HEADING 'Cherry'
COLUMN POTATO_DATA HEADING 'Potato'
COLUMN BRINJAL_DATA HEADING 'Brinjal'
COLUMN BEANS_DATA HEADING 'Beans'
COLUMN TIRAMISU_DATA HEADING 'Tiramisu'
COLUMN OREO_DATA HEADING 'Oreo Cake'

SELECT 
    TO_CHAR(subquery1.fruit1) AS BANANA_DATA,
    TO_CHAR(subquery2.fruit2) AS APPLE_DATA,
    TO_CHAR(subquery3.fruit3) AS MANGO_DATA,
    TO_CHAR(subquery4.fruit4) AS CHERRY_DATA,
    TO_CHAR(subquery5.veg1) AS POTATO_DATA,
    TO_CHAR(subquery6.veg2) AS BRINJAL_DATA,
    TO_CHAR(subquery7.veg3) AS BEANS_DATA,
    TO_CHAR(subquery8.dessert1) AS TIRAMISU_DATA,
    TO_CHAR(subquery9.dessert2) AS OREO_DATA 
FROM
........ (my sql query to fetch data from the groceries database)

Questions:

Any help or elegant workarounds would be hugely appreciated.

What I’ve Tried So Far:

I've tried injecting the top-level group headers (Fruits / Vegetables / Desserts) using PROMPT before the actual SQL query. The column headers come from COLUMN ... HEADING, and the data rows from the SQL query.

But the moment I try to combine them:

Also, SQL*Plus’s COLUMN heading format is flat — not hierarchical.

Additionally, if one uses an SQL-only approach in SQL*Plus:

What I'm Looking For:

I’m trying to create multi-row headers (i.e., category grouping) within SQL*Plus output, preferably:

Solution

  • Summary: No. SQL*Plus does not support that level of customisation.

    Is it possible in SQL*Plus (especially older versions like 18.4) to render multi-row or grouped headers cleanly?

    No. SQL*Plus commands only supports simple column headers.

    In the text output, you can include line breaks in column headers by using the pipe character:

    COLUMN banana_data HEADING 'Fruits|Banana'

    You can test whether this has any effect in a HTML formatted report but it will not support spanning multiple columns.

    Is there a native workaround that keeps the query and formatting in SQL, but renders correctly in HTML?

    This is relatively simple if you are going to query the data in SQL and then build the report in a third-party languages (PHP, Java, C#, Python, ColdFusion, etc.) as you can use the formatting options of the third-party language.

    If you particularly want to get the data using only SQL*Plus then you could manually build the output in SQL:

    SELECT XMLELEMENT(
         "TABLE",
         XMLELEMENT(
           "THEAD",
           XMLELEMENT(
             "TR",
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "rowspan"), 'TOTAL'),
             XMLELEMENT("TH", XMLATTRIBUTES(3 AS "colspan"), 'Fruits'),
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Vegetables'),
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Deserts')
           ),
           XMLELEMENT(
             "TR",
             XMLFOREST(
               'Banana' AS "TH",
               'Apple' AS "TH",
               'Grape' AS "TH",
               'Carrot' AS "TH",
               'Potato' AS "TH",
               'Tiramisu' AS "TH",
               'Oreo' AS "TH"
             )
           )
         ),
         XMLELEMENT(
           "TBODY",
           XMLELEMENT(
             "TR",
             XMLFOREST(
               'Grand Total' AS "TD",
               SUM(CASE name WHEN 'Banana'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Apple'    THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Grape'    THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Carrot'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Potato'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Tiramisu' THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Oreo'     THEN quantity ELSE 0 END) AS "TD"
             )
           )
         )
       ) AS html
FROM   groceries;

    fiddle

    Then you can save the result to a variable and spool it to a file (this may require you to manually build the entire HTML page and spool it as text).

    I’m trying to create multi-row headers (i.e., category grouping) within SQL*Plus output, preferably:

    • Without using raw HTML injection for the entire table
    • Without resorting to UNION or UNION ALL
    • While keeping the actual data query and formatting inside SQL

    It does not appear to be possible as there are no obvious options, nor examples, to support this in the SQL*Plus documentation (see Generating Reports from SQL*Plus or Formatting SQL*Plus Reports).