title: Alter column data type in production database
body: <p>I'm looking for the best way to change a data type of a column in a populated table. Oracle only allows changing of data type in colums with null values.</p>
  
  <p>My solution, so far, is a PLSQL statement which stores the data of the column to be modified in a collection, alters the table and then iterates over the collection, restoring the original data with data type converted.</p>
  
  <pre><code>-- Before: my_table ( id NUMBER, my_value VARCHAR2(255))
  -- After: my_table (id NUMBER, my_value NUMBER)
  
  DECLARE
    TYPE record_type IS RECORD ( id NUMBER, my_value VARCHAR2(255));
    TYPE nested_type IS TABLE OF record_type;
    foo nested_type;
  BEGIN
    SELECT id, my_value BULK COLLECT INTO foo FROM my_table;
    UPDATE my_table SET my_value = NULL;
    EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY my_value NUMBER';
    FOR i IN foo.FIRST .. foo.LAST
    LOOP
      UPDATE my_table 
          SET  = TO_NUMBER(foo(i).my_value) 
          WHERE my_table.id = foo(i).id;
    END LOOP;
  END;
  /
  </code></pre>
  
  <p>I'm looking for a more experienced way to do that.</p>