1 /**
 2  * Common procedure to merge TABLE_X to ARCH_TABLE_X.
 3  * The main logic is to build a common merge into statement for tables with different column
 4  * You many need change the target table prefix from 'ARCH' to the one you want in this procedure. 
 5  * Author 烂人
 6  * @tbl_name is the source data table
 7  * @unique_key is the key to uniquely identify a data row
 8  * @days_to_keep days to keep in original table, which means the rest data which plans to delete need merge into archiving table
 9  */
10 create or replace PROCEDURE "PRC_ARCH_TABLE" 
11   (tbl_name VARCHAR2, unique_key VARCHAR2, days_to_keep Integer)
12 IS
13   v_columns_1 VARCHAR2 (4000);
14   v_columns_2 VARCHAR2 (4000);
15   v_columns_3 VARCHAR2 (4000);
16   v_columns_4 VARCHAR2 (4000);
17   v_stmt VARCHAR2 (8500);
18 BEGIN
19   --SET days_to_keep := days_to_keep - 1;
20   -- bulk fetch the column of selected table
21   select 
22   rtrim(xmlagg(xmlparse(content column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ','),
23   rtrim(xmlagg(xmlparse(content 'x.'|| column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ','),
24   rtrim(xmlagg(xmlparse(content 'y.'|| column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ',')
25   into v_columns_1, v_columns_2, v_columns_3
26   from all_tab_cols t where table_name = tbl_name and hidden_column = 'NO' GROUP BY table_name;
27  
28   select 
29    rtrim(xmlagg(xmlparse(content 'x.'|| column_name || '=' || 'y.' || column_name || ',' wellformed) ORDER BY t.column_name).getclobval(), ',')
30   into v_columns_4
31   from all_tab_cols t where table_name = tbl_name and hidden_column = 'NO' and column_name <> upper(unique_key) GROUP BY table_name;
32 
33   v_stmt := 'MERGE INTO ARCH_'
34       || tbl_name 
35       ||' x USING (SELECT ' 
36       || v_columns_1 
37       || ' FROM '
38       || tbl_name
39       || ' WHERE CREATE_DATE <= current_date - NUMTODSINTERVAL(' || (days_to_keep-1) || ', ''day'')) y ON (x.'
40       || unique_key
41       || '=y.'
42       || unique_key
43       || ')WHEN MATCHED THEN UPDATE SET '
44       || v_columns_4
45       || ' WHEN NOT MATCHED THEN INSERT ('
46       || v_columns_2
47       || ') values ('
48       || v_columns_3
49       || ')';
50       --DBMS_OUTPUT.PUT_LINE(v_stmt); 
51       EXECUTE IMMEDIATE v_stmt;
52       DBMS_OUTPUT.PUT_LINE('Successfully execute ' || v_stmt); 
53     EXCEPTION
54       WHEN OTHERS THEN
55         DBMS_OUTPUT.PUT_LINE('Failed to execute: ' || v_stmt); 
56         DBMS_OUTPUT.PUT_LINE('Error msg: ' || sqlerrm); 
57 END;

 1 create or replace PROCEDURE  "PRC_ARCH_DATA_CLEAN" 
 2   (tbl_name VARCHAR2)
 3 IS
 4   v_stmt VARCHAR2 (4000);
 5   keep_month INTEGER;
 6 BEGIN
 7   SELECT NVL(SUM(TO_NUMBER(value)), 0) INTO keep_month FROM CONFIGURATION WHERE data_key=upper(tbl_name);
 8   IF  keep_month = '0' THEN
 9     DBMS_OUTPUT.PUT_LINE('The archiving table did not configured correctly!');
10   ELSE
11     v_stmt := 'DELETE FROM '
12       || tbl_name 
13       || ' WHERE CREATE_DATE < current_date - numtoyminterval('||keep_month||',''month'')';
14       EXECUTE IMMEDIATE v_stmt;
15       DBMS_OUTPUT.PUT_LINE('Successfully execute ' || v_stmt); 
16   END IF;
17  EXCEPTION
18   WHEN OTHERS THEN
19     DBMS_OUTPUT.PUT_LINE('Failed to execute: ' || v_stmt); 
20     DBMS_OUTPUT.PUT_LINE('Error msg: ' || sqlerrm);
21 END;