替换BLOB列中的文本 [英] Replacing text in a BLOB Column

问题描述

在我们的一个表中，我们有一个HUGEBLOB列(列名称为DYNAMIC_DATA)，该列保存XML数据.我需要做的是从此BLOB中更新文本的特定部分.

In one of our tables we have a HUGEBLOB Column (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.

我已经尝试过以下查询:

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'

但是出现以下错误:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

如何在BLOB上执行REPLACE?

How can I execute REPLACE on the BLOB ?

推荐答案

REPLACE 适用于以下数据类型:

REPLACE works on the following datatypes:

search_string和replace_string以及char都可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB中的任何数据类型.

Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

您已选择将字符数据存储为字节集合(BLOB).由于BLOB没有上下文，并且只有一个非常大的数字，所以不能直接处理这些内容一个>.没有您的输入，就无法将其转换为字符:您需要将其字符集转换为将二进制数据转换为文本.

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 your input: you need its character set to convert binary data to text.

您将必须自己编写函数REPLACE的代码(使用 DBMS_LOB.instr )，或将数据转换为可行的CLOB，并在CLOB上使用标准函数.

You'll have to either code the function REPLACE yourself (using DBMS_LOB.instr for instance) or convert your data to a workable CLOB and use standard functions on the 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.

如果您真的想使用Blob，请使用以下函数:

If you really want to work with blobs, use functions like these:

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

您可以直接从SQL调用这些函数:

You can call these functions directly from 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

这篇关于替换BLOB列中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！