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;

/