Build Html table from a collection with pl-sql

Gonçalo

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

Gonçalo

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.

fac586

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.

This provides no additional information.

Please respond the questions asked above.

• What is your:
  • Full database version
  • Full APEX version
  • APEX theme and theme style used in the application
• Does the code posted above produce the expected output for the test data provided?
• Can an individual dataset contain rows with different numbers of columns or data positions? For example:

dynamic_table.table_chunk(
    dynamic_table.table_record(1, 0, 'DATA')
  , dynamic_table.table_record(1, 1, 'UGW')
  , dynamic_table.table_record(2, 1, 'DEF')
  , dynamic_table.table_record(3, 0, 'Teste')
  , dynamic_table.table_record(3, 1, 'XYZ')
  , dynamic_table.table_record(3, 2, '586')
)

fac586

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

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

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.

fac586

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.

This provides no additional information.

Please respond the questions asked above.

• What is your:
  • Full database version
  • Full APEX version
  • APEX theme and theme style used in the application
• Does the code posted above produce the expected output for the test data provided?
• Can an individual dataset contain rows with different numbers of columns or data positions? For example:

dynamic_table.table_chunk(
    dynamic_table.table_record(1, 0, 'DATA')
  , dynamic_table.table_record(1, 1, 'UGW')
  , dynamic_table.table_record(2, 1, 'DEF')
  , dynamic_table.table_record(3, 0, 'Teste')
  , dynamic_table.table_record(3, 1, 'XYZ')
  , dynamic_table.table_record(3, 2, '586')
)

Gonçalo

Sorry, afterall its working.

Thank you so much.

Cheers