oracle: efficient way to configure columns in an output report

Question

I'm designing an HTML report that effectively extracts columns from a single table

The number of columns in this table is quite large, and I would like some way to configure the application to say which columns to display. Note: This is not a per-user setting.

Lets say I have the main table:

MAIN_TABLE
id
first_name
last_name
weight
height
attribute_4
attribute_5
attribute_6
...
attribute_99

I was thinking of a table like

MAIN_TABLE_DISPLAY
column_name
display

Or perhaps

MAIN_TABLE_DISPLAY
display_id
display_first_name
display_last_name
display_weight
display_height
display_attribute_4
display_attribute_5
display_attribute_6
...
display_attribute_99

But I would like to perform an efficient join.

Any suggestions?

Thanks.

Answer 1

Have you thought about using a view? Your application can pull it's data from there, and to change which columns you're showing, change the view. You would have to change the presentation side of things to account for the different columns also.

Answer 2

Dynamic column inclusion/exclusion == dynamic SQL. This solution might give you some ideas. http://tkyte.blogspot.com/2006/01/i-like-online-communities.html - he passes a ref_cursor to a function that returns a CLOB that is fully formatted HTML table with full resultset of that ref_cursor. All in less than 100 lines.

Answer 3

As jva said, you'll need to use dynamic SQL. Something like this (with the appropriate bug fixes) should do it:

type column_table is table of varchar2(30);

function table_as_html(table_name varchar2, columns column_table) return clob is
  sql_query  varchar2(32767);
  sql_cursor sql_refcursor;
  html_row   clob;
  html_text  clob;
begin
  sql_query := 'select ''<tr>';
  for column in 1 .. columns.count loop
    sql_query := sql_query||'||''<td>''||'||columns(column)||'||''</td>'''
  end loop;
  sql_query := sql_query||'||''</tr>'' from '||table_name;

  open sql_cursor for sql_query;
  loop
    fetch sql_cursor into html_row;
    exit when sql_cursor%notfound;

    html_text := html_text||html_row||chr(10);
  end loop;
  close sql_cursor;

  return html_text;
end;