Forum Stats

  • 3,781,447 Users
  • 2,254,521 Discussions
  • 7,879,706 Comments

Discussions

Build Html table from a collection with pl-sql

Gonçalo
Gonçalo Member Posts: 13 Red Ribbon
edited Feb 6, 2021 9:20PM in APEX Discussions

Hello,

I have a table of records of this type where i stored a collection:

TYPE TABLE_RECORD IS RECORD (n_lin number, n_col number, val varchar(4000)); TYPE TABLE_CHUNK IS TABLE OF TABLE_RECORD;

Let imagine i have this records inside:


-- 1 0 DATA

-- 1 1 UGW

-- 1 2 500

-- 2 0 DATA

-- 2 1 Teste

-- 2 2 100

The first is the line number,the second is the column number and the third the value.

I want to loop all the collection and build the table body in html, but the number of columns in the head can be variable so i never know how many static <td> i will have . For the head i loop a table that contain all the clauses for the select statement so its more easy.


l_columns_data DYNAMIC_TABLE.TABLE_CHUNK :=DYNAMIC_TABLE.TABLE_CHUNK();

htp.p(' <tbody>');


       FOR j IN 1..l_columns_data.COUNT LOOP
           
           
 
           htp.p( ' <tr>');
             

              htp.p('<td> '||l_columns_data(j).val||'</td>');
                  

             
                
            htp.p('</tr>');
           
            end loop;
           
           
htp.p( '</tbody>
        </table>
        ');


The objective is that ever 3 rows from the collection that has the line number "1" create a row in the table <tr> and then accordingly with the number of columns create a correspondent <td> with the associated value.

You know how can i achieve this?

Thanks.


APEX Version: 20

Database Version: 18C

Tagged:

Best Answers

  • Gonçalo
    Gonçalo Member Posts: 13 Red Ribbon
    Accepted Answer

    Thank you in advance.

    Yes the problem is because i never know how many columns i will have so i save in the collection the line, the column and the value, but to construct the body dinamically i's getting hard.


    Because when i loop the collection the first row is :

    dynamic_table.table_record(1, 0, 'DATA')
    

    and i can save them into a variable to get to the next row if necessary, but this won t work cause i never know exactly the number of columns.

    Thanks.

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,335 Red Diamond

    Please update your forum profile with a recognisable username instead of "User_25KFL", and supply the following information:

    • Full database version
    • Full APEX version
    • APEX theme and theme style used in the application


  • fac586
    fac586 Senior Technical Architect Member Posts: 20,335 Red Diamond

    This will work if the number of columns is consistent within the dataset.

    <<dynamic_table>>
    declare
    
      type table_record is record (n_lin number, n_col number, val varchar(4000));
      type table_chunk is table of table_record;
    
      i pls_integer;
      n pls_integer := 0;
    
      /*
        This form of test data generation requires Oracle 18+
      */
      l_columns_data dynamic_table.table_chunk := dynamic_table.table_chunk(
                                                      dynamic_table.table_record(1, 0, 'DATA')
                                                    , dynamic_table.table_record(1, 1, 'UGW')
                                                    , dynamic_table.table_record(1, 2, '500')
                                                    , dynamic_table.table_record(2, 0, 'DATA')
                                                    , dynamic_table.table_record(2, 1, 'Teste')
                                                    , dynamic_table.table_record(2, 2, '100')
                                                  );
    
    begin
    
      i := l_columns_data.first;
    
      if i is not null
      then
        htp.p('<table>');
        htp.p('<tbody>');
    
        while i is not null
        loop
          if l_columns_data(i).n_lin != n
          then
            htp.p('<tr>');
            n := l_columns_data(i).n_lin;
          end if;
    
          htp.p('<td>' || l_columns_data(i).val);
    
          i := l_columns_data.next(i);
    
        end loop;
    
        htp.p('</tbody>');
        htp.p('</table>');
    
      end if;
    
    end;
    


    Content-type: text/html
    Content-length: 96
    
    <table>
    <tbody>
    <tr>
    <td>DATA
    <td>UGW
    <td>500
    <tr>
    <td>DATA
    <td>Teste
    <td>100
    </tbody>
    </table>
    

    If the data contains rows with different numbers of columns then things will get more complicated.

    Gonçalo
  • Gonçalo
    Gonçalo Member Posts: 13 Red Ribbon
    Accepted Answer

    Thank you in advance.

    Yes the problem is because i never know how many columns i will have so i save in the collection the line, the column and the value, but to construct the body dinamically i's getting hard.


    Because when i loop the collection the first row is :

    dynamic_table.table_record(1, 0, 'DATA')
    

    and i can save them into a variable to get to the next row if necessary, but this won t work cause i never know exactly the number of columns.

    Thanks.

  • Gonçalo
    Gonçalo Member Posts: 13 Red Ribbon

    Sorry, afterall its working.

    Thank you so much.

    Cheers