1 PACKAGE BODY CS_SR_SYNC_INDEX_PKG AS
2 /* $Header: cssrsyxb.pls 120.2 2006/02/01 18:23:48 klou noship $ */
3
4 -- errbuf = err messages
5 -- retcode = 0 success, 1 = warning, 2=error
6
7 -- bmode: S = sync OFAST=optimize fast, OFULL = optimize full
8
9 -- (TEXT) ---
10 -- Add global exception handlers.
11 invalid_mode_error EXCEPTION;
12 invalid_action_error EXCEPTION;
13 drop_index_error EXCEPTION;
14 create_index_error EXCEPTION;
15 rebuild_cache_error EXCEPTION;
16
17 -- Declare cs and apps schema.
18 g_cs_short_name VARCHAR2(10) := UPPER('CS'); -- set at patching
19 g_apps_short_name VARCHAR2(10) := UPPER('APPS'); -- set at patching
20
21 -- (TEXT) eof --
22
23 -- (4917652): add batch size for bulk update.
24 G_BATCH_SIZE NUMBER := 10000;
25 -- 4917652_eof
26
27 -- Internal API to create summary index
28 PROCEDURE Create_Summary_Index (
29 ERRBUF OUT NOCOPY VARCHAR2,
30 RETCODE OUT NOCOPY NUMBER)
31 IS
32 l_create_cmmd VARCHAR2(500):= NULL;
33
34 l_index_name VARCHAR2(30) := 'SUMMARY_CTX_INDEX';
35 l_db_version NUMBER := null;
36 l_compatibility VARCHAR2(100) := null;
37 l_db_version_str VARCHAR2(100) := null;
38
39 l_parallel_cmd VARCHAR2(100);
40
41 CURSOR get_index_cursor(p_index_name VARCHAR2,
42 p_owner VARCHAR2) IS
43 SELECT COUNT(*) FROM dba_indexes
44 WHERE index_name = UPPER(p_index_name)
45 AND owner= UPPER(p_owner);
46
47 l_total NUMBER := 0;
48 l_drop_index VARCHAR2(500);
49
50 l_temp_var varchar2(200);
51
52
53 BEGIN
54 -- Initialize variables
55 l_parallel_cmd := ' ';
56
57 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
58
59 If l_db_version_str is null Then
60 l_db_version := 8;
61 Else
62 l_db_version := to_number(substr(l_db_version_str, 1,(instr(l_db_version_str, '.'))-1));
63 End If;
64
65 if l_db_version > 8 then
66 l_parallel_cmd := ' PARALLEL ';
67 end if ;
68
69 --1. Drop index if exists.
70 OPEN get_index_cursor(l_index_name, g_cs_short_name);
71 FETCH get_index_cursor INTO l_total;
72 CLOSE get_index_cursor;
73
74 IF l_total > 0 THEN
75 l_drop_index := 'drop index '||g_cs_short_name||
76 '.'||l_index_name||' force ';
77
78 EXECUTE IMMEDIATE l_drop_index;
79 END IF;
80
81 --2. Create summary index.
82 l_create_cmmd :=
83 ' create index '||g_cs_short_name||
84 '.SUMMARY_CTX_INDEX on cs_incidents_all_tl(summary) '||
85 ' indextype is ctxsys.context '||
86 ' parameters(''lexer ' || g_apps_short_name || '.CS_SR_GLOBAL_LEXER '||
87 ' language column source_lang '||
88 ' memory 10M '||
89 ' storage ' ||g_apps_short_name||'.CS_SR_INDEX_STORAGE '||
90 ' '' )';
91
92 l_create_cmmd := l_create_cmmd || l_parallel_cmd;
93
94 EXECUTE IMMEDIATE l_create_cmmd;
95
96 -- DO NOT CATCH EXCEPTION. LET IT STACK UP.
97
98 END Create_Summary_Index;
99
100
101 PROCEDURE Sync_All_Index (
102 ERRBUF OUT NOCOPY VARCHAR2,
103 RETCODE OUT NOCOPY NUMBER,
104 BMODE IN VARCHAR2 DEFAULT NULL )
105 IS
106 l_errbuf varchar2(2000);
107 l_retcode number;
108 l_mode varchar2(5);
109 BEGIN
110 l_mode := bmode;
111
112 if(bmode is null) then
113 l_mode := 'S';
114
115 -- 'DR_Mode: Added Support for 'DR' mode
116 elsif( bmode not in ('DR','S','OFAST', 'OFULL')) then
117 errbuf := 'Invalid mode specified';
118 begin
119 --3..FND_FILE.PUT_LINE(3..FND_FILE.LOG, errbuf);
120 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
121 exception
122 when others then
123 null;
124 end;
125
126 retcode := 2;
127 return;
128 end if;
129
130 Sync_Summary_Index (l_errbuf, l_retcode, l_mode);
131
132 -- If not success, return error
133 if( l_retcode <> 0 ) then
134 errbuf := l_errbuf;
135 retcode := l_retcode;
136 end if;
137
138 -- Return successfully
139 errbuf := 'Success';
140 retcode := 0;
141
142 END SYNC_ALL_INDEX;
143
144 PROCEDURE Sync_Summary_Index (
145 ERRBUF OUT NOCOPY VARCHAR2,
146 RETCODE OUT NOCOPY NUMBER,
147 BMODE IN VARCHAR2)
148
149 IS
150
151 -- To fix bug 3431755 added owner to the where clause
152 -- cursor to get the owner of the CTX_SUMMARY_INDEX
153 cursor get_ind_owner (p_owner varchar2) is
154 select owner
155 from all_indexes
156 where index_name = 'SUMMARY_CTX_INDEX'
157 and owner = p_owner
158 and index_type = 'DOMAIN';
159
160 -- end of changes for bug 3431755
161
162 l_ind_owner VARCHAR2(90);
163 sql_stmt1 VARCHAR2(250);
164
165 BEGIN
166
167 if(bmode is null or bmode not in ('DR', 'S', 'OFAST', 'OFULL')) then
168 errbuf := 'Invalid mode specified';
169
170 begin
171 --3..FND_FILE.PUT_LINE(3..FND_FILE.LOG, errbuf);
172 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
173 exception
174 when others then
175 null;
176 end;
177
178 retcode := 2;
179 return;
180 end if;
181
182 --DR_MODE
183 --1. Process the DR mode. We should not check the index
184 -- owner before creating index, as we drop the index
185 -- before calling the create index.
186 IF (bmode = 'DR') THEN
187 create_summary_index(errbuf,
188 retcode
189 );
190 ELSE -- If not DR mode, process as it used to.
191
192 open get_ind_owner(g_cs_short_name);
193 fetch get_ind_owner into l_ind_owner;
194
195 if ( get_ind_owner%NOTFOUND ) then
196 close get_ind_owner;
197
198 errbuf := 'Index SUMMARY_CTX_INDEX is not found. Please create the domain index ' ||
199 'before executing this concurrent program.';
200 begin
201 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
202 exception
203 when others then
204 null;
205 end;
206 retcode := 2;
207 return;
208 end if;
209
210 close get_ind_owner;
211
212 sql_stmt1 := 'alter index ' || l_ind_owner || '.summary_ctx_index REBUILD ONLINE';
213
214 if(bmode = 'S') then
215 sql_stmt1 := sql_stmt1 || ' parameters (''SYNC'') ';
216 elsif(bmode = 'OFAST') then
217 sql_stmt1 := sql_stmt1 || ' parameters (''OPTIMIZE FAST'') ';
218 elsif(bmode = 'OFULL') then
219 sql_stmt1 := sql_stmt1 || ' parameters (''OPTIMIZE FULL'') ';
220 end if;
221
222 if (bmode = 'S') then
223 --ctx_ddl.sync_index( '1..summary_ctx_index' );
224 ad_ctx_ddl.sync_index( l_ind_owner ||'.summary_ctx_index' );
225 else
226 EXECUTE IMMEDIATE sql_stmt1;
227 end if;
228 END IF;
229
230 -- Return successfully
231 errbuf := 'Success';
232 retcode := 0;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 -- Return error
237 errbuf := 'Unexpected error while attempting to sync domain index SUMMARY_CTX_INDEX.'
238 || ' Error : '|| SQLERRM;
239
240 begin
241 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
242 exception
243 when others then
244 null;
245 end;
246
247 retcode := 2;
248
249 END Sync_Summary_Index;
250
251 ---------------- (TEXT) -----------------------------
252
253 -- Internal function: sync_index
254 FUNCTION Sync_index( index1 IN VARCHAR2,
255 bmode IN VARCHAR2,
256 pindex_with IN VARCHAR2,
257 pworker IN NUMBER DEFAULT 0)
258 Return VARCHAR2
259 IS
260 l_index_name VARCHAR2(300) :=index1; -- g_cs_short_name||'.'||index1;
261
262 l_max_worker NUMBER := get_max_parallel_worker;
263 l_db_version NUMBER := null;
264 l_compatibility VARCHAR2(100) := null;
265 l_db_version_str VARCHAR2(100) := null;
266 l_worker NUMBER := pworker;
267 l_index_with VARCHAR2(30) := pindex_with;
268
269
270 BEGIN
271
272 IF bmode = 'S' THEN
273 ad_ctx_ddl.sync_index( l_index_name );
274 ELSIF bmode = 'OFAST' THEN
275 ad_ctx_ddl.optimize_index( l_index_name, CTX_DDL.OPTLEVEL_FAST, NULL, NULL );
276 ELSIF bmode = 'OFULL' THEN
277 ad_ctx_ddl.OPTIMIZE_INDEX( l_index_name, CTX_DDL.OPTLEVEL_FULL, NULL, NULL );
278 ELSIF bmode = 'R' THEN
279
280 -- Start of change for bug fix 4321240
281 --
282
283 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
284 If l_db_version_str is null Then
285 l_db_version := 8;
286 Else
287 l_db_version := to_number(substr(l_db_version_str, 1,
288 (instr(l_db_version_str, '.'))-1));
289 End If;
290
291 If l_db_version Is Not Null Then
292 If l_db_version > 8 Then
293 IF l_index_with = 'PARALLEL' THEN
294 IF l_worker > l_max_worker THEN
295 l_worker := l_max_worker;
296 END IF;
297 IF l_worker > 0 THEN
298 EXECUTE IMMEDIATE 'alter index ' || l_index_name ||' REBUILD parallel '||TO_CHAR(l_worker);
299 ELSE
300 EXECUTE IMMEDIATE 'alter index ' || l_index_name || ' REBUILD';
301 END IF;
302 ELSIF l_index_with = 'ONLINE' THEN
303 EXECUTE IMMEDIATE 'alter index ' || l_index_name ||' REBUILD PARAMETERS (''REPLACE'') ONLINE';
304
305 END IF;
306 ELSE
307 EXECUTE IMMEDIATE 'alter index ' || l_index_name ||' REBUILD';
308 End if; -- l_db_version eof
309 End If;
310 --
311 -- End of bug fix for bug 4321240
312
313 ELSIF bmode = 'DR' THEN
314 -- logic to drop or create is taken in the individual api.
315 NULL;
316 ELSE
317 FND_FILE.PUT_LINE(FND_FILE.LOG,
318 fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE'));
319 RAISE invalid_mode_error; -- let the exception populate back to the caller.
320 END IF;
321
322 Return 'Y';
323 END Sync_index;
324
325 /*
326 * Internal
327 * is_validate_mode: VALIDATE a synchronization MODE.
328 * RETURN 'Y' IF THE MODE IS valid. Otherwise RETURN 'N'.
329 */
330 FUNCTION is_validate_mode(bmode IN VARCHAR2) RETURN VARCHAR
331 IS
332 l_valid_mode VARCHAR2(1) := 'Y';
333 l_mode VARCHAR2(10) := bmode;
334 BEGIN
335 IF l_mode NOT IN ('S', 'R', 'OFAST', 'OFULL', 'DR' ) THEN
336 l_valid_mode := 'N';
337 END IF;
338 RETURN l_valid_mode;
339 END;
340
341 /*
342 * Internal
343 * get_max_parallel_worker: return the max number of processes
344 * to be used for parallel indexing.
345 */
346 FUNCTION get_max_parallel_worker RETURN NUMBER
347 IS
348 l_worker NUMBER := 0;
349 BEGIN
350 SELECT to_number(nvl(VALUE, 0)) INTO l_worker FROM v$parameter
351 WHERE NAME = 'job_queue_processes';
352
353 RETURN l_worker;
354 EXCEPTION
355 WHEN OTHERS THEN
356 RETURN l_worker;
357 END;
358
359 /*
360 * Internal
361 * resolve_parallel_indexing: Return an indexing command line that
362 * enables parallel indexing.
363 */
364 FUNCTION resolve_parallel_indexing (
365 p_create_cmd IN VARCHAR2,
366 p_worker IN NUMBER DEFAULT 0,
367 p_index_with IN VARCHAR2
368 ) RETURN VARCHAR
369 IS
370 l_cmd VARCHAR2(500) := p_create_cmd;
374
371 l_worker NUMBER := p_worker;
372 l_max_worker NUMBER := get_max_parallel_worker;
373 l_index_with VARCHAR2(30) := p_index_with;
375 --3576867
376 l_db_version NUMBER := null;
377 l_compatibility VARCHAR2(100) := null;
378 l_db_version_str VARCHAR2(100) := null;
379 BEGIN
380 If p_worker is null Then
381 l_worker := 0;
382 End If;
383
384 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
385 If l_db_version_str is null Then
386 l_db_version := 8;
387 Else
388 l_db_version := to_number(substr(l_db_version_str, 1,
389 (instr(l_db_version_str, '.'))-1));
390 End If;
391
392 If l_db_version Is Not Null Then
393 If l_db_version > 8 Then
394
395 -- Start of change for bug fix 4321240
396 --
397 IF l_index_with = 'PARALLEL' THEN
398 IF l_worker > l_max_worker THEN
399 l_worker := l_max_worker;
400 END IF;
401 IF l_worker > 0 THEN
402 l_cmd := l_cmd || ' parallel '||TO_CHAR(l_worker);
403 END IF;
404 ELSIF l_index_with = 'ONLINE' THEN
405 l_cmd := l_cmd || ' ONLINE';
406 END IF;
407 --
408 -- End of change for bug fix 4321240
409
410 End if; -- l_db_version eof
411 Else
412 l_worker := 0;
413 End If;
414
415 RETURN l_cmd;
416 EXCEPTION
417 WHEN OTHERS THEN
418 -- any errors: do not append anything.
419 RETURN p_create_cmd;
420 END resolve_parallel_indexing;
421
422
423 -- New internal procedures for bug 4917652
424 /*
425 * Populate service request text index.
426 * Index coulmn: cs_incidents_all_b.text_index
427 */
428 PROCEDURE populate_sr_text_index (
429 x_msg_error OUT NOCOPY VARCHAR2,
430 x_return_status OUT NOCOPY VARCHAR2)
431 IS
432 CURSOR all_srs IS
433 SELECT rowid
434 FROM cs_incidents_all_tl;
435
436 TYPE l_rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
437 l_rowid_list l_rowid_type;
438 l_index_name VARCHAR2(250);
439
440 BEGIN
441 -- Initialize local variables.
442 x_return_status := fnd_api.G_RET_STS_SUCCESS;
443 l_index_name := 'CS_INCIDENTS_ALL_TL_N1';
444
445 -- Bulk fetch a list of row id
446 OPEN all_srs;
447 LOOP
448 FETCH all_srs BULK COLLECT INTO l_rowid_list limit G_BATCH_SIZE;
449 FORALL i IN l_rowid_list.FIRST..l_rowid_list.LAST
450 UPDATE cs_incidents_all_tl
451 SET text_index = 'X'
452 WHERE rowid = l_rowid_list(i);
453
454 COMMIT;
455
456 -- Check if all_srs is notfound.
457 -- NOTE: this check should come at the end because for the last batch
458 -- the total number of sets being fetched may be less than the G_BATCH_SIZE.
459 -- If l_rowid_list is not filled with the exact number as the G_BATCH_SIZE,
460 -- all_srs%notfound is true. Putting this at the end
461 -- guarantees we process the last batch.
462 EXIT WHEN all_srs%NOTFOUND;
463 END LOOP;
464 CLOSE all_srs;
465
466 -- Call index synchronziation with
467 -- bmode: 'S'
468 -- pindex_with:
469 -- pworker: 0
470 x_return_status := Sync_index(
471 g_cs_short_name||'.'|| l_index_name,
472 'S',
473 'Online' -- will be by-passed in Sync_index for S mode
474 );
475 EXCEPTION
476 WHEN OTHERS THEN
477 ROLLBACK; -- do not use savepoint because savepoint is cleared when commit.
478 x_msg_error := 'populate_sr_text_index: '
479 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '
480 ||SQLERRM;
481 x_return_status := fnd_api.G_RET_STS_ERROR;
482 END populate_sr_text_index;
483 -- 4917652_eof
484
485 /*
486 * Sync_Text_Index
487 * Synchronize the CS_INCIDENTS_ALL_TL_N1 text index.
488 * Supported mode: S, DR, OFAST, OFAST, R
489 */
490 PROCEDURE Sync_Text_Index (
491 ERRBUF OUT NOCOPY VARCHAR2,
492 RETCODE OUT NOCOPY NUMBER,
493 BMODE IN VARCHAR2,
494 PINDEX_WITH IN VARCHAR2,
495 pworker IN NUMBER DEFAULT 0)
496
497 IS
498 index3 VARCHAR2(250) := 'CS_INCIDENTS_ALL_TL_N1';
499 l_mode VARCHAR2(10) := bmode;
500 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
501 l_create_cmmd VARCHAR2(500):= NULL;
502 l_index_with varchar2(30) := pindex_with;
503
504 BEGIN
505 -- Initialize some variables
506 retcode := 2; -- init return val to FAIL
507
508 IF l_mode IS NULL THEN -- default it to 'Sync'
509 l_mode := 'S';
510 END IF;
511
512 l_index_with := nvl(l_index_with, 'ONLINE');
513
514 IF is_validate_mode(l_mode) = 'N' THEN
515 RAISE invalid_mode_error;
516 END IF;
517
518 -- check whether it is 'DR'
519 IF l_mode = 'DR' THEN
520 Drop_Index(index3,
521 errbuf,
522 l_return_status);
523 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
524 RAISE drop_index_error;
525 END IF;
526
527 -- Create the index
528 l_create_cmmd :=
529 ' CREATE INDEX '||g_cs_short_name||'.cs_incidents_all_tl_n1 '
530 ||'on '||g_cs_short_name||'.cs_incidents_all_tl(text_index) '
531 ||' indextype is ctxsys.context parameters ('''
532 ||' datastore '||g_apps_short_name||'.CS_SR_TEXT_INDEX_ELES '
536 --4917652: leave command opened for further processing
533 ||' section group '||g_apps_short_name||'.CS_SR_BASIC_GRP '
534 ||' lexer '||g_apps_short_name
535 ||'.CS_SR_GLOBAL_LEXER language column SOURCE_LANG '
537 ||' storage '||g_apps_short_name||'.CS_SR_INDEX_STORAGE'; --< command not yet completed
538 --4917652_eof
539
540 -- 4917652:
541 -- If index with ONLINE mode, then we should create the index with nopopulate and
542 -- then mark the index offline for rebuild.
543 -- Else, create the text index in parallel. Note that this operation will block
544 -- any DML on the table.
545 IF l_index_with = 'ONLINE' THEN
546 -- Create index online
547 -- 1. Create index without populate
548 l_create_cmmd := l_create_cmmd || ' nopopulate '') ';
549 ELSE -- then, it is with Parallel mode.
550 -- 1. Completes the uncompleted command.
551 l_create_cmmd := l_create_cmmd || ''')';
552
553 -- 2. Resolve parallel indexing command.
554 l_create_cmmd := resolve_parallel_indexing(
555 l_create_cmmd,
556 pworker,
557 l_index_with);
558 END IF;
559 -- 4917652_eof
560
561 Begin
562 EXECUTE IMMEDIATE l_create_cmmd;
563
564 -- 4917652
565 -- If it is online mode, then we need to sync the index.
566 IF l_index_with = 'ONLINE' THEN
567 populate_sr_text_index (
568 x_msg_error => errbuf,
569 x_return_status => l_return_status);
570 END IF;
571 -- 4917652_eof
572
573 Exception
574 When others then
575 errbuf := 'Sync_Text_Index: '||index3||' :'
576 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
577 Raise create_index_error;
578 End;
579 ELSE -- Then it is OFAST, OFULL, S, and R modes:
580
581 -- 4917652
582 IF l_index_with = 'ONLINE' AND bmode = 'R' THEN
583 populate_sr_text_index (
584 x_msg_error => errbuf,
585 x_return_status => l_return_status);
586 ELSE
587 l_return_status := Sync_index(g_cs_short_name||'.'|| index3,
588 bmode,
589 pindex_with,
590 pworker );
591 END IF;
592 -- 4917652_eof
593 END IF;
594
595 -- Return successfully
596 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
597 retcode := 0;
598 EXCEPTION
599 WHEN invalid_mode_error THEN
600 errbuf := fnd_message.get_string('CS',
601 'CS_KB_SYN_INDEX_INV_MODE');
602 WHEN drop_index_error THEN
603 BEGIN
604 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
605 EXCEPTION
606 WHEN others THEN
607 NULL;
608 END;
609 WHEN create_index_error THEN
610 BEGIN
611 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
612 EXCEPTION
613 WHEN others THEN
614 NULL;
615 END;
616 WHEN others THEN
617 errbuf := 'Sync_Text_Index: '||
618 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '|| SQLERRM;
619 BEGIN
620 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
621 EXCEPTION
622 WHEN others THEN
623 NULL;
624 END;
625 END Sync_Text_Index;
626
627 /* Drop_index
628 * Check whether a text index exists in the CS schema. If yes, drops it.
629 */
630 PROCEDURE Drop_Index
631 ( p_index_name IN VARCHAR,
632 x_msg_error OUT NOCOPY VARCHAR2,
633 x_return_status OUT NOCOPY VARCHAR2
634 )
635 IS
636 drop_index VARCHAR2(100) := NULL;
637
638 CURSOR get_index_cursor(p_index_name VARCHAR2, p_owner VARCHAR2) IS
639 SELECT COUNT(*) FROM dba_indexes
640 WHERE index_name = UPPER(p_index_name)
641 AND owner= UPPER(p_owner);
642 l_total NUMBER := 0;
643
644 BEGIN
645 x_return_status := fnd_api.G_RET_STS_ERROR;
646 IF p_index_name IS NULL THEN
647 RETURN;
648 END IF;
649
650 -- If and only if the index exists:
651 OPEN get_index_cursor(p_index_name, g_cs_short_name);
652 FETCH get_index_cursor INTO l_total;
653 CLOSE get_index_cursor;
654
655 IF l_total > 0 THEN
656 drop_index := 'drop index '||g_cs_short_name||'.'||p_index_name||' force ';
657 EXECUTE IMMEDIATE drop_index;
658 END IF;
659
660 x_return_status := fnd_api.G_RET_STS_SUCCESS;
661 EXCEPTION
662 WHEN others THEN
663 x_msg_error := 'Drop_Index: '||
664 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '|| SQLERRM;
665 END Drop_Index;
666
667
668
669 -------------- (TEXT) eof ----------------------------
670
671
672 END CS_SR_SYNC_INDEX_PKG;
[Home] [Help]