The RawToHex function in Oracle 11g returns a hexadecimal representation of any source value. This function returns the value of Hex as varchar2.
What happens if I pass the BLOB to the RawToHex () function, which results in a hexadecimal representation that exceeds the varchar2 4000 limit?
Is there a way to convert very large BLOBs to a Hex view?
UPDATE:
I did some investigation and found the answer for the first part of my question. I can pass the BLOB to the RawToHex function, and this one will succeed until you get within the Raw DataType bounds. Oracle seems to implicitly convert from BLOB to 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;
Description of this error according to ora-code.com
ORA-06502: PL / SQL: numeric string or error string
Cause. An arithmetic, numeric, string, conversion, or restriction error has occurred. For example, this error occurs if an attempt is made to assign a NULL value to a variable declared NOT NULL, or if an attempt is made to assign an integer greater than 99 to a variable declared NUMBER (2).
Act. Change the data, how it can be manipulated or how it is declared so that the values do not violate the constraints.
UPDATE 2:
I have a solution to this problem. Separation blob into smaller blocks and their gradual transformation. It gives the correct result. Is this a decision right or can this decision fall at some point?
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;