[Home] [Help]
PACKAGE BODY: APPS.FND_ADG_MANAGE
Source
1 package body fnd_adg_manage as
2 /* $Header: AFDGMGRB.pls 120.0 2010/03/01 11:28:33 rsanders noship $ */
3
4 G_SESS_STANDY_TO_PROD_VALID boolean := null;
5
6 G_SESSION_IS_SLAVE_TO_STANDBY boolean := false; -- always starts as false.
7 -- no need for null.
8 G_SESS_HANDLE_SLAVE_RPC_DEBUG boolean := false; -- always starts as false.
9 G_SESS_HANDLE_RPC_DEBUG boolean := false; -- always starts as false.
10
11 G_SLAVE_SESSION_ID number := null;
12 G_SESS_MAX_COMMIT_WAIT_TIME number := null;
13
14 C_COMMIT_WAIT_SLEEP_TIME constant number := 1;
15
16 C_DEBUG_TRACE constant number := 1;
17
18 /*==========================================================================*/
19
20 procedure raise_rpc_exec_error(p_rpcDescriptor rpcDescriptor,
21 p_location varchar2,
22 p_additional_info varchar2 default null)
23 as
24 begin
25
26 fnd_adg_exception.raise_error(fnd_adg_exception.C_MGRERR_RPC_EXEC_ERROR,
27 'O='||p_rpcDescriptor.owner ||
28 ' P='||p_rpcDescriptor.package_name ||
29 ' RP='||p_rpcDescriptor.rpc_package_name ||
30 ' RS='||p_rpcDescriptor.rpc_synonym_name ||
31 ' M='||p_rpcDescriptor.method_name ||
32 ' : Location='||p_location ||
33 ' : Addtl Info='||p_additional_info
34 );
35 end;
36
37 /*==========================================================================*/
38
39 function boolean_to_char(p_bool boolean) return varchar2
40 as
41 begin
42
43 if ( p_bool is null )
44 then
45 return 'NULL';
46 else
47 if ( p_bool )
48 then
49 return 'TRUE';
50 else
51 return 'FALSE';
52 end if;
53 end if;
54
55 end;
56
57 /*==========================================================================*/
58
59 function is_session_slave_to_standby return boolean
60 as
61 begin
62 return G_SESSION_IS_SLAVE_TO_STANDBY;
63 end;
64
65 /*==========================================================================*/
66
67 procedure validate_primary_private(p_dbid number, p_dbname varchar2,
68 p_slave_session_id out nocopy number,
69 p_sid number, p_serial number,
70 p_audsid number,p_is_true_standby boolean,
71 p_valid out nocopy number)
72 as
73 cursor c1 is select a.dbid,a.name
74 from v$database a;
75
76 cursor c2 is select a.sid,a.serial#,a.audsid
77 from v$session a
78 where a.sid = ( select distinct b.sid from v$mystat b);
79
80 begin
81
82 p_slave_session_id := -1;
83
84 p_valid := 2;
85
86 if ( not fnd_adg_support.is_primary )
87 then
88 p_valid := 1;
89 return;
90 end if;
91
92 for f_rec in c1 loop
93
94 if ( f_rec.dbid = p_dbid and f_rec.name = p_dbname )
95 then
96 p_valid := 0;
97 end if;
98
99 exit;
100
101 end loop;
102
103 if ( p_valid <> 0 )
104 then
105 return;
106 end if;
107
108 -- Mark this session as slave to standby. This ensures SV_* cannot be
109 -- run directly and allows users to suppress commits. However,
110 -- with the use of autotx flag , user suppress should not be needed.
111
112 -- This is fine as this routine is only
113 -- ever called under the fnd_adg_manage_remote synonym and we would
114 -- never have got here unless we'd come from standby.
115
116 -- We also use this flag to determine where we are in simulated standby,
117 -- to ensure that the real call [ under SV_* ] returns is_standby as
118 -- false but the originator [ on primary also ] returns true.
119
120 if ( fnd_adg_utility.is_session_simulated_standby and not p_is_true_standby )
121 -- originator could be standby or primary.
122 -- only care when not true standby.
123 then
124
125 p_valid := 3;
126
127 if ( p_sid <> -1 and p_serial <> -1 and p_audsid <> -1 )
128 then
129
130 for f_rec in c2 loop
131
132 /*
133 dbms_system.ksdwrt(1,
134 'SLAVE=' || f_rec.sid || ' ' || f_rec.serial# || ' ' ||
135 f_rec.audsid || ' RPC=' || p_sid || ' ' || p_serial
136 || ' ' || p_audsid );
137 */
138
139 if ( f_rec.sid = p_sid and
140 f_rec.serial# = p_serial and
141 f_rec.audsid = p_audsid
142 )
143 then
144 null;
145 else
146 p_valid := 0;
147 end if;
148
149 exit;
150
151 end loop;
152 end if;
153 end if;
154
155 if ( p_valid <> 0 )
156 then
157 return;
158 end if;
159
160 G_SESSION_IS_SLAVE_TO_STANDBY := true;
161
162 -- Record session id.
163
164 G_SLAVE_SESSION_ID := userenv('SESSIONID');
165
166 p_slave_session_id := G_SLAVE_SESSION_ID;
167
168 end;
169
170 /*==========================================================================*/
171
172 procedure validate_standby_to_primary(p_err out nocopy number,
173 p_msg out nocopy varchar2,
174 p_once_per_session boolean default false)
175 as
176 cursor c1 is select a.dbid,a.name
177 from v$database a;
178
179 -- Don't use SESSIONID as invalid on standby!
180
181 cursor c2 is select a.sid,a.serial#,a.audsid
182 from v$session a
183 where a.sid = ( select distinct b.sid from v$mystat b);
184
185 l_valid number;
186 l_slave_session_id number;
187 l_sid number := -1;
188 l_serial number := -1;
189 l_audsid number := -1;
190
191 begin
192
193 p_err := 0;
194 p_msg := null;
195
196 if ( p_once_per_session )
197 then
198 if ( G_SESS_STANDY_TO_PROD_VALID is not null )
199 then
200 if ( G_SESS_STANDY_TO_PROD_VALID )
201 then
202 return;
203 else
204 p_err := fnd_adg_exception.C_MGRERR_FAILED_PREV_SES_CHK;
205 p_msg := fnd_adg_exception.get_error_msg
206 (fnd_adg_exception.C_MGRERR_FAILED_PREV_SES_CHK);
207 return;
208 end if;
209 end if;
210 end if;
211
212 if ( not fnd_adg_support.is_standby )
213 then
214 p_err := fnd_adg_exception.C_MGRERR_NOT_STANDBY;
215 p_msg := fnd_adg_exception.get_error_msg(fnd_adg_exception.C_MGRERR_NOT_STANDBY);
216 G_SESS_STANDY_TO_PROD_VALID := false;
217 return;
218 end if;
219
220 -- If simulated standby and we're really primary, need to
221 -- pass sid,serial, audsid to make sure slave is not a loopback.
222
223 if ( fnd_adg_utility.is_session_simulated_standby and
224 fnd_adg_support.is_primary )
225 then
226 for f_rec in c2 loop
227
228 l_sid := f_rec.sid;
229 l_serial := f_rec.serial#;
230 l_audsid := f_rec.audsid;
231
232 exit;
233 end loop;
234 end if;
235
236 for f_rec in c1 loop
237
238 begin
239
240 $if fnd_adg_compile_directive.enable_rpc
241 $then
242 fnd_adg_manage_remote.validate_primary_private
243 (f_rec.dbid,f_rec.name,
244 l_slave_session_id,
245 l_sid,l_serial,l_audsid,
246 fnd_adg_support.is_true_standby,
247 l_valid);
248 exception when others
249 then l_valid := -1;
250 $else
251 l_valid := -1;
252 $end
253
254 end;
255
256 if ( l_valid <> 0 )
257 then
258 case l_valid
259 when 1 then
260 p_err := fnd_adg_exception.C_MGRERR_REMOTE_NOT_PRIMARY;
261 p_msg := fnd_adg_exception.get_error_msg
262 (fnd_adg_exception.C_MGRERR_REMOTE_NOT_PRIMARY);
263 when 2 then
264 p_err := fnd_adg_exception.C_MGRERR_REMOTE_DOESNT_MATCH;
265 p_msg := fnd_adg_exception.get_error_msg
266 (fnd_adg_exception.C_MGRERR_REMOTE_DOESNT_MATCH);
267 when 3 then
268 p_err := fnd_adg_exception.C_MGRERR_REMOTE_IS_LOOPBACK;
269 p_msg := fnd_adg_exception.get_error_msg
270 (fnd_adg_exception.C_MGRERR_REMOTE_IS_LOOPBACK);
271 when -1 then
272 p_err := fnd_adg_exception.C_MGRERR_REMOTE_RESOLVE;
273 p_msg := fnd_adg_exception.get_error_msg
274 (fnd_adg_exception.C_MGRERR_REMOTE_RESOLVE);
275 else
276 p_err := fnd_adg_exception.C_MGRERR_UNKNOWN_REMOTE_ERROR;
277 p_msg := fnd_adg_exception.get_error_msg
278 (fnd_adg_exception.C_MGRERR_UNKNOWN_REMOTE_ERROR);
279 end case;
280 G_SESS_STANDY_TO_PROD_VALID := false;
281 return;
282 end if;
283
284 exit;
285
286 end loop;
287
288 G_SESS_STANDY_TO_PROD_VALID := true;
289
290 -- Session is valid - record slave session id.
291
292 G_SLAVE_SESSION_ID := l_slave_session_id;
293
294 end;
295
296 /*==========================================================================*/
297
298 function validate_rpc_timestamp(p_rpcDescriptor rpcDescriptor) return boolean
299 as
300 cursor c1 is select 1
301 from dba_objects a,dba_objects b
302 where a.owner = p_rpcDescriptor.owner
303 and a.object_name = p_rpcDescriptor.package_name
304 and a.object_type = 'PACKAGE'
305 and b.owner = p_rpcDescriptor.owner
306 and b.object_name = p_rpcDescriptor.rpc_package_name
307 and b.object_type = 'PACKAGE'
308 and to_date(b.timestamp,'YYYY-MM-DD:HH24:MI:SS') >=
309 to_date(a.timestamp,'YYYY-MM-DD:HH24:MI:SS') ;
310 begin
311
312 if ( not fnd_adg_utility.is_runtime_validate_timestamp )
313 then
314 return true;
315 end if;
316
317 for f_rec in c1 loop
318
319 return true;
320
321 end loop;
322
323 return false;
324
325 end;
326
327 /*==========================================================================*/
328
329 function validate_rpc_synonym(p_rpcDescriptor rpcDescriptor) return boolean
330 as
331 cursor c1 is select 1
332 from dba_synonyms a, dba_synonyms b
333 where a.owner = p_rpcDescriptor.owner
334 and a.synonym_name = fnd_adg_object.C_ADG_MANAGE_NAME_REMOTE
335 and ( a.table_owner = p_rpcDescriptor.owner or
336 a.table_owner is null )
337 and a.table_name = fnd_adg_object.C_ADG_MANAGE_PACKAGE
338 and b.owner = p_rpcDescriptor.owner
339 and b.synonym_name = p_rpcDescriptor.rpc_synonym_name
340 and ( b.table_owner = p_rpcDescriptor.owner or
341 b.table_owner is null )
342 and b.table_name = p_rpcDescriptor.rpc_package_name
343 and a.db_link = b.db_link
344 and a.db_link is not null
345 and b.db_link is not null;
346 begin
347
348 for f_rec in c1 loop
349
350 return true;
351
352 end loop;
353
354 return false;
355
356 end;
357
358 /*==========================================================================*/
359
360 procedure handle_runtime_debug(p_is_slave boolean)
361 as
362 l_debug_rpc number;
363 l_debug_slave_rpc number;
364 begin
365
366 fnd_adg_utility.get_rpc_debug(l_debug_rpc,l_debug_slave_rpc);
367
368 if ( p_is_slave and bitand(l_debug_slave_rpc,C_DEBUG_TRACE) <> 0 )
369 then
370 execute immediate 'alter session set sql_trace true';
371 end if;
372
373 if ( not p_is_slave and bitand(l_debug_rpc,C_DEBUG_TRACE) <> 0 )
374 then
375 execute immediate 'alter session set sql_trace true';
376 end if;
377
378 end;
379
380 /*==========================================================================*/
381
382 procedure handle_rpc_debug(p_once_per_session boolean default true)
383 as
384 begin
385
386 if ( p_once_per_session )
387 then
388 if ( G_SESS_HANDLE_RPC_DEBUG )
389 then
390 return;
391 end if;
392 end if;
393
394 handle_runtime_debug(false);
395
396 G_SESS_HANDLE_RPC_DEBUG := true;
397
398 end;
399
400 /*==========================================================================*/
401
402 procedure handle_slave_rpc_debug(p_once_per_session boolean default true)
403 as
404 begin
405
406 if ( p_once_per_session )
407 then
408 if ( G_SESS_HANDLE_SLAVE_RPC_DEBUG )
409 then
410 return;
411 end if;
412 end if;
413
414 handle_runtime_debug(true);
415
416 G_SESS_HANDLE_SLAVE_RPC_DEBUG := true;
417
418 end;
419
420 /*==========================================================================*/
421
422 function get_commit_wait_seq(p_rpcDescriptor rpcDescriptor) return number
423 as
424 l_commit_count number;
425 begin
426
427 if ( G_SLAVE_SESSION_ID is null ) -- should never happen
428 then
429 raise_rpc_exec_error(p_rpcDescriptor,'get_commit_wait_seq',
430 'G_SLAVE_SESSION_ID is null!');
431 end if;
432
433 -- Initial insert takes place on slave so row may not exist yet.
434 begin
435
436 select a.commit_count
437 into l_commit_count
438 from fnd_adg_commit_wait a
439 where a.session_id = G_SLAVE_SESSION_ID;
440
441 exception
442 when no_data_found
443 then l_commit_count := null;
444 end ;
445
446 return l_commit_count;
447
448 end;
449
450 /*==========================================================================*/
451
452 procedure increment_commit_count(p_rpcDescriptor rpcDescriptor)
453 as
454 l_commit_count number;
455 begin
456
457 if ( G_SLAVE_SESSION_ID is null ) -- should never happen
458 then
459 raise_rpc_exec_error(p_rpcDescriptor,'increment_commit_count',
460 'G_SLAVE_SESSION_ID is null!');
461 end if;
462
463 update fnd_adg_commit_wait a
464 set a.commit_count = a.commit_count + 1
465 where a.session_id = G_SLAVE_SESSION_ID;
466
467 if ( sql%notfound ) -- first time?
468 then
469 insert into fnd_adg_commit_wait(session_id,commit_count)
470 values (G_SLAVE_SESSION_ID,0);
471 end if;
472
473 -- debug_dump_state;
474
475 -- commit happens in the rpc.
476 end;
477
478 /*==========================================================================*/
479
480 procedure set_sess_max_commit_wait_time
481 as
482 begin
483
484 G_SESS_MAX_COMMIT_WAIT_TIME := -1;
485
486 G_SESS_MAX_COMMIT_WAIT_TIME := fnd_adg_utility.get_max_commit_wait_time;
487
488 end;
489
490 /*==========================================================================*/
491
492 function wait_for_commit_count(p_rpcDescriptor rpcDescriptor,
493 p_wait_seq number) return boolean
494 as
495 l_commit_wait_time number;
496 l_actual_wait_time number;
497 l_commit_count number;
498
499 begin
500
501 if ( G_SLAVE_SESSION_ID is null ) -- should never happen
502 then
503 raise_rpc_exec_error(p_rpcDescriptor,'wait_for_commit_count',
504 'G_SLAVE_SESSION_ID is null!');
505 end if;
506
507 if ( G_SESS_MAX_COMMIT_WAIT_TIME is null )
508 then
509 set_sess_max_commit_wait_time;
510 end if;
511
512 if ( G_SESS_MAX_COMMIT_WAIT_TIME < 0 )
513 then
514 raise_rpc_exec_error(p_rpcDescriptor,'wait_for_commit_count',
515 'commit count is -ve!');
516 end if;
517
518 if ( G_SESS_MAX_COMMIT_WAIT_TIME > fnd_adg_utility.C_MAX_COMMIT_WAIT_TIME )
519 then
520 l_commit_wait_time := fnd_adg_utility.C_MAX_COMMIT_WAIT_TIME;
521 else
522 l_commit_wait_time := G_SESS_MAX_COMMIT_WAIT_TIME;
523 end if;
524
525 l_actual_wait_time := 0;
526
527 loop
528
529 if ( l_actual_wait_time > l_commit_wait_time )
530 then
531 return false;
532 end if;
533
534 begin
535
536 select a.commit_count
537 into l_commit_count
538 from fnd_adg_commit_wait a
539 where a.session_id = G_SLAVE_SESSION_ID;
540
541 exception
542 when no_data_found
543 then l_commit_count := null;
544
545 end;
546
547 if ( l_commit_count is not null )
548 then
549 if ( p_wait_seq is null ) -- first time so any non-null count is ok
550 then
551 exit;
552 else
553 if ( l_commit_count > p_wait_seq )
554 then
555 exit; --ok
556 end if;
557 end if;
558 else
559 if ( p_wait_seq is not null ) -- can't happen!
560 then
561 raise_rpc_exec_error(p_rpcDescriptor,'wait_for_commit_count',
562 'commit count is null but wait seq isn''t!');
563 end if;
564 end if;
565
566 dbms_lock.sleep(C_COMMIT_WAIT_SLEEP_TIME);
567
568 l_actual_wait_time := l_actual_wait_time + C_COMMIT_WAIT_SLEEP_TIME;
569
570 end loop;
571
572 -- debug_dump_state;
573
574 return true;
575
576 end;
577
578 /*==========================================================================*/
579
580 procedure invoke_standby_error_handler(p_request_id number)
581 as
582 begin
583 -- This procedure is the rpc wrapper for handle_standby_error.
584
585 fnd_adg_support.handle_standby_error(p_request_id,false,false,1);
586
587 end;
588
589 /*==========================================================================*/
590
591 procedure rpc_invoke_standby_error ( p_request_id number)
592 as
593 begin
594
595 $if fnd_adg_compile_directive.enable_rpc
596 $then
597
598 fnd_adg_manage_remote.invoke_standby_error_handler(p_request_id);
599
600 $else
601
602 null;
603
604 $end
605
606 end;
607
608 /*==========================================================================*/
609
610 begin
611 null;
612 end fnd_adg_manage;