1 PACKAGE BODY HRI_UTL_DDL AS
2 /* $Header: hriutddl.pkb 120.2.12020000.2 2012/11/30 07:28:30 karthmoh ship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 -- PROCEDURE recreate_indexes
6 -- -----------------------------------------------------------------------------
7 --
8 -- This procedure recreates the indexes for the specified table using the
9 -- definitions stored in the temporary table
10 --
11 -- Parameter Type Description
12 -- ------------------------ ---- ---------------------------------------------
13 -- p_application_short_name IN Short name of the application product
14 -- calling this routine
15 -- p_table_name IN Table for which the indexes are to be dropped
16 -- p_table_owner IN Name of the Schema owning the table
17 -- -----------------------------------------------------------------------------
18 --
19 PROCEDURE recreate_indexes(p_application_short_name IN VARCHAR2,
20 p_table_name IN VARCHAR2,
21 p_table_owner IN VARCHAR2)
22 IS
23 --
24 -- Gets create index statements from the temporary ddl table
25 --
26 CURSOR index_csr IS
27 SELECT ddl_object
28 ,ddl_stmt
29 ,ddl_type
30 FROM hri_utl_dynmc_ddl_infrmtn
31 WHERE table_name = p_table_name
32 AND table_owner = p_table_owner
33 AND ddl_type IN ('INDEX', 'INDEX LOGGING')
34 ORDER BY DECODE(ddl_type, 'INDEX', 1, 2);
35 --
36 -- Variables for getting the APPLSYS schema name
37 --
38 l_fnd_schema VARCHAR2(300);
39 l_dummy1 VARCHAR2(2000);
40 l_dummy2 VARCHAR2(2000);
41 --
42 BEGIN
43 --
44 -- Get APPLSYS schema name
45 --
46 IF (fnd_installation.get_app_info('FND',l_dummy1, l_dummy2, l_fnd_schema)) THEN
47 --
48 -- Loop through indexes to recreate / alter
49 --
50 FOR index_rec IN index_csr LOOP
51 --
52 IF (index_rec.ddl_type = 'INDEX') THEN
53 --
54 -- use AD API to recreate index
55 --
56 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
57 application_short_name => p_application_short_name,
58 statement_type => ad_ddl.create_index,
59 statement => index_rec.ddl_stmt,
60 object_name => index_rec.ddl_object);
61 --
62 ELSIF (index_rec.ddl_type = 'INDEX LOGGING') THEN
63 --
64 -- alter index
65 --
66 EXECUTE IMMEDIATE index_rec.ddl_stmt;
67 --
68 END IF;
69 --
70 -- Remove temporary ddl
71 --
72 DELETE FROM hri_utl_dynmc_ddl_infrmtn
73 WHERE table_name = p_table_name
74 AND table_owner = p_table_owner
75 AND ddl_object = index_rec.ddl_object
76 AND ddl_type = index_rec.ddl_type;
77 --
78 END LOOP;
79 --
80 -- for bug 3738009, commiting the transaction so that the transaction is not open after
81 -- completion of this process
82 --
83 COMMIT;
84 --
85 END IF;
86 --
87 END recreate_indexes;
88 --
89 -- -----------------------------------------------------------------------------
90 -- PROCEDURE log_and_drop_indexes
91 -- -----------------------------------------------------------------------------
92 --
93 -- This procedure drops all the indexes for a table and inserts definition
94 -- of the indexes in a temporary table. Using this definition the procedure
95 -- recreate_indexes can recreate the indexes. If some of the indexes are not
96 -- to be dropped, a comma separated list can be passed in parameter
97 -- p_index_excptn_lst.
98 --
99 -- NOTE: The procedure will not drop the primary key index.
100 --
101 -- Parameter Type Description
102 -- ------------------------ ---- ---------------------------------------------
103 -- p_application_short_name IN Short name of the application product
104 -- calling this routine
105 -- p_table_name IN Table for which the indexes are to be dropped
106 -- p_table_owner IN Name of the Schema owning the table
107 -- p_index_excptn_lst IN Pass a comma separated list of indexes which
108 -- are not to be dropped
109 -- -----------------------------------------------------------------------------
110 --
111 PROCEDURE log_and_drop_indexes(p_application_short_name IN VARCHAR2,
112 p_table_name IN VARCHAR2,
113 p_table_owner IN VARCHAR2,
114 p_index_excptn_lst IN VARCHAR2 DEFAULT NULL )
115 IS
116 --
117 -- Cursor to get the index properties
118 --
119 -- Create index with NOLOGGING
120 -- Alter index after creation if LOGGING is set
121 --
122 CURSOR storage_csr IS
123 SELECT index_name index_name
124 ,DECODE(uniqueness, 'UNIQUE', 'UNIQUE ', null) uniqueness
125 ,DECODE(INDEX_TYPE,'NORMAL',null,INDEX_TYPE) index_type
126 ,NVL(LOGGING, 'NO') logging
127 ,DECODE(PARTITIONED,'YES','LOCAL ',' ') ||
128 DECODE(NVL(TABLESPACE_NAME,'###'),'###',null,'TABLESPACE ' ||TABLESPACE_NAME) ||
129 ' NOLOGGING' ||
130 ' STORAGE (INITIAL ' || NVL(to_char(initial_extent), '4K') ||
131 ' NEXT ' || NVL(to_char(next_extent), '40K') ||
132 ' MINEXTENTS ' || NVL(to_char(min_extents), '1') ||
133 ' MAXEXTENTS ' || NVL(to_char(max_extents), 'UNLIMITED') ||
134 ' PCTINCREASE ' || NVL(to_char(pct_increase), '0') ||
135 ' FREELIST GROUPS ' || NVL(to_char(freelist_groups), '4') ||
136 ' FREELISTS ' || NVL(to_char(freelists), '4') || ')' ||
137 ' PCTFREE ' || NVL(to_char(pct_free), '10') ||
138 ' INITRANS ' || NVL(to_char(ini_trans), '11') ||
139 ' MAXTRANS ' || NVL(to_char(max_trans), '255') ||
140 ' PARALLEL ' storage_clause
141 FROM all_indexes
142 WHERE table_name = p_table_name
143 AND table_owner = p_table_owner
144 AND owner = p_table_owner
145 --
146 -- for bug 3738009, filter out the system created indexes on the materialized
147 -- views since these are not to be dropped and recreated
148 --
149 AND index_name NOT LIKE 'I_SNAP$%';
150 --
151 -- Cursor to get the table columns referenced by the index
152 --
153 CURSOR index_columns_csr(v_index_name VARCHAR2) IS
154 SELECT column_name
155 FROM all_ind_columns
156 WHERE index_owner = p_table_owner
157 AND table_owner = p_table_owner
158 AND table_name = p_table_name
159 AND index_name = v_index_name
160 ORDER BY column_position;
161 --
162 -- Variables for getting the APPLSYS schema name
163 --
164 l_fnd_schema VARCHAR2(300);
165 l_dummy1 VARCHAR2(2000);
166 l_dummy2 VARCHAR2(2000);
167 --
168 -- Other local variable
169 --
170 l_index_columns VARCHAR2(2000);
171 l_create_index_stmt VARCHAR2(4000);
172 l_alter_index_stmt VARCHAR2(4000);
173 l_drop_index_stmt VARCHAR2(4000);
174 --
175 -- Exception
176 --
177 PRIMARY_KEY_INDEX EXCEPTION;
178 --
179 pragma exception_init(PRIMARY_KEY_INDEX,-02429);
180 --
181 BEGIN
182 --
183 -- Get APPLSYS schema name
184 --
185 IF (fnd_installation.get_app_info('FND',l_dummy1, l_dummy2, l_fnd_schema)) THEN
186 --
187 -- Loop through indexes defined on the table
188 --
189 FOR index_rec IN storage_csr LOOP
190 --
191 -- Do not drop the index if it is included in the list of exceptional
192 -- index which are not to be dropped.
193 --
194 IF nvl(instr(p_index_excptn_lst, index_rec.index_name),0) = 0 then
195 --
196 -- Initialize index column list
197 --
198 l_index_columns := NULL;
199 --
200 -- Loop through columns the index references
201 --
202 FOR index_column IN index_columns_csr(index_rec.index_name) LOOP
203 --
204 -- Build up the index column list
205 --
206 l_index_columns := l_index_columns || index_column.column_name || ',';
207 --
208 END LOOP;
209 --
210 -- Add the bracketing, remove the last comma to format the columnn string
211 --
212 IF (l_index_columns IS NOT NULL) THEN
213 --
214 l_index_columns := '(' || RTRIM(l_index_columns,',') || ')';
215 --
216 END IF;
217 --
218 -- Build up the index creation statement
219 --
220 l_create_index_stmt := 'CREATE ' || index_rec.uniqueness || ' '
221 || index_rec.index_type || ' INDEX '
222 || p_table_owner || '.'
223 || index_rec.index_name || ' ON '
224 || p_table_owner || '.'
225 || p_table_name || ' '
226 || l_index_columns || ' '
227 || index_rec.storage_clause;
228 --
229 -- Build alter index statement
230 --
231 IF index_rec.logging = 'YES' THEN
232 l_alter_index_stmt := 'ALTER INDEX ' || p_table_owner || '.'
233 || index_rec.index_name ||
234 ' LOGGING NOPARALLEL';
235 END IF;
236 --
237 -- Build drop index statement
238 --
239 l_drop_index_stmt := 'DROP INDEX ' || p_table_owner || '.' || index_rec.index_name;
240 --
241 -- use AD API to drop the index
242 --
243 BEGIN
244 --
245 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
246 application_short_name => p_application_short_name,
247 statement_type => ad_ddl.drop_index,
248 statement => l_drop_index_stmt,
249 object_name => index_rec.index_name);
250 --
251 BEGIN
252 --
253 -- Store the index creation statement in the temporary ddl table
254 --
255 INSERT INTO hri_utl_dynmc_ddl_infrmtn
256 (table_name
257 ,table_owner
258 ,ddl_object
259 ,ddl_type
260 ,ddl_stmt)
261 VALUES
262 (p_table_name
263 ,p_table_owner
264 ,index_rec.index_name
265 ,'INDEX'
266 ,l_create_index_stmt);
267 --
268 EXCEPTION WHEN OTHERS THEN
269 --
270 -- Unique index on table violated, so an entry already exist in
271 -- the table for the index. Update the existing record.
272 --
273 UPDATE hri_utl_dynmc_ddl_infrmtn
274 SET ddl_stmt = l_create_index_stmt
275 WHERE table_name = p_table_name
276 AND table_owner = p_table_owner
277 AND ddl_object = index_rec.index_name
278 AND ddl_type = 'INDEX';
279 --
280 END;
281 --
282 IF (index_rec.logging = 'YES') THEN
283 --
284 BEGIN
285 --
286 -- Store the alter index statement
287 --
288 INSERT INTO hri_utl_dynmc_ddl_infrmtn
289 (table_name
290 ,table_owner
291 ,ddl_object
292 ,ddl_type
293 ,ddl_stmt)
294 VALUES
295 (p_table_name
296 ,p_table_owner
297 ,index_rec.index_name
301 EXCEPTION WHEN OTHERS THEN
298 ,'INDEX LOGGING'
299 ,l_alter_index_stmt);
300 --
302 --
303 -- Unique index on table violated, so an entry already exist in
304 -- the table for the index. Update the existing record.
305 --
306 UPDATE hri_utl_dynmc_ddl_infrmtn
307 SET ddl_stmt = l_alter_index_stmt
308 WHERE table_name = p_table_name
309 AND table_owner = p_table_owner
310 AND ddl_object = index_rec.index_name
311 AND ddl_type = 'INDEX LOGGING';
312 --
313 END;
314 --
315 END IF;
316
317 EXCEPTION
318 --
319 -- ad_ddl.do_ddl raises an exception when it tries to drop a primary
320 -- key index on a table. ignore the error and continue
321 --
322 WHEN PRIMARY_KEY_INDEX THEN
323 --
324 null;
325 --
326 END;
327 --
328 END IF; -- End of exception index list handling
329 --
330 END LOOP; -- End of storage_csr loop
331 --
332 -- for bug 3738009, commiting the transaction so that the transaction is not open after
333 -- completion of this process
334 --
335 COMMIT;
336 --
337 END IF; -- End of FND product installation check
338 --
339 END log_and_drop_indexes;
340 --
341 PROCEDURE maintain_mthd_partitions(p_table_name IN VARCHAR2,
342 p_table_owner IN VARCHAR2) IS
343
344 CURSOR no_tab_parts_csr IS
345 SELECT partition_count
346 FROM all_part_tables
347 WHERE table_name = p_table_name
348 AND owner = p_table_owner;
349
350 l_no_threads PLS_INTEGER;
351 l_no_parts PLS_INTEGER;
352 l_sql_stmt VARCHAR2(32000);
353
354 BEGIN
355
356 -- Get number of child threads and add one for master process
357 l_no_threads := NVL(fnd_profile.value('HRI_NO_THRDS_LAUNCH'),8) + 1;
358
359 -- Get number of existing partitions
360 OPEN no_tab_parts_csr;
361 FETCH no_tab_parts_csr INTO l_no_parts;
362 CLOSE no_tab_parts_csr;
363
364 -- Add any extra partitions as required
365 IF (l_no_parts < l_no_threads) THEN
366
367 FOR i IN (l_no_parts + 1)..(l_no_threads) LOOP
368
369 l_sql_stmt :=
370 'ALTER TABLE ' || p_table_owner || '.' || p_table_name ||
371 ' ADD PARTITION p' || to_char(i) || ' VALUES(' || to_char(i) || ')';
372
373 EXECUTE IMMEDIATE l_sql_stmt;
374
375 END LOOP;
376
377 END IF;
378
379 END maintain_mthd_partitions;
380
381 END HRI_UTL_DDL;
[Home] [Help]