Hi
I need to replace a text string in clob column of a table. The table contains 8M+ rows.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Example:
create table test_clob_data (id raw(16), text clob);
insert into test_clob_data values (sys_guid(),'changes
{
Execution Time: 26 Jan 2017 20:08:42
Execution Venue: SEF
GC State: Ineligible
Dummy attr: 20.00
Non-Standard: false
Off Platform Confirmed: Electronic
Off Platform Verified: Electronic
Price Forming Event: true
Prv Block Trade: No
}');
commit;
Need to replace the Dummy attr and its value ( which can be any number) to null in the above text.
Result expected -
changes
{
Execution Time: 26 Jan 2017 20:08:42
Execution Venue: SEF
GC State: Ineligible
Non-Standard: false
Off Platform Confirmed: Electronic
Off Platform Verified: Electronic
Price Forming Event: true
Prv Block Trade: No
}
I tried this with a simple update using regexp_replace(text, 'Dummy attr: [[:digit:]].*') but is very slow. Using DBMS_LOB, how could I replace exact string as don't know what number value would be present after the attribute to be replaced. Any suggestions please
Regards