[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTICLE_KEYWORD_PVT
Source
1 package body okc_article_keyword_pvt as
2 -- $Header: OKCVAKWB.pls 120.11.12020000.2 2012/07/16 16:16:34 harchand ship $
3
4 retcode_success constant varchar2(1) := '0';
5 retcode_warning constant varchar2(1) := '1';
6 retcode_error constant varchar2(1) := '2';
7
8 starting constant number := 0;
9 finish constant number := 1;
10
11 rindex BINARY_INTEGER;
12 slno BINARY_INTEGER;
13
14 index_parallel NUMBER; -- Bug# 7508004
15
16 function l_debug return varchar2
17 is
18 begin
19 return nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
20 end;
21
22 --
23 -- our concurrent programs:
24 --
25 -- OKCARTCRTCTX
26 -- OKCARTSYNCCTX
27 -- OKCARTOPTCTX
28 --
29 function request_id(prog_name varchar2)
30 return fnd_concurrent_requests.request_id%type
31 is
32 result fnd_concurrent_requests.request_id%type;
33 begin
34 select request_id into result
35 from fnd_concurrent_requests
36 where concurrent_program_id in (
37 select concurrent_program_id
38 from fnd_concurrent_programs
39 where concurrent_program_name = prog_name
40 )
41 and phase_code = 'R';
42 return result;
43 exception when others then
44 return 0;
45 end;
46
47 procedure sync is
48 begin
49 ad_ctx_ddl.set_effective_schema('okc');
50 ad_ctx_ddl.sync_index('okc_articles_ctx');
51 exception when others then raise;
52 end;
53
54 procedure optimize is
55 begin
56 ad_ctx_ddl.set_effective_schema('okc');
57 ad_ctx_ddl.optimize_index (
58 idx_name => 'okc_articles_ctx',
59 optlevel => ad_ctx_ddl.optlevel_full,
60 maxtime => ad_ctx_ddl.maxtime_unlimited
61 );
62 exception when others then raise;
63 end;
64
65 function article_title(p_article_version_id in number) return varchar2
66 as
67 l_art_title okc_articles_all.article_title%type;
68 begin
69 select article_title into l_art_title
70 from okc_articles_all a, okc_article_versions v
71 where a.article_id = v.article_id
72 and article_version_id = p_article_version_id;
73 return l_art_title;
74 exception when others then
75 return null;
76 end;
77
78 function check_pending_rows(start_finish number) return number
79 as
80 counter number;
81 cursor pending_rows IS
82 select
83 to_char(pnd_timestamp,'DD-Mon-YYYY HH24:MI:SS') time,
84 a.article_id art_id,
85 article_version_id ver_id,
86 org_id,
87 v.article_language lang
88 from
89 ctxsys.ctx_pending p,
90 okc_article_versions v,
91 okc_articles_all a
92 where pnd_index_name = 'OKC_ARTICLES_CTX'
93 and a.article_id = v.article_id
94 and v.rowid = pnd_rowid;
95 begin
96 counter := 0;
97 if start_finish not in (starting, finish) then
98 return counter;
99 end if;
100 begin
101 if start_finish = starting then
102 fnd_file.put_line(fnd_file.log, 'Pending rows before synchronization:');
103 elsif start_finish = finish then
104 fnd_file.put_line(fnd_file.log, 'Pending rows after synchronization:');
105 end if;
106 for pending in pending_rows
107 loop
108 counter := counter + 1;
109 fnd_file.put_line(fnd_file.log, ' row# '|| counter ||
110 ' time= '|| pending.time ||
111 ' article_id= '|| pending.art_id ||
112 ' article_version_id= '|| pending.ver_id ||
113 ' org_id= '|| pending.org_id ||
114 ' language= '|| pending.lang);
115 end loop;
116 exception when others then
117 counter := 0;
118 end;
119 if start_finish = starting then
120 fnd_file.put_line(fnd_file.log, ' '|| counter ||' rows in pending state and should be synchronized');
121 elsif start_finish = finish then
122 fnd_file.put_line(fnd_file.log, ' '|| counter ||' rows in pending state after synchronization');
123 end if;
124 return counter;
125 end;
126
127 procedure sync_ctx(errbuf out nocopy varchar2, retcode out nocopy varchar2)
128 as
129 counter number;
130 request_id fnd_concurrent_requests.request_id%type;
131 begin
132 rindex := dbms_application_info.set_session_longops_nohint;
133 request_id := okc_article_keyword_pvt.request_id('OKCARTSYNCCTX');
134 dbms_application_info.set_session_longops(rindex, slno,
135 'Synchronize Clause Text Index', request_id, 0, 0, 1,
136 'OKCARTSYNCCTX concurrent program', 'steps');
137
138 --Modified for performance bug 6943402. Removing counter since its taking long time to fetch the value of counter.
139 IF nvl(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
140 counter := check_pending_rows(starting);
141 END IF;
142
143
144 -- if counter > 0 then
145 fnd_file.put_line(fnd_file.log, 'Start synchronization ...');
146 sync;
147 fnd_file.put_line(fnd_file.log, 'Synchronization complete');
148
149 IF FND_PROFILE.VALUE('AFLOG_ENABLED') = 'Y' THEN
150 counter := check_pending_rows(finish);
151 END IF;
152
153 dbms_application_info.set_session_longops( rindex, slno, null, request_id, 0, 1, 1);
154 /* else
155 fnd_file.put_line(fnd_file.log, 'Skipped synchronization (no pending rows)');
156
157 dbms_application_info.set_session_longops( rindex, slno, null, request_id, 0, 0, 1);
158 end if;*/
159
160
161 errbuf := 'OKC_ARTICLES_CTX text index has been synchronized successfully';
162 retcode := retcode_success;
163 exception when others then
164 retcode := retcode_error;
165 errbuf := substr(sqlerrm,1,200);
166 fnd_file.put_line(fnd_file.log, sqlerrm);
167 end;
168
169 procedure optimize_ctx(errbuf out nocopy varchar2, retcode out nocopy varchar2)
170 as
171 request_id fnd_concurrent_requests.request_id%type;
172 begin
173 rindex := dbms_application_info.set_session_longops_nohint;
174 request_id := okc_article_keyword_pvt.request_id('OKCARTOPTCTX');
175 dbms_application_info.set_session_longops(rindex, slno,
176 'Optimize Clause Text Index', request_id, 0, 0, 2,
177 'OKCARTOPTCTX concurrent program', 'steps');
178
179 begin
180 sync;
181 retcode := retcode_success;
182 errbuf := 'OKC_ARTICLES_CTX text index has been synchronized successfully';
183 exception when others then
184 retcode := retcode_error;
185 errbuf := substr(sqlerrm,1,200);
186 fnd_file.put_line(fnd_file.log, sqlerrm);
187 end;
188
189 if retcode <> retcode_success then
190 fnd_file.put_line(fnd_file.log, 'ERROR: Synchronization failed (have to skip optimization)');
191 dbms_application_info.set_session_longops( rindex, slno, null, request_id, 0, 0, 2);
192 return;
193 end if;
194
195 dbms_application_info.set_session_longops( rindex, slno, null, request_id, 0, 1, 2);
196 fnd_file.put_line(fnd_file.log, 'Start optimization ...');
197 optimize;
198
199 dbms_application_info.set_session_longops( rindex, slno, null, request_id, 0, 2, 2);
200 fnd_file.put_line(fnd_file.log, 'Optimization complete');
201 errbuf := 'OKC_ARTICLES_CTX text index has been optimized successfully';
202 retcode := retcode_success;
203 exception when others then
204 retcode := retcode_error;
205 errbuf := substr(sqlerrm,1,200);
206 fnd_file.put_line(fnd_file.log, sqlerrm);
207 end;
208
209 procedure create_ctx(errbuf out nocopy varchar2, retcode out nocopy varchar2,p_index_parallel number)
210 is
211 request_id fnd_concurrent_requests.request_id%type;
212 begin
213 rindex := dbms_application_info.set_session_longops_nohint;
214 request_id := okc_article_keyword_pvt.request_id('OKCARTCRTCTX');
215 dbms_application_info.set_session_longops(rindex, slno,
216 'Create Clause Text Index', request_id, request_id, 0, 18,
217 'OKCARTCRTCTX concurrent program', 'steps');
218
219 retcode := retcode_success;
220 errbuf := 'OKC_ARTICLES_CTX text index has been created successfully';
221 index_parallel:=p_index_parallel; --Bug# 7508004
222 fnd_file.put_line(fnd_file.log,'index_parallel '||index_parallel); --Bug# 7508004
223 crt;
224 exception when others then
225 retcode := retcode_error;
226 errbuf := 'ERROR: Couldn''t create OKC_ARTICLES_CTX text index';
227 end;
228
229 procedure crt is
230 apps sys.dba_objects.owner%type;
231 okc sys.dba_objects.owner%type;
232 cmd varchar2(4000);
233
234 generic exception;
235
236 request_id fnd_concurrent_requests.request_id%type;
237 begin
238 request_id := okc_article_keyword_pvt.request_id('OKCARTCRTCTX');
239 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_CTX text index creation started');
240
241 begin
242 fnd_file.put_line(fnd_file.log,'Checking OKC_ARTICLE_KEYWORD_PVT package ...');
243 select owner into apps
244 from sys.dba_objects
245 where object_name = 'OKC_ARTICLE_KEYWORD_PVT'
246 and object_type = 'PACKAGE';
247 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_KEYWORD_PVT package exists');
248 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_KEYWORD_PVT package owner is '||apps);
249
250 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 1, 18);
251
252 exception when others then
253 fnd_file.put_line(fnd_file.log,'ERROR: OKC_ARTICLE_KEYWORD_PVT package doesn''t exist');
254 raise generic;
255 end;
256
257 begin
261 where table_name = 'OKC_ARTICLES_ALL';
258 fnd_file.put_line(fnd_file.log,'Checking OKC_ARTICLES_ALL table ...');
259 select owner into okc
260 from sys.dba_tables
262 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_ALL table exists');
263 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_ALL table owner is '||okc);
264
265 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 2, 18);
266
267 exception when others then
268 fnd_file.put_line(fnd_file.log,'ERROR: OKC_ARTICLES_ALL table doesn''t exist');
269 raise generic;
270 end;
271
272 begin
273 execute immediate 'grant execute on '||apps||'.OKC_ARTICLE_KEYWORD_PVT to '||okc;
274 fnd_file.put_line(fnd_file.log,'Granted execute on '||apps||'.OKC_ARTICLE_KEYWORD_PVT to '||okc);
275
276 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 3, 18);
277
278 exception when others then
279 fnd_file.put_line(fnd_file.log,'ERROR: Couldn''t grant execute on '||apps||'.OKC_ARTICLE_KEYWORD_PVT to '||okc);
280 raise generic;
281 end;
282
283 -- ===================================================================
284 -- Create context index preferences
285 -- ===================================================================
286 -- context index searches through columns:
287 -- article_title
288 -- display_name
289 -- article_description
290 -- article_text
291 -- additional_instructions
292 -- -------------------------------------------------------------------
293 -- Create datastore okc_articles_datastore
294 -- -------------------------------------------------------------------
295 begin
296 -- DATASTORE
297 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_DATASTORE preference creation ...');
298 begin
299 ad_ctx_ddl.drop_preference('okc_articles_datastore');
300 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_ARTICLES_DATASTORE preference');
301 exception when others then
302 fnd_file.put_line(fnd_file.log,'Creating new OKC_ARTICLES_DATASTORE preference');
303 end;
304 ad_ctx_ddl.create_preference('okc_articles_datastore','multi_column_datastore');
305 ad_ctx_ddl.set_attribute('okc_articles_datastore','columns',
306 apps||'.okc_article_keyword_pvt.article_title(article_version_id) art_title,
307 display_name art_disp, article_description art_descr, article_text art_text, additional_instructions art_instr');
308 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_DATASTORE preference has been created successfully as MULTI_COLUMN_DATASTORE');
309
310 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 4, 18);
311
312 end;
313
314 -- -------------------------------------------------------------------
315 -- Create section group okc_article_sections
316 -- -------------------------------------------------------------------
317 begin
318 -- SECTION GROUP
319 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS section group creation ...');
320 begin
321 ad_ctx_ddl.drop_section_group('okc_article_sections');
322 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_ARTICLE_SECTIONS section group');
323 exception when others then
324 fnd_file.put_line(fnd_file.log,'Creating new OKC_ARTICLE_SECTIONS section group');
325 end;
326 ad_ctx_ddl.create_section_group('okc_article_sections', 'BASIC_SECTION_GROUP');
327 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS: add field section Title');
328 ad_ctx_ddl.add_field_section('okc_article_sections', 'Title', '<ART_TITLE>', true);
329 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS: add field section Display');
330 ad_ctx_ddl.add_field_section('okc_article_sections', 'Display', '<ART_DISP>', true);
334 ad_ctx_ddl.add_field_section('okc_article_sections', 'Instruction', '<ART_INSTR>', true);
331 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS: add field section Description');
332 ad_ctx_ddl.add_field_section('okc_article_sections', 'Description', '<ART_DESCR>', true);
333 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS: add field section Instruction');
335 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS: add field section Text');
336 ad_ctx_ddl.add_field_section('okc_article_sections', 'Text', '<ART_TEXT>', true);
337 fnd_file.put_line(fnd_file.log,'OKC_ARTICLE_SECTIONS section group has been created successfully as BASIC_SECTION_GROUP');
338
339 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 5, 18);
340
341 end;
342
343 -- -------------------------------------------------------------------
344 -- Create lexer okc_articles_lexer
345 -- -------------------------------------------------------------------
346 begin
347 -- LEXER
348 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER preference creation ...');
349 begin
350 ad_ctx_ddl.drop_preference('okc_articles_lexer');
351 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_ARTICLES_LEXER preference');
352 exception when others then
353 fnd_file.put_line(fnd_file.log,'Creating new OKC_ARTICLES_LEXER preference');
354 end;
355 ad_ctx_ddl.create_preference('okc_articles_lexer','multi_lexer');
356 begin
357 ad_ctx_ddl.drop_preference('okc_blexer');
358 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_BLEXER preference');
359 exception when others then
360 fnd_file.put_line(fnd_file.log,'Creating new OKC_BLEXER preference');
361 end;
362 ad_ctx_ddl.create_preference('okc_blexer','basic_lexer');
363 fnd_file.put_line(fnd_file.log,'OKC_BLEXER: set attribute index_themes=false');
364 ad_ctx_ddl.set_attribute('okc_blexer','index_themes','false');
365 fnd_file.put_line(fnd_file.log,'OKC_BLEXER: set attribute index_text=true');
366 ad_ctx_ddl.set_attribute('okc_blexer','index_text','true');
367 fnd_file.put_line(fnd_file.log,'OKC_BLEXER: set attribute base_letter=true');
368 ad_ctx_ddl.set_attribute('okc_blexer','base_letter','true');
369 fnd_file.put_line(fnd_file.log,'OKC_BLEXER: set attribute mixed_case=false');
370 ad_ctx_ddl.set_attribute('okc_blexer','mixed_case','false');
371 fnd_file.put_line(fnd_file.log,'OKC_BLEXER preference has been created as BASIC_LEXER');
372
373 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 6, 18);
374
375 -- Chinese lexer
376 begin
377 ad_ctx_ddl.drop_preference('okc_clexer');
378 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_CLEXER preference');
379 exception when others then
380 fnd_file.put_line(fnd_file.log,'Creating new OKC_CLEXER preference');
381 end;
382
383 declare
384 yes_no varchar2(1);
385 begin
386 begin
387 select 'Y' into yes_no
388 from ctx_objects
389 where obj_class = 'LEXER'
390 and obj_name = 'CHINESE_LEXER';
391 exception when others then
392 yes_no := 'N';
393 end;
394 if yes_no = 'Y' then
395 ad_ctx_ddl.create_preference('okc_clexer','chinese_lexer');
396 fnd_file.put_line(fnd_file.log,'OKC_CLEXER preference has been created as CHINESE_LEXER');
397 else
398 ad_ctx_ddl.create_preference('okc_clexer','chinese_vgram_lexer');
399 fnd_file.put_line(fnd_file.log,'OKC_CLEXER preference has been created as CHINESE_VGRAM_LEXER');
400 end if;
401
402 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 7, 18);
403
404 end;
405
406 -- Japanese lexer
407 begin
408 ad_ctx_ddl.drop_preference('okc_jlexer');
409 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_JLEXER preference');
410 exception
411 when others then
412 fnd_file.put_line(fnd_file.log,'Creating new OKC_JLEXER preference');
413 end;
414
415 declare
416 yes_no varchar2(1);
417 begin
418 begin
419 select 'Y' into yes_no
420 from ctx_objects
421 where obj_class = 'LEXER'
422 and obj_name = 'JAPANESE_LEXER';
423 exception
424 when others then
425 yes_no := 'N';
426 end;
427 if yes_no = 'Y' then
428 ad_ctx_ddl.create_preference('okc_jlexer','japanese_lexer');
429 fnd_file.put_line(fnd_file.log,'OKC_JLEXER preference has been created as JAPANESE_LEXER');
430 else
431 ad_ctx_ddl.create_preference('okc_jlexer','japanese_vgram_lexer');
432 fnd_file.put_line(fnd_file.log,'OKC_JLEXER preference has been created as JAPANESE_VGRAM_LEXER');
433 end if;
434
435 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 8, 18);
436
437 end;
438
442 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_KLEXER preference');
439 -- Korean lexer
440 begin
441 ad_ctx_ddl.drop_preference('okc_klexer');
443 exception when others then
444 fnd_file.put_line(fnd_file.log,'Creating new OKC_KLEXER preference');
445 end;
446
447 declare
448 yes_no varchar2(1);
449 begin
450 begin
451 select 'Y' into yes_no
452 from ctx_objects
453 where obj_class = 'LEXER'
454 and obj_name = 'KOREAN_MORPH_LEXER';
455 exception when others then
456 yes_no := 'N';
457 end;
458 if yes_no = 'Y' then
459 ad_ctx_ddl.create_preference('okc_klexer', 'korean_morph_lexer');
460 fnd_file.put_line(fnd_file.log,'OKC_KLEXER preference has been created as KOREAN_MORPH_LEXER');
461 else
462 ad_ctx_ddl.create_preference('okc_klexer', 'korean_lexer');
463 fnd_file.put_line(fnd_file.log,'OKC_KLEXER preference has been created as KOREAN_LEXER');
464 end if;
465 fnd_file.put_line(fnd_file.log,'OKC_KLEXER: set attribute one_char_word=true');
466 ad_ctx_ddl.set_attribute('okc_klexer', 'one_char_word', 'true');
467 fnd_file.put_line(fnd_file.log,'OKC_KLEXER: set attribute number=true');
468 ad_ctx_ddl.set_attribute('okc_klexer', 'number', 'true');
469
470 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 9, 18);
471
472 end;
473
474 -- sublexers
475 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER: add sublexer default=okc_blexer');
476 ad_ctx_ddl.add_sub_lexer('okc_articles_lexer','default','okc_blexer');
477 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER: add sublexer ja=okc_jlexer');
478 ad_ctx_ddl.add_sub_lexer('okc_articles_lexer','ja','okc_jlexer');
479 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER: add sublexer ko=okc_klexer');
480 ad_ctx_ddl.add_sub_lexer('okc_articles_lexer','ko','okc_klexer');
481 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER: add sublexer zhs=okc_clexer');
485 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER preference has been created as MULTI_LEXER');
482 ad_ctx_ddl.add_sub_lexer('okc_articles_lexer','zhs','okc_clexer');
483 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_LEXER: add sublexer zht=okc_clexer');
484 ad_ctx_ddl.add_sub_lexer('okc_articles_lexer','zht','okc_clexer');
486
487 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 10, 18);
488
489 end;
490
491 -- -------------------------------------------------------------------
492 -- Create stoplist okc_articles_stoplist
493 -- -------------------------------------------------------------------
494 begin
495 -- STOPLIST
496 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_STOPLIST preference creation ...');
497 ad_ctx_ddl.create_stoplist ('okc_articles_stoplist', 'multi_stoplist');
498 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_STOPLIST preference has been created as MULTI_STOPLIST');
499 exception when others then
500 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_STOPLIST preference already exists');
501 end;
502
503 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 11, 18);
504
505 -- -------------------------------------------------------------------
506 -- Create wordlist okc_articles_wordlist
507 -- -------------------------------------------------------------------
508 begin
509 -- WORDLIST
510 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST preference creation ...');
511 begin
512 ad_ctx_ddl.drop_preference('okc_articles_wordlist');
513 fnd_file.put_line(fnd_file.log,'Re-creating old OKC_ARTICLES_WORDLIST preference');
514 exception when others then
515 fnd_file.put_line(fnd_file.log,'Creating new OKC_ARTICLES_WORDLIST preference');
516 end;
517 ad_ctx_ddl.create_preference('okc_articles_wordlist','basic_wordlist');
521 ad_ctx_ddl.set_attribute('okc_articles_wordlist','fuzzy_match','auto');
518 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST: set attribute stemmer=auto');
519 ad_ctx_ddl.set_attribute('okc_articles_wordlist','stemmer','auto');
520 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST: set attribute fuzzy_match=auto');
522 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST: set attribute prefix_index=true');
523 ad_ctx_ddl.set_attribute('okc_articles_wordlist','prefix_index','true');
524 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST: set attribute prefix_min_length=3');
525 ad_ctx_ddl.set_attribute('okc_articles_wordlist','prefix_min_length',3);
526 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST: set attribute prefix_max_length=5');
527 ad_ctx_ddl.set_attribute('okc_articles_wordlist','prefix_max_length',5);
528 -- ad_ctx_ddl.set_attribute('okc_articles_wordlist','fuzzy_score',60);
529 -- ad_ctx_ddl.set_attribute('okc_articles_wordlist','fuzzy_numresults',100);
530 -- ad_ctx_ddl.set_attribute('okc_articles_wordlist','substring_index','true');
531 fnd_file.put_line(fnd_file.log,'OKC_ARTICLES_WORDLIST preference has been created as BASIC_WORDLIST');
532
533 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 12, 18);
534
535 end;
536
537 -- -------------------------------------------------------------------
538 -- Drop context index okc_articles_ctx (if exists)
539 -- -------------------------------------------------------------------
540 begin
541 fnd_file.put_line(fnd_file.log, okc||'.OKC_ARTICLES_CTX text index creation:');
542 execute immediate 'drop index '||okc||'.okc_articles_ctx force';
543 exception when others then
544 null;
545 end;
546
547 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 13, 18);
548
549 fnd_file.put_line(fnd_file.log, 'Generating '||okc||'.OKC_ARTICLES_CTX text index creation DDL ...');
550 -- -------------------------------------------------------------------
551 -- Create context index okc_articles_ctx
552 -- -------------------------------------------------------------------
553 declare
554 max_memory number;
555 cpu_count number;
556 db_version number;
557 sync varchar2(18);
558 begin
559 max_memory := 0;
560 cpu_count := 0;
561 db_version := 0;
562 -- sync := ' sync (on commit) ';
563 sync := '';
564 begin
565 select par_value into max_memory
566 from ctx_parameters
567 where par_name = 'MAX_INDEX_MEMORY';
568 fnd_file.put_line(fnd_file.log,'max_memory='||max_memory);
569 exception when others then
570 null;
571 end;
572
573 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 14, 18);
574
575 begin
576 select value into cpu_count
577 from v$parameter
578 where name = 'cpu_count';
579 fnd_file.put_line(fnd_file.log,'cpu_count='||cpu_count);
580 exception when others then
581 null;
582 end;
583
584 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 15, 18);
585
586 begin
587 select to_number(substr(version,0,2)) into db_version
588 from v$instance;
589 fnd_file.put_line(fnd_file.log,'db_version='||db_version);
590 exception
591 when others then
592 null;
593 end;
594
595 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 16, 18);
596
597 max_memory := round(max_memory/2);
598 cpu_count := round(cpu_count/2);
599 -- Bug# 7508004
600 if index_parallel = -1 then
601 index_parallel:= cpu_count;
602 fnd_file.put_line(fnd_file.log,'index_parallel2 '||index_parallel);
603 end if;
604
605 if db_version < 10 then sync := ' '; end if;
606 cmd :=
607 'create index '||okc||'.okc_articles_ctx on '||okc||'.okc_article_versions(article_text)
608 indextype is ctxsys.context
609 parameters ('''||sync||'
610 memory '||max_memory||'
611 datastore '||apps||'.okc_articles_datastore
612 filter ctxsys.null_filter
613 lexer '||apps||'.okc_articles_lexer language column article_language
614 section group '||apps||'.okc_article_sections
615 stoplist '||apps||'.okc_articles_stoplist
616 wordlist '||apps||'.okc_articles_wordlist'') parallel '||index_parallel;
617 fnd_file.put_line(fnd_file.log, 'Executing '||okc||'.OKC_ARTICLES_CTX text index creation DDL ...');
618 fnd_file.put_line(fnd_file.log, cmd);
619
620 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 17, 18);
621
622 execute immediate cmd;
623
624 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 18, 18);
625
626 fnd_file.put_line(fnd_file.log, okc||'.OKC_ARTICLES_CTX text index has been created successfully');
627 exception when others then
631 exception when others then
628 fnd_file.put_line(fnd_file.log, 'ERROR: Couldn''t create '||okc||'.OKC_ARTICLES_CTX text index');
629 begin
630 execute immediate 'drop index '||okc||'.okc_articles_ctx force';
632 null;
633 end;
634 fnd_file.put_line(fnd_file.log, cmd);
635 raise generic;
636 end;
637
638 end;
639
640 end;