Change the column data type in the production database

Question

I am looking for a better way to change the data type of a column in a populated table. Oracle only allows you to change the data type in columns with null values.

My solution, so far, is the PLSQL statement, which stores the data of a column to be modified in a collection, modifies the table and then iterates through the collection, restoring the original data with the converted data type.

-- 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;
/

I am looking for a more experienced way to do this.

Answer 1

The decision is incorrect. The alter table statement performs implicit commit. Thus, the solution has the following problems:

• alter table , alter table, .

- . ​​

Answer 2

. , . - , .

:

• .
• .
• .
• .