PL/SQL to update all columns

 

undefine schema_name;
declare 
  l_Err varchar2(200);
begin
  for r in (select  atc.table_name, atc.column_name, atc.data_length
            from    all_tab_columns  atc, all_tables tab WHERE atc.owner=tab.OWNER  and atc.table_name=tab.table_name
            and   atc.data_type   = 'VARCHAR2'
            and     atc.char_used   = 'B' --Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C)
--            and     atc.table_name  = 'xx' --TEST
            and     atc.owner       = upper('&&schema_name')) 
  loop
    begin
      execute immediate 'alter table '|| upper('&&schema_name')
                                      || '.'
                                      || r.table_name
                                      || ' modify '
                                      || r.column_name
                                      || ' varchar2('
                                      || r.data_length
                                      || ' char)'; 
    end; 
    commit; 
  end loop; 
end;
/