[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_RAC_UTILS
Source
1 package body FND_CONC_RAC_UTILS as
2 /* $Header: AFCPRACB.pls 120.2 2008/01/11 17:40:02 pferguso noship $ */
3 --
4 -- Package
5 -- FND_CONC_RAC_UTILS
6 -- Purpose
7 -- Utilities for RAC
8 -- History
9 --
10 -- PRIVATE VARIABLES
11 --
12
13 -- Exceptions
14
15 -- Exception Pragmas
16
17 --
18 -- PRIVATE FUNCTIONS
19 --
20
21 --
22 -- Name
23 -- kill_session
24 -- Purpose
25 -- Kills a session given a session id (sid) and serial#
26 --
27 -- Parameters:
28 -- p_sid - ID of session to kill.
29 -- p_serial - Instance ID of session.
30 --
31 --
32 procedure kill_session (p_sid in number,
33 p_serial# in number) is
34 l_sql varchar2(75); /* Cursor string for dbms_sql */
35 l_inst number;
36 l_respid number;
37 l_appid number;
38 l_userid number;
39 dummy number;
40 l_hndl varchar2(4000);
41 l_result number;
42 l_alive number;
43
44 begin
45
46 /* Call to FND_GLOBAL.APPS_INITIALIZE, so we can log messages */
47 SELECT user_id
48 into l_userid
49 from fnd_user
50 where user_name = 'CONCURRENT MANAGER';
51
52 SELECT responsibility_id
53 into l_respid
54 from fnd_responsibility
55 where responsibility_key = 'SYSTEM_ADMINISTRATOR';
56
57 SELECT application_id
58 into l_appid
59 from fnd_application
60 where application_short_name = 'SYSADMIN';
61
62 FND_GLOBAL.APPS_INITIALIZE(l_userid,l_respid,l_appid);
63
64 select instance_number
65 into l_inst
66 from v$instance;
67
68 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
69 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
70 'fnd.plsql.FND_CONC_RAC_UTILS.kill_session',
71 'Session ID=' ||to_char(p_sid)||', serial#=' ||to_char(p_serial#)||', instance='||to_char(l_inst));
72 end if;
73
74 l_sql := 'alter system kill session '''|| to_char(p_sid) || ',' ||
75 to_char(p_serial#)||'''';
76 begin
77 execute immediate l_sql;
78
79 exception
80 when others then
81 if SQLCODE = -30 then
82 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
83 fnd_log.string(FND_LOG.LEVEL_ERROR,
84 'fnd.plsql.FND_CONC_RAC_UTILS.kill_session',
85 'Session ID ' ||to_char(p_sid)||', serial# '||to_char(p_serial#)||' not found: '||' in instance '||to_char(l_inst));
86 end if;
87 raise;
88 else
89 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
90 fnd_log.string(FND_LOG.LEVEL_ERROR,
91 'fnd.plsql.FND_CONC_RAC_UTILS.kill_session',
92 'Unexpected error executing kill for session ID ' ||to_char(p_sid)||', serial# '||to_char(p_serial#)||' in instance '||to_char(l_inst));
93 end if;
94 raise;
95 end if;
96 end;
97
98 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
99 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
100 'fnd.plsql.FND_CONC_RAC_UTILS.kill_session',
101 'Alter system kill session executed');
102 end if;
103
104 exception
105 when others then
106 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
107 fnd_message.set_token('ROUTINE', 'FND_CONC_RAC_UTILS.KILL_SESSION');
108 fnd_message.set_token('ERRNO', SQLCODE);
109 fnd_message.set_token('REASON', SQLERRM);
110 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
111 fnd_log.string(FND_LOG.LEVEL_ERROR,
112 'fnd.plsql.FND_CONC_RAC_UTILS.kill_session',
113 'Oracle Error kill_session: '||SQLCODE||': '||SQLERRM);
114 end if;
115 end;
116
117 --
118 -- Name
119 -- submit_kill_session
120 -- Purpose
121 -- Calls dbms_scheduler to submit a job to kill a session
122 -- in a specific instance
123 -- CAUTION: This procedure does a COMMIT
124 -- (Now uses an autonomous_transaction)
125 --
126 -- Parameters:
127 -- p_jobno - Job number of the dbms_job
128 -- p_message - Oracle error message, allow 4000 characters
129 -- p_sid - Session ID of session to kill
130 -- p_serial# - Serial# of session to kill
131 -- p_inst - Instance ID where dbms_job should run
132 --
133 -- Returns:
134 -- 0 - Oracle error, message available
135 -- 1 - Could not submit job in given instance, message available
136 -- 2 - Success
137 --
138 function submit_kill_session (
139 p_jobno in out NOCOPY number,
140 p_message in out NOCOPY varchar2,
141 p_sid in number,
142 p_serial# in number,
143 p_inst in number default 1) return number is
144
145 l_inst number := 1;
146 l_retcode number := 2;
147
148 pragma autonomous_transaction;
149
150 begin
151 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
152 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
153 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
154 'Session ID ' ||to_char(p_sid)||', serial# ' ||to_char(p_serial#)||', instance '||to_char(p_inst));
155 end if;
156
157 select instance_number
158 into l_inst
159 from v$instance;
160
161 begin
162 if (l_inst = p_inst) then
163 p_jobno := 0;
164 kill_session(p_sid, p_serial#);
165 else
166 DBMS_JOB.SUBMIT(
167 job => p_jobno,
168 what => 'FND_CONC_RAC_UTILS.kill_session('''||to_char(p_sid)||''', '''||to_char(p_serial#)||''');',
169 instance => p_inst);
170 end if;
171
172 exception
173 when others then
174 if SQLCODE = -23428 then
175 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
176 fnd_log.string(FND_LOG.LEVEL_ERROR,
177 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
178 'Cannot submit dbms_job. Instance '||to_char(p_inst)||' not available.');
179 end if;
180 l_retcode := 1;
181 raise;
182 else
183 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
184 if l_inst <> p_inst then
185 fnd_log.string(FND_LOG.LEVEL_ERROR,
186 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
187 'Unexpected error submitting dbms_job to kill session ' ||to_char(p_sid)||', serial# '||to_char(p_serial#)||' in instance '||to_char(p_inst));
188 else
189 fnd_log.string(FND_LOG.LEVEL_ERROR,
190 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
191 'Unexpected error: '||fnd_message.get||' calling kill_session for session ' ||to_char(p_sid)||', serial# '||to_char(p_serial#)||' in instance '||to_char(p_inst));
192 end if;
193
194 end if;
195 l_retcode := 0;
196 raise;
197 end if;
198 end;
199
200 COMMIT;
201
202 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
203 if l_inst <> p_inst then
204 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
205 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
206 'Job '||to_char(p_jobno)||' submitted to kill session ID ' ||to_char(p_sid)||', serial# ' ||to_char(p_serial#)||', instance '||to_char(p_inst));
207 else
208 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
209 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
210 'Called kill_session '||'for session ID ' ||to_char(p_sid)||', serial# ' ||to_char(p_serial#)||', in current instance '||to_char(p_inst));
211 end if;
212 end if;
213
214 return l_retcode;
215
216 exception
217 when others then
218 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
219 fnd_message.set_token('ROUTINE', 'FND_CONC_RAC_UTILS.SUBMIT_KILL_SESSION');
220 fnd_message.set_token('ERRNO', SQLCODE);
221 fnd_message.set_token('REASON', SQLERRM);
222 p_message := fnd_message.get;
223 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
224 fnd_log.string(FND_LOG.LEVEL_ERROR,
225 'fnd.plsql.FND_CONC_RAC_UTILS.submit_kill_session',
226 'Exception in submit_kill_session: '||p_message);
227 end if;
228 return l_retcode;
229 end;
230
231 --
232 -- Name
233 -- submit_manager_kill_session
234 -- Purpose
235 -- Calls submit_kill_session given the concurrent_process_id of a manager
236 --
237 -- Parameters:
238 -- p_cpid - concurrent_process_id of manager to kill
239 -- p_jobno - job number of the dbms_job
240 -- p_message - message buffer for error, allow 4000 characters
241 --
242 -- Returns:
243 -- 0 - Oracle error. Check message
244 -- 1 - Session not found
245 -- 2 - Success
246 --
247 function submit_manager_kill_session (p_cpid in number,
248 p_jobno in out NOCOPY number,
249 p_message in out NOCOPY varchar2)
250 return number is
251
252 l_audsid number;
253 l_inst number;
254 l_sid number;
255 l_serial# number;
256 l_retcode number := 2;
257
258 begin
259
260 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
261 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
262 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
263 'Concurrent process ' ||to_char(p_cpid));
264 end if;
265
266 begin
267 select Session_Id, Instance_Number
268 into l_audsid, l_inst
269 from FND_CONCURRENT_PROCESSES
270 where CONCURRENT_PROCESS_ID = p_cpid;
271
272 exception
273 when no_data_found then
274 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
275 fnd_log.string(FND_LOG.LEVEL_ERROR,
276 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
277 'Concurrent process ' ||to_char(p_cpid)||' not found');
278 end if;
279 l_retcode := 1;
280 raise;
281 when others then
282 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
283 fnd_log.string(FND_LOG.LEVEL_ERROR,
284 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
285 'Unexpected Error querying concurrent process ' ||to_char(p_cpid));
286 end if;
287 l_retcode := 0;
288 raise;
289 end;
290
291 begin
292 select sid, serial#
293 into l_sid, l_serial#
294 from gv$session
295 where audsid = l_audsid
296 and inst_id = l_inst;
297 exception
298 when no_data_found then
299 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
300 fnd_log.string(FND_LOG.LEVEL_ERROR,
301 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
302 'Session (audsid) ' ||to_char(l_audsid)||' in instance '||to_char(l_inst)||' not found for concurrent process '||to_char(p_cpid));
303
304 end if;
305 l_retcode := 1;
306 raise;
307 when others then
308 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
309 fnd_log.string(FND_LOG.LEVEL_ERROR,
310 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
311 'Unexpected Error querying audsid '||to_char(l_audsid)||' in instance '||to_char(l_inst)||'for concurrent process '||to_char(p_cpid));
312 end if;
313 l_retcode := 0;
314 raise;
315 end;
316
317 l_retcode := submit_kill_session(p_jobno, p_message, l_sid, l_serial#, l_inst );
318
319 if (l_retcode <> 2) then
320 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
321 fnd_log.string(FND_LOG.LEVEL_ERROR,
322 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
323 'Submit_kill_session retcode: '||to_char(l_retcode)||', message: '||p_message);
324 end if;
325 end if;
326
327 return l_retcode;
328
329 exception
330 when others then
331 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
332 fnd_message.set_token('ROUTINE',
333 'FND_CONC_RAC_UTILS.SUBMIT_MANAGER_KILL_SESSION');
334 fnd_message.set_token('ERRNO', SQLCODE);
335 fnd_message.set_token('REASON', SQLERRM);
336 p_message := fnd_message.get;
337 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
338 fnd_log.string(FND_LOG.LEVEL_ERROR,
339 'fnd.plsql.FND_CONC_RAC_UTILS.submit_manager_kill_session',
340 'Exception in submit_manager_kill_session: '||p_message);
341 end if;
342 return l_retcode;
343 end;
344
345 --
346 -- Name
347 -- submit_req_mgr_kill_session
348 -- Purpose
349 -- Kills a manager session based on the request it is running
350 --
351 -- Parameters:
352 -- p_reqid - request_id for which manager session must be killed
353 -- p_jobno - job number of the dbms_job
354 -- p_message - message buffer for error, allow 4000 characters
355 --
356 -- Returns:
357 -- 0 - Oracle error. Check message
358 -- 1 - Request/Session not found
359 -- 2 - Success
360 --
361 function submit_req_mgr_kill_session (p_reqid in number,
362 p_jobno in out NOCOPY number,
363 p_message in out NOCOPY varchar2)
364 return number is
365
366 l_retcode number := 2;
370
367 l_cpid number := 2;
368
369 begin
371 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
372 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
373 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
374 'Concurrent request ' ||to_char(p_reqid));
375 end if;
376
377 begin
378 select controlling_manager
379 into l_cpid
380 from FND_CONCURRENT_REQUESTS
381 where request_id = p_reqid;
382
383 exception
384 when no_data_found then
385 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
386 fnd_log.string(FND_LOG.LEVEL_ERROR,
387 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
388 'Concurrent request ' ||to_char(p_reqid)||' not found');
389 end if;
390 l_retcode := 1;
391 raise;
392 when others then
393 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
394 fnd_log.string(FND_LOG.LEVEL_ERROR,
395 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
396 'Unexpected Error querying concurrent request '
397 ||to_char(p_reqid));
398 end if;
399 l_retcode := 0;
400 raise;
401 end;
402
403 l_retcode := submit_manager_kill_session(l_cpid, p_jobno, p_message);
404
405 if (l_retcode = 2) then
406 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
407 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
408 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
409 'Job '||to_char(p_jobno)||' to kill manager session of request '||to_char(p_reqid)||' submitted.');
410 end if;
411 else
412 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
413 fnd_log.string(FND_LOG.LEVEL_ERROR,
414 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
415 'Could not submit job to kill manager session of request '||to_char(p_reqid));
416 end if;
417 end if;
418
419 return l_retcode;
420
421 exception
422 when others then
423 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
424 fnd_message.set_token('ROUTINE',
425 'FND_CONC_RAC_UTILS.SUBMIT_REQ_MGR_KILL_SESSION');
426 fnd_message.set_token('ERRNO', SQLCODE);
427 fnd_message.set_token('REASON', SQLERRM);
428 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
429 fnd_log.message(FND_LOG.LEVEL_ERROR,
430 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_mgr_kill_session',
431 TRUE);
432 end if;
433 return l_retcode;
434 end;
435
436
437
438
439 --
440 -- Name
441 -- submit_req_kill_session
442 -- Purpose
443 -- Calls submit_kill_session given the request_id of a concurrent request
444 --
445 -- Parameters:
446 -- p_reqid - request_id for which session must be killed
447 -- p_jobno - job number of the dbms_job
448 -- p_message - message buffer for error, allow 4000 characters
449 --
450 -- Returns:
451 -- 0 - Oracle error. Check message
452 -- 1 - Request/Session not found
453 -- 2 - Success
454 --
455 function submit_req_kill_session (p_reqid in number,
456 p_jobno in out NOCOPY number,
457 p_message in out NOCOPY varchar2)
458 return number is
459
460 l_audsid number;
461 l_inst number;
462 l_sid number;
463 l_serial# number;
464 l_retcode number := 2;
465
466 begin
467
468 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
469 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
470 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
471 'Concurrent request ' ||to_char(p_reqid));
472 end if;
473
474 begin
475 select oracle_session_id
476 into l_audsid
477 from FND_CONCURRENT_REQUESTS
478 where request_id = p_reqid;
479
480 if l_audsid is null then
481 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
482 fnd_log.string(FND_LOG.LEVEL_ERROR,
483 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
484 'Cannot find audsid for request ' ||to_char(p_reqid));
485 end if;
486 return 1;
487 end if;
488
489 exception
490 when no_data_found then
491 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
492 fnd_log.string(FND_LOG.LEVEL_ERROR,
493 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
494 'Concurrent request ' ||to_char(p_reqid)||' not found');
495 end if;
496 l_retcode := 1;
497 raise;
498 when others then
499 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
500 fnd_log.string(FND_LOG.LEVEL_ERROR,
501 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
502 'Unexpected Error querying concurrent request ' ||to_char(p_reqid));
503 end if;
504 l_retcode := 0;
505 raise;
506 end;
507
508
509 begin
510 select inst_id, sid, serial#
511 into l_inst, l_sid, l_serial#
512 from gv$session
513 where audsid = l_audsid;
514
515 exception
516 when no_data_found then
517 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
521
518 fnd_log.string(FND_LOG.LEVEL_ERROR,
519 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
520 'Session (audsid) ' ||to_char(l_audsid)|| ' not found for concurrent request '||to_char(p_reqid));
522 end if;
523 -- If the session does not exist, our work here is done...
524 return 2;
525
526 when others then
527 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
528 fnd_log.string(FND_LOG.LEVEL_ERROR,
529 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
530 'Unexpected Error querying audsid '||to_char(l_audsid)|| 'for concurrent request '||to_char(p_reqid));
531 end if;
532 l_retcode := 0;
533 raise;
534 end;
535
536
537
538 l_retcode := submit_kill_session(p_jobno, p_message, l_sid, l_serial#, l_inst );
539
540 if (l_retcode <> 2) then
541 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
542 fnd_log.string(FND_LOG.LEVEL_ERROR,
543 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
544 'Submit_kill_session retcode: '||to_char(l_retcode)||', message: '||p_message);
545 end if;
546 end if;
547
548 return l_retcode;
549
550 exception
551 when others then
552 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
553 fnd_message.set_token('ROUTINE',
554 'FND_CONC_RAC_UTILS.SUBMIT_REQ_KILL_SESSION');
555 fnd_message.set_token('ERRNO', SQLCODE);
556 fnd_message.set_token('REASON', SQLERRM);
557 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
558 fnd_log.message(FND_LOG.LEVEL_ERROR,
559 'fnd.plsql.FND_CONC_RAC_UTILS.submit_req_kill_session',
560 TRUE);
561 end if;
562 return l_retcode;
563
564 end;
565
566 end FND_CONC_RAC_UTILS;