Sample Update Script

DECLARE

v_result NUMBER;
v_period_start_date DATE;
v_period_end_date DATE;
v_period_date   DATE;
v_table_name VARCHAR2(100);
v_table_own VARCHAR2(100);
v_par_period   VARCHAR2(6);
internalerror EXCEPTION;


BEGIN

v_period_start_date := to_date ('201701', 'yyyymm');
v_period_end_date := to_date ('201701', 'yyyymm');

v_period_date := v_period_start_date;

--to_char(v_period_date,'yyyymmdd')
-- MONTHLY LOOP
loop
  BEGIN   

     
          v_table_own:= 'imsdwh_lpin';
          v_table_name:='es_dwh_atc_his';


           --delete * from imsdwh_lpin.es_dwh_atc_his   WHERE t.dte_yer_mth = v_period_date

           IF (imsglobal.table_cmd.space_mgr(to_char(v_period_date,'yyyymm'), v_table_own,v_table_name) != 0) THEN
            imsglobal.put_line('', 1,'Error partitioning table  '||v_table_own || '.' || v_table_name ||' for period ' || to_char(v_period_date, 'yyyy/mm'));
            RAISE internalerror;
          END IF;
       
       
          INSERT INTO imsdwh_lpin.es_dwh_atc_his
          select * from imsdwh_lpin.es_dwh_atc_his@es_pp t
          WHERE t.dte_yer_mth = v_period_date ;
          imsglobal.put_line('', 1,'Updated table  '||v_table_own || '.' || v_table_name ||' for period ' || to_char(v_period_date, 'yyyy/mm'));
   
   
           COMMIT;
         

END;

  exit when v_period_date =v_period_end_date;
  v_period_date := add_months(v_period_date, +1);

end loop;

-- WEEKLY LOOP
v_period_start_date := to_date ('20170102', 'yyyymmdd');--> SET MONDAY
v_period_end_date := to_date ('20170102', 'yyyymmdd');

v_period_date := v_period_start_date;

loop
     BEGIN
         v_table_own:= 'imsdwh_lpin';
          v_table_name:='es_dwh_atc_wkl_his';

           --delete * from imsdwh_lpin.es_dwh_atc_wkl_his     WHERE t.dte_yer_mth = v_period_date
           IF (imsglobal.table_cmd.space_mgr_wkl(to_char(v_period_date,'yyyymm'), v_table_own,v_table_name) != 0) THEN
            imsglobal.put_line('', 1,'Error partitioning table  '||v_table_own || '.' || v_table_name ||' for period ' || to_char(v_period_date, 'yyyy/mm/dd'));
            RAISE internalerror;
           END IF;
         
          INSERT INTO imsdwh_lpin.es_dwh_atc_wkl_his
          select * from imsdwh_lpin.es_dwh_atc_wkl_his@es_pp t
          WHERE t.dte_yer_mth = v_period_date ;
          imsglobal.put_line('', 1,'Updated table  '||v_table_own || '.' || v_table_name ||' for period ' || to_char(v_period_date, 'yyyy/mm/dd'));
 
 COMMIT;
         

END;

  exit when v_period_date =v_period_end_date;
  v_period_date := v_period_date  +7;

end loop;

       


END;