How can I append/concatenate BLOB data to a BLOB column using SQL UPDATE command in ORACLE

user_751937 0
Viewed 32779

I need to append data to my BLOB field, how can I do this using an UPDATE command? What i am asking is; is it possible to concatenate blob data so that i can eventually set it to a field like UPDATE BLOB_table SET BLOB_field = BLOB_field + BLOB_data

I tried using DBMS_LOB.APPEND but it does not return a value; so i created a function which gives me an error of "invalid LOB locator specified"

CREATE OR REPLACE FUNCTION MAKESS.CONCAT_BLOB(A in BLOB,B in BLOB) RETURN BLOB IS
 C BLOB;
BEGIN
DBMS_LOB.APPEND(c,A);
DBMS_LOB.APPEND(c,B);
RETURN c;
END;
/
append blob oracle
2 Answers

With help of PL/SQL blob can be updated in place with no need for custom function at all:

BEGIN
   FOR c IN (select a, b from t where a is not null for update) LOOP
       DBMS_LOB.APPEND(c.a, c.b);
   END LOOP;
END;
/
user_6035160
Related