create or replace
PROCEDURE p_archive_test(archv varchar2) 
is

lv_stmt  varchar2(8000)  := 'insert /*+ append */ into TABLE1 (select * from TABLE2@db1 tab2 )
                            where not exists (select null
																	  from TABLE1 tab1
																	  where tab1.pk = tab2.pk
                                            )		
                            ';  
--(COLUMNS) INS_COLUMNS  PKCOLUMNS
   cursor c_tab is
      select tab.table_name,'ERR$_'||tab.table_name ERR_TABLE       
        from all_tables tab
		where tab.owner = archv
 --and   tab.table_name = 'action'
      --order by atb.TABLE_ORDER
	  ;

--  primary key columns	  
   cursor c_pk(civ_table_name in all_tables.TABLE_NAME%type) 
   is
      select acl.COLUMN_NAME
        from all_cons_columns acl
            ,all_constraints acn
      where acn.OWNER = archv
         and acn.CONSTRAINT_TYPE = 'P'
         and acn.TABLE_NAME = civ_table_name
         and acl.OWNER = acn.OWNER
         and acl.TABLE_NAME = acn.table_name
         and acl.CONSTRAINT_NAME = acn.CONSTRAINT_NAME
      order by acl.POSITION;

-- corresponding table columns names


   cursor c_ins_cols(civ_table_name in all_tab_columns.TABLE_NAME%type) 
   is
      select 'tab2.'||atc.COLUMN_NAME column_name
        from all_tab_columns atc
       where atc.OWNER = archv
         and atc.TABLE_NAME = civ_table_name
       order by atc.COLUMN_ID;

-- For each table get the columns names excluding the primary key columns
   cursor c_upd_cols(civ_table_name in all_tab_columns.TABLE_NAME%type) 
   is
      select 'tab1.'||atc.COLUMN_NAME||'=tab2.'||atc.column_name column_name
        from all_tab_columns atc
       where atc.OWNER = archv
         and atc.TABLE_NAME = civ_table_name
         and not exists (select 1
                           from all_cons_columns acl
                               ,all_constraints acn
                          where acl.OWNER = atc.owner
                            and acl.TABLE_NAME = atc.TABLE_NAME
                            and acl.column_name = atc.column_name
                            and acn.OWNER = acl.OWNER
                            and acn.TABLE_NAME = acl.TABLE_NAME
                            and acn.constraint_type = 'P')
       order by atc.COLUMN_ID;

-- For each table get the columns names
   cursor c_cols(civ_table_name in all_tab_columns.COLUMN_NAME%type) 
   is
      select 
      --'tab1.'||
        atc.COLUMN_NAME column_name
        from all_tab_columns atc
       where atc.owner = archv
         and atc.TABLE_NAME = civ_table_name
       order by atc.COLUMN_ID;


   lv_cols     varchar2(4000);
   lv_pk_cols  varchar2(4000);
   lv_ins_cols varchar2(4000);
   lv_upd_cols varchar2(4000);
   
BEGIN
   
   for r_tab in c_tab 
   loop
      lv_cols     := '';
      lv_pk_cols  := '';
      lv_ins_cols := '';
      lv_upd_cols := '';
  
      for r_pk in c_pk(civ_table_name => r_tab.table_name) 
	  loop
         lv_pk_cols := lv_pk_cols||'tab1.'||r_pk.column_name||'=tab2.'||r_pk.column_name||' and ';
      end loop r_pk_loop;

      lv_pk_cols := substr(str1 => lv_pk_cols
                          ,pos  => 1
                          ,len  => length(ch => lv_pk_cols) - 5);

      for r_ins_cols in c_ins_cols(civ_table_name => r_tab.table_name) 
      loop
         lv_ins_cols := lv_ins_cols||r_ins_cols.column_name||',';
      end loop r_ins_cols_loop;

      lv_ins_cols := substr(str1 => lv_ins_cols
                           ,pos  => 1
                           ,len  => length(ch => lv_ins_cols) - 1);

      for r_upd_cols in c_upd_cols(civ_table_name => r_tab.table_name) 
	  loop
         lv_upd_cols := lv_upd_cols||r_upd_cols.column_name||',';
      end loop r_upd_cols_loop;

      lv_upd_cols := substr(str1 => lv_upd_cols
                           ,pos  => 1
                           ,len  => length(ch => lv_upd_cols) - 1);

      for r_cols in c_cols(civ_table_name => r_tab.table_name) 
	  loop
         lv_cols := lv_cols||r_cols.column_name||',';
      end loop r_cols_loop;

      lv_cols := substr(str1 => lv_cols
                       ,pos  => 1
                       ,len  => length(ch => lv_cols) - 1);

      lv_stmt := replace(replace(replace(replace(replace(replace(replace(lv_stmt
                                                                        ,'TABLE1'
                                                                        ,r_tab.table_name)
                                                                ,'TABLE2'
                                                                --,'DIST_'||
                                                                ,r_tab.table_name)
                                                        ,'PKCOLUMNS'
                                                        ,lv_pk_cols)
                                                ,'UPD_COLUMNS'
                                                ,lv_upd_cols)
                                        ,'INS_COLUMNS'
                                        ,lv_ins_cols)
                                ,'COLUMNS'
                                ,LV_COLS)
                        ,'TABLE3' 
                        ,R_TAB.ERR_TABLE);    
    -- here It highy advisable to store the sql statement that will be submitted
    -- to the SQL engine before executing it dynamically
     insert into t_sql_statement values (lv_stmt);
     execute immediate lv_stmt;     

    end loop ;
  
    commit;
exception
   when others then    
   
   
   
      rollback;
      raise;
end  p_archive_test;