How to create a HTML table with the dynamic columns with the dynamic rows from the query in oracle

Question

My code:

Declare 
l_body CLOB;

 l_body := '<html><head><style>tr:last-child {font-weight: bold;}</style></head><body>'

                  ||'<table><tbody>'
                  || '<tr>'

                  || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Emp</th>'
                  || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Days</th>'
                  || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Amount</th>
                  </tr>';    

for r in (select  name, days, amount from employee)

loop
     
      l_body := l_body || '<tr><td style="color:#000000;border: 1px solid black;">';

      l_body_html  :=  l_body_html ||  r.emp || '</td><td style="color:#000000;border: 1px solid black;">'
                     || r.days||  '</td><td style="color:#000000;border: 1px solid black;">'
                     || r.amount||'</td> </tr>';

end loop;

How to create the HTML table header and rows dynamically instead of the static columns and rows?

Based on the query, i should create the HTML table with the selected columns from the query and rows.

Don't you think you should only return the result from back-end and front end application should be dynamic enough to show this data. — Ankit Bajpai, Jul 20 '20 at 06:01

score 0 · Answer 1 · answered Jul 20 '20 at 06:32

You table headers are set in the initialization of l_body. So all you can do, is make this initialization dynamic :

Instead of

     l_body := '<html><head><style>tr:last-child {font-weight: bold;}</style></head><body>'
      ||'<table><tbody>'
      || '<tr>'

      || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Emp</th>'
      || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Days</th>'
      || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">Amount</th>
      </tr>';

You can do :

        l_body := '

          <html>
                <head>
                    <style>tr:last-child {font-weight: bold;}</style>
                </head>
                <body>
                <table>
                    <tbody>
                        <tr>
                            <th style="font-weight:bold;border: 1px solid black; padding:6px;">#Header1#</th>
                            <th style="font-weight:bold;border: 1px solid black; padding:6px;">#Header2#</th>
                            <th style="font-weight:bold;border: 1px solid black; padding:6px;">#Header3#</th>
                        </tr>
        '; 

      l_body := replace(l_body,'#Header1#',WhatEverValue1);
      l_body := replace(l_body,'#Header2#',WhatEverValue2);
      l_body := replace(l_body,'#Header3#',WhatEverValue3);

This assumes that you know in advance the Headers of the query you want to show. You have to pay attention though to the length of your text. the function Replace works only for clob/varchar less than 32767 characters. It the text is too long, you have to switch to concatenations instead of replacements (or write your own large text replacement functions...) like :

l_body := l_body || '<th style="font-weight:bold;border: 1px solid black; padding:6px;">' || WhatEverValue || '</th>';

If you want it to be like really dynamic, this is going to be quite complex but feasible. Here is how :

Create a view for each query you want to use

Read the columns names of the view using the query :

 SELECT * FROM USER_TAB_COLUMNS where table_name = upper('MyViewName')

And repeat the headers replacement as before...

Hello Thomas Carlton, Thank you very much for the response. I would like to use like l_body := l_body || '' || WhatEverValue || ''; — user10777723, Jul 20 '20 at 14:18
l_body := l_body || '' || WhatEverValue || ''; I get the columns from the following query select regexp_substr(:P2_COLS,'[^,]+', 1, level) from dual connect by regexp_substr(:P2_COLS, '[^,]+', 1, level) is not null; I don't know how to pass the column value to the above "WhatEverValue" place in the loop to create the HTML header row. — user10777723, Jul 20 '20 at 14:22
I get the column names like NAME,DAYS,AMOUNT. I have to pass these values one by one to WhatEverValue place. These columns will be changed dynamically when the user filtered the columns. — user10777723, Jul 20 '20 at 14:30

score 0 · Answer 2 · answered Aug 15 '23 at 10:31

Here is the code to create an HTML table with dynamic columns from Oracle DB: Just change the static data sources mentioned below.

<!DOCTYPE html>
<html>
<head>
<title>Dynamic HTML Table</title>
</head>
<body>
<script>
function createTable(columns) {
  var table = document.createElement("table");
  var body = document.createElement("tbody");

  // Add the header row
  var tr = document.createElement("tr");
  for (var i = 0; i < columns.length; i++) {
    var th = document.createElement("th");
    th.innerHTML = columns[i];
    tr.appendChild(th);
  }
  body.appendChild(tr);

  // Add the data rows
  var rows = [];
  for (var i = 0; i < data.length; i++) {
    var row = [];
    for (var j = 0; j < columns.length; j++) {
      row.push(data[i][columns[j]]);
    }
    rows.push(row);
  }

  for (var i = 0; i < rows.length; i++) {
    var tr = document.createElement("tr");
    for (var j = 0; j < rows[i].length; j++) {
      var td = document.createElement("td");
      td.innerHTML = rows[i][j];
      tr.appendChild(td);
    }
    body.appendChild(tr);
  }

  table.appendChild(body);
  document.body.appendChild(table);
}

var columns = ["Name", "Age", "Occupation"];
var data = [
  ["John Doe", 30, "Software Engineer"],
  ["Jane Doe", 25, "Doctor"],
  ["Peter Smith", 40, "Lawyer"]
];

createTable(columns);
</script>
</body>
</html>

How to create a HTML table with the dynamic columns with the dynamic rows from the query in oracle

2 Answers2