create or replace TYPE "ARRAYOFSTRINGS" AS TABLE OF VARCHAR2(4000);

create or replace PROCEDURE P_RECONS_ZOMBI (ARG_TABLE VARCHAR2, ARG_ZOMBI VARCHAR2, ARG_UNIC_COLUMN_NAME VARCHAR2 DEFAULT NULL)
AS
  v_sql varchar2(4000);
  v_columns_list varchar2(4000);
  v_columns_concat varchar2(4000);
  v_table varchar2(30);
  v_zombi varchar2(30);
  v_unic_column_name varchar2(30);
  v_unic_column_type varchar2(100);
  TYPE t_array_rowid IS TABLE OF ROWID INDEX BY PLS_INTEGER; 
  v_array_rowid t_array_rowid;
  v_arrayofstrings ARRAYOFSTRINGS;
BEGIN

  v_table := upper(trim(arg_table));
  v_zombi := upper(trim(arg_zombi));
  v_unic_column_name := upper(trim(arg_unic_column_name));

  select 
    LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (order by COLUMN_ID) as columns_list,
    LISTAGG(COLUMN_NAME, ' || ') WITHIN GROUP (order by COLUMN_ID) as columns_concal
  into v_columns_list, v_columns_concat
  from user_tab_columns
  where table_name = v_zombi;
  
-- delete rows from zombi table
  if v_unic_column_name is not null
  then
    select 
      decode(DATA_TYPE,'NUMBER','to_number(t.column_value)','t.column_value')
    into v_unic_column_type
    from user_tab_columns
    where table_name = v_zombi
      and COLUMN_NAME = v_unic_column_name;
    EXECUTE IMMEDIATE '
    select to_char(' || v_unic_column_name || ') from (
    select ' || v_columns_list || ' from ' || v_zombi || '
    minus
    select ' || v_columns_list || ' from ' || v_table || '
    )' BULK COLLECT INTO v_arrayofstrings;
    v_sql := 'declare 
      v_arrayofstrings ARRAYOFSTRINGS; 
      begin 
        select /*+ cardinality(t 1) */
          rowidtochar(z.rowid)
        bulk collect into v_arrayofstrings 
        from ' || v_zombi || ' z,
        table(cast(:1 as arrayofstrings)) t
        where ' || v_unic_column_type || ' = z.' || v_unic_column_name || ';
        :1 := v_arrayofstrings;
      end;';
    EXECUTE IMMEDIATE v_sql USING IN OUT v_arrayofstrings;
    FORALL i IN v_arrayofstrings.FIRST..v_arrayofstrings.LAST
    EXECUTE IMMEDIATE 'DELETE FROM ' || v_zombi || ' WHERE rowid = chartorowid(:1)' USING v_arrayofstrings(i);
  else
    EXECUTE IMMEDIATE 'select z.row_id from 
    (select rowid as row_id, sys.dbms_obfuscation_toolkit.MD5(input_string => ' || v_columns_concat || ') as zombi_hash from ' || v_zombi || ') z
    left join (select sys.dbms_obfuscation_toolkit.MD5(input_string => ' || v_columns_concat || ') as zombi_hash from ' || v_table || ') t
    on z.zombi_hash = t.zombi_hash
    where t.zombi_hash is null'
    BULK COLLECT INTO v_array_rowid;
    FORALL i IN v_array_rowid.FIRST..v_array_rowid.LAST
    EXECUTE IMMEDIATE 'DELETE FROM ' || v_zombi || ' WHERE rowid = :1' USING v_array_rowid(i);
  end if;

-- insert rows to zombi table
    EXECUTE IMMEDIATE '
    insert into ' || v_zombi || ' (' || v_columns_list || ')
    select ' || v_columns_list || ' from (
    select ' || v_columns_list || ' from ' || v_table || '
    minus
    select ' || v_columns_list || ' from ' || v_zombi || '
    )';
  
  commit;
  
END P_RECONS_ZOMBI;

-- example 1
exec P_RECONS_ZOMBI('from_table','to_table','column_with_pk');

-- example 2
exec P_RECONS_ZOMBI('from_table','to_table');