sql - Oracle 10: Using HEXTORAW to fill in blob data -

we have table in oracle blob column needs filled small amount of arbitrary byte data--we never put in more 4000 bytes of data.

i working existing c++ oci-based infrastructure makes extremely difficult use bind variables in contexts, need populate blob column using simple query. (we working modernize that's not option today,)

we had luck query this:

update mytable    set blobdata = hextoraw('0ec1d7fa6b411da5814...lots of hex data...0ec1d7fa6b411da5814')  id = 123; 

at first, working great. however, encountered case need put in more 2000 bytes of data. @ point, hit oracle error, ora-01704: string literal long because string being passed hextoraw on 4000 characters. tried splitting string , concatenating ||, didn't dodge error.

so, need way update column , fill more 2000 bytes' worth of data using simple query. possible?

(i know if had bind variables @ disposal trivial--and in fact other apps interact table use exact technique--but unfortunately not in position refactor db guts here. need data table.)

edit:

one promising approach didn't work concatenating raws:

utl_raw.concat(hextoraw('...'), hextoraw('...'), hextoraw('...')) 

this dodges string-length limit, appears oracle has matching internal 2000 byte limit on length of raw. can't populate blob raw. maybe there function concatenates multiple raws blob.

apparently can exceed these limits if use pl/sql. doesn't work if hextoraw within update statement directly, either--it needs done in separate statement, this:

declare   buf raw(4000);  begin   buf := hextoraw('c2b97041074...lots of hex...0cc00cd00');   update mytable      set blobdata = buf    id = 462; end; 

for life of me i'll never understand of oracle's limitations. it's everything own little special case.