declare
  v_sql varchar2(1000);
begin 
  for cur_table in (select distinct(table_name) table_name from user_tab_columns a  
    where a.column_name = 'COMMODITYID' )
    loop
      v_sql :='update '||cur_table.table_name ||' set commodityid=''新的字符串'' where commodityid=''原来的字符串''';
      dbms_output.put_line(v_sql);
     execute immediate v_sql;
     commit;
    end loop;
end;

declare
  v_sql  varchar2(1000);
  count1 number;
begin
  for cur_table in (select distinct (table_name) table_name
                      from user_tab_columns a)
 loop
    for cur_column in (select distinct (column_name) column_name
                         from user_tab_columns a
                        where a.TABLE_NAME = cur_table.table_name) loop
      v_sql := 'select count(*) from ' || cur_table.table_name ||
               '  where instr(' || cur_column.column_name || ',''查找的字符串'')>0';
      execute immediate v_sql
        into count1;
      if (count1 > 0) then
        dbms_output.put_line(cur_column.column_name);
        dbms_output.put_line(count1 || ';' || v_sql);
      end if;
    end loop;
  end loop;
end;