how to update all columns of a table to uppercase?

Question

there is a table with about 800 columns that I have to update to upper case, do you know a "quick" way to do it?

Maybe with PLSQL but I can't think of how to do it.

Thanks!

Answer 1

Probably something like this would work. Using xmlagg because listagg would run out of space. You could also do a PL/SQL loop over user_tab_columns for your table if you needed something more detailed.

declare
    my_table varchar2(30) := 'TABLE1';
    command clob;
begin
    select to_clob('update ') || my_table || ' set ' 
        || rtrim(xmlagg(xmlelement(e,column_name || '=UPPER(' || column_name || '), ') order by column_id).extract('//text()').getclobval(),', ') as cols
        into command
    from user_tab_columns where table_name = my_table;

    execute immediate command;
end;
/

Try running the select statement by itself first (with your table name, and no into) if you want to run the update statement yourself.