Sometimes users have strange requests that I avoid asking the reason.
Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.
This reminded me from university times, when doing SQL exercises.
With Oracle 11g and listagg function, it is quite easy to fulfill the request:
set serveroutput on;
declare
col_list varchar(2500);
result number;
begin
select 'select round('||listagg('nvl(avg(length('||column_name||')),0)','+') within group (order by column_id)||')
from MYAPP.mytable
where creationdate > to_date(''01.01.2018'',''dd.mm.yyyy'')
and creationdate < to_date(''31.12.2018'',''dd.mm.yyyy'')' into col_list
from all_tab_columns where owner='MYAPP' and table_name='MYTABLE' ;
execute immediate (col_list) into result;
dbms_output.put_line(result);
end;
/