Oracle | ORA-01439: column to be modified must be empty to change datatype. Unable to alter datatype number to nvarchar In oracle 10g

Question

I want it without data deleting and moving.

I am Unable to alter datatype number to nvarchar In oracle 10g. its displaying message like ORA-01439: column to be modified must be empty to change datatype.

I have data in table and column is already set NOT NUll so i cannot set it null or blank. And Altering column is primary key which is related number of tables to foreign key.

Is there any quick solution to Fix it.

Answer 1

Suggested way:

1. Suspend constraints
2. Create another column with nvarchar type
3. Copy from number column to the new nvarchar column
4. Delete number column
5. Rename nvarchar column to the old one
6. Rebuild indexes
7. Activate constraints.

You cannot change a column type in oracle other way (that i know about).

Answer 2

1. Add the new column with a new name.
2. Update the new column from old column.
3. Drop the old column.
4. Rename the new column with the old column name.

NOTE I see you have also mentioned Oracle 11g tag, however your question says you are on 10g. If you are on 11g or up, you could use VIRTUAL COLUMN.