oracle 如何在Oracle中转换列的字符集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5296257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert characterset of column in Oracle
提问by ImranRazaKhan
I have a table in which our service provider insert UNICODE data but my oracle characterset is WE8ISO8859P1.
我有一个表，我们的服务提供商在其中插入了 UNICODE 数据，但我的 oracle 字符集是 WE8ISO8859P1。
Now to get that data i used following function of oracle but it is displaying ???????
现在要获取我使用以下 oracle 函数的数据，但它正在显示 ???????
select CONVERT(message,'AL32UTF8','WE8ISO8859P1') from client_campaigns
从 client_campaigns 中选择 CONVERT(message,'AL32UTF8','WE8ISO8859P1')
one thing more message coulmn is of CLOB type.
一件事更多的消息是 CLOB 类型。
I cant change characterset of my Database due to dataloss and second its in production and changes in characterset may lead to errors.
由于数据丢失，我无法更改我的数据库的字符集，其次在生产中，字符集的更改可能会导致错误。
Now please guide how i can get this data as UNICODE?
现在请指导我如何将这些数据作为 UNICODE 获取？
Regards, imran
问候，伊姆兰
回答by Vincent Malgrat
Strings inserted in a character (VARCHAR2 or CHAR or CLOB) column 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 unavailable in your character set are transformed into ?upon insert.
插入到字符（VARCHAR2 或 CHAR 或 CLOB）列中的字符串将始终转换为数据库字符集。这意味着在您的情况下，插入的数据将转换为 WE8ISO8859P1。由于 UNICODE 不是 WE8ISO8859P1 的子集，您将丢失信息。某些字符集中不可用的字符?在插入时被转换。
What should you do? There are a couple of options for new data:
你该怎么办？新数据有几个选项：
- Modify the datatype of the column to NVARCHAR2 instead of VARCHAR2 (or NCLOB instead of CLOB). NVARCHAR2is specifically designed so you can handle multi-byte characters without modifying your main db character set. See this SO questionfor differences between VARCHAR2 and NVARCHAR2). Also be aware that some applications may not work correctly with NVARCHAR2.
- You could modify the column to a RAW or BLOB and write directly your string as a binary stream. When you read it again it will still be UNICODE data. It will be difficult for the database to do anything with this column data however: sorting will be binary, searching will be problematic as you won't be able to use the LIKE operator properly.
- If you have lots of UNICODE input, you could consider modifying your database character set. This would be the most costly option (you will probably need to export/reinstall/import) but afterwards all your columns will have the correct datatype.
- 将列的数据类型修改为 NVARCHAR2 而不是 VARCHAR2（或 NCLOB 而不是 CLOB）。NVARCHAR2是专门设计的，因此您可以在不修改主数据库字符集的情况下处理多字节字符。请参阅此 SO 问题以了解 VARCHAR2 和 NVARCHAR2 之间的差异）。另请注意，某些应用程序可能无法与 NVARCHAR2 一起正常工作。
- 您可以将该列修改为 RAW 或 BLOB，然后将您的字符串作为二进制流直接写入。当您再次阅读时，它仍然是 UNICODE 数据。然而，数据库很难对这个列数据做任何事情：排序将是二进制的，搜索将有问题，因为您将无法正确使用 LIKE 运算符。
- 如果您有大量 UNICODE 输入，您可以考虑修改您的数据库字符集。这将是最昂贵的选项（您可能需要导出/重新安装/导入），但之后您的所有列都将具有正确的数据类型。
I would go with option (1) or (3) if given the choice. Working with RAW disables a lot of features and adds complexity.
如果有选择，我会选择选项（1）或（3）。使用 RAW 会禁用许多功能并增加复杂性。
Obviously prior data will be impossible to restore with only the data available to the database: you will have to reimport old data in the new structure.
显然，只有数据库可用的数据无法恢复先前的数据：您必须在新结构中重新导入旧数据。
