1 Package Body EDW_DEL_STG AS
2 /* $Header: EDWDELB.pls 120.1 2005/08/11 04:40:35 aguwalan noship $*/
3 g_dummy VARCHAR2(30);
4 g_dummy_int NUMBER;
5 cid NUMBER;
6 l_fact BOOLEAN := TRUE;
7 g_errbuf varchar2(200) := null;
8 g_retcode number := 0;
9 g_name varchar2(100) := null;
10
11 /* cursor alldimm is
12 select
13 dim.name dim_name
14 from
15 cmprelationmapping_v dim_map,
16 cmpwbdimension_v dim
17 where
18 dim_map.targetdataentity=dim.elementid; */
19
20 cursor alldimm is
21 select
22 dim.dim_long_name dim_name
23 from edw_relationmapping_md_v dim_map,
24 edw_dimensions_md_v dim
25 where dim_map.targetdataentity=dim.dim_id;
26
27 /* cursor dimm(p_dim_name varchar2) is
28 select
29 src_relation.name table_name
30 from
31 cmprelation_v relation,
32 cmprelation_v src_relation,
33 cmprelationmapping_v dim_map,
34 cmprelationmapping_v lvl_map,
35 cmpwbrelationusage_v dim_ru,
36 cmpwbdimension_v dim
37 where
38 dim.longname=rtrim(p_dim_name)
39 and dim_map.targetdataentity=dim.elementid
40 and dim_ru.cmprelationmapping=dim_map.elementid
41 and dim_ru.source=1
42 and relation.elementid=dim_ru.cmprelation
43 and relation.classname='CMPWBTable'
44 and lvl_map.targetdataentity=relation.elementid
45 and src_relation.elementid=lvl_map.sourcedataentity; */
46
47 cursor dimm(p_dim_name varchar2) is
48 select lstg.relation_name table_name
49 from edw_relations_md_v ltc,edw_relations_md_v lstg,
50 edw_relationmapping_md_v map,edw_dimensions_md_v dim,
51 edw_levels_md_v lvl
52 where dim.dim_long_name=p_dim_name
53 and lvl.dim_id=dim.dim_id
54 and ltc.relation_name=lvl.level_name||'_LTC'
55 and map.targetdataentity=ltc.relation_id
56 and lstg.relation_id=map.sourcedataentity;
57
58 /* cursor allfactt is
59 select cube.name fact_name
60 from cmprelationmapping_v map,
61 cmpwbcube_v cube
62 where cube.elementid = map.targetdataentity; */
63
64 /**cursor allfactt is
65 select cube.fact_longname fact_name
66 from edw_relationmapping_md_v map,edw_facts_md_v cube
67 where cube.fact_id = map.targetdataentity;**/
68
69 ----fix bug 2109920.Exclude derived facts from the fact list---
70 cursor allfactt is
71 select cube1.fact_longname fact_name
72 from edw_relationmapping_md_v map1,edw_facts_md_v cube1
73 where cube1.fact_id = map1.targetdataentity
74 and cube1.fact_longname not in
75 (select distinct cube.fact_longname
76 from
77 edw_facts_md_v cube,
78 edw_facts_md_v cube_src,
79 edw_relationmapping_md_v map where
80 map.targetdataentity=cube.fact_id
81 and map.sourcedataentity=cube_src.fact_id);
82
83
84
85 cursor factt(p_fact_name varchar2) is
86 select relation.relation_name table_name
87 from edw_relations_md_v relation,
88 edw_relationmapping_md_v map,
89 edw_relations_md_v lvl_relation
90 where lvl_relation.relation_long_name=p_fact_name
91 and map.targetdataentity=lvl_relation.relation_id
92 and relation.relation_id=map.sourcedataentity;
93
94
95 Procedure Delete_Table(p_stg_name in varchar2) IS
96 l_count NUMBER:=0;
97 TYPE curType IS REF CURSOR ;
98 del_cur curType;
99 l_stmt varchar2(1000);
100 BEGIN
101 begin
102 cid := DBMS_SQL.open_cursor;
103
104 DBMS_SQL.PARSE(cid, 'DELETE FROM '||p_stg_name||
105 ' WHERE nvl(COLLECTION_STATUS, ''ERROR'') = ''COLLECTED'' OR nvl(COLLECTION_STATUS, ''ERROR'') =''DUPLICATE-COLLECT''', dbms_sql.native);
106
107
108 g_dummy_int:=DBMS_SQL.EXECUTE(cid);
109 EXCEPTION
110 when others then
111 g_errbuf := sqlerrm;
112 g_retcode := -1;
113 end;
114 begin
115 DBMS_SQL.PARSE(cid, 'LOCK TABLE '||p_stg_name
116 ||' IN EXCLUSIVE MODE NOWAIT', dbms_sql.native);
117 g_dummy_int:=DBMS_SQL.EXECUTE(cid);
118
119 begin
120 l_stmt := 'select /*+ FIRST_ROWS */ 1 from '|| p_stg_name ||' where rownum=1';
121 open del_cur for l_stmt;
122 fetch del_cur into g_dummy_int;
123 if (del_cur%NotFound) then
124 l_stmt := 'TRUNCATE TABLE '|| p_stg_name ||' DROP STORAGE';
125 execute immediate l_stmt;
126 commit;
127 end if;
128 close del_cur;
129 end;
130 EXCEPTION when others then
131 g_errbuf := sqlerrm;
132 commit;
133 end;
134
135 IF DBMS_SQL.IS_OPEN(cid) THEN
136 DBMS_SQL.CLOSE_CURSOR(cid);
137 END IF;
138 END;
139
140 Procedure Delete_Dimension(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_dim_name in varchar2, p_purge_option in number) IS
141 l_mode number;
142 BEGIN
143 l_mode:=p_purge_option;
144 if l_mode is null then
145 l_mode:=0; -- 0 for delete, 1 for truncate
146 end if;
147 edw_log.put_line('Dimension name is '||p_dim_name);
148
149 IF (l_mode=0) THEN
150 edw_log.put_line('Deleting loaded records in interface tables');
151 IF (p_dim_name IS NULL) THEN
152 Delete_All_Dimensions;
153 ELSE
154 Delete_One_Dimension(p_dim_name);
155 END IF;
156 ELSE
157 edw_log.put_line('Truncating interface tables');
158 IF (p_dim_name IS NULL) THEN
159 EDW_TRUNC_STG.Truncate_All_Dimensions;
160 ELSE
161 EDW_TRUNC_STG.Truncate_One_Dimension(p_dim_name);
162 END IF;
163 END IF;
164 END;
165
166 Procedure Delete_One_Dimension(p_dim_name in varchar2) IS
167
168 BEGIN
169
170 /* delete from the level staging tables */
171 edw_log.put_line('Deleting staging tables for '||p_dim_name);
172 FOR r1 IN dimm(p_dim_name) LOOP
173 Delete_Table(r1.table_name);
174 edw_log.put_line('Deleted staging table '||r1.table_name);
175 END LOOP;
176 END;
177
178 Procedure Delete_All_Dimensions IS
179 BEGIN
180 for r1 in alldimm loop
181 Delete_One_Dimension(r1.dim_name);
182 end loop;
183 END;
184
185
186 Procedure Delete_Fact(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_fact_name in varchar2, p_purge_option in number) IS
187 l_mode number;
188 l_staging_table varchar2(120) := null;
189 BEGIN
190 l_mode:=p_purge_option;
191 if l_mode is null then
192 l_mode:=0;
193 -- 0 for delete loaded recods, 1 for truncate all records
194 end if;
195 edw_log.put_line('Fact name is '||p_fact_name);
196
197 IF (l_mode=0) THEN
198 edw_log.put_line('Deleting loaded records in interface tables');
199 IF (p_fact_name IS NULL) THEN
200 Delete_All_Facts;
201 ELSE
202 Delete_One_fact(p_fact_name);
203 END IF;
204 ELSE
205 edw_log.put_line('Truncating interface tables');
206 IF (p_fact_name IS NULL) THEN
207 EDW_TRUNC_STG.Truncate_All_Facts;
208 ELSE
209 EDW_TRUNC_STG.Truncate_One_Fact(p_fact_name);
210 END IF;
211 END IF;
212
213
214 END;
215
216 Procedure Delete_One_Fact(p_fact_name in varchar2) IS
217 l_staging_table varchar2(120) := null;
218 BEGIN
219 edw_log.put_line('Deleting staging tables for '||p_fact_name);
220 open factt(p_fact_name);
221 fetch factt into l_staging_table;
222 close factt;
223 Delete_Table(l_staging_table);
224 edw_log.put_line('Deleted staging table '||l_staging_table);
225
226 END;
227
228
229 Procedure Delete_All_Facts IS
230 BEGIN
231 for r1 in allfactt loop
232 Delete_One_Fact(r1.fact_name);
233 end loop;
234
235 END;
236
237 End EDW_DEL_STG;
[Home] [Help]