oracle 将 Blob 转换为 Clob
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12849025/
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
convert blob to clob
提问by user1739469
I'm using oracle 11g and I'm trying to find out the length of a text. I will normally use select length(myvar) from table, but I can't do that.
我正在使用 oracle 11g 并试图找出文本的长度。我通常会使用 select length(myvar) from table，但我不能这样做。
The table which I want to query has a BLOB column that saves characters or photos. I want to know the number of characters that have my BLOB column.
我要查询的表有一个 BLOB 列，用于保存字符或照片。我想知道我的 BLOB 列的字符数。
I tried to convert my BLOB into a char using UTL_RAW.CAST_TO_VARCHAR2(myblob) from table, but this functions isnt't working correctly or maybe I'm making a mistake.
我尝试使用表中的 UTL_RAW.CAST_TO_VARCHAR2(myblob) 将我的 BLOB 转换为字符，但此函数无法正常工作，或者我可能犯了错误。
For example: My BLOB have the word Section, when I see this in the databse in the hexadecimal form I see S.e.c.t.i.o.n.. I dont'k know why it have those points in between each letter. Then I used the this query
例如：我的 BLOB 有“节”这个词，当我在数据库中以十六进制形式看到它时，我会看到“节”。我不知道为什么每个字母之间都有这些点。然后我使用了这个查询
select UTL_RAW.CAST_TO_VARCHAR2(myblob)
from table
The result of this query is 'S' so it's not the complete word that my BLOB has, and when I make this query
这个查询的结果是“S”，所以它不是我的 BLOB 所拥有的完整词，当我进行这个查询时
select length(UTL_RAW.CAST_TO_VARCHAR2(myblob))
from table
the result is 18 and the word Sections doesn't have 18 characters.
结果是 18 并且单词 Sections 没有 18 个字符。
I was trying to convert the blob into a varchar, although I think my best choise would be a clob because the length of the text that it can save is more than the limit that varchar has. I tried to do that by making this query (I'm not sure if this is correct but is what I found in the internet)
我试图将 blob 转换为 varchar，尽管我认为我最好的选择是 clob，因为它可以保存的文本长度超过 varchar 的限制。我试图通过进行此查询来做到这一点（我不确定这是否正确，但这是我在互联网上找到的）
select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(myblob, 32767, 1))
from table
This query also returns 'S'
此查询还返回“S”
I hope you can help me with this problem. thanks for advanced
我希望你能帮我解决这个问题。感谢先进
回答by Rene
For anyone coming to this thread and wants to know how to convert a blob to a clob. Here is an example.
对于来到此线程并想知道如何将 blob 转换为 clob 的任何人。这是一个例子。
create function clobfromblob(p_blob blob) return clob is
l_clob clob;
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createTemporary(lob_loc => l_clob
,cache => false);
dbms_lob.converttoclob(dest_lob => l_clob
,src_blob => p_blob
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offsset
,src_offset => l_src_offsset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
return l_clob;
end;
回答by Craig
To convert blob to clob, try this:
要将 blob 转换为 clob，请尝试以下操作：
SELECT TO_CLOB(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(MYBLOB,2000)))
FROM MYTABLE;
回答by Justin Cave
SELECT DBMS_LOB.GetLength( myblob ) length_in_bytes
FROM table
will return the length of the BLOB in bytes. It sounds like the character data in your BLOB is probably encoded using the UTF-16 character set so the number of bytes is probably twice the number of characters (depending on the version of Unicode that is being used and the specific data being stored, some characters might require 4 bytes of storage but it is relatively unlikely that you're dealing with any of those characters).
将返回 BLOB 的长度（以字节为单位）。听起来您的 BLOB 中的字符数据可能是使用 UTF-16 字符集编码的，因此字节数可能是字符数的两倍（取决于正在使用的 Unicode 版本和存储的特定数据，某些字符可能需要 4 个字节的存储空间，但您处理这些字符中的任何一个的可能性相对较小）。
You can use the DBMS_LOB.ConvertToClobprocedure to convert a BLOB to a CLOB (though since this is a procedure, you'll need to call it in a PL/SQL block). As part of that conversion, you'll almost certainly need to specify the character set that the data is encoded in-- my assumption is that your application is using the UTF-16 character set but that's just an assumption.
您可以使用该DBMS_LOB.ConvertToClob过程将 BLOB 转换为 CLOB（尽管这是一个过程，您需要在 PL/SQL 块中调用它）。作为该转换的一部分，您几乎肯定需要指定数据编码的字符集——我假设您的应用程序使用的是 UTF-16 字符集，但这只是一个假设。
回答by Punnerud
Only converting to CLOB:
仅转换为 CLOB：
select TO_CLOB(UTL_RAW.CAST_TO_VARCHAR2(YOURCLOB)) from DUAL;
Inspired by Craig without limitation on size.
灵感来自克雷格，没有大小限制。
