Change all datatypes in a table
You can use a bit of dynamic SQL.  Assuming that you want to modify every VARCHAR2 column in one table

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR c IN (SELECT *
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = &lt;&lt;table name&gt;&gt;
               AND char_used = 'B')
  LOOP
    l_sql_stmt := 
       'ALTER TABLE &lt;&lt;table name&gt;&gt; MODIFY( ' || 
          c.column_name || ' VARCHAR2( ' || c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;