Replacing text in an BLOB Column

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 withing 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'

But I get the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

How can I execute REPLACE on the BLOB ?

Answers


REPLACE works on the following datatypes:

Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or 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 your input: you need its character set to convert binary data to text.

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.

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

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

Need Your Help

Uninitialized Structures in C

c struct initialization

Are the members of a global or static structure in C guaranteed to be automatically initialized to zero, in the same way that uninitialized global or static variables are?

Android database trouble

android database sqlite

I have couple questions regarding the use of SQLite for my Android App. I think my issues are pretty basic but I just can't seem to figure out the database aspect of the app.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.