如何在 Oracle 查询中将 CLOB 转换为 UTF8？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46915028/
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 CLOB to UTF8 in an Oracle query?
提问by José Carlos
I've got a query with a CLOB field which I want to return her value in UTF8 format. The next query works fine if the field are varchar, for example, but if it is CLOB doesn't return a correct UTF8 string.
我有一个带有 CLOB 字段的查询，我想以 UTF8 格式返回她的值。例如，如果字段是 varchar，则下一个查询可以正常工作，但如果是 CLOB，则不会返回正确的 UTF8 字符串。
select convert(field, 'AL32UTF8', 'WE8ISO8859P15') from table;
How can I do to return a UTF8 string from a CLOB in a query?
如何从查询中的 CLOB 返回 UTF8 字符串？
采纳答案by Needle file
use dbms_lob package for it
使用 dbms_lob 包
for example
例如
select convert(dbms_lob.substr(field,dbms_lob.getlength(field), **0**),
'AL32UTF8',
'WE8ISO8859P15')
from table;
Fixed it:
修复：
select convert(dbms_lob.substr(field,dbms_lob.getlength(field)),
'AL32UTF8',
'WE8ISO8859P15')
from table;
回答by D. Mika
Use DBMS_LOB.CONVERTTOBLOB.
使用 DBMS_LOB.CONVERTTOBLOB。
From the oracle documentation:
从oracle文档：
Oracle discourages the use of the CONVERT function in the current Oracle Database release. The return value of CONVERT has a character datatype, so it should be either in the database character set or in the national character set, depending on the datatype. Any dest_char_set that is not one of these two character sets is unsupported. …
Oracle 不鼓励在当前 Oracle 数据库版本中使用 CONVERT 函数。CONVERT 的返回值有一个字符数据类型，因此它应该在数据库字符集中或国家字符集中，这取决于数据类型。不支持任何不是这两个字符集之一的 dest_char_set。…
If you need a character datatype like CLOB in a character set that differs from those the database is setup with it should be converted into a BLOB. This is where DBMS_LOB.CONVERTTOBLOB comes in.
如果您需要一个字符数据类型，如 CLOB 的字符集与数据库设置的字符集不同，则应将其转换为 BLOB。这就是 DBMS_LOB.CONVERTTOBLOB 的用武之地。
If you need a function that returns a BLOB you have to wrap CONVERTTOBLOB into your own function. For example:
如果您需要一个返回 BLOB 的函数，您必须将 CONVERTTOBLOB 包装到您自己的函数中。例如：
CREATE OR REPLACE FUNCTION clob_to_blob (p_clob CLOB, p_charsetname VARCHAR2)
RETURN BLOB
AS
l_lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
l_warning INTEGER;
l_dest_offset NUMBER := 1;
l_src_offset NUMBER := 1;
l_return BLOB;
BEGIN
DBMS_LOB.createtemporary (l_return, FALSE);
DBMS_LOB.converttoblob (
l_return,
p_clob,
DBMS_LOB.lobmaxsize,
l_dest_offset,
l_src_offset,
CASE WHEN p_charsetname IS NOT NULL THEN NLS_CHARSET_ID (p_charsetname) ELSE DBMS_LOB.default_csid END,
l_lang_ctx,
l_warning);
RETURN l_return;
END;
This allows queries like:
这允许查询，如：
SELECT clob_to_blob (field, 'UTF8') FROM t;
To get a list of supported values for the character set name use:
要获取字符集名称支持的值列表，请使用：
SELECT *
FROM v$nls_valid_values
WHERE parameter = 'CHARACTERSET'
