I’m trying to update a table using oreplace but it won’t let me do it saying it exceeds the length. I have tried breaking it into multiple oreplace statements asin the first approach, and multiple update statements as the second approach but it still doesn’t work or give me the desired results.
Apparently oreplace can only return a maximum of 8000 characters? If yes, what is the solution/workaround for this?
First approach:
sel
oreplace (substr(text_val,1,8000),'CIM_OUTPUT','DD_CIM_OUTPUT')
|| oreplace (substr(text_val,8001,16000),'CIM_OUTPUT','DD_CIM_OUTPUT')
|| oreplace (substr(text_val,16001,18000),'CIM_OUTPUT','DD_CIM_OUTPUT')
from DB.TABLENAME ;
Second approach:
The problem with this is that text_val is truncated after the first update and the rest of the updates do not apply on the complete value of text_Val thus rendering them as useles.
update DB.TABLENAME set text_val = oreplace (substr(text_val,1,8000),'CIM_OUTPUT','DD_CIM_OUTPUT');
update DB.TABLENAME set text_val = text_val||oreplace (substr(text_val,8001,16000),'CIM_OUTPUT','DD_CIM_OUTPUT');
update DB.TABLENAME set text_val = text_val || oreplace (substr(text_val,16001,18000),'CIM_OUTPUT','DD_CIM_OUTPUT');
oReplace is limited to 8000 chars (maybe because it's based on Oracle). REGEXP_REPLACE has the same limit for VarChar input, but works on CLOBs, too. This this should work:
SET TEXT_VAL=cast(regexp_replace(cast(text_Val as CLOB),'cim_output','DD_cim_ouput') as varchar(18000));