How to convert column characters to Oracle

Question

I have a table in which our service provider inserts UNICODE data, but my oracle character set is WE8ISO8859P1.

Now, to get this data, I used the following oracle function, but it displays ???????

select CONVERT (message, 'AL32UTF8', 'WE8ISO8859P1') from client_campaigns

another coulmn message is of type CLOB.

I cannot change the character set of my database due to dataloss and its second in production, and changes in the character set can lead to errors.

Now please tell me how can I get this data as UNICODE?

Regards, imran

Answer 1

Rows inserted in a character column (VARCHAR2 or CHAR or CLOB) will always be converted to the database character set. This means that the inserted data is converted to WE8ISO8859P1 in your case. Since UNICODE is not a subset of WE8ISO8859P1, you will lose information. Some characters that are not available in your character set are converted to ? after insertion.

What should you do? There are several options for new data:

• Change the column data type to NVARCHAR2 instead of VARCHAR2 (or NCLOB instead of CLOB). NVARCHAR2 is specifically designed so that you can handle multi-byte characters without changing your basic db character set. See this SO question for the differences between VARCHAR2 and NVARCHAR2). Also keep in mind that some applications may not work properly with NVARCHAR2 .
• You can change the column in RAW or BLOB and write your row directly as a binary stream. When you read it again, there will still be UNICODE data. However, the database will not do anything with the data of this column: the sorting will be binary, the search will be problematic, since you cannot use the LIKE operator correctly.
• If you have a lot of UNICODE input, you might consider changing the database character set. This will be the most expensive option (you probably need to export / reinstall / import), but subsequently all your columns will have the correct data type.

I would go with option (1) or (3) if I gave a choice. Working with RAW disables many features and adds complexity.

Obviously, the previous data cannot be restored only with the data available for the database: you will have to reimport the old data in the new structure.