oracle更新blob文件,如何使用ORACLE中的SQL UPDATE命令将BLOB数据附加到BLOB列

您需要使用

DBMS_LOB.createtemporary创建一个临时blob：

sql> CREATE OR REPLACE FUNCTION CONCAT_BLOB(A IN BLOB,B IN BLOB) RETURN BLOB IS

2 C BLOB;

3 BEGIN

4 dbms_lob.createtemporary(c,TRUE);

5 DBMS_LOB.APPEND(c,A);

6 DBMS_LOB.APPEND(c,B);

7 RETURN c;

8 END;

9 /

Function created

那么你应该可以在update语句中使用它：

sql> CREATE TABLE t (a BLOB,b BLOB,c BLOB);

Table created

sql> INSERT INTO t VALUES

2 (utl_raw.cast_to_raw('aaa'),utl_raw.cast_to_raw('bbb'),NULL);

1 row inserted

sql> UPDATE t SET c=CONCAT_BLOB(a,b);

1 row updated

sql> SELECT utl_raw.cast_to_varchar2(a),2 utl_raw.cast_to_varchar2(b),3 utl_raw.cast_to_varchar2(c)

4 FROM t;

UTL_RAW.CAST_TO_VARCHAR2(A UTL_RAW.CAST_TO_VARCHAR2(B UTL_RAW.CAST_TO_VARCHAR2(C

-------------------------- -------------------------- --------------------------

aaa bbb aaabbb