Re: changing datatype

In article <36A34D64.6FE010BD_at_uk.ibm.com>, Richard <gbdslrba_at_uk.ibm.com> wrote:
>I have a table with about 20 columns (including a long raw) and about
>60000 rows. I want to change the datatype of one of the columns from
>number to varchar2. I understand that the column must be empty to do
>this and so tried exporting the table, clearing the table and changing
>the datatype and then, unsuccessfully, tried reimporting.
>Does anyone know if, and how, it is possible to change a datatype and
>keep the data?

You can change the datatype only if the column has only null values; You can increase the length anytime, but can only decrease it if all values are null. My preferred method (for non-giant tables at least) is:

1. Save off the column data: create table TmpTable as select PrimaryKey, ColumnToSave from MyTable;
2. Null out the data in the original table:
3. drop constraints/indexes on MyTable.ColumnToSave /* if they exist */
4. alter table MyTable modify (ColumnToSave NULL); /* if not nullable */
5. update MyTable set ColumnToSave = NULL;
6. Modify the column datatype or length: alter table MyTable modify (ColumnToSave varchar2(80));
7. Restore the saved column values: update MyTable set ColumnToSave = (select ColumnToSave from TmpTable where TmpTable.PrimaryKey = MyTable.PrimaryKey);
8. Clean up:
9. alter table MyTable modify (ColumnToSave NOT NULL); /* if 2)b used */
10. drop table TmpTable
11. recreate constraints & indexes on MyTable.ColumnToSave/* if 2)a used */

This assumes that MyTable is not being updated. If MyTable is huge, you may need an index on TmpTable.PrimaryKey. If it is really huge, perhaps create one with the new definition, insert as select from original, drop original, rename new to original name, and recreate necessary triggers, constraints, and indexes.

• OraSaurus --
• Remove "not_" to reply... --