在 Oracle 中使用 BLOB 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12801755/
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
Work with BLOB object in Oracle
提问by user1731968
I have an SQL statement like this-
我有一个这样的 SQL 语句-
select utl_encode.utl_encode.base64_encode(IMAGE1)
from IPHONE.accidentreports
where "key" = 66
But when I run it I get this error-
但是当我运行它时，我收到此错误-
ORA-00904: "UTL_ENCODE"."UTL_ENCODE"."BASE64_ENCODE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 2 Column: 8
I want to convert my BLOBobject into BASE64. How could it be done?
我想将我的BLOB对象转换为BASE64. 怎么可能呢？
回答by Vincent Malgrat
Since the UTL_ENCODE.BASE64_ENCODEfunction works on RAWs, it will work with BLOBs up to 32767 bytes in PL/SQLor 4000 bytes in SQL.
由于UTL_ENCODE.BASE64_ENCODE函数适用于 RAW，因此它将适用PL/SQL于 SQL 中最多 32767 字节或 4000 字节的BLOB 。
If your images are larger, you'll have to write your own function. Here's an example:
如果您的图像较大，则必须编写自己的函数。下面是一个例子：
CREATE OR REPLACE FUNCTION base64_encode_blob (p BLOB) RETURN BLOB IS
l_raw RAW(24573);
l_base64 RAW(32767);
l_result BLOB;
l_offset NUMBER := 1;
l_amount NUMBER := 24573;
BEGIN
DBMS_LOB.createtemporary(l_result, FALSE);
DBMS_LOB.open(l_result, DBMS_LOB.lob_readwrite);
LOOP
DBMS_LOB.read(p, l_amount, l_offset, l_raw);
l_offset := l_offset + l_amount;
l_base64 := utl_encode.base64_encode(l_raw);
DBMS_LOB.writeappend(l_result, utl_raw.length(l_base64), l_base64);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN l_result;
END;
/
回答by AnBisw
First of all UTL_ENCODE.BASE64_ENCODEworks on binary representation of a RAWvalue and the function looks like:
首先处理UTL_ENCODE.BASE64_ENCODE一个RAW值的二进制表示，函数如下所示：
UTL_ENCODE.BASE64_ENCODE (
r IN RAW)
RETURN RAW;
So, considering IMAGE1is of RAWtype:
所以，考虑IMAGE1是RAW类型：
SELECT UTL_ENCODE.BASE64_ENCODE(CAST(IMAGE1 AS RAW)) --if IMAGE1 is LOB
FROM IPHONE.accidentreports
WHERE "key" = 66;
More on CASTing here.
更多关于CASTing在这里。
