[Home] [Help]
PACKAGE BODY: APPS.EAM_TEXT_INDEX_PVT
Source
1 PACKAGE BODY EAM_TEXT_INDEX_PVT AS
2 /* $Header: EAMVTICB.pls 120.4 2006/05/23 07:07:20 yjhabak noship $*/
3 -- Start of comments
4 -- API name : EAM_TEXT_INDEX_PVT
5 -- Type : Private.
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional
11 -- Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_validation_level IN NUMBER Optional
15 -- Default = FND_API.G_VALID_LEVEL_FULL
16 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes Note text
38 --
39 -- End of comments
40
41 -- -----------------------------------------------------------------------------
42 -- Private Globals
43 -- -----------------------------------------------------------------------------
44 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_TEXT_INDEX_PVT';
45
46 g_Ctx_Schema CONSTANT VARCHAR2(30) := 'CTXSYS';
47 g_Prod_Schema VARCHAR2(30);
48 g_Index_Owner VARCHAR2(30);
49 g_Pref_Owner VARCHAR2(30);
50
51 g_installed BOOLEAN;
52 g_inst_status VARCHAR2(1);
53 g_industry VARCHAR2(1);
54
55 -- Log mode
56 g_Log_Mode VARCHAR2(30) := NULL;
57 g_Conc_Req_flag BOOLEAN := TRUE;
58 g_Log_Sqlplus_Flag BOOLEAN := FALSE;
59 g_Log_File_Flag BOOLEAN := FALSE;
60 g_Log_Dbdrv_Flag BOOLEAN := FALSE;
61
62 g_Msg_Text VARCHAR2(1000);
63
64 -- Log directory
65 g_Log_Dir v$parameter.value%TYPE;
66 g_Dump_Dir v$parameter.value%TYPE;
67
68 -- Log files for Sqlplus
69 g_Log_File VARCHAR2(30) := 'eam_text_index.log';
70 g_Out_File VARCHAR2(30) := 'eam_text_index.out';
71
72
73
74 -- -----------------------------------------------------------------------------
75 -- Log_Line
76 -- -----------------------------------------------------------------------------
77
78 PROCEDURE Log_Line ( p_Buffer IN VARCHAR2
79 , p_Log_Type IN NUMBER
80 , p_Module IN VARCHAR2 )
81 IS
82 BEGIN
83 IF ( g_Log_File_Flag AND p_Log_Type >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
84 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
85 FND_LOG.STRING(p_Log_Type, p_module, SUBSTRB(p_Buffer, l*240 + 1, 240));
86 --FND_FILE.Put_Line (FND_FILE.Log, SUBSTRB(p_Buffer, l*240 + 1, 240));
87 END LOOP;
88 END IF;
89
90 IF ( g_Log_Sqlplus_Flag ) THEN
91 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
92 --DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
93 NULL;
94 END LOOP;
95 END IF;
96
97 IF ( g_Log_Dbdrv_Flag ) THEN
98 --IF ( (INSTR(p_Buffer, 'Error:') + INSTR(p_Buffer, 'Warning:')) > 0 ) THEN
99 IF ( ( INSTR(p_Buffer, 'Error:') > 0 )
100 OR ( INSTR(p_Buffer, 'Warning:') > 0 )
101 OR ( INSTR(p_Buffer, 'Executing:') > 0 )
102 OR ( INSTR(p_Buffer, 'Done.') > 0 )
103 OR ( INSTR(p_Buffer, 'Upgrade:') > 0 ) )
104 THEN
105 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
106 END IF;
107 END IF;
108 END Log_Line;
109
110 -- -----------------------------------------------------------------------------
111 -- Requets_Log
112 -- -----------------------------------------------------------------------------
113
114 PROCEDURE Request_Log (p_msg_name IN VARCHAR2)
115 IS
116 BEGIN
117 FND_MESSAGE.SET_NAME('EAM', p_msg_name);
118 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
119 END Request_Log;
120
121 -- -----------------------------------------------------------------------------
122 -- Out_Line
123 -- -----------------------------------------------------------------------------
124 /*
125 PROCEDURE Out_Line ( p_Buffer IN VARCHAR2 )
126 IS
127 BEGIN
128 IF ( g_Log_File_Flag ) THEN
129 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
130 FND_FILE.Put_Line (FND_FILE.Log, SUBSTRB(p_Buffer, l*240 + 1, 240));
131 END LOOP;
132 END IF;
133
134 IF ( g_Log_Sqlplus_Flag ) THEN
135 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
136 DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
137 NULL;
138 END LOOP;
139 END IF;
140
141 IF ( g_Log_Dbdrv_Flag ) THEN
142 IF ( INSTR(p_Buffer, 'Completed ') > 0 ) THEN
143 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
144 END IF;
145 END IF;
146 END Out_Line;
147 */
148
149 -- -----------------------------------------------------------------------------
150 -- set_Log_Mode
151 -- -----------------------------------------------------------------------------
152
153 FUNCTION set_Log_Mode ( p_Mode IN VARCHAR2 )
154 RETURN VARCHAR2
155 IS
156 l_api_name CONSTANT VARCHAR2(30) := 'set_Log_Mode';
157 BEGIN
158 g_Log_Mode := p_Mode;
159
160 g_Log_Sqlplus_Flag := ( INSTR(g_Log_Mode, 'SQLPLUS') > 0 );
161 g_Log_File_Flag := ( (INSTR(g_Log_Mode, 'FILE') + INSTR(g_Log_Mode, 'SRS')) > 0 );
162 g_Log_Dbdrv_Flag := ( INSTR(g_Log_Mode, 'DBDRV') > 0 );
163
164 g_Msg_Text := NULL;
165
166 -- Determine log directory
167
168 BEGIN
169 SELECT value INTO g_Dump_Dir
170 FROM v$parameter WHERE name = 'user_dump_dest';
171
172 SELECT TRANSLATE(LTRIM(value), ',', ' ') INTO g_Log_Dir
173 FROM v$parameter
174 WHERE name = 'utl_file_dir';
175
176 IF ( g_Log_Dir IS NOT NULL ) THEN
177 IF ( INSTR(g_Log_Dir, ' ') > 0 ) THEN
178 g_Log_Dir := SUBSTRB(g_Log_Dir, 1, INSTR(g_Log_Dir,' ')-1);
179 END IF;
180 END IF;
181
182 --EXCEPTION
183 -- WHEN others THEN
184 -- Log_Line ('Error determining CTX log directory: ' || SQLERRM);
185 END;
186
187 IF ( NVL(FND_GLOBAL.CONC_REQUEST_ID,-1) < 0 ) THEN
188 g_Conc_Req_flag := FALSE;
189 FND_FILE.Put_Names (g_Log_File, g_Out_File, g_Log_Dir);
190 Log_Line (l_api_name || ': standalone execution', FND_LOG.LEVEL_EVENT, l_api_name);
191 ELSE
192 Log_Line (l_api_name || ': concurrent request', FND_LOG.LEVEL_EVENT, l_api_name);
193 END IF;
194
195 Log_Line (l_api_name || ': log mode: ' || g_Log_Mode, FND_LOG.LEVEL_EVENT, l_api_name);
196 Log_Line (l_api_name || ': dump directory: ' || g_Dump_Dir, FND_LOG.LEVEL_EVENT, l_api_name);
197 Log_Line (l_api_name || ': CTX log directory: ' || g_Log_Dir, FND_LOG.LEVEL_EVENT, l_api_name);
198
199 RETURN (NULL);
200
201 END set_Log_Mode;
202
203
204
205 PROCEDURE set_Log_Mode ( p_Mode IN VARCHAR2 )
206 IS
207 l_output_name VARCHAR2(255);
208 BEGIN
209 l_output_name := set_Log_Mode (p_Mode);
210 END set_Log_Mode;
211
212
213
214 -- -----------------------------------------------------------------------------
215 -- Set_Asset_Index_Preferences
216 -- -----------------------------------------------------------------------------
217
218 PROCEDURE Set_Asset_Index_Preferences
219 (
220 p_Index_Name IN VARCHAR2
221 , x_return_status OUT NOCOPY VARCHAR2
222 )
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'Set_Asset_Index_Preferences';
225 l_return_status VARCHAR2(1);
226
227 tspace_tbl_param VARCHAR2(256);
228 tspace_idx_param VARCHAR2(256);
229
230 Lang_Code VARCHAR2(4);
231 Lexer_Name VARCHAR2(30);
232
233 TYPE Lang_Code_List_type IS TABLE OF VARCHAR2(4);
234 --TYPE Lang_ISO_List_type IS TABLE OF VARCHAR2(2);
235
236 Lang_Code_List Lang_Code_List_type := Lang_Code_List_type
237 ( 'US', 'GB', 'NL', 'D', 'DK', 'S', 'N',
238 'F', 'I', 'E', 'ESA', 'EL',
239 'JA', 'KO', 'ZHS', 'ZHT' );
240
241 --Lang_ISO_List Lang_ISO_List_type :=
242 -- Lang_ISO_List_type ('EN', '', 'DE', 'SV', 'NO', 'FR', '', '');
243
244 BEGIN
245 Log_Line (l_api_name || ': begin', FND_LOG.LEVEL_EVENT, l_api_name);
246
247 l_return_status := G_STATUS_SUCCESS;
248
249 ------------------------------
250 -- Drop existing preferences
251 ------------------------------
252
253 Log_Line (l_api_name || ': dropping all existing preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
254
255 FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
256 FROM ctxsys.ctx_preferences
257 WHERE pre_name = 'EAM_ASSET_MULTI_LEXER' )
258 LOOP
259 ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
260 END LOOP;
261
262 FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
263 FROM ctxsys.ctx_preferences
264 WHERE pre_name LIKE 'EAM_ASSET_LEXER%' )
265 LOOP
266 ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
267 END LOOP;
268
269 FOR wordlist_rec IN ( SELECT pre_owner, pre_name
270 FROM ctxsys.ctx_preferences
271 WHERE pre_name = 'EAM_ASSET_WORDLIST' )
272 LOOP
273 ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
274 END LOOP;
275
276 FOR stoplist_rec IN ( SELECT spl_owner, spl_name
277 FROM ctxsys.ctx_stoplists
278 WHERE spl_name = 'EAM_ASSET_STOPLIST' )
279 LOOP
280 --ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EAM_ASSET_STOPLIST');
281 ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.'|| stoplist_rec.spl_name);
282 END LOOP;
283
284 FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
285 FROM ctxsys.ctx_section_groups
286 WHERE sgp_name = 'EAM_ASSET_SECTION_GROUP' )
287 LOOP
288 ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
289 END LOOP;
290
291 FOR datastore_rec IN ( SELECT pre_owner, pre_name
292 FROM ctxsys.ctx_preferences
293 WHERE pre_name = 'EAM_ASSET_DATASTORE' )
294 LOOP
295 ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
296 END LOOP;
297
298 FOR storage_rec IN ( SELECT pre_owner, pre_name
299 FROM ctxsys.ctx_preferences
300 WHERE pre_name = 'EAM_ASSET_STORAGE' )
301 LOOP
302 ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
303 END LOOP;
304
305 ------------------------------
306 -- Create STORAGE preference
307 ------------------------------
308 -- Index tables use the same tablespaces used by other EAM tables and indexes
309 -- or use logical tablespace for indexes (TRANSACTION_INDEXES).
310
311 Log_Line (l_api_name || ': querying tablespace parameters ...', FND_LOG.LEVEL_EVENT, l_api_name);
312
313 SELECT 'tablespace ' || tablespace_name ||
314 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
315 INTO tspace_tbl_param
316 FROM all_tables
317 WHERE owner = g_Prod_Schema AND table_name = 'EAM_ASSET_TEXT';
318
319 SELECT 'tablespace ' || tablespace_name ||
320 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
321 INTO tspace_idx_param
322 FROM all_indexes
323 WHERE owner = g_Prod_Schema
324 AND index_name = 'EAM_ASSET_TEXT_U1'
325 AND table_name = 'EAM_ASSET_TEXT';
326
327 Log_Line (l_api_name || ': creating STORAGE preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
328
329 ad_ctx_ddl.create_preference (g_Pref_Owner || '.EAM_ASSET_STORAGE', 'BASIC_STORAGE');
330
331 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
332 'I_TABLE_CLAUSE', tspace_tbl_param);
333
334 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
335 'K_TABLE_CLAUSE', tspace_tbl_param);
336
337 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
338 'R_TABLE_CLAUSE', tspace_tbl_param || ' LOB (data) STORE AS (CACHE)');
339
340 -- Caching the "data" LOB column is the default (at later versions of Oracle Text).
341 -- For index specific STORAGE preference, setting the clause "lob (data) (cache reads)"
342 -- should be ensured (the "lob .. store as" clause is only for newly added LOB columns).
343 -- alter table dr$prd_ctx_index$r modify lob (data) (cache reads);
344
345 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
346 'N_TABLE_CLAUSE', tspace_tbl_param);
347
348 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
349 'P_TABLE_CLAUSE', tspace_tbl_param);
350
351 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
352 'I_INDEX_CLAUSE', tspace_idx_param || ' COMPRESS 2');
353
354 --ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_ASSET_STORAGE',
355 -- 'I_ROWID_INDEX_CLAUSE', tspace_idx_param);
356
357 --------------------------------
358 -- Create DATASTORE preference
359 --------------------------------
360
361 Log_Line (l_api_name || ': creating DATASTORE preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
362
363 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_ASSET_DATASTORE', 'USER_DATASTORE');
364
365 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_DATASTORE', 'OUTPUT_TYPE', 'CLOB');
366 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_DATASTORE', 'PROCEDURE', '"EAM_TEXT_CTX_PKG"."Get_Asset_Text_CLOB"');
367
368 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_DATASTORE', 'OUTPUT_TYPE', 'VARCHAR2');
369 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_DATASTORE', 'PROCEDURE', '"EAM_TEXT_CTX_PKG"."Get_Asset_Text_VARCHAR2"');
370
371 ------------------------------------
372 -- Create SECTION GROUP preference
373 ------------------------------------
374
375 --Log_Line (l_api_name || ': creating SECTION_GROUP preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
376
377 ad_ctx_ddl.Create_Section_Group (g_Pref_Owner || '.EAM_ASSET_SECTION_GROUP', 'AUTO_SECTION_GROUP');
378
379 -------------------------------
380 -- Create STOPLIST preference
381 -------------------------------
382
383 Log_Line (l_api_name || ': creating STOPLIST preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
384
385 -- This should create stoplist equivalent to CTXSYS.EMPTY_STOPLIST
386 ad_ctx_ddl.Create_Stoplist (g_Pref_Owner || '.EAM_ASSET_STOPLIST');
387
388 -------------------------------
389 -- Create WORDLIST preference
390 -------------------------------
391
392 Log_Line (l_api_name || ': creating WORDLIST preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
393
394 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'BASIC_WORDLIST');
395
396 -- Enable prefix indexing to improve performance for wildcard searches
397 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'PREFIX_INDEX', 'TRUE');
398 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'PREFIX_MIN_LENGTH', 2);
399 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'PREFIX_MAX_LENGTH', 32);
400 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'WILDCARD_MAXTERMS', 5000);
401
402 -- This option should be TRUE only when left-truncated wildcard searching is expected
403 -- to be frequent and needs to be fast (at the cost of increased index time and space).
404 --
405 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'SUBSTRING_INDEX', 'FALSE');
406
407 -- WORDLIST attribute defaults: STEMMER: 'ENGLISH'; FUZZY_MATCH: 'GENERIC'
408 -- Use automatic language detection for stemming and fuzzy matching
409 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'STEMMER', 'AUTO');
410 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'FUZZY_MATCH', 'AUTO');
411 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'FUZZY_SCORE', 40);
412 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_ASSET_WORDLIST', 'FUZZY_NUMRESULTS', 120);
413
414 -----------------------------------------------
415 -- Create language-specific LEXER preferences
416 -----------------------------------------------
417
418 Log_Line (l_api_name || ': creating language-specific LEXER preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
419 Lexer_Name := g_Pref_Owner || '.EAM_ASSET_LEXER_BASIC';
420
421 /*
422 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
423 LOOP
424 Lexer_Name := g_Pref_Owner || '.EAM_ASSET_LEXER_' || Lang_Code_List(i);
425
426 IF ( Lang_Code_List(i) = 'JA' ) THEN
427 -- Use JAPANESE_LEXER if db charset is UTF8, JA16SJIS, or JA16EUC.
428 IF ( eam_text_util.get_DB_Version_Num >= 9.0 ) THEN
429 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_LEXER');
430 ELSE
431 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_VGRAM_LEXER');
432 END IF;
433
434 ELSIF ( Lang_Code_List(i) = 'KO' ) THEN
435 -- Use KOREAN_MORPH_LEXER if db charset is UTF8 or KO16KSC5601.
436 IF ( eam_text_util.get_DB_Version_Num >= 9.0 ) THEN
437 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_MORPH_LEXER');
438 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB_ADJECTIVE', 'TRUE');
439 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONE_CHAR_WORD', 'TRUE');
440 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
441 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'NGRAM');
442 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
443 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'HANJA', 'FALSE');
444 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'LONG_WORD', 'TRUE');
445 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'JAPANESE', 'FALSE');
449 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_LEXER');
446 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ENGLISH', 'TRUE');
447 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TO_UPPER', 'TRUE');
448 ELSE
450 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB', 'TRUE');
451 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADJECTIVE', 'TRUE');
452 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADVERB', 'TRUE');
453 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONECHAR', 'TRUE');
454 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
455 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'TRUE');
456 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
457 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOHANGEUL', 'TRUE');
458 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOUPPER', 'TRUE');
459 END IF;
460
461 ELSIF ( Lang_Code_List(i) IN ('ZHS', 'ZHT') ) THEN
462 IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
463 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_LEXER');
464 ELSE
465 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_VGRAM_LEXER');
466 END IF;
467
468 ELSE
469 -- All other languages use basic lexer.
470 */
471 /* For now we will use basic lexer only */
472
473 ad_ctx_ddl.Create_Preference (Lexer_Name, 'BASIC_LEXER');
474
475 -- The following language-independent attributes are
476 -- common to the BASIC_LEXER preference object.
477
478 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_TEXT', 'YES');
479 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_THEMES', 'NO');
480
481 -- For printjoin characters include all possible flex segment separators
482 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PRINTJOINS', '-_*~^+.$#@:|&');
483 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'CONTINUATION', '-\');
484 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PUNCTUATIONS', '.?!');
485
486 -- The default values for numjoin and numgroup are determined by
487 -- the NLS initialization parameters that are specified for the database.
488 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMGROUP', ',');
489 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMJOIN', '.');
490
491 -- Stem indexing stems tokens at indexing time to a single base form in addition
492 -- to the normal forms. This enables better query performance for stem ($) queries.
493
494 -- Disable stem indexing to improve index creation performance.
495 -- This would not affect stem expansion (with $) at query time.
496 --
497 --IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
498 -- IF ( Lang_Code_List(i) IN ('US', 'GB') ) THEN
499 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ENGLISH');
500 -- ELSIF ( Lang_Code_List(i) = 'NL' ) THEN
501 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'DUTCH');
502 -- ELSIF ( Lang_Code_List(i) = 'D' ) THEN
503 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'GERMAN');
504 -- ELSIF ( Lang_Code_List(i) = 'F' ) THEN
505 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'FRENCH');
506 -- ELSIF ( Lang_Code_List(i) = 'I' ) THEN
507 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ITALIAN');
508 -- ELSIF ( Lang_Code_List(i) IN ('E', 'ESA') ) THEN
509 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'SPANISH');
510 -- END IF;
511 --END IF;
512 /*
513 -- Language-specific attribute values for BASIC_LEXER preference object
514
515 IF ( Lang_Code_List(i) = 'NL' ) THEN
516 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'DUTCH');
517
518 ELSIF ( Lang_Code_List(i) = 'D' ) THEN
519 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'GERMAN');
520 -- Basic lexer in 8.1.7 allows the MIXED_CASE to be FALSE when COMPOSITE is set.
521 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MIXED_CASE', 'NO');
522 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'GERMAN');
523
524 ELSIF ( Lang_Code_List(i) = 'DK' ) THEN
525 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
526
527 ELSIF ( Lang_Code_List(i) = 'S' ) THEN
528 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'SWEDISH');
529
530 ELSIF ( Lang_Code_List(i) = 'N' ) THEN
531 -- Both Norwegian and Danish use the same special characters that are
532 -- rendered alternatively as "aa", "ae", and "oe".
533 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
534
535 ELSIF ( Lang_Code_List(i) = 'F' ) THEN
536 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'BASE_LETTER', 'YES');
537
538 ELSE
539 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'NONE');
540
541 END IF;
542 END IF;
543
544 END LOOP; -- Lang_Code_List
545 */
546 ----------------------------------
547 -- Create MULTI_LEXER preference
548 ----------------------------------
549 /*
550 Log_Line (l_api_name || ': creating MULTI_LEXER preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
551
552 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_ASSET_MULTI_LEXER', 'MULTI_LEXER');
553
554 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
555 LOOP
556 Lexer_Name := g_Pref_Owner || '.EAM_ASSET_LEXER_' || Lang_Code_List(i);
557
558 -- The language column is case-independent, and can contain either the NLS name
559 -- or abbreviation of the language. If the table uses some other value for the
563 IF ( Lang_Code_List(i) = 'US' ) THEN
560 -- language, that alternate value needs to be specified as the fourth argument
561 -- when adding the sub lexers:
562
564 -- US English lexer to handle everything else.
565 ad_ctx_ddl.Add_Sub_Lexer ( g_Pref_Owner || '.EAM_ASSET_MULTI_LEXER', 'DEFAULT'
566 , g_Pref_Owner || '.EAM_ASSET_LEXER_US' );
567 ELSE
568 ad_ctx_ddl.Add_Sub_Lexer
569 ( lexer_name => g_Pref_Owner || '.EAM_ASSET_MULTI_LEXER'
570 , language => Lang_Code_List(i)
571 , sub_lexer => Lexer_Name
572 --, alt_value => Lang_ISO_List(i)
573 );
574 END IF;
575
576 END LOOP;*/
577
578 x_return_status := l_return_status;
579
580 Log_Line (l_api_name || ': end', FND_LOG.LEVEL_EVENT, l_api_name);
581
582 EXCEPTION
583
584 WHEN G_EXC_ERROR THEN
585 x_return_status := G_STATUS_ERROR;
586
587 WHEN others THEN
588 Log_Line (l_api_name || ': Error: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
589 x_return_status := G_STATUS_UNEXP_ERROR;
590
591 END Set_Asset_Index_Preferences;
592
593
594
595 -- -----------------------------------------------------------------------------
596 -- Process_Asset_Text_Index
597 -- -----------------------------------------------------------------------------
598
599 PROCEDURE Process_Asset_Text_Index
600 (
601 p_Index_Name IN VARCHAR2
602 , p_Action IN VARCHAR2
603 , x_return_status OUT NOCOPY VARCHAR2
604 )
605 IS
606 l_api_name CONSTANT VARCHAR2(30) := 'Process_Asset_Text_Index';
607 l_return_status VARCHAR2(1);
608 ctx_Log_File_Name VARCHAR2(512) := NULL;
609
610 l_index_exists BOOLEAN := FALSE;
611 l_index_valid BOOLEAN := TRUE;
612
613 l_create_index BOOLEAN := TRUE;
614 l_drop_index BOOLEAN := FALSE;
615
616 l_rows_processed INTEGER;
617
618 -- Limit the indexing memory in case the default parameter max value is higher.
619 --l_Index_Memory ctx_parameters.par_value%TYPE := '67108864'; -- '64M'
620 l_Index_Memory ctx_parameters.par_value%TYPE := '134217728'; -- '128M'
621 l_Index_Memory_Max ctx_parameters.par_value%TYPE;
622
623 l_idx_docid_count NUMBER;
624 l_idx_status VARCHAR2(256);
625
626 l_index_populate VARCHAR2(30) := 'POPULATE';
627
628 -- Use parallel indexing when this issue is resolved
629 c_parallel_clause CONSTANT VARCHAR2(30) := 'PARALLEL 4';
630 --c_parallel_clause CONSTANT VARCHAR2(30) := NULL;
631
632 l_index_parallel VARCHAR2(30) := NULL;
633 sql_stmt VARCHAR2(32767);
634 BEGIN
635
636 Log_Line (l_api_name || ': begin: Index_Name=' || p_Index_Name || ' Action=' || p_Action, FND_LOG.LEVEL_EVENT, l_api_name);
637
638 l_return_status := G_STATUS_SUCCESS;
639
640 IF ( p_Action NOT IN (1,2,4) ) THEN
641 Log_Line (l_api_name || ': Error: invalid value for parameter p_Action: ' || p_Action, FND_LOG.LEVEL_EVENT, l_api_name);
642 RAISE G_EXC_ERROR;
643 END IF;
644
645 -- Check for existing indexes in the EAM product, APPS Universal, and interMedia schemas.
646 --
647 FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
648 FROM all_indexes
649 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
650 AND table_name = 'EAM_ASSET_TEXT'
651 AND index_name = p_Index_Name )
652 LOOP
653 -- Check index schema
654 --
655 IF ( index_rec.owner <> g_Index_Owner )
656 THEN
657 Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner, FND_LOG.LEVEL_EVENT, l_api_name);
658 BEGIN
659 Log_Line (l_api_name || ': dropping index: ' || index_rec.owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
660 EXECUTE IMMEDIATE 'DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE';
661 EXCEPTION
662 WHEN others THEN
663 Log_Line (l_api_name || ': Error: DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
664 l_return_status := G_STATUS_ERROR;
665 END;
666
667 ELSE
668 l_index_exists := TRUE;
669
670 -- Check status of an existing index, if any.
671 --
672 IF ( (NVL(index_rec.status, 'FAILED') <> 'VALID') OR
673 (NVL(index_rec.domidx_status, 'FAILED') <> 'VALID') OR
674 (NVL(index_rec.domidx_opstatus, 'FAILED') <> 'VALID') )
675 THEN
676 l_index_valid := FALSE;
677 Log_Line (l_api_name || ': Warning: existing index status is invalid:'
678 || ' status=' || index_rec.status
679 || ' domidx_status=' || index_rec.domidx_status
680 || ' domidx_opstatus=' || index_rec.domidx_opstatus, FND_LOG.LEVEL_EVENT, l_api_name);
681 ELSE
682 Log_Line (l_api_name || ': valid index exists: ' || index_rec.owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
683 END IF;
684
685 END IF; -- index owner
686
687 END LOOP; -- check for any existing indexes
688
689 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
690 RAISE G_EXC_ERROR;
691 END IF;
692
693 -- x_return_status := l_return_status;
694
695 -- Set indexing flags depending on the action and the index status
696
697 IF ( p_Action = 1 ) THEN
701 Request_Log ('EAM_TEXT_INDEX_EXISTS');
698 IF ( l_index_exists ) THEN
699 IF ( l_index_valid ) THEN
700 Log_Line (l_api_name || ': Error: cannot execute Create because the index exists.', FND_LOG.LEVEL_EVENT, l_api_name);
702 l_return_status := G_STATUS_WARNING;
703 l_create_index := FALSE;
704 ELSE
705 l_drop_index := TRUE;
706 END IF;
707 END IF;
708
709 ELSIF ( p_Action = 2 ) THEN
710 l_drop_index := l_index_exists;
711 /*
712 ELSIF ( p_Action = 'UPGRADE' ) THEN
713 IF ( l_index_exists ) THEN
714 IF ( l_index_valid ) THEN
715 Log_Line (l_api_name || ': Upgrade: skipping index creation because valid index exists.', FND_LOG.LEVEL_EVENT, l_api_name);
716 l_create_index := FALSE;
717 ELSE
718 l_drop_index := TRUE;
719 END IF;
720 END IF;
721 */
722 ELSIF ( p_Action = 4 ) THEN
723 l_create_index := FALSE;
724 IF ( l_index_exists ) THEN
725 l_drop_index := TRUE;
726 ELSE
727 Log_Line (l_api_name || ': Warning: cannot execute Drop because the index does not exist.', FND_LOG.LEVEL_EVENT, l_api_name);
728 Request_Log ('EAM_TEXT_INDEX_NOT_EXISTS');
729 l_return_status := G_STATUS_WARNING;
730 END IF;
731
732 END IF; -- Action
733
734 IF ( l_drop_index ) THEN
735 BEGIN
736 Log_Line (l_api_name || ': dropping index: ' || g_Index_Owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
737 request_log('EAM_TEXT_DROP');
738 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
739 IF ( l_index_valid ) THEN
740 Log_Line (l_api_name || ': existing index has been successfully dropped.', FND_LOG.LEVEL_EVENT, l_api_name);
741 ELSE
742 Log_Line (l_api_name || ': invalid index has been dropped.', FND_LOG.LEVEL_EVENT, l_api_name);
743 END IF;
744 EXCEPTION
745 WHEN others THEN
746 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
747 l_return_status := G_STATUS_ERROR;
748 END;
749 END IF; -- drop index
750
751 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
752 RAISE G_EXC_ERROR;
753 END IF;
754
755 -- Build index
756
757 IF ( l_create_index ) THEN
758
759 Log_Line (': calling Set_Asset_Index_Preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
760 request_log('EAM_TEXT_INDEX_PREF');
761 Set_Asset_Index_Preferences
762 (
763 p_Index_Name => p_Index_Name
764 , x_return_status => l_return_status
765 );
766
767 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
768 RAISE G_EXC_ERROR;
769 END IF;
770
771 log_Line ('Re-created Text Index preferences.', FND_LOG.LEVEL_EVENT, l_api_name);
772
773 -- Determine index memory parameter value
774 --ctxsys.CTX_ADM.Set_Parameter ('MAX_INDEX_MEMORY', l_Index_Memory);
775 BEGIN
776 SELECT par_value INTO l_Index_Memory_Max
777 FROM ctx_parameters
778 WHERE par_name = 'MAX_INDEX_MEMORY';
779 EXCEPTION
780 WHEN no_data_found THEN
781 Log_Line (l_api_name || ': Error: MAX_INDEX_MEMORY parameter record not found.', FND_LOG.LEVEL_EVENT, l_api_name);
782 RAISE G_EXC_ERROR;
783 END;
784
785 IF ( TO_NUMBER(l_Index_Memory) > TO_NUMBER(l_Index_Memory_Max) ) THEN
786 l_Index_Memory := l_Index_Memory_Max;
787 END IF;
788
789 Log_Line (l_api_name || ': CTX index_memory: ' || l_Index_Memory, FND_LOG.LEVEL_EVENT, l_api_name);
790
791 -- Decide whether parallel indexing can be used, depending on DB version.
792
793 IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
794 l_index_parallel := c_parallel_clause;
795 Log_Line (l_api_name || ': DB version: ' || eam_text_util.get_DB_Version_Str || ', using parallel clause: ' || l_index_parallel, FND_LOG.LEVEL_EVENT, l_api_name);
796 END IF;
797
798 -- Start logging indexing progress
799
800 IF ( g_Log_Dir IS NOT NULL ) THEN
801 BEGIN
802 Log_Line (l_api_name || ': CTX Log_Directory: ' || g_Log_Dir, FND_LOG.LEVEL_EVENT, l_api_name);
803 ctxsys.CTX_ADM.Set_Parameter ( 'LOG_DIRECTORY', g_Log_Dir );
804 Log_Line (l_api_name || ': CTX Start_Log', FND_LOG.LEVEL_EVENT, l_api_name);
805 CTX_OUTPUT.Start_Log ( LOWER(p_Index_Name) || '.log' );
806 ctx_Log_File_Name := CTX_OUTPUT.LogFileName;
807 Log_Line (l_api_name || ': CTX LogFileName: ' || ctx_Log_File_Name, FND_LOG.LEVEL_EVENT, l_api_name);
808 EXCEPTION
809 WHEN others THEN
810 Log_Line (l_api_name || ': Warning: CTX Start_Log: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
811 l_return_status := G_STATUS_WARNING;
812 END;
813 END IF;
814
815 -- Choose indexing method
816 --IF ( ) THEN
817 -- l_index_populate := 'NOPOPULATE';
818 --END IF;
819
820 IF ( l_create_index ) THEN
821 BEGIN
822 request_log('EAM_TEXT_CREATE');
823 eam_text_util.Set_Context ('CREATE_INDEX');
824
825 sql_stmt :=
826 'CREATE INDEX ' || g_Index_Owner || '.' || p_Index_Name ||
827 ' ON ' || g_Prod_Schema || '.EAM_ASSET_TEXT (text) ' ||
828 ' INDEXTYPE IS CTXSYS.context ' ||
832 ' STOPLIST ' || g_Pref_Owner || '.EAM_ASSET_STOPLIST ' ||
829 ' PARAMETERS ' ||
830 ' (''DATASTORE ' || g_Pref_Owner || '.EAM_ASSET_DATASTORE ' ||
831 ' WORDLIST ' || g_Pref_Owner || '.EAM_ASSET_WORDLIST ' ||
833 /* For now we will use basic lexer. For using multi lexer we need language column */
834 ' LEXER ' || g_Pref_Owner || '.EAM_ASSET_LEXER_BASIC ' ||
835 -- ' LANGUAGE COLUMN language ' ||
836 ' SECTION GROUP ' || g_Pref_Owner || '.EAM_ASSET_SECTION_GROUP ' ||
837 -- ' SECTION GROUP CTXSYS.NULL_SECTION_GROUP ' ||
838 ' STORAGE ' || g_Pref_Owner || '.EAM_ASSET_STORAGE ' ||
839 ' MEMORY ' || l_Index_Memory ||
840 --' ' || l_index_populate ||
841 ' '')'
842 || ' ' || l_index_parallel
843 ;
844
845 Log_Line (l_api_name || ': creating index ' || g_Index_Owner || '.' || p_Index_Name || ' ...', FND_LOG.LEVEL_EVENT, l_api_name);
846 --Log_Line (l_api_name || ': sql_stmt = ' || sql_stmt || ' /* End SQL */');
847
848 EXECUTE IMMEDIATE sql_stmt;
849
850 Log_Line (l_api_name || ': done creating index.', FND_LOG.LEVEL_EVENT, l_api_name);
851
852 EXCEPTION
853
854 WHEN others THEN
855 Log_Line (l_api_name || ': Error creating index ' || g_Index_Owner || '.' || p_Index_Name || ': ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
856
857 -- Drop the index in case of an error during index creation to prevent the table lock.
858 BEGIN
859 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
860 EXCEPTION
861 WHEN others THEN
862 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
863 RAISE G_EXC_ERROR;
864 END;
865
866 RAISE G_EXC_ERROR;
867
868 END; -- execute sql
869 END IF; -- create index
870 /*
871 Log_Line (l_api_name || ': calling Incremental_Sync', FND_LOG.LEVEL_EVENT, l_api_name);
872
873 eam_text_util.Incremental_Sync
874 (
875 p_Index_Name => p_Index_Name
876 , p_batch_size => 40000
877 , x_rows_processed => l_rows_processed
878 , x_return_status => l_return_status
879 );
880 */
881 -- End logging
882
883 IF ( ctx_Log_File_Name IS NOT NULL ) THEN
884 BEGIN
885 CTX_OUTPUT.End_Log;
886 Log_Line (l_api_name || ': CTX End_Log', FND_LOG.LEVEL_EVENT, l_api_name);
887 EXCEPTION
888 WHEN others THEN
889 Log_Line (l_api_name || ': Warning: CTX End_Log: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
890 l_return_status := G_STATUS_WARNING;
891 END;
892 END IF;
893
894 log_Line ('Completed building Asset Text Index.', FND_LOG.LEVEL_EVENT, l_api_name);
895
896 -- Check the created index status
897
898 SELECT idx_docid_count, idx_status
899 INTO l_idx_docid_count, l_idx_status
900 FROM ctxsys.ctx_indexes
901 WHERE idx_owner = g_Prod_Schema AND idx_name = 'EAM_ASSET_TEXT_CTX1'
902 AND idx_table = 'EAM_ASSET_TEXT';
903
904 IF NOT( l_idx_status = 'INDEXED' ) THEN
905 Log_Line (l_api_name || ': Error: Index status is ' || l_idx_status || '.', FND_LOG.LEVEL_EVENT, l_api_name);
906 l_return_status := G_STATUS_ERROR;
907 END IF;
908
909 IF ( NVL(l_idx_docid_count, 0) = 0 ) THEN
910 Log_Line (l_api_name || ': Error: Indexed document count is ' || TO_CHAR(l_idx_docid_count) || '.', FND_LOG.LEVEL_EVENT, l_api_name);
911 l_return_status := G_STATUS_ERROR;
912 END IF;
913
914 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
915 RAISE G_EXC_ERROR;
916 END IF;
917
918 END IF; -- build index
919
920 x_return_status := l_return_status;
921 Log_Line (l_api_name || ': end', FND_LOG.LEVEL_EVENT, l_api_name);
922
923 EXCEPTION
924
925 WHEN G_EXC_ERROR THEN
926 x_return_status := G_STATUS_ERROR;
927
928 WHEN others THEN
929 Log_Line (l_api_name || ': Error: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
930 x_return_status := G_STATUS_UNEXP_ERROR;
931
932 END Process_Asset_Text_Index;
933
934
935
936 -- -----------------------------------------------------------------------------
937 -- Set_Wo_Index_Preferences
938 -- -----------------------------------------------------------------------------
939
940 PROCEDURE Set_Wo_Index_Preferences
941 (
942 p_Index_Name IN VARCHAR2
943 , x_return_status OUT NOCOPY VARCHAR2
944 )
945 IS
946 l_api_name CONSTANT VARCHAR2(30) := 'Set_Wo_Index_Preferences';
947 l_return_status VARCHAR2(1);
948
949 tspace_tbl_param VARCHAR2(256);
950 tspace_idx_param VARCHAR2(256);
951
952 Lang_Code VARCHAR2(4);
953 Lexer_Name VARCHAR2(30);
954
955 TYPE Lang_Code_List_type IS TABLE OF VARCHAR2(4);
956 --TYPE Lang_ISO_List_type IS TABLE OF VARCHAR2(2);
957
958 Lang_Code_List Lang_Code_List_type := Lang_Code_List_type
959 ( 'US', 'GB', 'NL', 'D', 'DK', 'S', 'N',
960 'F', 'I', 'E', 'ESA', 'EL',
961 'JA', 'KO', 'ZHS', 'ZHT' );
962
963 --Lang_ISO_List Lang_ISO_List_type :=
964 -- Lang_ISO_List_type ('EN', '', 'DE', 'SV', 'NO', 'FR', '', '');
965
966 BEGIN
967 Log_Line (l_api_name || ': begin', FND_LOG.LEVEL_EVENT, l_api_name);
968
969 l_return_status := G_STATUS_SUCCESS;
970
971 ------------------------------
972 -- Drop existing preferences
973 ------------------------------
974
975 Log_Line (l_api_name || ': dropping all existing preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
976
977 FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
978 FROM ctxsys.ctx_preferences
979 WHERE pre_name = 'EAM_WORK_ORDER_MULTI_LEXER' )
980 LOOP
981 ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
982 END LOOP;
983
984 FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
985 FROM ctxsys.ctx_preferences
986 WHERE pre_name LIKE 'EAM_WORK_ORDER_LEXER%' )
987 LOOP
988 ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
989 END LOOP;
990
991 FOR wordlist_rec IN ( SELECT pre_owner, pre_name
992 FROM ctxsys.ctx_preferences
993 WHERE pre_name = 'EAM_WORK_ORDER_WORDLIST' )
994 LOOP
995 ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
996 END LOOP;
997
998 FOR stoplist_rec IN ( SELECT spl_owner, spl_name
999 FROM ctxsys.ctx_stoplists
1000 WHERE spl_name = 'EAM_WORK_ORDER_STOPLIST' )
1001 LOOP
1002 --ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EAM_WORK_ORDER_STOPLIST');
1003 ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.'|| stoplist_rec.spl_name);
1004 END LOOP;
1005
1006 FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
1007 FROM ctxsys.ctx_section_groups
1008 WHERE sgp_name = 'EAM_WORK_ORDER_SECTION_GROUP' )
1009 LOOP
1010 ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
1011 END LOOP;
1012
1013 FOR datastore_rec IN ( SELECT pre_owner, pre_name
1014 FROM ctxsys.ctx_preferences
1015 WHERE pre_name = 'EAM_WORK_ORDER_DATASTORE' )
1016 LOOP
1017 ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
1018 END LOOP;
1019
1020 FOR storage_rec IN ( SELECT pre_owner, pre_name
1021 FROM ctxsys.ctx_preferences
1022 WHERE pre_name = 'EAM_WORK_ORDER_STORAGE' )
1023 LOOP
1024 ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
1025 END LOOP;
1026
1027 ------------------------------
1028 -- Create STORAGE preference
1029 ------------------------------
1030 -- Index tables use the same tablespaces used by other EAM tables and indexes
1031 -- or use logical tablespace for indexes (TRANSACTION_INDEXES).
1032
1033 Log_Line (l_api_name || ': querying tablespace parameters ...', FND_LOG.LEVEL_EVENT, l_api_name);
1034
1035 SELECT 'tablespace ' || tablespace_name ||
1036 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
1037 INTO tspace_tbl_param
1038 FROM all_tables
1039 WHERE owner = g_Prod_Schema AND table_name = 'EAM_WORK_ORDER_TEXT';
1040
1041 SELECT 'tablespace ' || tablespace_name ||
1042 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
1043 INTO tspace_idx_param
1044 FROM all_indexes
1045 WHERE owner = g_Prod_Schema
1046 AND index_name = 'EAM_WORK_ORDER_TEXT_U1'
1047 AND table_name = 'EAM_WORK_ORDER_TEXT';
1048
1049 Log_Line (l_api_name || ': creating STORAGE preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
1050
1051 ad_ctx_ddl.create_preference (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE', 'BASIC_STORAGE');
1052
1053 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1054 'I_TABLE_CLAUSE', tspace_tbl_param);
1055
1056 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1057 'K_TABLE_CLAUSE', tspace_tbl_param);
1058
1059 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1060 'R_TABLE_CLAUSE', tspace_tbl_param || ' LOB (data) STORE AS (CACHE)');
1061
1062 -- Caching the "data" LOB column is the default (at later versions of Oracle Text).
1063 -- For index specific STORAGE preference, setting the clause "lob (data) (cache reads)"
1064 -- should be ensured (the "lob .. store as" clause is only for newly added LOB columns).
1065 -- alter table dr$prd_ctx_index$r modify lob (data) (cache reads);
1066
1067 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1068 'N_TABLE_CLAUSE', tspace_tbl_param);
1069
1070 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1071 'P_TABLE_CLAUSE', tspace_tbl_param);
1072
1073 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1074 'I_INDEX_CLAUSE', tspace_idx_param || ' COMPRESS 2');
1075
1076 --ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE',
1077 -- 'I_ROWID_INDEX_CLAUSE', tspace_idx_param);
1078
1079 --------------------------------
1080 -- Create DATASTORE preference
1084
1081 --------------------------------
1082
1083 Log_Line (l_api_name || ': creating DATASTORE preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
1085 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE', 'USER_DATASTORE');
1086
1087 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE', 'OUTPUT_TYPE', 'CLOB');
1088 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE', 'PROCEDURE', '"EAM_TEXT_CTX_PKG"."Get_Wo_Text_CLOB"');
1089
1090 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE', 'OUTPUT_TYPE', 'VARCHAR2');
1091 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE', 'PROCEDURE', '"EAM_TEXT_CTX_PKG"."Get_Wo_Text_VARCHAR2"');
1092
1093 ------------------------------------
1094 -- Create SECTION GROUP preference
1095 ------------------------------------
1096
1097 --Log_Line (l_api_name || ': creating SECTION_GROUP preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
1098
1099 ad_ctx_ddl.Create_Section_Group (g_Pref_Owner || '.EAM_WORK_ORDER_SECTION_GROUP', 'AUTO_SECTION_GROUP');
1100
1101 -------------------------------
1102 -- Create STOPLIST preference
1103 -------------------------------
1104
1105 Log_Line (l_api_name || ': creating STOPLIST preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
1106
1107 -- This should create stoplist equivalent to CTXSYS.EMPTY_STOPLIST
1108 ad_ctx_ddl.Create_Stoplist (g_Pref_Owner || '.EAM_WORK_ORDER_STOPLIST');
1109
1110 -------------------------------
1111 -- Create WORDLIST preference
1112 -------------------------------
1113
1114 Log_Line (l_api_name || ': creating WORDLIST preference ...', FND_LOG.LEVEL_EVENT, l_api_name);
1115
1116 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'BASIC_WORDLIST');
1117
1118 -- Enable prefix indexing to improve performance for wildcard searches
1119 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'PREFIX_INDEX', 'TRUE');
1120 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'PREFIX_MIN_LENGTH', 2);
1121 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'PREFIX_MAX_LENGTH', 32);
1122 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'WILDCARD_MAXTERMS', 5000);
1123
1124 -- This option should be TRUE only when left-truncated wildcard searching is expected
1125 -- to be frequent and needs to be fast (at the cost of increased index time and space).
1126 --
1127 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'SUBSTRING_INDEX', 'FALSE');
1128
1129 -- WORDLIST attribute defaults: STEMMER: 'ENGLISH'; FUZZY_MATCH: 'GENERIC'
1130 -- Use automatic language detection for stemming and fuzzy matching
1131 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'STEMMER', 'AUTO');
1132 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'FUZZY_MATCH', 'AUTO');
1133 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'FUZZY_SCORE', 40);
1134 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST', 'FUZZY_NUMRESULTS', 120);
1135
1136 -----------------------------------------------
1137 -- Create language-specific LEXER preferences
1138 -----------------------------------------------
1139
1140 Log_Line (l_api_name || ': creating language-specific LEXER preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
1141 /* For now we will use basic lexer */
1142 Lexer_Name := g_Pref_Owner || '.EAM_WORK_ORDER_LEXER_BASIC';
1143
1144 /*
1145 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
1146 LOOP
1147 Lexer_Name := g_Pref_Owner || '.EAM_WORK_ORDER_LEXER_' || Lang_Code_List(i);
1148
1149 IF ( Lang_Code_List(i) = 'JA' ) THEN
1150
1151 -- Use JAPANESE_LEXER if db charset is UTF8, JA16SJIS, or JA16EUC.
1152 IF ( eam_text_util.get_DB_Version_Num >= 9.0 ) THEN
1153 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_LEXER');
1154 ELSE
1155 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_VGRAM_LEXER');
1156 END IF;
1157
1158 ELSIF ( Lang_Code_List(i) = 'KO' ) THEN
1159
1160 -- Use KOREAN_MORPH_LEXER if db charset is UTF8 or KO16KSC5601.
1161 IF ( eam_text_util.get_DB_Version_Num >= 9.0 ) THEN
1162 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_MORPH_LEXER');
1163 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB_ADJECTIVE', 'TRUE');
1164 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONE_CHAR_WORD', 'TRUE');
1165 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
1166 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'NGRAM');
1167 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
1168 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'HANJA', 'FALSE');
1169 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'LONG_WORD', 'TRUE');
1170 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'JAPANESE', 'FALSE');
1171 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ENGLISH', 'TRUE');
1172 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TO_UPPER', 'TRUE');
1173 ELSE
1174 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_LEXER');
1175 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB', 'TRUE');
1176 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADJECTIVE', 'TRUE');
1177 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADVERB', 'TRUE');
1178 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONECHAR', 'TRUE');
1179 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
1180 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'TRUE');
1181 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
1182 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOHANGEUL', 'TRUE');
1183 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOUPPER', 'TRUE');
1184 END IF;
1185
1186 ELSIF ( Lang_Code_List(i) IN ('ZHS', 'ZHT') ) THEN
1187
1191 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_VGRAM_LEXER');
1188 IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
1189 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_LEXER');
1190 ELSE
1192 END IF;
1193
1194 ELSE
1195 -- All other languages use basic lexer. */
1196
1197 ad_ctx_ddl.Create_Preference (Lexer_Name, 'BASIC_LEXER');
1198
1199 -- The following language-independent attributes are
1200 -- common to the BASIC_LEXER preference object.
1201
1202 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_TEXT', 'YES');
1203 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_THEMES', 'NO');
1204
1205 -- For printjoin characters include all possible flex segment separators
1206 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PRINTJOINS', '-_*~^+.$#@:|&');
1207 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'CONTINUATION', '-\');
1208 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PUNCTUATIONS', '.?!');
1209
1210 -- The default values for numjoin and numgroup are determined by
1211 -- the NLS initialization parameters that are specified for the database.
1212 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMGROUP', ',');
1213 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMJOIN', '.');
1214
1215 -- Stem indexing stems tokens at indexing time to a single base form in addition
1216 -- to the normal forms. This enables better query performance for stem ($) queries.
1217
1218 -- Disable stem indexing to improve index creation performance.
1219 -- This would not affect stem expansion (with $) at query time.
1220 --
1221 --IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
1222 -- IF ( Lang_Code_List(i) IN ('US', 'GB') ) THEN
1223 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ENGLISH');
1224 -- ELSIF ( Lang_Code_List(i) = 'NL' ) THEN
1225 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'DUTCH');
1226 -- ELSIF ( Lang_Code_List(i) = 'D' ) THEN
1227 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'GERMAN');
1228 -- ELSIF ( Lang_Code_List(i) = 'F' ) THEN
1229 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'FRENCH');
1230 -- ELSIF ( Lang_Code_List(i) = 'I' ) THEN
1231 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ITALIAN');
1232 -- ELSIF ( Lang_Code_List(i) IN ('E', 'ESA') ) THEN
1233 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'SPANISH');
1234 -- END IF;
1235 --END IF;
1236 /*
1237 -- Language-specific attribute values for BASIC_LEXER preference object
1238
1239 IF ( Lang_Code_List(i) = 'NL' ) THEN
1240 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'DUTCH');
1241
1242 ELSIF ( Lang_Code_List(i) = 'D' ) THEN
1243 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'GERMAN');
1244 -- Basic lexer in 8.1.7 allows the MIXED_CASE to be FALSE when COMPOSITE is set.
1245 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MIXED_CASE', 'NO');
1246 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'GERMAN');
1247
1248 ELSIF ( Lang_Code_List(i) = 'DK' ) THEN
1249 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
1250
1251 ELSIF ( Lang_Code_List(i) = 'S' ) THEN
1252 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'SWEDISH');
1253
1254 ELSIF ( Lang_Code_List(i) = 'N' ) THEN
1255 -- Both Norwegian and Danish use the same special characters that are
1256 -- rendered alternatively as "aa", "ae", and "oe".
1257 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
1258
1259 ELSIF ( Lang_Code_List(i) = 'F' ) THEN
1260 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'BASE_LETTER', 'YES');
1261
1262 ELSE
1263 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'NONE');
1264
1265 END IF;
1266 END IF;
1267
1268 END LOOP; -- Lang_Code_List
1269 */
1270 ----------------------------------
1271 -- Create MULTI_LEXER preference
1272 ----------------------------------
1273 /*
1274 Log_Line (l_api_name || ': creating MULTI_LEXER preference ...');
1275
1276 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EAM_WORK_ORDER_MULTI_LEXER', 'MULTI_LEXER');
1277
1278 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
1279 LOOP
1280 Lexer_Name := g_Pref_Owner || '.EAM_WORK_ORDER_LEXER_' || Lang_Code_List(i);
1281
1282 -- The language column is case-independent, and can contain either the NLS name
1283 -- or abbreviation of the language. If the table uses some other value for the
1284 -- language, that alternate value needs to be specified as the fourth argument
1285 -- when adding the sub lexers:
1286
1287 IF ( Lang_Code_List(i) = 'US' ) THEN
1288 -- US English lexer to handle everything else.
1289 ad_ctx_ddl.Add_Sub_Lexer ( g_Pref_Owner || '.EAM_WORK_ORDER_MULTI_LEXER', 'DEFAULT'
1290 , g_Pref_Owner || '.EAM_WORK_ORDER_LEXER_US' );
1291 ELSE
1292 ad_ctx_ddl.Add_Sub_Lexer
1293 ( lexer_name => g_Pref_Owner || '.EAM_WORK_ORDER_MULTI_LEXER'
1294 , language => Lang_Code_List(i)
1295 , sub_lexer => Lexer_Name
1296 --, alt_value => Lang_ISO_List(i)
1297 );
1298 END IF;
1299
1300 END LOOP;*/
1301
1302 x_return_status := l_return_status;
1303
1304 Log_Line (l_api_name || ': end', FND_LOG.LEVEL_EVENT, l_api_name);
1305
1306 EXCEPTION
1307
1308 WHEN G_EXC_ERROR THEN
1309 x_return_status := G_STATUS_ERROR;
1310
1311 WHEN others THEN
1315 END Set_Wo_Index_Preferences;
1312 Log_Line (l_api_name || ': Error: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1313 x_return_status := G_STATUS_UNEXP_ERROR;
1314
1316
1317
1318
1319
1320 -- -----------------------------------------------------------------------------
1321 -- Process_Wo_Text_Index
1322 -- -----------------------------------------------------------------------------
1323
1324 PROCEDURE Process_Wo_Text_Index
1325 (
1326 p_Index_Name IN VARCHAR2
1327 , p_Action IN VARCHAR2
1328 , x_return_status OUT NOCOPY VARCHAR2
1329 )
1330 IS
1331 l_api_name CONSTANT VARCHAR2(30) := 'Process_Wo_Text_Index';
1332 l_return_status VARCHAR2(1);
1333 ctx_Log_File_Name VARCHAR2(512) := NULL;
1334
1335 l_index_exists BOOLEAN := FALSE;
1336 l_index_valid BOOLEAN := TRUE;
1337
1338 l_create_index BOOLEAN := TRUE;
1339 l_drop_index BOOLEAN := FALSE;
1340
1341 l_rows_processed INTEGER;
1342
1343 -- Limit the indexing memory in case the default parameter max value is higher.
1344 --l_Index_Memory ctx_parameters.par_value%TYPE := '67108864'; -- '64M'
1345 l_Index_Memory ctx_parameters.par_value%TYPE := '134217728'; -- '128M'
1346 l_Index_Memory_Max ctx_parameters.par_value%TYPE;
1347
1348 l_idx_docid_count NUMBER;
1349 l_idx_status VARCHAR2(256);
1350
1351 l_index_populate VARCHAR2(30) := 'POPULATE';
1352
1353 -- Use parallel indexing when this issue is resolved
1354 c_parallel_clause CONSTANT VARCHAR2(30) := 'PARALLEL 4';
1355 --c_parallel_clause CONSTANT VARCHAR2(30) := NULL;
1356
1357 l_index_parallel VARCHAR2(30) := NULL;
1358 sql_stmt VARCHAR2(32767);
1359 BEGIN
1360
1361 Log_Line (l_api_name || ': begin: Index_Name=' || p_Index_Name || ' Action=' || p_Action, FND_LOG.LEVEL_EVENT, l_api_name);
1362
1363 l_return_status := G_STATUS_SUCCESS;
1364
1365 IF ( p_Action NOT IN (1,2,4) ) THEN
1366 Log_Line (l_api_name || ': Error: invalid value for parameter p_Action: ' || p_Action, FND_LOG.LEVEL_EVENT, l_api_name);
1367 RAISE G_EXC_ERROR;
1368 END IF;
1369
1370 -- Check for existing indexes in the EAM product, APPS Universal, and interMedia schemas.
1371 --
1372 FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
1373 FROM all_indexes
1374 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
1375 AND table_name = 'EAM_WORK_ORDER_TEXT'
1376 AND index_name = p_Index_Name )
1377 LOOP
1378 -- Check index schema
1379 --
1380 IF ( index_rec.owner <> g_Index_Owner )
1381 THEN
1382 Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner, FND_LOG.LEVEL_EVENT, l_api_name);
1383 BEGIN
1384 Log_Line (l_api_name || ': dropping index: ' || index_rec.owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
1385 EXECUTE IMMEDIATE 'DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE';
1386 EXCEPTION
1387 WHEN others THEN
1388 Log_Line (l_api_name || ': Error: DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1389 l_return_status := G_STATUS_ERROR;
1390 END;
1391
1392 ELSE
1393 l_index_exists := TRUE;
1394
1395 -- Check status of an existing index, if any.
1396 --
1397 IF ( (NVL(index_rec.status, 'FAILED') <> 'VALID') OR
1398 (NVL(index_rec.domidx_status, 'FAILED') <> 'VALID') OR
1399 (NVL(index_rec.domidx_opstatus, 'FAILED') <> 'VALID') )
1400 THEN
1401 l_index_valid := FALSE;
1402 Log_Line (l_api_name || ': Warning: existing index status is invalid:'
1403 || ' status=' || index_rec.status
1404 || ' domidx_status=' || index_rec.domidx_status
1405 || ' domidx_opstatus=' || index_rec.domidx_opstatus, FND_LOG.LEVEL_EVENT, l_api_name);
1406 ELSE
1407 Log_Line (l_api_name || ': valid index exists: ' || index_rec.owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
1408 END IF;
1409
1410 END IF; -- index owner
1411
1412 END LOOP; -- check for any existing indexes
1413
1414 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1415 RAISE G_EXC_ERROR;
1416 END IF;
1417
1418 -- x_return_status := l_return_status;
1419
1420 -- Set indexing flags depending on the action and the index status
1421
1422 IF ( p_Action = 1 ) THEN
1423 IF ( l_index_exists ) THEN
1424 IF ( l_index_valid ) THEN
1425 Log_Line (l_api_name || ': Error: cannot execute Create because the index exists.', FND_LOG.LEVEL_EVENT, l_api_name);
1426 Request_Log ('EAM_TEXT_INDEX_EXISTS');
1427 l_return_status := G_STATUS_WARNING;
1428 l_create_index := FALSE;
1429 ELSE
1430 l_drop_index := TRUE;
1431 END IF;
1432 END IF;
1433
1434 ELSIF ( p_Action = 2 ) THEN
1435 l_drop_index := l_index_exists;
1436 /*
1437 ELSIF ( p_Action = 'UPGRADE' ) THEN
1438 IF ( l_index_exists ) THEN
1439 IF ( l_index_valid ) THEN
1440 Log_Line (l_api_name || ': Upgrade: skipping index creation because valid index exists.', FND_LOG.LEVEL_EVENT, l_api_name);
1441 l_create_index := FALSE;
1442 ELSE
1443 l_drop_index := TRUE;
1444 END IF;
1445 END IF;
1446 */
1447 ELSIF ( p_Action = 4) THEN
1448 l_create_index := FALSE;
1452 Log_Line (l_api_name || ': Warning: cannot execute Drop because the index does not exist.', FND_LOG.LEVEL_EVENT, l_api_name);
1449 IF ( l_index_exists ) THEN
1450 l_drop_index := TRUE;
1451 ELSE
1453 Request_Log ('EAM_TEXT_INDEX_NOT_EXISTS');
1454 l_return_status := G_STATUS_WARNING;
1455 END IF;
1456
1457 END IF; -- Action
1458
1459 IF ( l_drop_index ) THEN
1460 BEGIN
1461 Log_Line (l_api_name || ': dropping index: ' || g_Index_Owner || '.' || p_Index_Name, FND_LOG.LEVEL_EVENT, l_api_name);
1462 request_log('EAM_TEXT_DROP');
1463 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
1464 IF ( l_index_valid ) THEN
1465 Log_Line (l_api_name || ': existing index has been successfully dropped.', FND_LOG.LEVEL_EVENT, l_api_name);
1466 ELSE
1467 Log_Line (l_api_name || ': invalid index has been dropped.', FND_LOG.LEVEL_EVENT, l_api_name);
1468 END IF;
1469 EXCEPTION
1470 WHEN others THEN
1471 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1472 l_return_status := G_STATUS_ERROR;
1473 END;
1474 END IF; -- drop index
1475
1476 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1477 RAISE G_EXC_ERROR;
1478 END IF;
1479
1480 -- Build index
1481
1482 IF ( l_create_index ) THEN
1483
1484 Log_Line ('Calling Set_Wo_Index_Preferences ...', FND_LOG.LEVEL_EVENT, l_api_name);
1485 request_log('EAM_TEXT_INDEX_PREF');
1486 Set_Wo_Index_Preferences
1487 (
1488 p_Index_Name => p_Index_Name
1489 , x_return_status => l_return_status
1490 );
1491
1492 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1493 RAISE G_EXC_ERROR;
1494 END IF;
1495
1496 log_Line ('Re-created Text Index preferences.', FND_LOG.LEVEL_EVENT, l_api_name);
1497
1498 -- Determine index memory parameter value
1499 --ctxsys.CTX_ADM.Set_Parameter ('MAX_INDEX_MEMORY', l_Index_Memory);
1500 BEGIN
1501 SELECT par_value INTO l_Index_Memory_Max
1502 FROM ctx_parameters
1503 WHERE par_name = 'MAX_INDEX_MEMORY';
1504 EXCEPTION
1505 WHEN no_data_found THEN
1506 Log_Line (l_api_name || ': Error: MAX_INDEX_MEMORY parameter record not found.', FND_LOG.LEVEL_EVENT, l_api_name);
1507 RAISE G_EXC_ERROR;
1508 END;
1509
1510 IF ( TO_NUMBER(l_Index_Memory) > TO_NUMBER(l_Index_Memory_Max) ) THEN
1511 l_Index_Memory := l_Index_Memory_Max;
1512 END IF;
1513
1514 Log_Line (l_api_name || ': CTX index_memory: ' || l_Index_Memory, FND_LOG.LEVEL_EVENT, l_api_name);
1515
1516 -- Decide whether parallel indexing can be used, depending on DB version.
1517
1518 IF ( eam_text_util.get_DB_Version_Num >= 9.2 ) THEN
1519 l_index_parallel := c_parallel_clause;
1520 Log_Line (l_api_name || ': DB version: ' || eam_text_util.get_DB_Version_Str || ', using parallel clause: ' || l_index_parallel, FND_LOG.LEVEL_EVENT, l_api_name);
1521 END IF;
1522
1523 -- Start logging indexing progress
1524
1525 IF ( g_Log_Dir IS NOT NULL ) THEN
1526 BEGIN
1527 Log_Line (l_api_name || ': CTX Log_Directory: ' || g_Log_Dir, FND_LOG.LEVEL_EVENT, l_api_name);
1528 ctxsys.CTX_ADM.Set_Parameter ( 'LOG_DIRECTORY', g_Log_Dir );
1529 Log_Line (l_api_name || ': CTX Start_Log', FND_LOG.LEVEL_EVENT, l_api_name);
1530 CTX_OUTPUT.Start_Log ( LOWER(p_Index_Name) || '.log' );
1531 ctx_Log_File_Name := CTX_OUTPUT.LogFileName;
1532 Log_Line (l_api_name || ': CTX LogFileName: ' || ctx_Log_File_Name, FND_LOG.LEVEL_EVENT, l_api_name);
1533 EXCEPTION
1534 WHEN others THEN
1535 Log_Line (l_api_name || ': Warning: CTX Start_Log: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1536 l_return_status := G_STATUS_WARNING;
1537 END;
1538 END IF;
1539
1540 -- Choose indexing method
1541 --IF ( ) THEN
1542 -- l_index_populate := 'NOPOPULATE';
1543 --END IF;
1544
1545 IF ( l_create_index ) THEN
1546 BEGIN
1547 request_log('EAM_TEXT_CREATE');
1548 eam_text_util.Set_Context ('CREATE_INDEX');
1549
1550 sql_stmt :=
1551 'CREATE INDEX ' || g_Index_Owner || '.' || p_Index_Name ||
1552 ' ON ' || g_Prod_Schema || '.EAM_WORK_ORDER_TEXT (text) ' ||
1553 ' INDEXTYPE IS CTXSYS.context ' ||
1554 ' PARAMETERS ' ||
1555 ' (''DATASTORE ' || g_Pref_Owner || '.EAM_WORK_ORDER_DATASTORE ' ||
1556 ' WORDLIST ' || g_Pref_Owner || '.EAM_WORK_ORDER_WORDLIST ' ||
1557 ' STOPLIST ' || g_Pref_Owner || '.EAM_WORK_ORDER_STOPLIST ' ||
1558 /* For now we will use basic lexer. For using multi lexer we need language column */
1559 ' LEXER ' || g_Pref_Owner || '.EAM_WORK_ORDER_LEXER_BASIC ' ||
1560 -- ' LANGUAGE COLUMN language ' ||
1561 ' SECTION GROUP ' || g_Pref_Owner || '.EAM_WORK_ORDER_SECTION_GROUP ' ||
1562 -- ' SECTION GROUP CTXSYS.NULL_SECTION_GROUP ' ||
1563 ' STORAGE ' || g_Pref_Owner || '.EAM_WORK_ORDER_STORAGE ' ||
1564 ' MEMORY ' || l_Index_Memory ||
1565 --' ' || l_index_populate ||
1566 ' '')'
1567 || ' ' || l_index_parallel
1568 ;
1569
1573 EXECUTE IMMEDIATE sql_stmt;
1570 Log_Line (l_api_name || ': creating index ' || g_Index_Owner || '.' || p_Index_Name || ' ...', FND_LOG.LEVEL_EVENT, l_api_name);
1571 --Log_Line (l_api_name || ': sql_stmt = ' || sql_stmt || ' /* End SQL */');
1572
1574
1575 Log_Line (l_api_name || ': done creating index.', FND_LOG.LEVEL_EVENT, l_api_name );
1576
1577 EXCEPTION
1578
1579 WHEN others THEN
1580 Log_Line (l_api_name || ': Error creating index ' || g_Index_Owner || '.' || p_Index_Name || ': ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1581
1582 -- Drop the index in case of an error during index creation to prevent the table lock.
1583 BEGIN
1584 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
1585 EXCEPTION
1586 WHEN others THEN
1587 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1588 RAISE G_EXC_ERROR;
1589 END;
1590
1591 RAISE G_EXC_ERROR;
1592
1593 END; -- execute sql
1594 END IF; -- create index
1595 /*
1596 Log_Line (l_api_name || ': calling Incremental_Sync');
1597
1598 eam_text_util.Incremental_Sync
1599 (
1600 p_Index_Name => p_Index_Name
1601 , p_batch_size => 40000
1602 , x_rows_processed => l_rows_processed
1603 , x_return_status => l_return_status
1604 );
1605 */
1606 -- End logging
1607
1608 IF ( ctx_Log_File_Name IS NOT NULL ) THEN
1609 BEGIN
1610 CTX_OUTPUT.End_Log;
1611 Log_Line (l_api_name || ': CTX End_Log', FND_LOG.LEVEL_EVENT, l_api_name);
1612 EXCEPTION
1613 WHEN others THEN
1614 Log_Line (l_api_name || ': Warning: CTX End_Log: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1615 l_return_status := G_STATUS_WARNING;
1616 END;
1617 END IF;
1618
1619 -- Check the created index status
1620
1621 SELECT idx_docid_count, idx_status
1622 INTO l_idx_docid_count, l_idx_status
1623 FROM ctxsys.ctx_indexes
1624 WHERE idx_owner = g_Prod_Schema AND idx_name = 'EAM_WORK_ORDER_TEXT_CTX1'
1625 AND idx_table = 'EAM_WORK_ORDER_TEXT';
1626
1627 IF NOT( l_idx_status = 'INDEXED' ) THEN
1628 Log_Line (l_api_name || ': Error: Index status is ' || l_idx_status || '.', FND_LOG.LEVEL_EVENT, l_api_name);
1629 l_return_status := G_STATUS_ERROR;
1630 END IF;
1631
1632 IF ( NVL(l_idx_docid_count, 0) = 0 ) THEN
1633 Log_Line (l_api_name || ': Error: Indexed document count is ' || TO_CHAR(l_idx_docid_count) || '.', FND_LOG.LEVEL_EVENT, l_api_name);
1634 l_return_status := G_STATUS_ERROR;
1635 END IF;
1636
1637 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1638 RAISE G_EXC_ERROR;
1639 END IF;
1640
1641 END IF; -- build index
1642
1643 x_return_status := l_return_status;
1644 Log_Line (l_api_name || ': end', FND_LOG.LEVEL_EVENT, l_api_name);
1645
1646 EXCEPTION
1647
1648 WHEN G_EXC_ERROR THEN
1649 x_return_status := G_STATUS_ERROR;
1650
1651 WHEN others THEN
1652 Log_Line (l_api_name || ': Error: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1653 x_return_status := G_STATUS_UNEXP_ERROR;
1654
1655 END Process_Wo_Text_Index;
1656
1657
1658
1659 -- -----------------------------------------------------------------------------
1660 -- Build_Text_Index
1661 -- -----------------------------------------------------------------------------
1662 /*
1663 p_text_context :
1664 1 - Asset
1665 2 - Work Order
1666
1667 p_action :
1668 1 - Create
1669 2 - Update / Rebuild
1670 3 - Optimize
1671 4 - Drop
1672 5- When work order status code is updated from user defined statuses form. The Status_Id which was updated will be passed in p_dummy1 parameter
1673 */
1674
1675
1676 PROCEDURE Build_Text_Index
1677 (
1678 ERRBUF OUT NOCOPY VARCHAR2
1679 , RETCODE OUT NOCOPY NUMBER
1680 , p_text_context IN NUMBER
1681 , p_Action IN NUMBER
1682 , p_dummy1 IN NUMBER DEFAULT NULL
1683 , p_optlevel IN NUMBER DEFAULT NULL
1684 , p_dummy2 IN NUMBER DEFAULT NULL
1685 , p_maxtime IN NUMBER DEFAULT AD_CTX_DDL.Maxtime_Unlimited
1686 )
1687 IS
1688 l_api_name CONSTANT VARCHAR2(30) := 'Build_Text_Index';
1689 l_Index_Name VARCHAR2(30);
1690 l_return_status VARCHAR2(1);
1691 l_optim_level VARCHAR2(30) := AD_CTX_DDL.Optlevel_Full;
1692 BEGIN
1693
1694 IF ( g_Log_Mode IS NULL ) THEN
1695 set_Log_Mode ('FILE');
1696 --set_Log_Mode ('SQLPLUS');
1697 END IF;
1698
1699 Log_Line ('Begin : Action=' || p_Action, FND_LOG.LEVEL_EVENT, l_api_name);
1700
1701 l_return_status := G_STATUS_SUCCESS;
1702
1703 IF (p_optlevel IS NOT NULL) THEN
1704 IF (p_optlevel = 1) THEN
1705 l_optim_level := AD_CTX_DDL.Optlevel_Fast;
1706 ELSIF (p_optlevel = 2) THEN
1707 l_optim_level := AD_CTX_DDL.Optlevel_Full;
1708 END IF;
1709 END IF;
1710
1711 IF p_text_context = 1 THEN
1712 l_Index_Name := 'EAM_ASSET_TEXT_CTX1';
1713
1714 Log_Line ('Calling Process_Asset_Text_Index ...', FND_LOG.LEVEL_EVENT, l_api_name);
1715
1716 IF (p_Action IN (1,2,4)) THEN
1717 Process_Asset_Text_Index
1721 , x_return_status => l_return_status
1718 (
1719 p_Index_Name => l_Index_Name
1720 , p_Action => p_Action
1722 );
1723 ELSIF (p_Action = 3) THEN
1724 Log_Line ('Calling Optimize_Index ...', FND_LOG.LEVEL_EVENT, l_api_name);
1725 request_log('EAM_TEXT_OPTIMIZE');
1726 Optimize_Index
1727 (
1728 x_return_status => l_return_status
1729 , p_optlevel => l_optim_level
1730 , p_maxtime => p_maxtime
1731 , p_index_name => l_Index_Name
1732 );
1733 END IF;
1734 ELSE --for workorders
1735
1736 l_Index_Name := 'EAM_WORK_ORDER_TEXT_CTX1';
1737
1738 Log_Line ('Calling Process_Wo_Text_Index ...', FND_LOG.LEVEL_EVENT, l_api_name);
1739
1740 IF (p_Action IN (1,2,4)) THEN
1741 Process_Wo_Text_Index
1742 (
1743 p_Index_Name => l_Index_Name
1744 , p_Action => p_Action
1745 , x_return_status => l_return_status
1746 );
1747 ELSIF (p_Action = 3) THEN
1748 Log_Line ('Calling Optimize_Index ...', FND_LOG.LEVEL_EVENT, l_api_name);
1749 request_log('EAM_TEXT_OPTIMIZE');
1750 Optimize_Index
1751 (
1752 x_return_status => l_return_status
1753 , p_optlevel => l_optim_level
1754 , p_maxtime => p_maxtime
1755 , p_index_name => l_Index_Name
1756 );
1757 ELSIF (p_action = 5) THEN
1758 Log_Line ('Calling Eam_Text_Util.Process_Status_Update_Event ...', FND_LOG.LEVEL_EVENT, l_api_name);
1759
1760 Eam_Text_Util.Process_Status_Update_Event(p_status_id => p_dummy1, --User Defined Status Id whose status code has been updated
1761 p_commit => FND_API.G_TRUE,
1762 x_return_status => l_return_status);
1763
1764 Log_Line ('after calling update status event ...Return Status is : '|| l_return_status, FND_LOG.LEVEL_EVENT, l_api_name);
1765 END IF;
1766
1767 END IF;
1768
1769 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1770 RAISE G_EXC_ERROR;
1771 END IF;
1772
1773 -- Assign conc request return code
1774
1775 IF ( l_return_status = G_STATUS_SUCCESS ) THEN
1776 RETCODE := G_RETCODE_SUCCESS;
1777 ERRBUF := FND_MESSAGE.Get_String('EAM', 'EAM_TEXT_INDEX_SUCCESS');
1778 ELSIF ( l_return_status = G_STATUS_WARNING ) THEN
1779 RETCODE := G_RETCODE_WARNING;
1780 ERRBUF := FND_MESSAGE.Get_String('EAM', 'EAM_TEXT_INDEX_WARNING');
1781 ELSE
1782 RETCODE := G_RETCODE_ERROR;
1783 ERRBUF := FND_MESSAGE.Get_String('EAM', 'EAM_TEXT_INDEX_FAILURE');
1784 END IF;
1785
1786 IF NOT(g_Conc_Req_flag) THEN
1787 FND_FILE.Close;
1788 END IF;
1789
1790 Log_Line (l_api_name || ': end', FND_LOG.LEVEL_EVENT, l_api_name);
1791
1792 EXCEPTION
1793
1794 WHEN G_EXC_ERROR THEN
1795 Log_Line (l_api_name || ': Error.', FND_LOG.LEVEL_EVENT, l_api_name);
1796 RETCODE := G_RETCODE_ERROR;
1797 ERRBUF := FND_MESSAGE.Get_String('EAM', 'EAM_TEXT_INDEX_FAILURE');
1798 ERRBUF := substr(ERRBUF || '(' || G_PKG_NAME || '.' ||G_PKG_NAME||'):'|| SQLERRM,1,240);
1799 log_line(ERRBUF, FND_LOG.LEVEL_EVENT, l_api_name);
1800
1801 IF NOT(g_Conc_Req_flag) THEN
1802 FND_FILE.Close;
1803 END IF;
1804
1805 WHEN others THEN
1806 Log_Line (l_api_name || ': Unexpected Error: ' || SQLERRM, FND_LOG.LEVEL_EVENT, l_api_name);
1807 RETCODE := G_RETCODE_ERROR;
1808 ERRBUF := FND_MESSAGE.Get_String('EAM', 'EAM_CP_FAILURE');
1809 ERRBUF := substr(ERRBUF || '(' || G_PKG_NAME || '.' ||G_PKG_NAME||'):'|| SQLERRM,1,240);
1810
1811 IF NOT(g_Conc_Req_flag) THEN
1812 FND_FILE.Close;
1813 END IF;
1814
1815 END Build_Text_Index;
1816
1817
1818 -- -----------------------------------------------------------------------------
1819 -- Optimize_Index
1820 -- -----------------------------------------------------------------------------
1821
1822 -- Start : Concurrent Program for Optimize Intermedia index
1823 PROCEDURE Optimize_Index
1824 (
1825 x_return_status OUT NOCOPY VARCHAR2
1826 , p_optlevel IN VARCHAR2 DEFAULT AD_CTX_DDL.Optlevel_Full
1827 , p_maxtime IN NUMBER DEFAULT AD_CTX_DDL.Maxtime_Unlimited
1828 , p_index_name IN VARCHAR2
1829 )
1830 IS
1831
1832 l_api_name CONSTANT VARCHAR2(30) := 'Optimize_Index';
1833 l_maxtime NUMBER := NVL(p_maxtime,AD_CTX_DDL.Maxtime_Unlimited);
1834
1835 BEGIN
1836
1837 log_line(l_api_name ||' : Started AD_CTX_DDL.Optimize_Index..', FND_LOG.LEVEL_EVENT, l_api_name);
1838 log_line(l_api_name ||' : Optimization Level :'||p_optlevel, FND_LOG.LEVEL_EVENT, l_api_name);
1839 log_line(l_api_name ||' : Maximum Optimization Time :'||p_maxtime, FND_LOG.LEVEL_EVENT, l_api_name);
1840
1841 x_return_status := G_STATUS_SUCCESS;
1842
1843 -- Maxtime should be null for FAST Optimize mode
1844 IF p_optlevel ='FAST' THEN
1845 l_maxtime := NULL;
1846 END IF;
1847
1848 AD_CTX_DDL.Optimize_Index ( idx_name => g_Index_Owner ||'.'|| p_index_name
1849 , optlevel => NVL(p_optlevel,AD_CTX_DDL.Optlevel_Full)
1850 , maxtime => l_maxtime);
1851
1852 log_line(l_api_name ||' : Completed AD_CTX_DDL.Optimize_Index..', FND_LOG.LEVEL_EVENT, l_api_name);
1853
1854
1855 EXCEPTION
1856 WHEN OTHERS THEN
1857 x_return_status := G_STATUS_UNEXP_ERROR;
1858 log_line(l_api_name ||' : Index optimization has failed ...', FND_LOG.LEVEL_EVENT, l_api_name);
1859 -- conc_status := FND_CONCURRENT.set_completion_status('ERROR', l_err_msg);
1860
1864
1861 END Optimize_Index;
1862 -- End : Concurrent Program for Optimize iM index
1863
1865
1866 -- *****************************************************************************
1867 -- ** Package initialization block **
1868 -- *****************************************************************************
1869
1870 BEGIN
1871
1872 -- Get EAM product schema name
1873 --
1874 g_installed := FND_INSTALLATION.Get_App_Info ('EAM', g_inst_status, g_industry, g_Prod_Schema);
1875
1876 g_Index_Owner := g_Prod_Schema;
1877 g_Pref_Owner := g_Prod_Schema;
1878
1879 END EAM_TEXT_INDEX_PVT;