set echo off set verify off set serveroutput on declare v_str clob; conversion_date varchar2(10) := '05/15/2014'; begin -- select '&1' into conversion_date from dual; for rec in (select table_name from hdms_table where table_name in ('SCIENTIFIC_NAME','EO','SOURCE_FEATURE','MA','CONSERVATION_SITE') and core=1 and table_type = 'B' order by table_name) loop select wm_concat(column_name || ' = regexp_replace(regexp_replace(' || column_name || ', ''<(/?)em>'',''<\1i>''),''<(/?)strong>'',''<\1b>'')') into v_str from user_tab_columns where table_name = rec.table_name and (data_type='CLOB' or (data_type='VARCHAR2' and data_length >= 200)); if v_str != empty_clob() then dbms_output.put_line('update '|| rec.table_name || ' set ' || v_str || ' where (not exists (select * from user_tab_columns where table_name = ''' || rec.table_name || ''' and column_name = ''REC_LAST_MOD_DATE'')) or (rec_create_date >= to_date(''' || conversion_date ||''',''mm/dd/yyyy'') or rec_last_mod_date >= to_date('''|| conversion_date || ''',''mm/dd/yyyy''));'); execute immediate 'update '|| rec.table_name || ' set ' || v_str || ' where (not exists (select * from user_tab_columns where table_name = ''' || rec.table_name || ''' and column_name = ''REC_LAST_MOD_DATE'')) or (rec_create_date >= to_date(''' || conversion_date ||''',''mm/dd/yyyy'') or rec_last_mod_date >= to_date('''|| conversion_date || ''',''mm/dd/yyyy''))'; end if; end loop; end; / set verify on set echo on