Hi,
I have this situation where in I am trying to replace a particular piece of line in CLOB variable.
Example -
This is the input (CLOB variable)
...
...
...
where 1 in (
decode(O."some_value_1",N."some_value_1",0,1), decode(O."some_value_2",N."some_value_2",0,1),
decode(O."some_value_3"),N."some_value_3",0,1),
decode(O."some_value_4",N."some_value_5",0,1),
...
...
...
)
This should be the result (CLOB variable)
...
...
...
where 1 in (
decode(O."some_value_1",N."some_value_1",0,1), decode(O."some_value_2",N."some_value_2",0,1),
decode(function_f1(O."some_value_3")),function_f1(N."some_value_3"),0,1),
decode(O."some_value_4",N."some_value_5",0,1),
...
...
...
)
I tried using REGEXP_INST, REGEXP_SUBSTR, REGEXP_REPLACE - but unable to achieve the objective. The logic I am trying is -
1. Identify if the line exists in my CLOB using REGEXP_LIKE
2. traverse to that piece of line using REGEXP_SUBSTR and extract that entire line that I need to add a function_f1
3. and I get stuck - how do I traverse ahead - this being a CLOB I find myself stuck - had it been a varchar; I could have used combination of INSTR, SUBSTR and REPLACE.
4. DBMS_LOB does not provide in its substr function to match any character and that makes it hard for my objective as I need to traverse to a particular line using a Regular expression
Am I missing some oracle functionality that helps me achieve my goal ?
Any help would be appreciated.
Thanks!