Oracle change column type from CLOB to NCLOB

Question

We are using Oracle and we have a requirement to allow greek characters to be stored in the DB. Currently, our DB instance doesn't let us insert greek characters such as 'ϕ'. On googling, I found that it is to do with the character set. My oracle uses NLS_CHARACTERSET - WE8MSWIN1252 that doesn't support greek characters. I will have to change the character set to one of AL32UTF8, UTF8, AL16UTF16 or WE8ISO8859P7 if it has to work. Now that we have so much of data in the DB already, it would be a risk to change the character set now.

The other option I have is to change the column type (used to insert greek) from CLOB or VARCHAR2 to NVARCHAR2 and it works fine.

Before changing the column type, I want to know what are the risks involved in changing column type from CLOB to NVARCHAR2 and what are the things I need to keep in mind before changing.

Also, I would like to know the pros and cons of changing my existing character set to AL32UTF8.

EDIT:

There is also an option of changing CLOB to NCLOB and this seems to be less risky as both are closely related (almost same) types. Please do let me know the pros and cons of changing CLOB to NCLOB.

Answer 1

Ok. I was googling and posting Qs in other forums and got a much needed answer in here.

https://www.toolbox.com/tech/oracle/question/migrating-clob-to-nclob-010917/

Answer 2

So I just encountered this myself and I had issues with the above solution as it didn't copy across the foreign keys and constraints of my other columns. To get around this I did the following:

1. Created a new column for my NCLOB data:

ALTER TABLE table_name
ADD new_table_column NCLOB;

2. I then copied my CLOB data into my new NCLOB data column using the TO_NCLOB() function:

UPDATE table_name
SET new_table_column = TO_NCLOB(old_table_column);

3. Finally I dropped the old column and then renamed my new column to my old column name:

ALTER TABLE table_name
DROP COLUMN old_table_column;

ALTER TABLE table_name
RENAME COLUMN new_table_column TO old_table_column;

Please make sure you backup your data if you do this though as dropping the column will get rid of it and commit any transactions you've got

I also did this in Oracle so the syntax may differ slightly in other versions of SQL.