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