Oracle blob - insert long (more than 4000 hexadecimal digits) data into sql command

Question

How can I insert big data (~ 100000 hex-digits) into the Blob field in Oracle 11 , using only the sql command (without any external data with load cluase or such).

 update tablename set fieldname='AA'; 

Jobs - 1 byte;

 update tablename set fieldname='AA...(4000 hex-digits)...AA'; 

does not. Niether Concat helps; strings cannot be more than 4000 characters. Is there any other way using only sql command?

Answer 1

As far as I know, this is not possible. What you can do is:

• select blob from table
• get blob from the results.
• get output stream from blob
• write to stream
• flush and blob update in table
• commit

You should be able to replace steps 1-2 by creating a temporary blob and using this for recording and updating.

Answer 2

 DECLARE buf RAW(100000); BEGIN buf := hextoraw('626567696E2030207575656E636F64652E6275660D0A6'); UPDATE tableName SET columnName = buf; --or INSERT INTO tableName(charColumn, BlobColumn) values ('Sting', buf); END;