sqloracle-databaseblobvarbinary

Oracle 10: Using HEXTORAW to fill in blob data

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.

Solution

  • Apparently you can exceed these limits if you use PL/SQL. It doesn't work if you do the HEXTORAW within the UPDATE statement directly, either--it needs to be done in a separate statement, like this:

    DECLARE
  buf RAW(4000); 
BEGIN
  buf := HEXTORAW('C2B97041074...lots of hex...0CC00CD00');
  UPDATE MyTable
     SET blobData = buf
   WHERE ID = 462;
END;

    For the life of me I'll never understand some of Oracle's limitations. It's like everything is its own little special case.