如何在ORACLE中使用SQL UPDATE命令将BLOB数据附加/连接到BLOB列 [英] How can I append/concatenate BLOB data to a BLOB column using SQL UPDATE command in ORACLE
问题描述
我需要将数据附加到我的BLOB字段中, 如何使用UPDATE命令来做到这一点? 我要问的是;是否有可能连接Blob数据,以便我最终可以将其设置为像 更新BLOB_table 放 BLOB_field = BLOB_field + BLOB_data
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
我尝试使用DBMS_LOB.APPEND,但是它没有返回值;所以我创建了一个函数,该函数给我一个指定了无效的LOB定位器"的错误
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;
/
推荐答案
You need to create a temporary blob with DBMS_LOB.createtemporary
:
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
然后,您应该可以在更新语句中使用它:
Then you should be able to use it in an update statement:
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
这篇关于如何在ORACLE中使用SQL UPDATE命令将BLOB数据附加/连接到BLOB列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!