Hello,
I have a pretty dumb situation. We have many tables that have varchar2 fields and I need
to convert them to clobs. However, whenever I try to issue
ALTER TABLE table1
MODIFY column_name CLOB;
I get
ORA - 22858 - Invalid alteration of datatype
It seems I can't change the datatype, although they are compatible.
The best I can do this way is to increase scale to varchar2(4000).
I took another approach and I created exact copy of table1 and filled it with table1 data and then tried to drop table1, but I got
ora-02449 unique primary keys in table referenced by foreign keys
because there are a lot of foreign keys pointing to it.
I tried to disable them and then drop the main table, but this still did not help.
ora-02449 unique primary keys in table referenced by foreign keys
was coming up all the same.
Because there are quite a few tables where I have to make datatype adjustment, I'm about to create a procedure that would save foreign key constraints in collections, drop them and then use the names again for the new table.
However, I started wondering is this really necessary and is there something that I am missing that would simplify the process.
Don't know if it is importaint , but I'm running 10g.
Thanks in advance for any replies and suggestions