Replacing text in a BLOB Column

Question

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'

But I get the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

How can I execute REPLACE on the BLOB ?

Solution 1

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

Solution 2

We can use something like the below query also with Oracle 11 and above if the blob object is of text.

`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';`

Comments

[Alon Adler]

Yes, the field is BLOB not CLOB. Anyway the version is Ora11.1.