[Home] [Help]
PACKAGE BODY: APPS.OKC_CHR_KEYWORD_PVT
Source
1 PACKAGE BODY okc_chr_keyword_pvt AS
2 /* $Header: OKCRCKWB.pls 120.1.12010000.2 2008/10/24 08:01:55 ssreekum ship $ */
3
4 retcode_success CONSTANT VARCHAR2(1) := '0';
5 retcode_warning CONSTANT VARCHAR2(1) := '1';
6 retcode_error CONSTANT VARCHAR2(1) := '2';
7
8 starting constant number := 0;
9 finish constant number := 1;
10
11 rindex BINARY_INTEGER;
12 slno BINARY_INTEGER;
13
14 PROCEDURE sync IS
15 BEGIN
16 ad_ctx_ddl.set_effective_schema('okc');
17 ad_ctx_ddl.sync_index('okc_k_headers_tl_ctx');
18 EXCEPTION
19 WHEN OTHERS THEN
20 RAISE;
21 END;
22
23 PROCEDURE optimize IS
24 BEGIN
25 ad_ctx_ddl.set_effective_schema('okc');
26 ad_ctx_ddl.optimize_index
27 ( idx_name => 'okc_k_headers_tl_ctx',
28 optlevel => ad_ctx_ddl.optlevel_full,
29 maxtime => ad_ctx_ddl.maxtime_unlimited);
30 EXCEPTION
31 WHEN OTHERS THEN
32 RAISE;
33 END;
34
35 PROCEDURE sync_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
36 l_api_name CONSTANT VARCHAR2(30) := 'concurrent_sync_ctx';
37 l_api_version CONSTANT VARCHAR2(30) := 1.0;
38 BEGIN
39 sync;
40 retcode := retcode_success;
41 EXCEPTION
42 WHEN OTHERS THEN
43 retcode := retcode_error;
44 errbuf := SUBSTR(sqlerrm,1,200);
45 END;
46
47 PROCEDURE optimize_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
48 l_api_name CONSTANT VARCHAR2(30) := 'concurrent_optimize_ctx';
49 l_api_version CONSTANT VARCHAR2(30) := 1.0;
50 BEGIN
51 sync_ctx(errbuf, retcode);
52 IF retcode <> retcode_success THEN
53 RETURN;
54 END IF;
55 optimize;
56 retcode := retcode_success;
57 EXCEPTION
58 WHEN OTHERS THEN
59 retcode := retcode_error;
60 errbuf := SUBSTR(sqlerrm,1,200);
61 END;
62
63 FUNCTION get_request_id(prog_name varchar2)
64 RETURN fnd_concurrent_requests.request_id%TYPE
65 IS
66 result fnd_concurrent_requests.request_id%TYPE;
67 BEGIN
68 SELECT request_id INTO result
69 FROM fnd_concurrent_requests
70 WHERE concurrent_program_id IN (SELECT concurrent_program_id
71 FROM fnd_concurrent_programs
72 WHERE concurrent_program_name = prog_name)
73 AND phase_code = 'R';
74 RETURN result;
75 EXCEPTION WHEN OTHERS THEN
76 return 0;
77 END;
78
79 PROCEDURE create_ctx(x_return_status OUT NOCOPY VARCHAR2) IS
80 apps sys.dba_objects.owner%TYPE;
81 okc sys.dba_objects.owner%TYPE;
82 cmd VARCHAR2(4000);
83
84 anyexp EXCEPTION;
85
86 request_id fnd_concurrent_requests.request_id%TYPE;
87 BEGIN
88
89 x_return_status := 'S';
90
91 request_id := get_request_id('OKCCHRCRCTX');
92 fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL_CTX text index creation started');
93
94 BEGIN
95 fnd_file.put_line(fnd_file.log,'Checking OKC_CHR_KEYWORD_PVT package exists or not...');
96 SELECT owner INTO apps
97 FROM sys.dba_objects
98 WHERE object_name = 'OKC_CHR_KEYWORD_PVT'
99 AND object_type = 'PACKAGE';
100 fnd_file.put_line(fnd_file.log,'OKC_CHR_KEYWORD_PVT package exists');
101 fnd_file.put_line(fnd_file.log,'OKC_CHR_KEYWORD_PVT package owner is '||apps);
102
103 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 1, 13);
104 EXCEPTION WHEN OTHERS THEN
105 fnd_file.put_line(fnd_file.log,'ERROR: OKC_CHR__KEYWORD_PVT package doesn''t exist');
106 RAISE anyexp;
107 END;
108
109 BEGIN
110 fnd_file.put_line(fnd_file.log,'Checking OKC_K_HEADERS_TL table ...');
111 SELECT owner INTO okc
112 FROM sys.dba_tables
113 WHERE table_name = 'OKC_K_HEADERS_TL';
114 fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL table exists');
115 fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL table owner is '||okc);
116
117 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 2, 13);
118
119 EXCEPTION WHEN OTHERS THEN
120 fnd_file.put_line(fnd_file.log,'ERROR: OKC_K_HEADERS_TL table doesn''t exist');
121 RAISE anyexp;
122 END;
123
124 BEGIN
125 EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||apps||'.OKC_CHR_KEYWORD_PVT TO '||okc;
126 fnd_file.put_line(fnd_file.log,'Granted execute on '||apps||'.OKC_CHR_KEYWORD_PVT to '||okc);
127
128 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 3, 13);
129
130 EXCEPTION WHEN OTHERS THEN
131 fnd_file.put_line(fnd_file.log,'ERROR: Couldn''t grant execute on '||apps||'.OKC_CHR_KEYWORD_PVT to '||okc);
132 RAISE anyexp;
133 END;
134
135
136 -- Create context index preferences
137 -- Context index searches through columns
138 -- cognomen
139 -- short_description
140 -- description
141 -- Create datastore okc_k_headers_tl_datastore
142
143 BEGIN
144 BEGIN
145 fnd_file.put_line(fnd_file.log,'Before drop preference');
146 ad_ctx_ddl.drop_preference('okc_k_headers_tl_datastore');
147 fnd_file.put_line(fnd_file.log,'Preference dropped successfully');
148 EXCEPTION
149 WHEN OTHERS THEN
150 NULL;
151 END;
152 fnd_file.put_line(fnd_file.log,'Before create preference');
153 ad_ctx_ddl.create_preference('okc_k_headers_tl_datastore','multi_column_datastore');
154 ad_ctx_ddl.set_attribute('okc_k_headers_tl_datastore','columns',
155 'cognomen, short_description, description');
156 fnd_file.put_line(fnd_file.log,'Preference created successfully');
157 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 4, 13);
158 END;
159
160 -- Creating all Lexers and sublexers
161 BEGIN
162 BEGIN
163 fnd_file.put_line(fnd_file.log,'Dropping multi lexer');
164 ad_ctx_ddl.drop_preference('okc_k_headers_tl_lexer');
165 fnd_file.put_line(fnd_file.log,'Multi lexer successfully');
166 EXCEPTION
167 WHEN OTHERS THEN
168 NULL;
169 END;
170 fnd_file.put_line(fnd_file.log,'Creating multi lexer');
171 ad_ctx_ddl.create_preference('okc_k_headers_tl_lexer','multi_lexer');
172 BEGIN
173 fnd_file.put_line(fnd_file.log,'Dropping basic lexer');
174 ad_ctx_ddl.drop_preference('okc_chrtl_blexer');
175 fnd_file.put_line(fnd_file.log,'Basic lexer dropped successfully');
176 EXCEPTION
177 WHEN OTHERS THEN
178 NULL;
179 END;
180 fnd_file.put_line(fnd_file.log,'Creating basic lexer with its attributes');
181 ad_ctx_ddl.create_preference('okc_chrtl_blexer','basic_lexer');
182 ad_ctx_ddl.set_attribute('okc_chrtl_blexer','index_themes','false');
183 ad_ctx_ddl.set_attribute('okc_chrtl_blexer','index_text','true');
184 ad_ctx_ddl.set_attribute('okc_chrtl_blexer','base_letter','true');
185 ad_ctx_ddl.set_attribute('okc_chrtl_blexer','mixed_case','false');
186 fnd_file.put_line(fnd_file.log,'Basic lexer with its attributes created successfully');
187
188 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 5, 13);
189
190 BEGIN
191 fnd_file.put_line(fnd_file.log,'Dropping Chinese lexer');
192 ad_ctx_ddl.drop_preference('okc_chrtl_clexer');
193 fnd_file.put_line(fnd_file.log,'Chinese lexer dropped successfully');
194 EXCEPTION
195 WHEN OTHERS THEN
196 NULL;
197 END;
198
199 DECLARE
200 yes_no VARCHAR2(1);
201 BEGIN
202 BEGIN
203 SELECT 'Y' INTO yes_no
204 FROM ctxsys.ctx_objects
205 WHERE obj_class = 'LEXER'
206 AND obj_name = 'CHINESE_LEXER';
207 EXCEPTION
208 WHEN OTHERS THEN yes_no := 'N';
209 END;
210 fnd_file.put_line(fnd_file.log,'Chinese lexer: yes_no: ' || yes_no);
211 IF yes_no = 'Y' THEN
212 ad_ctx_ddl.create_preference('okc_chrtl_clexer','chinese_lexer');
213 ELSE
214 ad_ctx_ddl.create_preference('okc_chrtl_clexer','chinese_vgram_lexer');
215 END IF;
216 fnd_file.put_line(fnd_file.log,'Chinese lexer created successfully');
217 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 6, 13);
218
219 END;
220
221 BEGIN
222 fnd_file.put_line(fnd_file.log,'Dropping Japanese lexer');
223 ad_ctx_ddl.drop_preference('okc_chrtl_jlexer');
224 fnd_file.put_line(fnd_file.log,'Japanese lexer dropped successfully');
225 EXCEPTION
226 WHEN OTHERS THEN
227 NULL;
228 END;
229
230 DECLARE
231 yes_no VARCHAR2(1);
232 BEGIN
233 BEGIN
234 SELECT 'Y' INTO yes_no
235 FROM ctxsys.ctx_objects
236 WHERE obj_class = 'LEXER'
237 AND obj_name = 'JAPANESE_LEXER';
238 EXCEPTION
239 WHEN OTHERS THEN
240 yes_no := 'N';
241 END;
242 fnd_file.put_line(fnd_file.log,'Japanese lexer: yes_no: ' || yes_no);
243 IF yes_no = 'Y' THEN
244 ad_ctx_ddl.create_preference('okc_chrtl_jlexer','japanese_lexer');
245 ELSE
246 ad_ctx_ddl.create_preference('okc_chrtl_jlexer','japanese_vgram_lexer');
247 END IF;
248 fnd_file.put_line(fnd_file.log,'Japanese lexer created successfully');
249 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 7, 13);
250 END;
251
252 BEGIN
253 fnd_file.put_line(fnd_file.log,'Dropping korean lexer');
254 ad_ctx_ddl.drop_preference('okc_chrtl_klexer');
255 fnd_file.put_line(fnd_file.log,'Korean lexer dropped successfully');
256 EXCEPTION
257 WHEN OTHERS THEN
258 NULL;
259 END;
260
261 DECLARE
262 yes_no varchar2(1);
263 BEGIN
264 BEGIN
265 SELECT 'Y' INTO yes_no
266 FROM ctxsys.ctx_objects
267 WHERE obj_class = 'LEXER'
268 AND obj_name = 'KOREAN_MORPH_LEXER';
269 EXCEPTION
270 WHEN OTHERS THEN
271 yes_no := 'N';
272 END;
273 fnd_file.put_line(fnd_file.log,'Korean lexer: yes_no: ' || yes_no);
274 IF yes_no = 'Y' THEN
275 ad_ctx_ddl.create_preference('okc_chrtl_klexer', 'korean_morph_lexer');
276 ad_ctx_ddl.set_attribute('okc_chrtl_klexer', 'one_char_word', 'true');
277 ad_ctx_ddl.set_attribute('okc_chrtl_klexer', 'number', 'true');
278 ELSE
279 ad_ctx_ddl.create_preference('okc_chrtl_klexer', 'korean_lexer');
280 END IF;
281
282 fnd_file.put_line(fnd_file.log,'Korean lexer created successfully');
283 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 8, 13);
284
285 END;
286
287 fnd_file.put_line(fnd_file.log,'Before Add Sub Lexer');
288 ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','default','okc_chrtl_blexer');
289 ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','ja','okc_chrtl_jlexer');
290 ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','ko','okc_chrtl_klexer');
291 ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','zhs','okc_chrtl_clexer');
292 ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','zht','okc_chrtl_clexer');
293
294 fnd_file.put_line(fnd_file.log,'Sub Lexer Added Successfully');
295 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 9, 13);
296
297 END;
298
299 --Create wordlist okc_k_headers_tl_wordlist
300 BEGIN
301 BEGIN
302 fnd_file.put_line(fnd_file.log,'Dropping WordList');
303 ad_ctx_ddl.drop_preference('okc_k_headers_tl_wordlist');
304 fnd_file.put_line(fnd_file.log,'WordList dropped successfully');
305 EXCEPTION
306 WHEN OTHERS THEN
307 NULL;
308 END;
309 fnd_file.put_line(fnd_file.log,'Creating WordList...');
310 ad_ctx_ddl.create_preference('okc_k_headers_tl_wordlist','basic_wordlist');
311 ad_ctx_ddl.set_attribute('okc_k_headers_tl_wordlist','stemmer','auto');
312 ad_ctx_ddl.set_attribute('okc_k_headers_tl_wordlist','fuzzy_match','auto');
313 fnd_file.put_line(fnd_file.log,'WordList created succesfully');
314 END;
315
316 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 10, 13);
317
318 -- Drop context index okc_k_headers_tl_ctx (if exists)
319 BEGIN
320 fnd_file.put_line(fnd_file.log,'Dropping INDEX okc_k_headers_tl_ctx...');
321 EXECUTE IMMEDIATE 'DROP INDEX ' || okc ||'.okc_k_headers_tl_ctx FORCE';
322 fnd_file.put_line(fnd_file.log,'INDEX okc_k_headers_tl_ctx succesfully dropped');
323 EXCEPTION
324 WHEN OTHERS THEN
325 NULL;
326 END;
327
328 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 11, 13);
329
330 -- Create context index okc_k_headers_tl_ctx
331 DECLARE
332 l_max_memory NUMBER;
333 l_cpu_count NUMBER;
334 BEGIN
335 fnd_file.put_line(fnd_file.log,'Index Creation Logic Begins');
336
337 BEGIN
338 SELECT par_value INTO l_max_memory
339 FROM ctx_parameters
340 WHERE par_name = 'MAX_INDEX_MEMORY';
341 EXCEPTION WHEN OTHERS THEN
342 NULL;
343 END;
344
345 fnd_file.put_line(fnd_file.log,'Max Memory: '||l_max_memory);
346
347 BEGIN
348 SELECT value INTO l_cpu_count
349 FROM v$parameter
350 WHERE name = 'cpu_count';
351 EXCEPTION WHEN OTHERS THEN
352 NULL;
353 END;
354
355 fnd_file.put_line(fnd_file.log,'CPU Count: '||l_cpu_count);
356 fnd_file.put_line(fnd_file.log,'Before Index Creation');
357
358 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 12, 13);
359
360 -- Changes for Bug# 6921424
361 -- l_max_memory and l_cpu_count should be an integer in order to
362 -- pass them as parameters to the create index statement
363 l_max_memory := round(l_max_memory/2);
364 l_cpu_count := round(l_cpu_count/2);
365 -- Changes For Bug# 6921424 Ends
366
367 EXECUTE IMMEDIATE
368 'CREATE INDEX ' || okc || '.okc_k_headers_tl_ctx ON okc_k_headers_tl(short_description)
369 INDEXTYPE IS CTXSYS.CONTEXT
370 PARAMETERS (''
371 DATASTORE ' || apps || '.okc_k_headers_tl_datastore
372 FILTER ctxsys.null_filter
373 LEXER ' || apps || '.okc_k_headers_tl_lexer language column source_lang
374 SECTION GROUP ctxsys.null_section_group
375 MEMORY ' || l_max_memory || -- For Bug# 6921424
376 ' STOPLIST ctxsys.default_stoplist
377 WORDLIST ' || apps || '.okc_k_headers_tl_wordlist'') PARALLEL ' || l_cpu_count; -- For Bug# 6921424
378
379 fnd_file.put_line(fnd_file.log,'Index OKC_K_HEADERS_TL_CTX created successfully');
380 dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 13, 13);
381
382 EXCEPTION
383 WHEN ANYEXP THEN
384 x_return_status := 'E';
385 WHEN OTHERS THEN
386 BEGIN
387 EXECUTE IMMEDIATE 'DROP INDEX ' || okc || '.okc_k_headers_tl_ctx FORCE';
388 EXCEPTION
389 WHEN OTHERS THEN
390 NULL;
391 END;
392 END;
393 END create_ctx;
394
395 PROCEDURE create_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) AS
396 l_api_name CONSTANT VARCHAR2(30) := 'concurrent_create_ctx';
397 l_api_version CONSTANT VARCHAR2(30) := 1.0;
398 request_id fnd_concurrent_requests.request_id%type;
399 l_return_status VARCHAR2(3) := 'S';
400 stop_proc EXCEPTION;
401 BEGIN
402 rindex := dbms_application_info.set_session_longops_nohint;
403 request_id := get_request_id('OKCCHRCRCTX');
404 dbms_application_info.set_session_longops
405 (rindex, slno, 'Create Contract Header Text Index', request_id, request_id, 0, 13,
406 'OKCCHRCRCTX concurrent program', 'steps');
407
408 create_ctx(x_return_status => l_return_status);
409 IF l_return_status <> 'S'
410 THEN
411 RAISE stop_proc;
412 END IF;
413
414 errbuf := 'OKC_K_HEADERS_TL_CTX text index has been created successfully';
415 retcode := retcode_success;
416 EXCEPTION
417 WHEN stop_proc THEN
418 retcode := retcode_error;
419 errbuf := 'ERROR: Couldn''t create OKC_K_HEADERS_TL_CTX text index';
420 WHEN OTHERS THEN
421 retcode := retcode_error;
422 errbuf := 'ERROR: Couldn''t create OKC_K_HEADERS_TL_CTX text index';
423 END create_ctx;
424
425 END;