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

Question

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

1.5k views Asked by user10777723 At 14 September 2024 at 08:15

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.

Original Q&A

There are 2 answers

**Thomas Carlton** · Answer 1 · 2020-07-20 06:32:02

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...

**Bipul Jaishwal** · Answer 2 · 2023-08-15 10:31:26

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>

TechQA.

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

There are 2 answers

Related Questions in ORACLE

Related Questions in ORACLE-APEX-18.2

Popular Questions

Popular Tags

Trending Questions