SQL 替换 BLOB 列中的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16337814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replacing text in a BLOB Column
提问by Alon Adler
In one of our tables we have a HUGEBLOBColumn (Column name is DYNAMIC_DATA) which holding an XML data. What I need to do is updating a certain part of the text from within this BLOB.
在我们的一个表中，我们有一个HUGEBLOBColumn（列名称是DYNAMIC_DATA），其中包含一个 XML 数据。我需要做的是从这个 BLOB 中更新文本的某个部分。
I've tried this query:
我试过这个查询：
UPDATE ape1_item_version
SET DYNAMIC_DATA = REPLACE (DYNAMIC_DATA,'Single period','Single period period set1')
WHERE name = 'PRIT ALL POOL for Duration Telephony 10_NA_G_V_H_N_Z2'
But I get the following error:
但我收到以下错误：
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
How can I execute REPLACEon the BLOB ?
如何REPLACE在 BLOB 上执行？
回答by Vincent Malgrat
REPLACEworks on the following datatypes:
REPLACE适用于以下数据类型：
Both search_string and replacement_string, as well as char, can be any of the data types
CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, orNCLOB.
search_string 和 replacement_string 以及 char 都可以是任何数据类型
CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、 或NCLOB。
You have chosen to store character data as a collection of bytes (BLOB). These can not be worked on directly because a BLOB has no context and is only a very very big number. It can't be converted to characters without yourinput: you need its character set to convert binary data to text.
您已选择将字符数据存储为字节集合 (BLOB)。这些不能直接处理，因为 BLOB 没有上下文并且只是一个非常非常大的数字。没有您的输入，它无法转换为字符：您需要其字符集才能将二进制数据转换为文本。
You'll have to either code the function REPLACEyourself (using DBMS_LOB.instrfor instance) or convert your data to a workable CLOB and use standard functions on the CLOB.
您必须REPLACE自己编写函数（DBMS_LOB.instr例如使用）或将数据转换为可行的 CLOB 并在 CLOB 上使用标准函数。
I would advise strongly to change the datatype of your column. This will prevent any further character set conversion error you will likely run into in the future.
我强烈建议您更改列的数据类型。这将防止您将来可能遇到的任何进一步的字符集转换错误。
If you really want to work with blobs, use functions like these:
如果您真的想使用 blob，请使用以下函数：
SQL> CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
2 l_clob CLOB;
3 l_dest_offset NUMBER := 1;
4 l_src_offset NUMBER := 1;
5 l_lang_context NUMBER := dbms_lob.default_lang_ctx;
6 l_warning NUMBER;
7 BEGIN
8 dbms_lob.createtemporary(l_clob, TRUE);
9 dbms_lob.converttoclob(dest_lob => l_clob,
10 src_blob => l_blob,
11 amount => dbms_lob.lobmaxsize,
12 dest_offset => l_dest_offset,
13 src_offset => l_src_offset,
14 blob_csid => nls_charset_id('AL32UTF8'),
15 lang_context => l_lang_context,
16 warning => l_warning);
17 RETURN l_clob;
18 END convert_to_clob;
19 /
Function created
SQL> CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
2 l_blob BLOB;
3 l_dest_offset NUMBER := 1;
4 l_src_offset NUMBER := 1;
5 l_lang_context NUMBER := dbms_lob.default_lang_ctx;
6 l_warning NUMBER;
7 BEGIN
8 dbms_lob.createtemporary(l_blob, TRUE);
9 dbms_lob.converttoblob(dest_lob => l_blob,
10 src_clob => l_clob,
11 amount => dbms_lob.lobmaxsize,
12 dest_offset => l_dest_offset,
13 src_offset => l_src_offset,
14 blob_csid => nls_charset_id('AL32UTF8'),
15 lang_context => l_lang_context,
16 warning => l_warning);
17 RETURN l_blob;
18 END convert_to_blob;
19 /
Function created
You can call these functions directly from SQL:
您可以直接从 SQL 调用这些函数：
SQL> UPDATE ape1_item_version
2 SET DYNAMIC_DATA = convert_to_blob(
3 REPLACE(convert_to_clob(DYNAMIC_DATA),
4 'Single period',
5 'Single period period set1')
6 )
7 WHERE NAME = 'PRIT ALL POOL for Duration Telephony 10_NA_G_V_H_N_Z2';
1 row updated
回答by Dinesh
We can use something like the below query also with Oracle 11 and above if the blob object is of text.
如果 blob 对象是文本，我们也可以在 Oracle 11 及更高版本中使用类似以下查询的内容。
`UPDATE table_name
SET text_blob_column-name = UTL_RAW.CAST_TO_RAW(
REPLACE(UTL_RAW.CAST_TO_VARCHAR2(text_blob_column-name),
'<existing value>',
'<value to update>')
)
WHERE where_clause_Column-name='171';`
