sql - Replacing text in an BLOB Column -

in 1 of our tables have hugeblob column (column name dynamic_data) holding xml data. need updating part of text withing blob.

i've tried query:

update ape1_item_version  set dynamic_data = replace (dynamic_data,'single period','single period period set1')  name = 'prit pool duration telephony 10_na_g_v_h_n_z2' 

but following error:

ora-00932: inconsistent datatypes: expected number got blob

how can execute replace on blob ?

replace works on following datatypes:

both search_string , replacement_string, char, can of data types char, varchar2, nchar, nvarchar2, clob, or nclob.

you have chosen store character data collection of bytes (blob). these can not worked on directly because blob has no context , only very big number. can't converted characters without your input: need character set convert binary data text.

you'll have either code function replace (using dbms_lob.instr instance) or convert data workable clob , use standard functions on clob.

i advise change datatype of column. prevent further character set conversion error run in future.

if want work blobs, use functions these:

sql> create or replace function convert_to_clob(l_blob blob) return clob   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   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 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   name = 'prit pool duration telephony 10_na_g_v_h_n_z2';  1 row updated