Oracle RawToHex function - what happens if return value exceeds varchar2 limit?

Question

The RawToHex function in Oracle 11g returns a hexadecimal representation of any raw value. This function returns the Hex Value as a varchar2.

What happens if I pass a BLOB into the RawToHex() function that will result in a hex representation that exceeds the varchar2 limit of 4000?

Is there any way to convert very big BLOBs into a Hex representation?

UPDATE:

I did some investigation and found an answer for the first part of my question. I can pass a BLOB into the RawToHex function and this one will execute successfully as long as you won't hit the boundaries of the Raw DataType. Oracle seems to convert from BLOB to Raw implicitly.

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 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.

UPDATE 2:

I've got a solution for this issue. Splitting the blob into smaller blocks and converting them step by step. It delivers the correct result. Is this a correct approach or could this solution fall over 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;

Answer 1

You'll get ORA-00932

SQL> select rawtohex(empty_blob()) from dual;
select rawtohex(empty_blob()) from dual
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB
SQL>

because

As a SQL built-in function, RAWTOHEX accepts an argument of any scalar data type other than LONG, LONG RAW, CLOB, BLOB, or BFILE.

As stated by the documentation.