Oracle 10: Using HEXTORAW to fill in blob data

Question

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.

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,)

We had some luck with a query like this:

UPDATE MyTable
   SET blobData = HEXTORAW('0EC1D7FA6B411DA5814...lots of hex data...0EC1D7FA6B411DA5814')
 WHERE ID = 123;

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, ORA-01704: string literal too long because the string being passed to HEXTORAW was over 4000 characters. I tried splitting up the string and then concatenating with ||, but this didn't dodge the error.

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?

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

EDIT:

One promising approach that didn't work was concatenating RAWs:

UTL_RAW.CONCAT(HEXTORAW('...'), HEXTORAW('...'), HEXTORAW('...'))

This dodges the string-length limit, but it appears that Oracle also has a matching internal 2000 byte limit on the length of a RAW. So I can't populate the blob with a RAW. Maybe there is a function that concatenates multiple RAWs into a BLOB.

Answer 1

To update a BLOB longer than 16383 bytes something like this may by used (each line has even number of hex digits up to 32766):

DECLARE
  buf BLOB; 
BEGIN
  dbms_lob.createtemporary(buf, FALSE);
  dbms_lob.append(buf, HEXTORAW('0EC1D7FA6B411DA58149'));
  --...lots of hex data...
  dbms_lob.append(buf, HEXTORAW('0EC1D7FA6B411DA58149'));
  UPDATE MyTable
     SET blobData = buf
   WHERE ID = 123;
END;

now the limit is only the size of the statement, which might by imposed by operating environment (e.g. SQLPlus, Pro*C, VB, JDBC...). For very big statements, PL/SQL may also fail with "out of Diana nodes" error.