create or replace
PROCEDURE p_archive_dynamically(piv_owner varchar2) 
is
/* ***************************************************************************************
 * NAME:       p_archive_dynamically                                                    *
 * PURPOSE:    Give a schema owner to this procedure as an input parameter and it will  *
 *             select all tables belonging to this owner and generate a insert/select   *
 *             statement filling those tables with data coming from another database.   *
 *             Tables coming from the other database must have the same structure as    *
 *             tables belonging to the input owner.                                     *
 *             This procedure can be invoked when we want, for example,archiving tables *
 *             from the real time dataabse to the archived data base                    *
 *                                                                                      *
 *             In this pl/sql stored procedure template, for sake of simplicity, I've   *
 *             supposed that tables belonging to the archived data base and those 
 *             belonging to the real time data base have the same except that the later *
 *             table names are prefixed with "real_" prefix                             *
 *                                                                                      *
 *             In real life situation you might have a dblink between the owner data    *
 *             base and the archived one. It's up to you to adapt distant table names   *
 ****************************************************************************************
 * CHANGE HISTORY                                                                       *
 * VERSION     WHEN        WHO                WHAT                                      *
 * 1.0.0       2012-05-16  Mohamed Houri      wrote initial code                        *
 *                                                                                      *
 ****************************************************************************************/  

-- ****************************************************************************************
-- The dynamic sql statement template looks like the following
-- where 
--    TABLE1 		: Table selected from all_tables where owner = piv_owner
--    COLUMNS		: Columns of TABLE1
--    TABLE2 		: table belonging to the other data base. Here they are supposed to 
--             		  have a name like TABLE1_dist
--    INS_COLUMNS	: Columns of TABLE2
--    PKCOLUMNS     : Primary key columns of TABLE1 (and TABLE2 also)
--    TABLE3        : DML_ERROR logging table names. You can suppress them if you want
--                    It's not easy to select them dynamically because it seems that 
--                    they are not stored into a dictionary table together with their
--                    corresponding table so that one can select them.

--                   for this initial version I have commented the use of the dml_error logging
-- ****************************************************************************************
lv_stmt  varchar2(2000)  := 'insert /*+ append */ into TABLE1 (COLUMNS) 
													select INS_COLUMNS
													from TABLE2 tab2
													where not exists (select null
																	  from TABLE1 tab1
																	  where PKCOLUMNS
                                            )																	  
												    --log errors into TABLE3 reject limit unlimited
                            ';       


-- 	The main cursor selecting all tables to be filled up by distant data
--  Of course here you have to take care of any parent/child relationship
--  that have to be respected during the insert operation: parent first and 
--  child in second position. I let this exercise open for others to implement 
--  a correct order of the below select.												
   cursor c_tab is
      select tab.table_name,'ERR$_'||tab.table_name ERR_TABLE       
        from all_tables tab
		where tab.owner = piv_owner
    and   tab.table_name = 'T_REAL_TIME'
      --order by atb.TABLE_ORDER
	  ;

-- For each table get the 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 = piv_owner
         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;

-- For each table get the 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 = piv_owner
         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 = piv_owner
         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 = piv_owner
         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_dynamically;
/