1 package body AD_TSPACE_UTIL as
2 /* $Header: adtsutlb.pls 120.0 2005/05/25 11:38:21 appldev noship $*/
3 -- Star of Comments
4 --
5 -- Name
6 --
7 -- Package name: AD_TSPACE_UTIL
8 --
9 -- History
10 --
11 -- Sept-10-02 hxue Creation Date
12 -- Dec-10-02 sgadag Added function to return
13 -- application_short_name
14 --
15 -- End of Comments
16 --
17 global_is_new_ts_mode varchar2 (10);
18
19 function get_appl_id(x_product_short_name in varchar2) return number
20 is
21 l_appl_id number;
22
23 begin
24 SELECT application_id
25 INTO l_appl_id
26 FROM fnd_application
27 WHERE UPPER(application_short_name) = UPPER(x_product_short_name);
28
29 return(l_appl_id);
30
31 exception
32 when no_data_found then
33 raise_application_error(-20001,
34 'Application short name "'||UPPER(x_product_short_name)
35 ||'" is not registered in FND_APPLICATION. ');
36
37 end;
38
39 function get_product_short_name(x_appl_id in number) return varchar2
40 is
41 l_product_short_name varchar2(100);
42
43 begin
44 SELECT application_short_name
45 INTO l_product_short_name
46 FROM fnd_application
47 WHERE application_id = x_appl_id;
48
49 return(l_product_short_name);
50
51 exception
52 when no_data_found then
53 raise_application_error(-20001,
54 'Application ID "'|| x_appl_id
55 ||'" is not registered in FND_APPLICATION. ');
56
57 end get_product_short_name;
58
59
60
61 procedure is_new_ts_mode(x_ts_mode out NOCOPY varchar2) is
62 l_ts_mode varchar2(10);
63
64 begin
65
66 -- global_is_new_ts_mode already set
67
68 if global_is_new_ts_mode is not NULL
69 then
70 x_ts_mode := global_is_new_ts_mode;
71
72 else
73
74 -- global_is_new_ts_mode is NULL
75
76 begin
77 SELECT UPPER(nvl(is_new_ts_mode, 'N'))
78 INTO l_ts_mode
79 FROM FND_PRODUCT_GROUPS
80 WHERE PRODUCT_GROUP_ID = 1;
81
82 x_ts_mode := l_ts_mode;
83 global_is_new_ts_mode := l_ts_mode;
84
85 exception
86 when no_data_found then
87 raise_application_error(-20001,
88 'is_new_ts_mode() failed. '||
89 'No row with PRODUCT_GROUP_ID = 1 in FND_PRODUCT_GROUPS');
90 end;
91
92 end if;
93
94 end is_new_ts_mode;
95 --
96
97 procedure get_object_tablespace(x_product_short_name in varchar2,
98 x_object_name in varchar2,
99 x_object_type in varchar2,
100 x_index_lookup_flag in varchar2,
101 x_validate_ts_exists in varchar2,
102 x_is_object_registered out NOCOPY varchar2,
103 x_ts_exists out NOCOPY varchar2,
104 x_tablespace out NOCOPY varchar2) is
105 l_dummy varchar2(30);
106 l_appl_id number;
107 l_new_ts_mode varchar2(10);
108 l_is_object_registered varchar2(30);
109 l_object_name varchar2(30);
110 l_object_type varchar2(30);
111 l_tspace_type varchar2(30);
112 l_tspace varchar2(30);
113
114
115 CURSOR OBJ_INFO (c_appl_id number,
116 c_object_name in varchar2)
117 is
118 select upper(TABLESPACE_TYPE), upper(OBJECT_TYPE)
119 from FND_OBJECT_TABLESPACES
120 where UPPER(OBJECT_NAME)=UPPER(c_object_name)
121 AND APPLICATION_ID = c_appl_id;
122
123 CURSOR VALIDATE_TS (c_tspace in varchar2)
124 is
125 select TABLESPACE_NAME
126 from DBA_TABLESPACES
127 where TABLESPACE_NAME=UPPER(c_tspace);
128
129
130 begin
131
132
133 --
134 -- get appl_id
135 --
136
137 l_appl_id := get_appl_id(x_product_short_name);
138
139
140 --
141 -- Get is_new_ts_mode
142 --
143
144 is_new_ts_mode(l_new_ts_mode); -- already UPPER
145 -- dbms_output.PUT_LINE(l_ts_mode);
146
147
148 --
149 -- check mis-use case: check if user is using index name as object name
150 -- and index flag is Y
151 --
152
153 if (UPPER(x_object_type) like '%INDEX%')
154
155 then
156 raise_application_error(-20001,
157 'To get tablespace for index "'||UPPER(x_object_name)||
158 '", pass TABLE NAME on which index is based, '||
159 ' pass OBJECT TYPE as TABLE,'||
160 ' and INDEX LOOKUP FLAG as Y.');
161 end if;
162
163
164 --
165 -- Valid x_object_type
166 --
167
168 if (UPPER(x_object_type) <> 'TABLE'
169 AND
170 UPPER(x_object_type) <> 'MVIEW'
171 AND
172 UPPER(x_object_type) <> 'AQ_TABLE'
173 AND
174 UPPER(x_object_type) <> 'IOT_TABLE'
175 AND
176 UPPER(x_object_type) <> 'MV_LOG')
177
178 then
179 raise_application_error(-20001,
180 'Unknown object type "'||UPPER(x_object_type)||
181 '". Valid object types are TABLE, MVIEW, AQ_TABLE, IOT_TABLE and MV_LOG.');
182 end if;
183
184
185 --
186 -- open cursor, get tspace type, object_type
187 -- and set flag x_is_object_registered
188 --
189
190
191 open OBJ_INFO(l_appl_id, x_object_name);
192
193 fetch OBJ_INFO
194 into l_tspace_type, l_object_type;
195
196 if OBJ_INFO%NOTFOUND then
197 close OBJ_INFO;
198 l_is_object_registered:='N';
199 x_is_object_registered:='N';
200
201 else
202 close OBJ_INFO;
203 l_is_object_registered:='Y';
204 x_is_object_registered:='Y';
205
206 end if;
207
208
209 --
210 -- get tablespace in new and old mode
211 --
212
213 if (l_new_ts_mode = 'Y') -- New mode
214 then
215
216 if (l_is_object_registered='N')
217 then
218
219 -- Object not classified, try to get default logical tablespace
220
221 if (UPPER(x_object_type)='TABLE')
222 then
223 l_tspace_type := 'TRANSACTION_TABLES';
224
225 elsif (UPPER(x_object_type)='MVIEW')
226 then
227 l_tspace_type := 'SUMMARY';
228
229 elsif (UPPER(x_object_type)='AQ_TABLE')
230 then
231 l_tspace_type := 'AQ';
232
233 elsif (UPPER(x_object_type)='IOT_TABLE')
234 then
235 l_tspace_type := 'TRANSACTION_TABLES';
236
237 elsif (UPPER(x_object_type)='MV_LOG')
238 then
239 l_tspace_type := 'SUMMARY';
240
241 else
242 raise_application_error(-20001,
243 'Internal error in get_object_tablespace: the passed object type "'||UPPER(x_object_type)||
244 '" is incorrect');
245 end if;
246
247
248 else -- object classified in FND_OBJECT_TABLESPACES
249
250 -- check if object type match
251
252 if (UPPER(l_object_type) <> UPPER(x_object_type))
253 then
254 raise_application_error(-20001,
255 'The passed object type "'||UPPER(x_object_type)||
256 '" for "'||UPPER(x_object_name)||
257 '" does not match seeded object type "'||UPPER(l_object_type)||
258 '" in FND_OBJECT_TABLESPACES.');
259 end if;
260
261 end if; -- end if l_is_object_registered='N''
262
263
264 -- remap logical tspace
265
266 if (UPPER(x_object_type)='TABLE'
267 and
268 UPPER(x_index_lookup_flag) = 'Y'
269 and
270 UPPER(l_tspace_type) = 'TRANSACTION_TABLES')
271
272 then
273
274 l_tspace_type := 'TRANSACTION_INDEXES';
275
276 end if; -- end remapping
277
278
279
280 -- get physical tspace
281
282 begin
283 SELECT UPPER(TABLESPACE)
284 INTO l_tspace
285 FROM FND_TABLESPACES
286 WHERE UPPER(TABLESPACE_TYPE)=UPPER(l_tspace_type);
287
288 x_tablespace := l_tspace;
289
290 exception
291 when NO_DATA_FOUND then
292 raise_application_error(-20001,
293 'TABLESPACE_TYPE "'||
294 UPPER(l_tspace_type)||
295 '" is not found in FND_TABLESPACES.');
296 end;
297
298
299 else -- old mode
300
301 begin
302
303
304 if (UPPER(x_object_type)='MVIEW')
305
306 --get tablespace for MVIEW
307
308 then
309 begin
310 SELECT UPPER(nvl(TABLESPACE, 'UNKNOWN'))
311 INTO l_tspace
312 FROM FND_PRODUCT_INSTALLATIONS
313 WHERE APPLICATION_ID = 191
314 AND install_group_num in (0, 1);
315
316 if (l_tspace = 'UNKNOWN')
317 then raise_application_error(-20001,
318 'The tablespace value for application "BIS" is NULL '||
319 'in FND_PRODUCT_INSTALLATIONS. The default tablespace '||
320 'for MVIEW is the tablespace of "BIS".');
321 end if;
322
323 x_tablespace := l_tspace;
324
325 exception
326 when NO_DATA_FOUND then
327 raise_application_error(-20001,
328 'Application "BIS" is not registered in '||
329 'FND_PRODUCT_INSTALLATIONS. The default'||
330 ' tablespace for MVIEW is the tablespace of "BIS".');
331 end;
332
333
334 elsif (UPPER(x_index_lookup_flag) = 'Y')
335
336 --get tablespace for index
337
338 then
339 begin
340 SELECT UPPER(nvl(INDEX_TABLESPACE, 'UNKNOWN'))
341 INTO l_tspace
342 FROM FND_PRODUCT_INSTALLATIONS
343 WHERE APPLICATION_ID = l_appl_id;
344
345 if (l_tspace = 'UNKNOWN')
346 then raise_application_error(-20001,
347 'The index tablespace value for application "'||
348 UPPER(x_product_short_name)||
349 '" is NULL '||
350 'in FND_PRODUCT_INSTALLATIONS');
351 end if;
352
353 x_tablespace := l_tspace;
354
355 exception
356 when NO_DATA_FOUND then
357 raise_application_error(-20001,
358 'Application "'||
359 UPPER(x_product_short_name)||
360 '" is not registered in FND_PRODUCT_INSTALLATIONS.');
361 end;
362
363
364 else
365
366 -- get tablespace from FND_PRODUCT_INSTALLATIONS for
367 -- TABLE, AQ_TABLE, IOT_TABLE and MV_LOGs
368
369 begin
370
371 SELECT UPPER(nvl(TABLESPACE, 'UNKNOWN'))
372 INTO l_tspace
373 FROM FND_PRODUCT_INSTALLATIONS
374 WHERE APPLICATION_ID = l_appl_id;
375
376 if (l_tspace = 'UNKNOWN')
377 then raise_application_error(-20001,
378 'The data tablespace value for application "'||
379 UPPER(x_product_short_name)||
380 '" is NULL '||
381 'in FND_PRODUCT_INSTALLATIONS');
382
383 end if;
384
385 x_tablespace := l_tspace;
386
387 exception
388 when NO_DATA_FOUND then
389 raise_application_error(-20001,
390 'Application "'||
391 UPPER(x_product_short_name)||
392 '" is not registered in FND_PRODUCT_INSTALLATIONS.');
393 end;
394
395 end if; -- end if (UPPER(x_index_lookup_flag) = 'Y')
396
397 end; -- end else: old mode
398
399 end if;-- end if new or old mode
400
401
402
403 --
404 -- validate tablespace exists
405 --
406
407 if (UPPER(x_validate_ts_exists) = 'Y')
411 fetch VALIDATE_TS
408 then
409 open VALIDATE_TS(l_tspace);
410
412 into l_dummy;
413
414 if VALIDATE_TS%NOTFOUND then
415 -- no row matched in FND_OBJECT_TABLESPACES with given x_object_name and l_appl_id
416 close VALIDATE_TS;
417 x_ts_exists:='N';
418
419 else
420 close VALIDATE_TS;
421 x_ts_exists:='Y';
422
423 end if;
424
425 end if;
426
427
428 end get_object_tablespace;
429
430
431 procedure get_tablespace_name(x_product_short_name in varchar2,
432 x_tablespace_type in varchar2,
433 x_validate_ts_exists in varchar2,
434 x_ts_exists out NOCOPY varchar2,
435 x_tablespace out NOCOPY varchar2) is
436
437 l_dummy varchar2(30);
438 l_appl_id number;
439 l_new_ts_mode varchar2(10);
440 l_tspace varchar2(30);
441
442
443 CURSOR TS_NEWMODE (c_tspace_type in varchar2)
444 is
445 select UPPER(TABLESPACE)
446 from FND_TABLESPACES
447 where UPPER(TABLESPACE_TYPE)=UPPER(c_tspace_type);
448
449
450 CURSOR VALIDATE_TS (c_tspace in varchar2)
451 is
452 select TABLESPACE_NAME
453 from DBA_TABLESPACES
454 where TABLESPACE_NAME=UPPER(c_tspace);
455
456
457 begin
458
459
460 --
461 -- get appl_id
462 --
463
464 l_appl_id := get_appl_id(x_product_short_name);
465
466
467 --
468 -- Get is_new_ts_mode
469 --
470
471 is_new_ts_mode(l_new_ts_mode); -- already UPPER
472
473
474 --
475 -- validate tablespace type
476 --
477
478
479 open TS_NEWMODE(x_tablespace_type);
480
481 fetch TS_NEWMODE
482 into l_tspace;
483
484 if TS_NEWMODE%NOTFOUND then
485
486 -- no row matched in FND_TABLESPACES with given x_tablespace_type
487
488 close TS_NEWMODE;
489
490 raise_application_error(-20001,
491 'TABLESPACE_TYPE "'||
492 UPPER(x_tablespace_type)||
493 '" not found in FND_TABLESPACES.');
494
495 else
496 close TS_NEWMODE;
497
498 end if;
499
500
501 --
502 -- get tablespace in new and old mode
503 --
504
505 if (l_new_ts_mode = 'Y') -- New mode
506 then
507
508 -- Use previous cursor to get physical tspace
509
510 x_tablespace:=l_tspace;
511
512
513 else -- old mode
514
515 begin
516
517 if (UPPER(x_tablespace_type) = 'TRANSACTION_INDEXES')
518
519 --get tablespace for index
520
521 then
522 begin
523 SELECT UPPER(nvl(INDEX_TABLESPACE, 'UNKNOWN'))
524 INTO l_tspace
525 FROM FND_PRODUCT_INSTALLATIONS
526 WHERE APPLICATION_ID = l_appl_id;
527
528 if (l_tspace = 'UNKNOWN')
529 then raise_application_error(-20001,
530 'The index tablespace value for application "'||
531 UPPER(x_product_short_name)||
532 '" is NULL '||
533 'in FND_PRODUCT_INSTALLATIONS');
534 end if;
535
536 x_tablespace := l_tspace;
537
538 exception
539 when NO_DATA_FOUND then
540 raise_application_error(-20001,
541 'Application "'||
542 UPPER(x_product_short_name)||
543 '" is not registered in FND_PRODUCT_INSTALLATIONS.');
544 end;
545
546 -- get tablespace for SUMMARY (MVIEW)
547 -- same logic as AD_TABLESPACE_UTILITIES
548
549 elsif (UPPER(x_tablespace_type) = 'SUMMARY')
550
551
552 then
553 begin
554 SELECT UPPER(nvl(TABLESPACE, 'UNKNOWN'))
555 INTO l_tspace
556 FROM FND_PRODUCT_INSTALLATIONS
557 WHERE APPLICATION_ID = 191
558 AND install_group_num in (0, 1);
559
560 if (l_tspace = 'UNKNOWN')
561 then raise_application_error(-20001,
562 'The tablespace value for application "BIS" is NULL '||
563 'in FND_PRODUCT_INSTALLATIONS. The default tablespace '||
564 'for SUMMARY is the tablespace of "BIS".');
565 end if;
566
567 x_tablespace := l_tspace;
568
569 exception
570 when NO_DATA_FOUND then
571 raise_application_error(-20001,
572 'Application "BIS" is not registered in '||
573 'FND_PRODUCT_INSTALLATIONS. The default'||
574 ' tablespace for SUMMARY is the tablespace of "BIS".');
575 end;
576
577 else
578
579 --get tablespace for TRANSACTION_TABLES, REFERENCE, INTERFACE, NOLOGGING, ARCHIVE
580
581 begin
582
583 SELECT UPPER(nvl(TABLESPACE, 'UNKNOWN'))
584 INTO l_tspace
585 FROM FND_PRODUCT_INSTALLATIONS
586 WHERE APPLICATION_ID = l_appl_id;
587
588 if (l_tspace = 'UNKNOWN')
589 then raise_application_error(-20001,
590 'The data tablespace value for application "'||
591 UPPER(x_product_short_name)||
592 '" is NULL '||
593 'in FND_PRODUCT_INSTALLATIONS');
594
595 end if;
596
597 x_tablespace := l_tspace;
598
599 exception
600 when NO_DATA_FOUND then
601 raise_application_error(-20001,
602 'Application "'||
603 UPPER(x_product_short_name)||
604 '" is not registered in FND_PRODUCT_INSTALLATIONS.');
605 end;
606
607 end if; -- end if (UPPER(x_tablespace_type) = 'TRANSACTION_INDEXES')
608
609 end; -- end else: old mode
610
611 end if;-- end if new or old mode
612
613
614
615 --
616 -- validate tablespace exists
617 --
618
619 if (UPPER(x_validate_ts_exists) = 'Y')
620 then
621 open VALIDATE_TS(l_tspace);
622
623 fetch VALIDATE_TS
624 into l_dummy;
625
626 if VALIDATE_TS%NOTFOUND then
627 -- no row matched in FND_OBJECT_TABLESPACES
628 -- with given x_object_name and l_appl_id
629
630 close VALIDATE_TS;
631 x_ts_exists:='N';
632
633 else
634 close VALIDATE_TS;
635 x_ts_exists:='Y';
636
637 end if;
638
639 end if;
640
641
642 end get_tablespace_name;
643
644 end AD_TSPACE_UTIL;
[Home] [Help]