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
, ornclob
.
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
Comments
Post a Comment