title: Oracle 10: Using HEXTORAW to fill in blob data
body: <p>We have a table in Oracle with a BLOB column that needs to be filled with a small amount of arbitrary byte data--we will never put in more than 4000 bytes of data. </p>
  
  <p>I am working with an existing C++ OCI-based infrastructure that makes it extremely difficult to use bind variables in certain contexts, so I need to populate this BLOB column using only a simple query. (We are working to modernize it but that's not an option today,)</p>
  
  <p>We had some luck with a query like this:</p>
  
  <pre><code>UPDATE MyTable
     SET blobData = HEXTORAW('0EC1D7FA6B411DA5814...lots of hex data...0EC1D7FA6B411DA5814')
   WHERE ID = 123;
  </code></pre>
  
  <p>At first, this was working great. However, recently we encountered a case where we need to put in more than 2000 bytes of data. At this point, we hit an Oracle error, <code>ORA-01704: string literal too long</code> because the string being passed to <code>HEXTORAW</code> was over 4000 characters. I tried splitting up the string and then concatenating with <code>||</code>, but this didn't dodge the error.</p>
  
  <p>So, I need a way to update this column and fill it with more than 2000 bytes' worth of data using a simple query. Is it possible? </p>
  
  <p>(I know if I had bind variables at my disposal it would be trivial--and in fact other apps which interact with this table use that exact technique--but unfortunately I am not in a position to refactor the DB guts here. Just need to get data into the table.)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>One promising approach that didn't work was concatenating RAWs:</p>
  
  <pre><code>UTL_RAW.CONCAT(HEXTORAW('...'), HEXTORAW('...'), HEXTORAW('...'))
  </code></pre>
  
  <p>This dodges the string-length limit, but it appears that Oracle also has a matching internal 2000 byte limit on the length of a <code>RAW</code>. So I can't populate the blob with a <code>RAW</code>. Maybe there is a function that concatenates multiple <code>RAW</code>s into a <code>BLOB</code>.</p>