1 PACKAGE BODY fii_index_util as
2 /* $Header: FIIIDUTB.pls 120.2.12000000.3 2007/04/26 08:42:05 dhmehra ship $ */
3
4 procedure set_table_name( p_table_name IN VARCHAR2, p_owner VARCHAR2) IS
5
6 l_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
7
8 begin
9 IF p_table_name is NOT NULL THEN
10 g_tab_name := p_table_name;
11 g_owner := p_owner;
12 END IF;
13
14 IF l_debug_flag = 'Y' THEN
15 g_debug_msg := 'Setting the table name to '||g_tab_name;
16 FII_UTIL.put_line('');
17 FII_UTIL.put_line(g_debug_msg);
18 END IF;
19
20 end set_table_name; -- set_table_name
21
22
23 procedure drop_index(p_table_name VARCHAR2, p_owner VARCHAR2,
24 p_retcode in out NOCOPY Varchar2) is
25
26 l_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
27
28 var1 varchar2(2000) := NULL;
29 var2 varchar2(500) := NULL;
30 var3 varchar2(500) := NULL;
31 var4 varchar2(500) := NULL;
32 var5 varchar2(500) := NULL;
33 Errbuf VARCHAR2(200) := NULL;
34 l_index_exists NUMBER := 0;
35 l_counter number:=0;
36 l_rows number:=0;
37 l_next_extent varchar2(20);
38
39 l_unique varchar2(100) := NULL;
40
41 parallel_var number:=0;
42
43 rec fii_indexes%rowtype;
44 rec2 dba_indexes%rowtype;
45 rec3 dba_ind_columns%rowtype;
46
47 cursor c1 is select * from fii_indexes where table_name = g_tab_name;
48
49 cursor c2 is select * from dba_indexes where table_name = g_tab_name
50 and TABLE_OWNER = g_owner;
51
52 cursor c3 (ind_name varchar2) is select * from dba_ind_columns
53 where index_name = ind_name
54 and INDEX_OWNER = g_owner
55 ORDER BY column_position;
56
57 begin
58
59 -- get degree of parallelism
60
61 parallel_var:=bis_common_parameters.get_degree_of_parallelism;
62
63
64 set_table_name(p_table_name, p_owner);
65
66 IF l_debug_flag = 'Y' THEN
67 FII_UTIL.put_line('');
68 FII_UTIL.put_line(parallel_var);
69 g_debug_msg := 'Check if index exists, if not then they have already been dropped';
70 FII_UTIL.put_line('');
71 FII_UTIL.put_line(g_debug_msg);
72 END IF;
73
74 -- bug 4177221: added filter for SNAP$ indexes
75 /* select count(*) into l_index_exists from dba_indexes
76 where table_name = g_tab_name
77 and TABLE_OWNER = g_owner
78 and index_name not like 'I_SNAP$_FII_%'
79 and index_name not like 'U_SNAP$_FII_%';*/
80
81 --Changed above query for Performance bug 4992919
82 begin
83 select 1 into l_index_exists from dba_indexes
84 where table_name = g_tab_name
85 and TABLE_OWNER = g_owner
86 and index_name not like 'I_SNAP$_FII_%'
87 and index_name not like 'U_SNAP$_FII_%'
88 and rownum = 1;
89 exception
90 when others then
91 l_index_exists := 0;
92 end;
93
94 -- select count(*) into l_rows from fii_indexes where table_name = g_tab_name;
95 --Changed above query for Performance bug 4992919
96 begin
97 select 1 into l_rows from fii_indexes where table_name = g_tab_name and rownum = 1;
98 exception
99 when others then
100 l_rows := 0;
101 end;
102 if(l_index_exists = 0) then
103
104 -- index do not exist , so no action
105 IF l_debug_flag = 'Y' THEN
106 -- if no information exists to recreate them, provide message
107 IF (l_rows=0) THEN
108 g_debug_msg := 'Indexes do not exist and no information found to create them, so please create manually';
109 FII_UTIL.put_line('');
110 FII_UTIL.put_line(g_debug_msg);
111 ELSE
112 g_debug_msg := 'Index do not exist , so no need to drop';
113 FII_UTIL.put_line('');
114 FII_UTIL.put_line(g_debug_msg);
115 END IF;
116 END IF;
117
118 else -- indexes are there and need to be dropped
119
120 IF l_debug_flag = 'Y' THEN
121 g_debug_msg := 'Index exist , so save definition and drop indexes';
122 FII_UTIL.put_line('');
123 FII_UTIL.put_line(g_debug_msg);
124 END IF;
125
126 -- store index names in table fii_indexes
127 g_debug_msg := 'First, delete from fii_indexes for ' || g_tab_name;
128 delete from fii_indexes where table_name = g_tab_name;
129
130 g_debug_msg := 'Then, insert into fii_indexes for ' || g_tab_name || ' from dba_indexes';
131 insert into fii_indexes (
132 TABLE_NAME, INDEX_NAME, CREATE_STMT,
133 CREATION_DATE, CREATED_BY,
134 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
135 )
136 select g_tab_name, index_name, null, sysdate, -1, sysdate, -1, -1
137 from dba_indexes
138 where table_name = g_tab_name
139 and TABLE_OWNER = g_owner;
140
141 -- form "create index" statements
142 g_debug_msg := 'form create index statements';
143 open c2;
144 loop
145 fetch c2 into rec2;
146 var2:=rec2.index_name;
147 exit when c2%notfound;
148 l_counter := 0;
149
150 open c3(rec2.index_name);
151 loop
152 fetch c3 into rec3;
153 exit when c3%notfound;
154 if l_counter = 0 then
155 var4:=rec3.column_name;
156 l_counter := l_counter+1;
157 else
158 var4:=var4||','||rec3.column_name;
159 l_counter := l_counter+1;
160 end if;
161 end loop;
162 close c3;
163
164 -- update fii_indexes to store the "create index" statements
165 g_debug_msg := 'update fii_indexes to store the create index statements';
166
167 if (rec2.uniqueness = 'UNIQUE') then
168 l_unique := ' unique ';
169 else
170 l_unique := ' ';
171 end if;
172
173 var5:=rec2.next_extent;
174 -- if next_extent is empty dont include next clause in "create index" statement
175 if var5 is null then
176 var3:='create'||l_unique||'index '||g_owner||'.'||rec2.index_name||' on '||g_owner||'.'||g_tab_name||' ('||var4 ||')' ||'
177 storage ( INITIAL '|| rec2.initial_extent||') tablespace '
178 ||rec2.tablespace_name||' parallel '||parallel_var||' nologging ';
179 else
180 --use NEXT 10M rather than rec2.next_extent since it might be too large
181 l_next_extent := '10M';
182 var3:='create'||l_unique||'index '||g_owner||'.'||rec2.index_name||' on '||g_owner||'.'||g_tab_name||' ('||var4 ||')' ||'
183 storage ( INITIAL '|| rec2.initial_extent||' NEXT '||l_next_extent||') tablespace '
184 ||rec2.tablespace_name||' parallel '||parallel_var||' nologging ';
185 end if;
186
187 update fii_indexes
188 set create_stmt=var3
189 where table_name = g_tab_name
190 and index_name=rec2.index_name;
191
192 IF l_debug_flag = 'Y' THEN
193 g_debug_msg := 'Index definition saved in fii_indexes';
194 FII_UTIL.put_line('');
195 FII_UTIL.put_line(g_debug_msg);
196 END IF;
197
198
199 end loop;
200 close c2;
201
202 commit;
203
204 --bug 3152517: delete system-generated index like 'I_SNAP$_FII_%' and
205 -- 'U_SNAP$_FII_%' from FII_INDEXES
206 g_debug_msg := 'delete scripts for system-generated index I_SNAP$_FII_% from FII_INDEXES';
207
208 delete from FII_INDEXES
209 where table_name = g_tab_name
210 and (index_name like 'I_SNAP$_FII_%' OR
211 index_name like 'U_SNAP$_FII_%');
212
213 IF l_debug_flag = 'Y' and SQL%ROWCOUNT > 0 THEN
214 FII_UTIL.put_line('');
215 FII_UTIL.put_line(g_debug_msg);
216 END IF;
217
218 --bug 3162509: should commit after the above delete
219 commit;
220
221 -- drop other indexes in FII_INDEXES
222 open c1;
223 loop
224 fetch c1 into rec;
225 exit when c1%notfound;
226 var1:=rec.index_name;
227 g_debug_msg := 'Trying to drop index ' || var1;
228 execute immediate 'drop index ' ||g_owner||'.'||var1;
229 end loop;
230 close c1;
231
232 IF l_debug_flag = 'Y' THEN
233 g_debug_msg := 'Indexes dropped';
234 FII_UTIL.put_line('');
235 FII_UTIL.put_line(g_debug_msg);
236 END IF;
237
238 end if; -- check on index exists
239
240 EXCEPTION
241 when others then
242 Errbuf:= sqlerrm;
243 p_retcode:=sqlcode;
244 if l_debug_flag = 'Y' then
245 FII_UTIL.put_line('ERROR in drop_index--> ' || p_retcode||':'||Errbuf);
246 FII_UTIL.put_line('Phase--> ' || g_debug_msg);
247 end if;
248
249 end drop_index; -- drop index procedure
250
251
252 procedure create_index(p_table_name VARCHAR2, p_owner VARCHAR2,
253 p_retcode in out NOCOPY Varchar2) is
254
255 l_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
256 l_rows number:=0;
257 var2 varchar2(500) := NULL;
258 var5 varchar2(2000) := NULL;
259
260 l_counter number:=0;
261 Errbuf VARCHAR2(200);
262 rec2 dba_indexes%rowtype;
263
264 cursor c1A is select distinct create_stmt from fii_indexes
265 where table_name = g_tab_name;
266 cursor c1B is select index_name from fii_indexes
267 where table_name = g_tab_name;
268 cursor c2 is select * from dba_indexes where table_name = g_tab_name
269 and TABLE_OWNER = g_owner;
270
271 begin
272
273 -- create indexes
274
275 set_table_name(p_table_name, p_owner);
276 begin
277 select 1 into l_rows from fii_indexes where table_name = g_tab_name and rownum = 1;
278 exception
279 when others then
280 l_rows := 0;
281 end;
282
283 IF l_debug_flag = 'Y' THEN
284 IF (l_rows=0) THEN
285 FII_UTIL.put_line('');
286 ELSE
287 g_debug_msg := 'Creating Indexes';
288 FII_UTIL.put_line('');
289 FII_UTIL.put_line(g_debug_msg);
290 END IF;
291 END IF;
292
293 g_debug_msg := 'Create indexes using statements from fii_indexes...';
294 for r1a in c1A loop
295 var5:=r1a.create_stmt;
296 execute immediate var5;
297 end loop;
298
299 g_debug_msg := 'Alter the index definitions...';
300 for r1b in c1B loop
301 execute immediate 'alter index '||g_owner||'.'||r1b.index_name||' logging noparallel';
302 end loop;
303
304
305 IF l_debug_flag = 'Y' THEN
306 g_debug_msg := 'Indexes created';
307 FII_UTIL.put_line('');
308 FII_UTIL.put_line(g_debug_msg);
309 END IF;
310
311
312
313 EXCEPTION
314 when others then
315 Errbuf:= sqlerrm;
316 p_retcode:=sqlcode;
317 if l_debug_flag = 'Y' then
318 FII_UTIL.put_line('ERROR in create_index--> ' ||p_retcode||':'||Errbuf);
319 FII_UTIL.put_line('Phase--> ' || g_debug_msg);
320 FII_UTIL.put_line('Failing Statement: ' || var5);
321 end if;
322
323
324 -- Index creation has failed. Drop any indexes already created.
325 IF l_debug_flag = 'Y' THEN
326 g_debug_msg := 'Index creation failed. Dropping any indexes that may have been created';
327 FII_UTIL.put_line('');
328 FII_UTIL.put_line(g_debug_msg);
329 END IF;
330
331 open c2;
332 loop
333 fetch c2 into rec2;
334 exit when c2%NOTFOUND;
335 var2:=rec2.index_name;
336 execute immediate 'drop index ' ||g_owner||'.'||var2;
337 end loop;
338
339 close c2;
340
341
342 end create_index; -- create index
343
344 end fii_index_util;
[Home] [Help]