1 package body CZ_IMP_MGR as
2 /* $Header: czimngrb.pls 120.0 2005/05/25 06:45:39 appldev noship $ */
3
4 TYPE tRunId IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE tName IS TABLE OF dba_tables.table_name%TYPE INDEX BY BINARY_INTEGER;
6 -------------------------------------------
7 PROCEDURE delete_runs(p_run_id_tbl tRunId) IS
8 l_schema_owner dba_tables.owner%TYPE := 'CZ';
9 l_str VARCHAR2(2000);
10 l_tab_name_tbl tName;
11 l_count NUMBER:=0;
12 BEGIN
13 BEGIN
14 SELECT TO_NUMBER(VALUE) INTO CZ_IMP_MGR.BATCH_SIZE
15 FROM cz_db_settings
16 WHERE UPPER(setting_id)='BATCHSIZE';
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 NULL;
20 END;
21 l_tab_name_tbl.DELETE;
22 SELECT table_name BULK COLLECT INTO l_tab_name_tbl
23 FROM dba_tables
24 WHERE table_name like 'CZ_IMP%' AND owner = UPPER(l_schema_owner);
25 IF p_run_id_tbl.COUNT > 0 THEN
26 FOR i IN p_run_id_tbl.FIRST..p_run_id_tbl.LAST LOOP
27 FOR j IN l_tab_name_tbl.FIRST..l_tab_name_tbl.LAST LOOP
28 l_str := 'DELETE FROM '||l_tab_name_tbl(j) || ' WHERE run_id = :1';
29 EXECUTE IMMEDIATE l_str USING p_run_id_tbl(i);
30 l_count := l_count + SQL%ROWCOUNT;
31 IF l_count >= BATCH_SIZE THEN
32 COMMIT;
33 l_count := 0;
34 END IF;
35 END LOOP;
36 l_str := 'DELETE FROM cz_xfr_run_results WHERE run_id = :1';
37 EXECUTE IMMEDIATE l_str USING p_run_id_tbl(i);
38 l_str := 'DELETE FROM cz_xfr_run_infos WHERE run_id = :1';
39 EXECUTE IMMEDIATE l_str USING p_run_id_tbl(i);
40 END LOOP;
41 END IF;
42 COMMIT;
43 END delete_runs;
44 -------------------------------------------
45 PROCEDURE purge
46 IS
47 l_schema_owner dba_tables.owner%TYPE := 'CZ';
48 l_error BOOLEAN;
49 BEGIN
50 CZ_ADMIN.SPX_SYNC_IMPORTSESSIONS;
51 DBMS_APPLICATION_INFO.SET_MODULE('CZIMPORT','');
52 FOR i IN (SELECT table_name FROM dba_tables WHERE table_name like 'CZ_IMP%' AND owner = UPPER(l_schema_owner)) LOOP
53 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema_owner||'.'|| i.table_name;
54 END LOOP;
55 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema_owner||'.'||'CZ_XFR_RUN_INFOS';
56 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema_owner||'.'||'CZ_XFR_RUN_RESULTS';
57 COMMIT;
58 DBMS_APPLICATION_INFO.SET_MODULE('','');
59 EXCEPTION
60 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
61 l_error := cz_utils.log_report(cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS'),1,'CZ_IMP_MGR.PURGE',11276);
62 RAISE;
63 WHEN OTHERS THEN
64 l_error:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_MGR.PURGE',11276);
65 DBMS_APPLICATION_INFO.SET_MODULE('','');
66 RAISE;
67 END purge;
68 -------------------------------------------
69 PROCEDURE purge_to_date(p_days IN NUMBER)
70 IS
71 l_run_id_tbl tRunId;
72 l_error BOOLEAN;
73 BEGIN
74 CZ_ADMIN.SPX_SYNC_IMPORTSESSIONS;
75 DBMS_APPLICATION_INFO.SET_MODULE('CZIMPORT','');
76 l_run_id_tbl.DELETE;
77 SELECT run_id BULK COLLECT INTO l_run_id_tbl
78 FROM cz_xfr_run_infos
79 WHERE TRUNC(started) < (SELECT TRUNC(SYSDATE) - p_days FROM DUAL);
80 IF (l_run_id_tbl.COUNT > 0) THEN
81 delete_runs(l_run_id_tbl);
82 END IF;
83 DBMS_APPLICATION_INFO.SET_MODULE('','');
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 DBMS_APPLICATION_INFO.SET_MODULE('','');
87 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
88 l_error := cz_utils.log_report(cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS'),1,'CZ_IMP_MGR.PURGE',11276);
89 RAISE;
90 WHEN OTHERS THEN
91 l_error:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_MGR.PURGE_to_date',11276);
92 DBMS_APPLICATION_INFO.SET_MODULE('','');
93 RAISE;
94 END purge_to_date;
95 -------------------------------------------
96 PROCEDURE purge_to_runid(p_run_id IN NUMBER)
97 IS
98 l_run_id_tbl tRunId;
99 l_error BOOLEAN;
100 BEGIN
101 CZ_ADMIN.SPX_SYNC_IMPORTSESSIONS;
102 DBMS_APPLICATION_INFO.SET_MODULE('CZIMPORT','');
103 l_run_id_tbl.DELETE;
104 SELECT run_id BULK COLLECT INTO l_run_id_tbl
105 FROM cz_xfr_run_infos
106 WHERE run_id <= p_run_id;
107 IF (l_run_id_tbl.COUNT > 0) THEN
108 delete_runs(l_run_id_tbl);
109 END IF;
110 DBMS_APPLICATION_INFO.SET_MODULE('','');
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 DBMS_APPLICATION_INFO.SET_MODULE('','');
114 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
115 l_error := cz_utils.log_report(cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS'),1,'CZ_IMP_MGR.PURGE',11276);
116 RAISE;
117 WHEN OTHERS THEN
118 l_error:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_MGR.PURGE_TO_RUNID',11276);
119 DBMS_APPLICATION_INFO.SET_MODULE('','');
120 RAISE;
121 END purge_to_runid;
122 -------------------------------------------
123 PROCEDURE purge_cp(errbuf IN OUT NOCOPY VARCHAR2,
124 retcode IN OUT NOCOPY pls_integer) IS
125 BEGIN
126 retcode := G_CONCURRENT_SUCCESS;
127 purge;
128 EXCEPTION
129 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
130 retcode := G_CONCURRENT_ERROR;
131 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
132 WHEN OTHERS THEN
133 retcode := G_CONCURRENT_ERROR;
134 errbuf := cz_utils.get_text('CZ_PURGE_FATAL_ERR', 'SQLERRM',Sqlerrm);
135 DBMS_APPLICATION_INFO.SET_MODULE('','');
136 END purge_cp;
137 -------------------------------------------
138 PROCEDURE purge_to_date_cp(errbuf IN OUT NOCOPY VARCHAR2,
139 retcode IN OUT NOCOPY pls_integer,
140 p_days IN NUMBER) IS
141 BEGIN
142 retcode := G_CONCURRENT_SUCCESS;
143 purge_to_date(p_days);
144 EXCEPTION
145 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
146 retcode := G_CONCURRENT_ERROR;
147 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
148 WHEN OTHERS THEN
149 retcode := G_CONCURRENT_ERROR;
150 errbuf := cz_utils.get_text('CZ_PURGE_FATAL_ERR', 'SQLERRM',Sqlerrm);
151 DBMS_APPLICATION_INFO.SET_MODULE('','');
152 END purge_to_date_cp;
153 -------------------------------------------
154 PROCEDURE purge_to_runid_cp(errbuf IN OUT NOCOPY VARCHAR2,
155 retcode IN OUT NOCOPY pls_integer,
156 p_run_id IN NUMBER) IS
157 BEGIN
158 retcode := G_CONCURRENT_SUCCESS;
159 purge_to_runid(p_run_id);
160 EXCEPTION
161 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
162 retcode := G_CONCURRENT_ERROR;
163 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
164 WHEN OTHERS THEN
165 retcode := G_CONCURRENT_ERROR;
166 errbuf := cz_utils.get_text('CZ_PURGE_FATAL_ERR', 'SQLERRM',Sqlerrm);
167 DBMS_APPLICATION_INFO.SET_MODULE('','');
168 END purge_to_runid_cp;
169
170 END CZ_IMP_MGR;
[Home] [Help]