Oracle 10: Using HEXTORAW to fill in blob data

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.


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.