[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_TEXT_PVT
Source
1 PACKAGE BODY EGO_ITEM_TEXT_PVT AS
2 /* $Header: EGOVIDXB.pls 120.4 2006/03/17 03:57:04 bparthas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_TEXT_PVT';
5
6 -- -----------------------------------------------------------------------------
7 -- Private Globals
8 -- -----------------------------------------------------------------------------
9
10 g_Ctx_Schema CONSTANT VARCHAR2(30) := 'CTXSYS';
11 --g_Apps_Schema VARCHAR2(30);
12 g_Prod_Schema VARCHAR2(30);
13 g_Index_Owner VARCHAR2(30);
14 g_Pref_Owner VARCHAR2(30);
15
16 g_installed BOOLEAN;
17 g_inst_status VARCHAR2(1);
18 g_industry VARCHAR2(1);
19
20 --g_Debug VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
21 --g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
22
23 -- Log mode
24 g_Log_Mode VARCHAR2(30) := NULL;
25 g_Conc_Req_flag BOOLEAN := TRUE;
26 g_Log_Sqlplus_Flag BOOLEAN := FALSE;
27 g_Log_File_Flag BOOLEAN := FALSE;
28 g_Log_Dbdrv_Flag BOOLEAN := FALSE;
29
30 g_Msg_Text VARCHAR2(1000);
31
32 -- Log directory
33 g_Log_Dir v$parameter.value%TYPE;
34 g_Dump_Dir v$parameter.value%TYPE;
35
36 -- Log files for Sqlplus
37 g_Log_File VARCHAR2(30) := 'ego_item_text_tl_srs.log';
38 g_Out_File VARCHAR2(30) := 'ego_item_text_tl_srs.out';
39
40 -- -----------------------------------------------------------------------------
41 -- Log_Line
42 -- -----------------------------------------------------------------------------
43
44 PROCEDURE Log_Line ( p_Buffer IN VARCHAR2 )
45 IS
46 BEGIN
47 IF ( g_Log_File_Flag ) THEN
48 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
49 FND_FILE.Put_Line (FND_FILE.Log, SUBSTRB(p_Buffer, l*240 + 1, 240));
50 END LOOP;
51 END IF;
52
53 IF ( g_Log_Sqlplus_Flag ) THEN
54 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
55 --DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
56 NULL;
57 END LOOP;
58 END IF;
59
60 IF ( g_Log_Dbdrv_Flag ) THEN
61 --IF ( (INSTR(p_Buffer, 'Error:') + INSTR(p_Buffer, 'Warning:')) > 0 ) THEN
62 IF ( ( INSTR(p_Buffer, 'Error:') > 0 )
63 OR ( INSTR(p_Buffer, 'Warning:') > 0 )
64 OR ( INSTR(p_Buffer, 'Executing:') > 0 )
65 OR ( INSTR(p_Buffer, 'Done.') > 0 )
66 OR ( INSTR(p_Buffer, 'Upgrade:') > 0 ) )
67 THEN
68 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
69 END IF;
70 END IF;
71 END Log_Line;
72
73 PROCEDURE Out_Line ( p_Buffer IN VARCHAR2 )
74 IS
75 BEGIN
76 IF ( g_Log_File_Flag ) THEN
77 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
78 FND_FILE.Put_Line (FND_FILE.Output, SUBSTRB(p_Buffer, l*240 + 1, 240));
79 END LOOP;
80 END IF;
81
82 IF ( g_Log_Sqlplus_Flag ) THEN
83 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
84 --DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
85 NULL;
86 END LOOP;
87 END IF;
88
89 IF ( g_Log_Dbdrv_Flag ) THEN
90 IF ( INSTR(p_Buffer, 'Completed ') > 0 ) THEN
91 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
92 END IF;
93 END IF;
94 END Out_Line;
95
96 -- -----------------------------------------------------------------------------
97 -- set_Log_Mode
98 -- -----------------------------------------------------------------------------
99
100 FUNCTION set_Log_Mode ( p_Mode IN VARCHAR2 )
101 RETURN VARCHAR2
102 IS
103 l_api_name CONSTANT VARCHAR2(30) := 'set_Log_Mode';
104 BEGIN
105 g_Log_Mode := p_Mode;
106
107 g_Log_Sqlplus_Flag := ( INSTR(g_Log_Mode, 'SQLPLUS') > 0 );
108 g_Log_File_Flag := ( (INSTR(g_Log_Mode, 'FILE') + INSTR(g_Log_Mode, 'SRS')) > 0 );
109 g_Log_Dbdrv_Flag := ( INSTR(g_Log_Mode, 'DBDRV') > 0 );
110
111 g_Msg_Text := NULL;
112
113 -- Determine log directory
114
115 BEGIN
116 SELECT value INTO g_Dump_Dir
117 FROM v$parameter WHERE name = 'user_dump_dest';
118
119 SELECT TRANSLATE(LTRIM(value), ',', ' ') INTO g_Log_Dir
120 FROM v$parameter
121 WHERE name = 'utl_file_dir';
122
123 IF ( g_Log_Dir IS NOT NULL ) THEN
124 IF ( INSTR(g_Log_Dir, ' ') > 0 ) THEN
125 g_Log_Dir := SUBSTRB(g_Log_Dir, 1, INSTR(g_Log_Dir,' ')-1);
126 END IF;
127 END IF;
128
129 --EXCEPTION
130 -- WHEN others THEN
131 -- Log_Line ('Error determining CTX log directory: ' || SQLERRM);
132 END;
133
134 IF ( NVL(FND_GLOBAL.CONC_REQUEST_ID,-1) < 0 ) THEN
135 g_Conc_Req_flag := FALSE;
136 FND_FILE.Put_Names (g_Log_File, g_Out_File, g_Log_Dir);
137 Log_Line (l_api_name || ': standalone execution');
138 ELSE
139 Log_Line (l_api_name || ': concurrent request');
140 END IF;
141
142 Log_Line (l_api_name || ': log mode: ' || g_Log_Mode);
143 Log_Line (l_api_name || ': dump directory: ' || g_Dump_Dir);
144 Log_Line (l_api_name || ': CTX log directory: ' || g_Log_Dir);
145
146 RETURN (NULL);
147
148 END set_Log_Mode;
149
150 PROCEDURE set_Log_Mode ( p_Mode IN VARCHAR2 )
151 IS
152 l_output_name VARCHAR2(255);
153 BEGIN
154 l_output_name := set_Log_Mode (p_Mode);
155 END set_Log_Mode;
156
157 -- -----------------------------------------------------------------------------
158 -- Process_Index_Preferences
159 -- -----------------------------------------------------------------------------
160
161 PROCEDURE Process_Index_Preferences
162 (
163 p_Index_Name IN VARCHAR2
164 , x_return_status OUT NOCOPY VARCHAR2
165 )
166 IS
167 l_api_name CONSTANT VARCHAR2(30) := 'Process_Index_Preferences';
168 l_return_status VARCHAR2(1);
169
170 tspace_tbl_param VARCHAR2(256);
171 tspace_idx_param VARCHAR2(256);
172
173 Lang_Code VARCHAR2(4);
174 Lexer_Name VARCHAR2(30);
175
176 l_pref_name VARCHAR2(30);
177
178 TYPE Lang_Code_List_type IS TABLE OF VARCHAR2(4);
179 --TYPE Lang_ISO_List_type IS TABLE OF VARCHAR2(2);
180
181 Lang_Code_List Lang_Code_List_type := Lang_Code_List_type
182 ( 'US', 'GB', 'NL', 'D', 'DK', 'S', 'N',
183 'F', 'I', 'E', 'ESA', 'EL',
184 'JA', 'KO', 'ZHS', 'ZHT' );
185
186 --Lang_ISO_List Lang_ISO_List_type :=
187 -- Lang_ISO_List_type ('EN', '', 'DE', 'SV', 'NO', 'FR', '', '');
188
189 BEGIN
190 Log_Line (l_api_name || ': begin');
191
192 l_return_status := G_STATUS_SUCCESS;
193
194 ------------------------------
195 -- Drop existing preferences
196 ------------------------------
197
198 Log_Line (l_api_name || ': dropping all existing preferences ...');
199
200 l_pref_name := 'EGO_ITEM_MULTI_LEXER';
201 FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
202 FROM ctxsys.ctx_preferences
203 WHERE pre_name = l_pref_name )
204 LOOP
205 ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
206 END LOOP;
207
208 FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
209 FROM ctxsys.ctx_preferences
210 WHERE pre_name LIKE 'EGO_ITEM_LEXER%' )
211 LOOP
212 ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
213 END LOOP;
214
215 l_pref_name := 'EGO_ITEM_WORDLIST';
216 FOR wordlist_rec IN ( SELECT pre_owner, pre_name
217 FROM ctxsys.ctx_preferences
218 WHERE pre_name = l_pref_name )
219 LOOP
220 ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
221 END LOOP;
222
223 FOR stoplist_rec IN ( SELECT spl_owner, spl_name
224 FROM ctxsys.ctx_stoplists
225 WHERE spl_name = 'EGO_ITEM_STOPLIST' )
226 LOOP
227 ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EGO_ITEM_STOPLIST');
228 END LOOP;
229
230 FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
231 FROM ctxsys.ctx_section_groups
232 WHERE sgp_name = 'EGO_ITEM_SECTION_GROUP' )
233 LOOP
234 ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
235 END LOOP;
236
237 l_pref_name := 'EGO_ITEM_DATASTORE';
238 FOR datastore_rec IN ( SELECT pre_owner, pre_name
239 FROM ctxsys.ctx_preferences
240 WHERE pre_name = l_pref_name )
241 LOOP
242 ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
243 END LOOP;
244
245 l_pref_name := 'EGO_ITEM_STORAGE';
246 FOR storage_rec IN ( SELECT pre_owner, pre_name
247 FROM ctxsys.ctx_preferences
248 WHERE pre_name = l_pref_name )
249 LOOP
250 ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
251 END LOOP;
252
253 ------------------------------
254 -- Create STORAGE preference
255 ------------------------------
256
257 -- Index tables use the same tablespaces used by other EGO tables and indexes
258 -- or use logical tablespace for indexes (TRANSACTION_INDEXES).
259
260 Log_Line (l_api_name || ': querying tablespace parameters ...');
261
262 SELECT 'tablespace ' || tablespace_name ||
263 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
264 INTO tspace_tbl_param
265 FROM all_tables
266 WHERE owner = g_Prod_Schema AND table_name = 'EGO_ITEM_TEXT_TL';
267
268 SELECT 'tablespace ' || tablespace_name ||
269 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
270 INTO tspace_idx_param
271 FROM all_indexes
272 WHERE owner = g_Prod_Schema
273 AND index_name = 'EGO_ITEM_TEXT_TL_U1'
274 AND table_name = 'EGO_ITEM_TEXT_TL';
275
276 Log_Line (l_api_name || ': creating STORAGE preference ...');
277
278 ad_ctx_ddl.create_preference (g_Pref_Owner || '.EGO_ITEM_STORAGE', 'BASIC_STORAGE');
279
280 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
281 'I_TABLE_CLAUSE', tspace_tbl_param);
282
283 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
284 'K_TABLE_CLAUSE', tspace_tbl_param);
285
286 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
287 'R_TABLE_CLAUSE', tspace_tbl_param || ' LOB (data) STORE AS (CACHE)');
288
289 -- Caching the "data" LOB column is the default (at later versions of Oracle Text).
290 -- For index specific STORAGE preference, setting the clause "lob (data) (cache reads)"
291 -- should be ensured (the "lob .. store as" clause is only for newly added LOB columns).
292 --alter table dr$prd_ctx_index$r modify lob (data) (cache reads);
293
294 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
295 'N_TABLE_CLAUSE', tspace_tbl_param);
296
297 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
298 'P_TABLE_CLAUSE', tspace_tbl_param);
299
300 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
301 'I_INDEX_CLAUSE', tspace_idx_param || ' COMPRESS 2');
302
303 --ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
304 -- 'I_ROWID_INDEX_CLAUSE', tspace_idx_param);
305
306 --------------------------------
307 -- Create DATASTORE preference
308 --------------------------------
309
310 Log_Line (l_api_name || ': creating DATASTORE preference ...');
311
312 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'USER_DATASTORE');
313
314 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'OUTPUT_TYPE', 'CLOB');
315 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'PROCEDURE', '"EGO_ITEM_TEXT_CTX_PKG"."Get_Item_Text_CLOB"');
316
317 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'OUTPUT_TYPE', 'VARCHAR2');
318 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'PROCEDURE', '"EGO_ITEM_TEXT_CTX_PKG"."Get_Item_Text_VARCHAR2"');
319
320 ------------------------------------
321 -- Create SECTION GROUP preference
322 ------------------------------------
323
324 Log_Line (l_api_name || ': creating SECTION_GROUP preference ...');
325
326 ad_ctx_ddl.Create_Section_Group (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'BASIC_SECTION_GROUP');
327 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_ITEM_SECTION', 'item');
328 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_DESC_SECTION', 'desc');
329 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_SHORTDESC_SECTION', 'shortdesc');
330 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_LONGDESC_SECTION', 'longdesc');
331 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_CAT_SECTION', 'cat');
332 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_AML_SECTION', 'aml');
333 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_MFG_SECTION', 'mfg');
334 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_MPN_SECTION', 'mpn');
335 --Bug 5094325 begin
336 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_ORG_SECTION', 'org');
337 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_LANG_SECTION', 'lang');
338 --Bug 5094325 end
339
340 -------------------------------
341 -- Create STOPLIST preference
342 -------------------------------
343
344 --We are sticking with the default stoplist, containing several common English words and abbreviations
345 --Log_Line (l_api_name || ': creating STOPLIST preference ...');
346
347 -- This should create stoplist equivalent to CTXSYS.EMPTY_STOPLIST
348 --ad_ctx_ddl.Create_Stoplist (g_Pref_Owner || '.EGO_ITEM_STOPLIST');
349
350 -------------------------------
351 -- Create WORDLIST preference
352 -------------------------------
353
354 Log_Line (l_api_name || ': creating WORDLIST preference ...');
355
356 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'BASIC_WORDLIST');
357
358 -- Enable prefix indexing to improve performance for wildcard searches
359 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_INDEX', 'TRUE');
360 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_MIN_LENGTH', 2);
361 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_LENGTH_MIN', 2);
362 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_MAX_LENGTH', 32);
363 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_LENGTH_MAX', 32);
364 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'WILDCARD_MAXTERMS', 5000);
365
366 -- This option should be TRUE only when left-truncated wildcard searching is expected
367 -- to be frequent and needs to be fast (at the cost of increased index time and space).
368 --
369 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'SUBSTRING_INDEX', 'FALSE');
370
371 -- WORDLIST attribute defaults: STEMMER: 'ENGLISH'; FUZZY_MATCH: 'GENERIC'
372 -- Use automatic language detection for stemming and fuzzy matching
373 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'STEMMER', 'AUTO');
374 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_MATCH', 'AUTO');
375 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_SCORE', 40);
376 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_NUMRESULTS', 120);
377
378 -----------------------------------------------
379 -- Create language-specific LEXER preferences
380 -----------------------------------------------
381
382 Log_Line (l_api_name || ': creating language-specific LEXER preferences ...');
383
384 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
385 LOOP
386 Lexer_Name := g_Pref_Owner || '.EGO_ITEM_LEXER_' || Lang_Code_List(i);
387
388 IF ( Lang_Code_List(i) = 'JA' ) THEN
389
390 -- Use JAPANESE_LEXER if db charset is UTF8, JA16SJIS, or JA16EUC.
391 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.0 ) THEN
392 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_LEXER');
393 ELSE
394 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_VGRAM_LEXER');
395 END IF;
396
397 ELSIF ( Lang_Code_List(i) = 'KO' ) THEN
398
399 -- Use KOREAN_MORPH_LEXER if db charset is UTF8 or KO16KSC5601.
400 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.0 ) THEN
401 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_MORPH_LEXER');
402 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB_ADJECTIVE', 'TRUE');
403 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONE_CHAR_WORD', 'TRUE');
404 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
405 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'NGRAM');
406 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
407 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'HANJA', 'FALSE');
408 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'LONG_WORD', 'TRUE');
409 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'JAPANESE', 'FALSE');
410 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ENGLISH', 'TRUE');
411 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TO_UPPER', 'TRUE');
412 ELSE
413 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_LEXER');
414 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB', 'TRUE');
415 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADJECTIVE', 'TRUE');
416 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADVERB', 'TRUE');
417 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONECHAR', 'TRUE');
418 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
419 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'TRUE');
420 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
421 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOHANGEUL', 'TRUE');
422 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOUPPER', 'TRUE');
423 END IF;
424
425 ELSIF ( Lang_Code_List(i) IN ('ZHS', 'ZHT') ) THEN
426
427 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
428 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_LEXER');
429 ELSE
430 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_VGRAM_LEXER');
431 END IF;
432
433 ELSE
434 -- All other languages use basic lexer.
435
436 ad_ctx_ddl.Create_Preference (Lexer_Name, 'BASIC_LEXER');
437
438 -- The following language-independent attributes are
439 -- common to the BASIC_LEXER preference object.
440
441 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_TEXT', 'YES');
442 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_THEMES', 'NO');
443
444 -- For printjoin characters include all possible flex segment separators
445 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PRINTJOINS', '-_*~^+.$#@:|&');
446 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'CONTINUATION', '-\');
447 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PUNCTUATIONS', '.?!');
448
449 -- The default values for numjoin and numgroup are determined by
450 -- the NLS initialization parameters that are specified for the database.
451 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMGROUP', ',');
452 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMJOIN', '.');
453
454 -- Stem indexing stems tokens at indexing time to a single base form in addition
455 -- to the normal forms. This enables better query performance for stem ($) queries.
456
457 -- Disable stem indexing to improve index creation performance.
458 -- This would not affect stem expansion (with $) at query time.
459 --
460 --IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
461 -- IF ( Lang_Code_List(i) IN ('US', 'GB') ) THEN
462 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ENGLISH');
463 -- ELSIF ( Lang_Code_List(i) = 'NL' ) THEN
464 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'DUTCH');
465 -- ELSIF ( Lang_Code_List(i) = 'D' ) THEN
466 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'GERMAN');
467 -- ELSIF ( Lang_Code_List(i) = 'F' ) THEN
468 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'FRENCH');
469 -- ELSIF ( Lang_Code_List(i) = 'I' ) THEN
470 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ITALIAN');
471 -- ELSIF ( Lang_Code_List(i) IN ('E', 'ESA') ) THEN
472 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'SPANISH');
473 -- END IF;
474 --END IF;
475
476 -- Language-specific attribute values for BASIC_LEXER preference object
477
478 IF ( Lang_Code_List(i) = 'NL' ) THEN
479 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'DUTCH');
480
481 ELSIF ( Lang_Code_List(i) = 'D' ) THEN
482 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'GERMAN');
483 -- Basic lexer in 8.1.7 allows the MIXED_CASE to be FALSE when COMPOSITE is set.
484 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MIXED_CASE', 'NO');
485 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'GERMAN');
486
487 ELSIF ( Lang_Code_List(i) = 'DK' ) THEN
488 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
489
490 ELSIF ( Lang_Code_List(i) = 'S' ) THEN
491 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'SWEDISH');
492
493 ELSIF ( Lang_Code_List(i) = 'N' ) THEN
494 -- Both Norwegian and Danish use the same special characters that are
495 -- rendered alternatively as "aa", "ae", and "oe".
496 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
497
498 ELSIF ( Lang_Code_List(i) = 'F' ) THEN
499 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'BASE_LETTER', 'YES');
500
501 ELSE
502 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'NONE');
503
504 END IF;
505 END IF;
506
507 END LOOP; -- Lang_Code_List
508
509 ----------------------------------
510 -- Create MULTI_LEXER preference
511 ----------------------------------
512
513 Log_Line (l_api_name || ': creating MULTI_LEXER preference ...');
514
515 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER', 'MULTI_LEXER');
516
517 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
518 LOOP
519 Lexer_Name := g_Pref_Owner || '.EGO_ITEM_LEXER_' || Lang_Code_List(i);
520
521 -- The language column is case-independent, and can contain either the NLS name
522 -- or abbreviation of the language. If the table uses some other value for the
523 -- language, that alternate value needs to be specified as the fourth argument
524 -- when adding the sub lexers:
525
526 IF ( Lang_Code_List(i) = 'US' ) THEN
527 -- US English lexer to handle everything else.
528 ad_ctx_ddl.Add_Sub_Lexer ( g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER', 'DEFAULT'
529 , g_Pref_Owner || '.EGO_ITEM_LEXER_US' );
530 ELSE
531 ad_ctx_ddl.Add_Sub_Lexer
532 ( lexer_name => g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER'
533 , language => Lang_Code_List(i)
534 , sub_lexer => Lexer_Name
535 --, alt_value => Lang_ISO_List(i)
536 );
537 END IF;
538
539 END LOOP;
540
541 x_return_status := l_return_status;
542
543 Log_Line (l_api_name || ': end');
544
545 EXCEPTION
546
547 WHEN G_EXC_ERROR THEN
548 x_return_status := G_STATUS_ERROR;
549
550 WHEN others THEN
551 Log_Line (l_api_name || ': Error: ' || SQLERRM);
552 x_return_status := G_STATUS_UNEXP_ERROR;
553
554 END Process_Index_Preferences;
555
556 -- -----------------------------------------------------------------------------
557 -- Process_Item_Text_Index
558 -- -----------------------------------------------------------------------------
559
560 PROCEDURE Process_Item_Text_Index
561 (
562 p_Index_Name IN VARCHAR2
563 , p_Policy_Name IN VARCHAR2
564 , p_Action IN VARCHAR2
565 , x_return_status OUT NOCOPY VARCHAR2
566 )
567 IS
568 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Text_Index';
569 l_return_status VARCHAR2(1);
570 ctx_Log_File_Name VARCHAR2(512) := NULL;
571
572 l_index_exists BOOLEAN := FALSE;
573 l_index_valid BOOLEAN := TRUE;
574
575 l_create_index BOOLEAN := TRUE;
576 l_drop_index BOOLEAN := FALSE;
577
578 l_rows_processed INTEGER;
579
580 -- Limit the indexing memory in case the default parameter max value is higher.
581 --l_Index_Memory ctx_parameters.par_value%TYPE := '67108864'; -- '64M'
582 l_Index_Memory ctx_parameters.par_value%TYPE := '134217728'; -- '128M'
583 l_Index_Memory_Max ctx_parameters.par_value%TYPE;
584
585 l_idx_docid_count NUMBER;
586 l_idx_status VARCHAR2(256);
587
588 l_index_populate VARCHAR2(30) := 'POPULATE';
589
590 -- Use parallel indexing when this issue is resolved
591 c_parallel_clause CONSTANT VARCHAR2(30) := 'PARALLEL 4';
592 --c_parallel_clause CONSTANT VARCHAR2(30) := NULL;
593 l_index_parallel VARCHAR2(30) := NULL;
594 sql_stmt VARCHAR2(32767);
595 BEGIN
596
597 Log_Line (l_api_name || ': begin: Index_Name=' || p_Index_Name || ' Action=' || p_Action);
598
599 l_return_status := G_STATUS_SUCCESS;
600
601 IF ( p_Action NOT IN ('CREATE', 'REBUILD', 'UPGRADE', 'DROP') ) THEN
602 Log_Line (l_api_name || ': Error: invalid value for parameter p_Action: ' || p_Action);
603 RAISE G_EXC_ERROR;
604 END IF;
605
606 -- Check for existing indexes in the EGO product, APPS Universal, and interMedia schemas.
607 --
608 FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
609 FROM all_indexes
610 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
611 AND table_name = 'EGO_ITEM_TEXT_TL'
612 AND index_name = p_Index_Name )
613 LOOP
614 -- Check index schema
615 --
616 IF ( index_rec.owner <> g_Index_Owner )
617 THEN
618 Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner);
619 BEGIN
620 Log_Line (l_api_name || ': dropping index: ' || index_rec.owner || '.' || p_Index_Name);
621 EXECUTE IMMEDIATE 'DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE';
622 EXCEPTION
623 WHEN others THEN
624 Log_Line (l_api_name || ': Error: DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
625 l_return_status := G_STATUS_ERROR;
626 END;
627
628 ELSE
629 l_index_exists := TRUE;
630
631 -- Check status of an existing index, if any.
632 --
633 IF ( (NVL(index_rec.status, 'FAILED') <> 'VALID') OR
634 (NVL(index_rec.domidx_status, 'FAILED') <> 'VALID') OR
635 (NVL(index_rec.domidx_opstatus, 'FAILED') <> 'VALID') )
636 THEN
637 l_index_valid := FALSE;
638 Log_Line (l_api_name || ': Warning: existing index status is invalid:'
639 || ' status=' || index_rec.status
640 || ' domidx_status=' || index_rec.domidx_status
641 || ' domidx_opstatus=' || index_rec.domidx_opstatus);
642 ELSE
643 Log_Line (l_api_name || ': valid index exists: ' || index_rec.owner || '.' || p_Index_Name);
644 END IF;
645
646 END IF; -- index owner
647
648 END LOOP; -- check for any existing indexes
649
650 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
651 RAISE G_EXC_ERROR;
652 END IF;
653
654 -- x_return_status := l_return_status;
655
656 -- Set indexing flags depending on the action and the index status
657
658 IF ( p_Action = 'CREATE' ) THEN
659 IF ( l_index_exists ) THEN
660 IF ( l_index_valid ) THEN
661 Log_Line (l_api_name || ': Error: cannot execute ' || p_Action || ' because the index exists.');
662 RAISE G_EXC_ERROR;
663 ELSE
664 l_drop_index := TRUE;
665 END IF;
666 END IF;
667
668 ELSIF ( p_Action = 'REBUILD' ) THEN
669 l_drop_index := l_index_exists;
670
671 ELSIF ( p_Action = 'UPGRADE' ) THEN
672 IF ( l_index_exists ) THEN
673 IF ( l_index_valid ) THEN
674 Log_Line (l_api_name || ': Upgrade: skipping index creation because valid index exists.');
675 l_create_index := FALSE;
676 ELSE
677 l_drop_index := TRUE;
678 END IF;
679 END IF;
680
681 ELSIF ( p_Action = 'DROP' ) THEN
682 l_create_index := FALSE;
683 IF ( l_index_exists ) THEN
684 l_drop_index := TRUE;
685 ELSE
686 Log_Line (l_api_name || ': Warning: cannot execute ' || p_Action || ' because the index does not exist.');
687 l_return_status := G_STATUS_WARNING;
688 END IF;
689
690 END IF; -- Action
691
692 IF ( l_drop_index ) THEN
693 BEGIN
694 Log_Line (l_api_name || ': dropping index: ' || g_Index_Owner || '.' || p_Index_Name);
695 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
696 -- Drop the policy too, if it exists
697 IF (p_Policy_Name IS NOT NULL) THEN
698 BEGIN
699 ctx_ddl.Drop_Policy(g_Index_Owner || '.' || p_Policy_Name);
700 EXCEPTION
701 WHEN OTHERS THEN
702 Log_Line (l_api_name || ': policy does not exist; cannot be dropped.');
703 END;
704 END IF;
705
706 IF ( l_index_valid ) THEN
707 Log_Line (l_api_name || ': existing index has been successfully dropped.');
708 Out_Line ('Existing index has been successfully dropped: ' || g_Index_Owner || '.' || p_Index_Name);
709 ELSE
710 Log_Line (l_api_name || ': invalid index has been dropped.');
711 Out_Line ('Invalid index has been dropped: ' || g_Index_Owner || '.' || p_Index_Name);
712 END IF;
713 EXCEPTION
714 WHEN others THEN
715 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
716 l_return_status := G_STATUS_ERROR;
717 END;
718 END IF; -- drop index
719
720 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
721 RAISE G_EXC_ERROR;
722 END IF;
723
724 -- Build index
725
726 IF ( l_create_index ) THEN
727
728 -- Determine indexing memory limit parameter value
729 BEGIN
730 SELECT par_value INTO l_Index_Memory_Max
731 FROM ctx_parameters
732 WHERE par_name = 'MAX_INDEX_MEMORY';
733 EXCEPTION
734 WHEN no_data_found THEN
735 Log_Line (l_api_name || ': Error: MAX_INDEX_MEMORY parameter record not found.');
736 RAISE G_EXC_ERROR;
737 END;
738
739 IF ( TO_NUMBER(l_Index_Memory) > TO_NUMBER(l_Index_Memory_Max) ) THEN
740 --l_Index_Memory := l_Index_Memory_Max;
741 -- Adjust limit on memory used for indexing
742 ctxsys.CTX_ADM.Set_Parameter ('MAX_INDEX_MEMORY', l_Index_Memory);
743 END IF;
744
745 Log_Line (l_api_name || ': CTX index_memory: ' || l_Index_Memory);
746
747 -- Decide whether parallel indexing can be used, depending on DB version.
748
749 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
750 l_index_parallel := c_parallel_clause;
751 Log_Line (l_api_name || ': DB version: ' || EGO_ITEM_TEXT_UTIL.get_DB_Version_Str || ', using parallel clause: ' || l_index_parallel);
752 END IF;
753
754 -- Start logging indexing progress
755
756 IF ( g_Log_Dir IS NOT NULL ) THEN
757 BEGIN
758 Log_Line (l_api_name || ': CTX Log_Directory: ' || g_Log_Dir);
759 ctxsys.CTX_ADM.Set_Parameter ( 'LOG_DIRECTORY', g_Log_Dir );
760 Log_Line (l_api_name || ': CTX Start_Log');
761 CTX_OUTPUT.Start_Log ( LOWER(p_Index_Name) || '.log' );
762 ctx_Log_File_Name := CTX_OUTPUT.LogFileName;
763 Log_Line (l_api_name || ': CTX LogFileName: ' || ctx_Log_File_Name);
764 EXCEPTION
765 WHEN others THEN
766 Log_Line (l_api_name || ': Warning: CTX Start_Log: ' || SQLERRM);
767 l_return_status := G_STATUS_WARNING;
768 END;
769 END IF;
770
771 -- Choose indexing method
772 --IF ( ) THEN
773 -- l_index_populate := 'NOPOPULATE';
774 --END IF;
775
776 IF ( l_create_index ) THEN
777 BEGIN
778
779 EGO_ITEM_TEXT_UTIL.Set_Context ('CREATE_INDEX');
780
781 sql_stmt :=
782 'CREATE INDEX ' || g_Index_Owner || '.' || p_Index_Name ||
783 ' ON ' || g_Prod_Schema || '.EGO_ITEM_TEXT_TL (text) ' ||
784 ' INDEXTYPE IS CTXSYS.context ' ||
785 ' PARAMETERS ' ||
786 ' (''DATASTORE ' || g_Pref_Owner || '.EGO_ITEM_DATASTORE ' ||
787 ' WORDLIST ' || g_Pref_Owner || '.EGO_ITEM_WORDLIST ' ||
788 --' STOPLIST ' || g_Pref_Owner || '.EGO_ITEM_STOPLIST ' ||
789 ' LEXER ' || g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER ' ||
790 ' LANGUAGE COLUMN language ' ||
791 ' SECTION GROUP ' || g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP ' ||
792 --' SECTION GROUP CTXSYS.NULL_SECTION_GROUP ' ||
793 ' STORAGE ' || g_Pref_Owner || '.EGO_ITEM_STORAGE ' ||
794 ' MEMORY ' || l_Index_Memory ||
795 --' ' || l_index_populate ||
796 ' '')' || ' ' || l_index_parallel ;
797
798 Log_Line (l_api_name || ': creating index ' || g_Index_Owner || '.' || p_Index_Name || ' ...');
799 Log_Line (l_api_name || ': sql_stmt = ' || sql_stmt || ' /* End SQL */');
800
801 EXECUTE IMMEDIATE sql_stmt;
802
803 Log_Line (l_api_name || ': done creating index.');
804
805 EXCEPTION
806
807 WHEN others THEN
808 Log_Line (l_api_name || ': Error creating index ' || g_Index_Owner || '.' || p_Index_Name || ': ' || SQLERRM);
809
810 -- Drop the index in case of an error during index creation to prevent the table lock.
811 BEGIN
812 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
813 EXCEPTION
814 WHEN others THEN
815 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
816 RAISE G_EXC_ERROR;
817 END;
818
819 RAISE G_EXC_ERROR;
820
821 END; -- execute sql
822 END IF; -- create index
823 /*
824 Log_Line (l_api_name || ': calling Incremental_Sync');
825
826 EGO_ITEM_TEXT_UTIL.Incremental_Sync
827 (
828 p_Index_Name => p_Index_Name
829 , p_batch_size => 40000
830 , x_rows_processed => l_rows_processed
831 , x_return_status => l_return_status
832 );
833 */
834 -- End logging
835
836 IF ( ctx_Log_File_Name IS NOT NULL ) THEN
837 BEGIN
838 CTX_OUTPUT.End_Log;
839 Log_Line (l_api_name || ': CTX End_Log');
840 EXCEPTION
841 WHEN others THEN
842 Log_Line (l_api_name || ': Warning: CTX End_Log: ' || SQLERRM);
843 l_return_status := G_STATUS_WARNING;
844 END;
845 END IF;
846
847 Out_Line ('Completed building Catalog Item Text Index.');
848
849 -- Check the created index status
850
851 SELECT idx_docid_count, idx_status
852 INTO l_idx_docid_count, l_idx_status
853 FROM ctxsys.ctx_indexes
854 WHERE idx_owner = g_Index_Owner AND idx_name = p_Index_Name
855 AND idx_table = 'EGO_ITEM_TEXT_TL';
856
857 IF NOT( l_idx_status = 'INDEXED' ) THEN
858 Log_Line (l_api_name || ': Error: Index status is ' || l_idx_status || '.');
859 l_return_status := G_STATUS_ERROR;
860 END IF;
861
862 IF ( NVL(l_idx_docid_count, 0) = 0 ) THEN
863 Log_Line (l_api_name || ': Error: Indexed document count is ' || TO_CHAR(l_idx_docid_count) || '.');
864 l_return_status := G_STATUS_ERROR;
865 END IF;
866
867 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
868 RAISE G_EXC_ERROR;
869 END IF;
870
871 -- Copy the policy too, if it doesn't already exist
872 IF (p_Policy_Name IS NOT NULL) THEN
873 BEGIN
874 Log_Line (l_api_name || ': Copying policy from index...');
875 ctx_ddl.Copy_Policy(g_Index_Owner || '.' || p_Index_Name, g_Index_Owner || '.' || p_Policy_Name);
876 EXCEPTION
877 WHEN OTHERS THEN
878 Log_Line (l_api_name || ': policy could not be copied - probably already exists.');
879 END;
880 END IF;
881
882 END IF; -- build index
883
884 x_return_status := l_return_status;
885 /*
886 Out_Line ('Product Catalog Text Index');
887 Out_Line ('--------------------------');
888 Out_Line ('Number of rows indexed: ' || 0);
889 */
890 Log_Line (l_api_name || ': end');
891
892 EXCEPTION
893
894 WHEN G_EXC_ERROR THEN
895 x_return_status := G_STATUS_ERROR;
896
897 WHEN others THEN
898 Log_Line (l_api_name || ': Error: ' || SQLERRM);
899 x_return_status := G_STATUS_UNEXP_ERROR;
900
901 END Process_Item_Text_Index;
902
903 -- -----------------------------------------------------------------------------
904 -- Build_Item_Text_Index
905 -- -----------------------------------------------------------------------------
906
907 PROCEDURE Build_Item_Text_Index
908 (
909 ERRBUF OUT NOCOPY VARCHAR2
910 , RETCODE OUT NOCOPY NUMBER
911 , p_Action IN VARCHAR2
912 )
913 IS
914 l_api_name CONSTANT VARCHAR2(30) := 'Build_Item_Text_Index';
915 l_Index_Name VARCHAR2(30) := 'EGO_ITEM_TEXT_TL_CTX1';
916 l_Policy_Name VARCHAR2(30) := 'EGO_ITEM_TEXT_TL_POL1';
917 l_return_status VARCHAR2(1);
918 BEGIN
919 Log_Line (l_api_name || ': begin: Action=' || p_Action);
920
921 IF ( g_Log_Mode IS NULL ) THEN
922 set_Log_Mode ('FILE');
923 END IF;
924
925 l_return_status := G_STATUS_SUCCESS;
926
927 IF ( p_Action IN ('CREATE', 'REBUILD', 'UPGRADE') ) THEN
928 Log_Line (l_api_name || ': calling Process_Index_Preferences ...');
929
930 Process_Index_Preferences
931 (
932 p_Index_Name => l_Index_Name
933 , x_return_status => l_return_status
934 );
935
936 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
937 RAISE G_EXC_ERROR;
938 END IF;
939
940 Out_Line ('Re-created Text Index preferences.');
941 END IF;
942
943 Log_Line (l_api_name || ': calling Process_Item_Text_Index ...');
944
945 Process_Item_Text_Index
946 (
947 p_Index_Name => l_Index_Name
948 , p_Policy_Name => l_Policy_Name
949 , p_Action => p_Action
950 , x_return_status => l_return_status
951 );
952
953 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
954 RAISE G_EXC_ERROR;
955 END IF;
956
957 -- Assign conc request return code
958
959 IF ( l_return_status = G_STATUS_SUCCESS ) THEN
960 RETCODE := G_RETCODE_SUCCESS;
961 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_SUCCESS');
962 ELSIF ( l_return_status = G_STATUS_WARNING ) THEN
963 RETCODE := G_RETCODE_WARNING;
964 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_WARNING');
965 ELSE
966 RETCODE := G_RETCODE_ERROR;
967 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
968 END IF;
969
970 IF NOT(g_Conc_Req_flag) THEN
971 FND_FILE.Close;
972 END IF;
973
974 Log_Line (l_api_name || ': end');
975
976 EXCEPTION
977
978 WHEN G_EXC_ERROR THEN
979 Log_Line (l_api_name || ': Error.');
980 RETCODE := G_RETCODE_ERROR;
981 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
982 --ERRBUF := 'Build of Catalog Item Text Index failed. Please check error messages for further details.';
983
984 IF NOT(g_Conc_Req_flag) THEN
985 FND_FILE.Close;
986 END IF;
987
988 WHEN others THEN
989 Log_Line (l_api_name || ': Unexpected Error: ' || SQLERRM);
990 RETCODE := G_RETCODE_ERROR;
991 ERRBUF := 'Build of Catalog Item Text Index failed due to unexpected error. Please check error messages for further details.';
992
993 IF NOT(g_Conc_Req_flag) THEN
994 FND_FILE.Close;
995 END IF;
996
997 END Build_Item_Text_Index;
998
999 -- -----------------------------------------------------------------------------
1000 -- Report_Item_Text_Index
1001 -- -----------------------------------------------------------------------------
1002 /*
1003 PROCEDURE Report_Item_Text_Index
1004 (
1005 ERRBUF OUT NOCOPY VARCHAR2
1006 , RETCODE OUT NOCOPY NUMBER
1007 )
1008 IS
1009 l_api_name CONSTANT VARCHAR2(30) := 'Report_Item_Text_Index';
1010 l_index_exists BOOLEAN := FALSE;
1011 l_return_status VARCHAR2(1);
1012 BEGIN
1013
1014 Log_Line (l_api_name || ': begin');
1015
1016 IF ( g_Log_Mode IS NULL ) THEN
1017 set_Log_Mode ('FILE');
1018 END IF;
1019
1020 l_return_status := G_STATUS_SUCCESS;
1021
1022 Out_Line ('Product Catalog Text Index');
1023 Out_Line ('--------------------------');
1024
1025 Log_Line (l_api_name || ': loop through DOMAIN type indexes');
1026
1027 FOR index_rec IN ( SELECT owner, index_name, index_type, status, domidx_opstatus
1028 FROM all_indexes
1029 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
1030 AND table_name = 'EGO_ITEM_TEXT_TL'
1031 AND ( index_type = 'DOMAIN'
1032 OR index_name LIKE 'EGO_ITEM_TEXT_TL_CTX%' )
1033 )
1034 LOOP
1035 l_index_exists := TRUE;
1036
1037 Out_Line ('Index: ' || index_rec.owner || '.' || index_rec.index_name ||
1038 'Type: ' || index_rec.index_type);
1039 Out_Line ('status = ' || index_rec.status || ' opstatus = ' || index_rec.domidx_opstatus);
1040
1041 END LOOP;
1042
1043 IF ( NOT(l_index_exists) ) THEN
1044 Log_Line (l_api_name || ': Warning: no DOMAIN or ''EGO_ITEM_TEXT_TL_CTX%'' indexes exist');
1045 l_return_status := G_STATUS_WARNING;
1046 --RAISE G_EXC_ERROR;
1047 END IF;
1048
1049 -- Assign conc request return code
1050
1051 IF ( l_return_status = G_STATUS_SUCCESS ) THEN
1052 RETCODE := G_RETCODE_SUCCESS;
1053 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_SUCCESS');
1054 ELSIF ( l_return_status = G_STATUS_WARNING ) THEN
1055 RETCODE := G_RETCODE_WARNING;
1056 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_WARNING');
1057 ELSE
1058 RETCODE := G_RETCODE_ERROR;
1059 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1060 END IF;
1061
1062 IF NOT(g_Conc_Req_flag) THEN
1063 FND_FILE.Close;
1064 END IF;
1065
1066 Log_Line (l_api_name || ': end');
1067
1068 EXCEPTION
1069
1070 WHEN G_EXC_ERROR THEN
1071 Log_Line (l_api_name || ': Error.');
1072 RETCODE := G_RETCODE_ERROR;
1073 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1074 --ERRBUF := 'Catalog Item Text Index report failed. Please check error messages for further details.';
1075
1076 IF NOT(g_Conc_Req_flag) THEN
1077 FND_FILE.Close;
1078 END IF;
1079
1080 WHEN others THEN
1081 Log_Line (l_api_name || ': Unexpected Error: ' || SQLERRM);
1082 RETCODE := G_RETCODE_ERROR;
1083 ERRBUF := 'Catalog Item Text Index report failed due to unexpected error. Please check error messages for further details.';
1084
1085 IF NOT(g_Conc_Req_flag) THEN
1086 FND_FILE.Close;
1087 END IF;
1088
1089 END Report_Item_Text_Index;
1090 */
1091
1092 -- -----------------------------------------------------------------------------
1093 -- get_Msg_Text
1094 -- -----------------------------------------------------------------------------
1095
1096 FUNCTION get_Msg_Text
1097 RETURN VARCHAR2
1098 IS
1099 BEGIN
1100 RETURN (g_Msg_Text);
1101 END get_Msg_Text;
1102
1103 -- *****************************************************************************
1104 -- ** Package initialization block **
1105 -- *****************************************************************************
1106
1107 BEGIN
1108
1109 -- Get EGO product schema name
1110 --
1111 g_installed := FND_INSTALLATION.Get_App_Info ('EGO', g_inst_status, g_industry, g_Prod_Schema);
1112
1113 g_Index_Owner := g_Prod_Schema;
1114 g_Pref_Owner := g_Prod_Schema;
1115
1116 END EGO_ITEM_TEXT_PVT;