Atualizando um campo no banco todo.

ora_udt_all_fields.sql

declare
  cursor c_sql_stmt is
    select
      cl.table_name,
      cl.column_name,
      'UPDATE '||cl.table_name||' set '||cl.column_name||' = :1 where regexp_like('||cl.column_name||', :2, ''i'')' as cmd
    from
      user_tab_columns cl
      inner join user_tables tb
        on tb.table_name = cl.table_name
    where
      regexp_like(cl.column_name, 'codmat', 'i')
      and cl.data_type = 'CHAR';
      
    codigo_antigo varchar2(40) := 'MOLDET7035';
    codigo_novo varchar2(40) := 'MOLDET7035';    
      
begin
    for sql_stmt in c_sql_stmt loop
      begin
        dbms_output.put_line('Atualizando '||sql_stmt.table_name||'.'||sql_stmt.column_name||' ...');
        execute immediate sql_stmt.cmd using codigo_novo, codigo_antigo;
        dbms_output.put_line('... Atualizado!');
      exception
        when others then
          dbms_output.put_line('... '||sqlerrm);
      end;
    end loop;
end;