workaround etc... plsql could really do what you guys think you cant with yer rb, py, php, etc

ora_sh_t_solver.sql

declare
  
  entity_count integer := 0;
  
  type t_ct_cur is ref cursor;
  ct_cur t_ct_cur;
  
  type t_codmat is table of varchar2(255);
  
  blk_codmat t_codmat;
  blk_ct_codmat t_codmat; 
  
  cnt_del integer := 0;
  cnt_udt integer := 0;
  
  
begin

  for r_fld in (
    select 
      cl.table_name, 
      cl.column_name 
    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'
      -- and tb.table_name <> 'GTMATERIAL'
  ) loop
    
    open ct_cur for
      'select 
        ct.'||r_fld.column_name||' as ct_codmat,
        mt.codmat
      from 
        gtmaterial mt
        inner join '||r_fld.table_name||' ct 
          on trim(ct.'||r_fld.column_name||') = regexp_replace(trim(mt.codmat), ''-\d(\d{4})$'', ''-\1'')
      where 
        trim(mt.codmatpck) is not null
        and mt.matcrt = 4
        and regexp_like(trim(mt.codmat), ''-\d{5}$'', ''i'')';
    
    fetch ct_cur bulk collect into blk_ct_codmat, blk_codmat;
    
    entity_count := blk_ct_codmat.count;
    
    if entity_count > 0 then
      dbms_output.put_line('> '||r_fld.table_name||'.'||r_fld.column_name);
      dbms_output.put_line('> > occurrences '||entity_count);
    end if;
    
    for i in 1..blk_ct_codmat.count loop
      
      begin
        
        execute immediate 'update '||r_fld.table_name||' set '||r_fld.column_name||' = :1 where '||r_fld.column_name||' = :2' 
          using blk_codmat(i), blk_ct_codmat(i);
        
        cnt_udt := cnt_udt + 1;
        
      exception
        when dup_val_on_index then
          execute immediate 'delete '||r_fld.table_name||' where '||r_fld.column_name||' = :2'
            using blk_ct_codmat(i);
          cnt_del := cnt_del + 1;
          
        when others then 
          dbms_output.put_line(' ! ' || sqlerrm);
      end;
      
    end loop;
    
    if entity_count > 0 then
      dbms_output.put_line(' < done for ' || r_fld.table_name ||' '|| cnt_udt || ' updated and ' || cnt_del ||' deleted');
      cnt_udt := 0;
      cnt_del := 0;
    end if;
    
    
    
  end loop;

end;
/