title: Oracle RawToHex function - what happens if return value exceeds varchar2 limit?
body: <p>The RawToHex function in Oracle 11g returns a hexadecimal representation of any raw value.
  This function returns the Hex Value as a varchar2.</p>
  <p>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?</p>
  <p>Is there any way to convert very big BLOBs into a Hex representation?</p>
  <p><strong>UPDATE:</strong></p>
  <p>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.</p>
  <pre><code>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)); --&gt; output: 216
  dbms_output.put_line(rawtohex(empty_blob())); --&gt; converted blob
  
  select blob_column into b from a_table where a_table_id = 2;
  dbms_output.put_line(dbms_lob.getlength(b)); --&gt; output: 140000
  dbms_output.put_line(rawtohex(empty_blob())); --&gt; ORA-06502: PL/SQL: numeric or value error
  
  END;
  </code></pre>
  <p>Description of this error according to <a href="http://ora-06502.ora-code.com/" rel="nofollow noreferrer">ora-code.com</a></p>
  <blockquote>
  <p>ORA-06502:    PL/SQL: numeric or value error string</p>
  <p>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).</p>
  <p>Action:    Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.</p>
  </blockquote>
  <p><strong>UPDATE 2:</strong></p>
  <p>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?</p>
  <pre><code>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 '&quot;&quot;';
      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;
  </code></pre>