[Home] [Help]
PACKAGE BODY: APPS.IBC_CONTENT_SYNC_INDEX_PKG
Source
1 PACKAGE BODY IBC_CONTENT_SYNC_INDEX_PKG AS
2 /* $Header: ibcsyinb.pls 120.3 2005/09/01 21:37:03 srrangar noship $ */
3
4 -- errbuf = err messages
5 -- retcode = 0 success, 1 = warning, 2=error
6 -- bmode: S = sync OFAST=optimize fast, OFULL = optimize full
7
8 -- Add global exception handlers.
9 invalid_mode_error EXCEPTION;
10 invalid_action_error EXCEPTION;
11 drop_index_error EXCEPTION;
12 create_index_error EXCEPTION;
13 rebuild_cache_error EXCEPTION;
14
15 -- Declare ibc and apps schema.
16 g_apps_short_name VARCHAR2(10) := UPPER('APPS'); -- set at patching
17 g_ibc_short_name VARCHAR2(10) := UPPER('IBC'); -- set at patching
18
19 -- Internal function: sync_index
20 FUNCTION Sync_index( index1 IN VARCHAR2,
21 bmode IN VARCHAR2,
22 pworker IN NUMBER DEFAULT 0)
23 Return VARCHAR2
24 IS
25 l_index_name VARCHAR2(300) := index1; --g_ibc_short_name||'.'||index1;
26
27 BEGIN
28
29 IF bmode = 'S' THEN
30 ad_ctx_ddl.sync_index( l_index_name );
31 ELSIF bmode = 'OFAST' THEN
32 ad_ctx_ddl.optimize_index( l_index_name, CTX_DDL.OPTLEVEL_FAST, NULL, NULL );
33 ELSIF bmode = 'OFULL' THEN
34 ad_ctx_ddl.OPTIMIZE_INDEX( l_index_name, CTX_DDL.OPTLEVEL_FULL, NULL, NULL );
35 ELSIF bmode = 'R' THEN
36 EXECUTE IMMEDIATE 'alter index ' || l_index_name ||' REBUILD';
37 ELSIF bmode = 'DR' THEN
38 -- logic to drop or create is taken in the individual api.
39 NULL;
40 ELSE
41 FND_FILE.PUT_LINE(FND_FILE.LOG,
42 fnd_message.get_string('ibc', 'IBC_SYNC_INDEX_INV_MODE'));
43 RAISE invalid_mode_error; -- let the exception populate back to the caller.
44 END IF;
45
46 Return 'Y';
47 END Sync_index;
48
49 /*
50 * Internal
51 * is_validate_mode: VALIDATE a synchronization MODE.
52 * RETURN 'Y' IF THE MODE IS valid. Otherwise RETURN 'N'.
53 */
54 FUNCTION is_validate_mode(bmode IN VARCHAR2) RETURN VARCHAR
55 IS
56 l_valid_mode VARCHAR2(1) := 'Y';
57 l_mode VARCHAR2(10) := bmode;
58 BEGIN
59 IF l_mode NOT IN ('S', 'R', 'OFAST', 'OFULL', 'DR' ) THEN
60 l_valid_mode := 'N';
61 END IF;
62 RETURN l_valid_mode;
63 END;
64
65 /*
66 * Internal
67 * get_max_parallel_worker: return the max number of processes
68 * to be used for parallel indexing.
69 */
70 FUNCTION get_max_parallel_worker RETURN NUMBER
71 IS
72 l_worker NUMBER := 0;
73 BEGIN
74 SELECT to_number(nvl(VALUE, 0)) INTO l_worker FROM v$parameter
75 WHERE NAME = 'job_queue_processes';
76
77 RETURN l_worker;
78 EXCEPTION
79 WHEN OTHERS THEN
80 RETURN l_worker;
81 END;
82
83 /*
84 * Internal
85 * resolve_parallel_indexing: Return an indexing command line that
86 * enables parallel indexing.
87 */
88 FUNCTION resolve_parallel_indexing (
89 p_create_cmd IN VARCHAR2,
90 p_worker IN NUMBER DEFAULT 0
91 ) RETURN VARCHAR
92 IS
93 l_cmd VARCHAR2(500) := p_create_cmd;
94 l_worker NUMBER := p_worker;
95 l_max_worker NUMBER := get_max_parallel_worker;
96
97
98 l_db_version NUMBER := null;
99 l_compatibility VARCHAR2(100) := null;
100 l_db_version_str VARCHAR2(100) := null;
101
102 BEGIN
103 If p_worker is null Then
104 l_worker := 0;
105 End If;
106
107 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
108 If l_db_version_str is null Then
109 l_db_version := 8;
110 Else
111 l_db_version := to_number(substr(l_db_version_str, 1,
112 (instr(l_db_version_str, '.'))-1));
113 End If;
114
115 If l_db_version Is Not Null Then
116 If l_db_version > 8 Then
117 IF l_worker > l_max_worker THEN
118 l_worker := l_max_worker;
119 END IF;
120 End if; -- l_db_version eof
121 Else
122 l_worker := 0;
123 End If;
124
125 IF l_worker > 0 THEN
126 l_cmd := l_cmd || ' parallel '||TO_CHAR(l_worker);
127 END IF;
128
129 RETURN l_cmd;
130 EXCEPTION
131 WHEN OTHERS THEN
132 -- any errors: do not append anything.
133 RETURN p_create_cmd;
134 END resolve_parallel_indexing;
135
136 /*
137 * Sync_Text_Index
138 * Synchronize the IBC_CITEM_VERSIONS_TL_CTX1 text index.
139 * Supported mode: S, DR, OFAST, OFAST, R
140 */
141 PROCEDURE Sync_Text_Index (
142 ERRBUF OUT NOCOPY VARCHAR2,
143 RETCODE OUT NOCOPY NUMBER,
144 BMODE IN VARCHAR2,
145 pworker IN NUMBER DEFAULT 0)
146 IS
147
148 cursor c_index_name(l_index_name IN VARCHAR2) is
149 select idx_name from ctxsys.ctx_indexes
150 where idx_name=l_index_name
151 and upper(idx_owner)=UPPER('IBC');
152
153 index3 VARCHAR2(250) := 'IBC_CITEM_VERSIONS_TL_CT1';
154 index1 VARCHAR2(250) := 'IBC_ATTRIBUTE_BUNDLES_CTX';
155 l_mode VARCHAR2(10) := bmode;
156 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
157 l_create_cmmd VARCHAR2(500):= NULL;
158 l_temp VARCHAR2(250);
159
160 BEGIN
161 -- Initialize some variables
162 retcode := 2; -- init return val to FAIL
163
164 OPEN c_index_name(index3);
165 FETCH c_index_name INTO l_temp;
166
167 IF c_index_name%NOTFOUND THEN
168 l_mode := 'DR';
169 END IF ;
170
171 CLOSE c_index_name;
172
173 IF l_mode IS NULL THEN -- default it to 'Sync'
174 l_mode := 'S';
175 END IF;
176
177 IF is_validate_mode(l_mode) = 'N' THEN
178 RAISE invalid_mode_error;
179 END IF;
180
181 -- check whether it is 'DR'
182 IF l_mode = 'DR' THEN
183 Drop_Index(index3,
184 errbuf,
185 l_return_status);
186 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
187 RAISE drop_index_error;
188 END IF;
189
190 -- Create the index
191 l_create_cmmd :=
192 ' CREATE INDEX '||g_ibc_short_name||'.'||index3
193 ||' on '||g_ibc_short_name||'.ibc_citem_versions_tl(textidx) '
194 ||' indextype is ctxsys.context parameters ('''
195 ||' datastore '||g_apps_short_name||'.IBC_CONTENT_INDEX_ELES '
196 ||' section group '||g_apps_short_name||'.IBC_CONTENT_BASIC_GRP '
197 ||' lexer '||g_apps_short_name
198 ||'.IBC_CONTENT_GLOBAL_LEXER language column SOURCE_LANG '
199 ||' storage '||g_apps_short_name||'.IBC_CONTENT_INDEX_STORAGE'') ';
200
201 l_create_cmmd := resolve_parallel_indexing(l_create_cmmd, pworker);
202
203 Begin
204 EXECUTE IMMEDIATE l_create_cmmd;
205 Exception
206 When others then
207 errbuf := 'Sync_Text_Index: '||index3||' :'
208 ||fnd_message.GET_STRING('IBC','IBC_UNEXPECTED_ERROR')||' '||SQLERRM;
209 Raise create_index_error;
210 End;
211 ELSE
212 -- execute sync on IBC_CITEM_VERSIONS_TL_CT1
213 l_return_status := Sync_index(g_ibc_short_name||'.'|| index3,
214 bmode );
215 -- execute sync on IBC_ATTRIBUTE_BUNDLES_CTX
216 l_return_status := Sync_index(g_ibc_short_name||'.'|| index1,
217 bmode );
218
219 END IF;
220
221 -- Return successfully
222 errbuf := fnd_message.get_string('IBC', 'IBC_SUCCESS');
223 retcode := 0;
224 EXCEPTION
225 WHEN invalid_mode_error THEN
226 errbuf := fnd_message.get_string('IBC',
227 'IBC_SYNC_INDEX_INV_MODE');
228 WHEN drop_index_error THEN
229 BEGIN
230 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
231 EXCEPTION
232 WHEN others THEN
233 NULL;
234 END;
235 WHEN create_index_error THEN
236 BEGIN
237 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
238 EXCEPTION
239 WHEN others THEN
240 NULL;
241 END;
242 WHEN others THEN
243 errbuf := 'Sync_Text_Index: '||
244 fnd_message.GET_STRING('IBC','IBC_UNEXPECTED_ERROR')||' '|| SQLERRM;
245 BEGIN
246 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
247 EXCEPTION
248 WHEN others THEN
249 NULL;
250 END;
251 END Sync_Text_Index;
252
253 /* Drop_index
254 * Check whether a text index exists in the IBC schema. If yes, drop it.
255 */
256 PROCEDURE Drop_Index
257 ( p_index_name IN VARCHAR,
258 x_msg_error OUT NOCOPY VARCHAR2,
259 x_return_status OUT NOCOPY VARCHAR2
260 )
261 IS
262 drop_index VARCHAR2(100) := NULL;
263
264 CURSOR get_index_cursor(p_index_name VARCHAR2, p_owner VARCHAR2) IS
265 SELECT COUNT(*) FROM dba_indexes
266 WHERE index_name = UPPER(p_index_name)
267 AND owner= UPPER(p_owner);
268 l_total NUMBER := 0;
269
270 BEGIN
271 x_return_status := fnd_api.G_RET_STS_ERROR;
272 IF p_index_name IS NULL THEN
273 RETURN;
274 END IF;
275
276 -- If and only if the index exists:
277 OPEN get_index_cursor(p_index_name, g_ibc_short_name);
278 FETCH get_index_cursor INTO l_total;
279 CLOSE get_index_cursor;
280
281 IF l_total > 0 THEN
282 drop_index := 'drop index '||g_ibc_short_name||'.'||p_index_name||' force ';
283 EXECUTE IMMEDIATE drop_index;
284 END IF;
285
286 x_return_status := fnd_api.G_RET_STS_SUCCESS;
287 EXCEPTION
288 WHEN others THEN
289 x_msg_error := 'Drop_Index: '||
290 fnd_message.GET_STRING('IBC','IBC_UNEXPECTED_ERROR')||' '|| SQLERRM;
291 END Drop_Index;
292
293 /*
294 * Request_Content_Sync_Index - This procedure submits a concurrent request
295 * to sync Content Text indexes.
296 */
297
298 PROCEDURE Request_Content_Sync_Index
299 ( x_request_id OUT NOCOPY NUMBER,
300 x_return_status OUT NOCOPY VARCHAR2 )
301 IS
302 l_request_id NUMBER;
303 l_sync_idx_progname VARCHAR2(100) := 'IBC_CONTENT_SYNC_TEXT_INDEX';
304 l_sync_mode VARCHAR2(1) := 'S';
305 l_pending_phase_code VARCHAR2(1) := 'P';
306 l_num_pending_requests NUMBER := 0;
307 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
308 begin
309
310 -- Detect how many Pending, but not scheduled Content Sync-Index
311 -- concurrent program requests.
312 select count(*)
313 into l_num_pending_requests
314 from fnd_concurrent_programs cp,
315 fnd_application ap,
316 fnd_concurrent_requests cr
317 where ap.application_short_name = g_ibc_short_name
318 and cp.concurrent_program_name = l_sync_idx_progname
319 and cp.application_id = ap.application_id
320 and cr.concurrent_program_id = cp.concurrent_program_id
321 and cr.phase_code = l_pending_phase_code
322 and cr.requested_start_date <= sysdate;
323
324 --
325 -- If there are no unscheduled pending Content Sync-Index concurrent
326 -- requests, then submit one. Otherwise, if there is already
327 -- an unscheduled pending request, which will anyway run
328 -- there is no need to submit another request.
329 --
330
331 if( l_num_pending_requests = 0 )
332 then
333 l_request_id :=
334 fnd_request.submit_request
335 ( application => g_ibc_short_name,
336 program => l_sync_idx_progname,
337 description => null,
338 start_time => null,
339 sub_request => FALSE,
340 argument1 => l_sync_mode,
341 argument2 => 0);
342
343 if( l_request_id > 0 )
344 then
345 l_return_status := fnd_api.G_RET_STS_SUCCESS;
346 end if;
347 else
348 -- There is already a pending request, so just return success
349 l_request_id := 0;
350 l_return_status := fnd_api.G_RET_STS_SUCCESS;
351 end if;
352
353 x_request_id := l_request_id;
354 x_return_status := l_return_status;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 x_request_id := 0;
359 x_return_status := fnd_api.G_RET_STS_ERROR;
360 END Request_Content_Sync_Index;
361
362 END IBC_CONTENT_SYNC_INDEX_PKG;