Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Build Html table from a collection with pl-sql

GonçaloFeb 6 2021 — edited Feb 6 2021

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
This post has been answered by Gonçalo on Feb 6 2021
Jump to Answer

Comments

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.

1 person found this helpful
Gonçalo
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.

Marked as Answer by Gonçalo · Feb 7 2021
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

1 - 5

Post Details

Added on Feb 6 2021
#apex-discussions, #html, #pl-sql
5 comments
3,687 views