Change oracle data type
Run through the following steps, substituting your table and field names, and the required datatype.
-- Create a new temporary holding field with the target datatype
ALTER TABLE table_name ADD temporary_field VARCHAR2(100) NULL;
-- Move the data to the new field and cast to the new datatype accordingly
UPDATE table_name SET temporary_field=TO_CHAR(original_field);
-- If the original_field has dependent constraints, they
-- must be dropped now.
-- If the original_field is the primary key -- Drop the primary key constraint
ALTER table table_name DROP PRIMARY KEY;
-- If the original field cannot be null
-- Update the not null constraint for the original field
ALTER TABLE table_name MODIFY original_field NULL;
-- Delete everything in the original field
UPDATE table_name SET original_field=NULL;
-- Modify the datatype of the original field
ALTER TABLE table_name MODIFY original_field VARCHAR2(100);
-- Move the data back to the original field
UPDATE table_name SET original_field=temporary_field;
-- Add the not null constraint back if necessary
ALTER TABLE table_name MODIFY original_field NOT NULL;
-- Add the primary key back if necessary
ALTER TABLE table_name
ADD PRIMARY KEY (original_field);
-- Drop the temporary holding field
ALTER TABLE table_name DROP COLUMN temporary_field;
-- Done.