問題描述

我需要將數(shù)據(jù)附加到我的 BLOB 字段中，如何使用 UPDATE 命令執(zhí)行此操作?我要問的是；是否可以連接 blob 數(shù)據(jù)，以便我最終可以將其設(shè)置為類似的字段更新 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 但它沒有返回值；所以我創(chuàng)建了一個函數(shù)，它給了我指定的 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;
/

推薦答案

您需要使用 DBMS_LOB.createtemporary:

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 

這篇關(guān)于如何在 ORACLE 中使用 SQL UPDATE 命令將 BLOB 數(shù)據(jù)附加/連接到 BLOB 列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！