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.