Oracle 11g中的RawToHex函数返回任何原始值的十六进制表示形式.
此函数将Hex值作为varchar2返回.
如果我将BLOB传递给RawToHex()函数会导致十六进制表示超过varchar2限制4000,会发生什么?
有没有办法将非常大的BLOB转换为十六进制表示?
更新:
我做了一些调查,找到了问题第一部分的答案.
我可以将BLOB传递给RawToHex函数,只要您不会触及Raw DataType的边界,这个就会成功执行. Oracle似乎隐式地从BLOB转换为Raw.
DECLARE
a varchar2(32767);
b blob;
BEGIN
select blob_column into b from a_table where a_table_id = 1;
dbms_output.put_line(dbms_lob.getlength(b)); --> output: 216
dbms_output.put_line(rawtohex(empty_blob())); --> converted blob
select blob_column into b from a_table where a_table_id = 2;
dbms_output.put_line(dbms_lob.getlength(b)); --> output: 140000
dbms_output.put_line(rawtohex(empty_blob())); --> ORA-06502: PL/SQL: numeric or value error
END;
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
更新2:
我有一个解决这个问题的方法.将blob拆分为更小的块并逐步转换它们.它提供了正确的结果.这是一种正确的方法还是可以在某个时候解决这个问题?
function BlobToHex(data in blob) return clob
is
v_clob clob;
v_start pls_integer := 1;
v_buffer pls_integer := 4000;
begin
if data is null
then
return '""';
end if;
dbms_lob.createtemporary(v_clob, true);
dbms_lob.append(v_clob, '0x');
for i in 1..ceil(dbms_lob.getlength(data) / v_buffer)
loop
dbms_lob.append(v_clob, rawtohex(DBMS_LOB.SUBSTR(data, v_buffer, v_start)));
v_start := v_start + v_buffer;
end loop;
return v_clob;
end;