1 PACKAGE BODY Ad_Event_Registry_Pkg AS
2 -- $Header: adevntrgb.pls 120.0 2005/05/25 12:03:04 appldev noship $
3
4 -- Event Lock Mode
5 EVENT_LOCK_MODE CONSTANT NUMBER := DBMS_LOCK.X_MODE;
6 -- Constant for Relase on commit option for locks.
7 RELEASE_COMMIT_CONST CONSTANT BOOLEAN := FALSE ;
8 -- Default value of Event Version
9 DEFAULT_VERSION CONSTANT NUMBER := 1;
10
11 -- Event record type is declared here.
12 TYPE Event_Rec_Type IS RECORD(
13 Owner ad_events.owner%TYPE,
14 Event_Name ad_events.event_name%TYPE,
15 Event_Context ad_events.event_context%TYPE,
16 Event_Id ad_events.event_id%TYPE,
17 Event_Status ad_event_versions.status%TYPE,
18 -- worker status will be a string indicating status of all the workers
19 Worker_Status ad_event_versions.worker_status%TYPE,
20 Event_Version ad_events.last_version%TYPE,
21 Event_Type ad_events.event_type%TYPE ,
22 Module_Name ad_event_transitions.module_name%TYPE,
23 Worker_Id ad_event_transitions.worker_id%TYPE,
24 Max_Workers ad_event_versions.num_workers%TYPE );
25
26 -- Type of table to store the list of events.
27 TYPE Event_Tbl_Type IS TABLE OF Event_Rec_Type INDEX BY BINARY_INTEGER ;
28
29 -- Lock Record
30 TYPE Lock_Rec_Type IS RECORD (
31 Lock_Name VARCHAR2 (128),
32 Lock_handle VARCHAR2 (128) );
33
34 -- Lock table type.
35 TYPE Lock_Tbl_Type IS TABLE OF Lock_Rec_Type INDEX BY BINARY_INTEGER ;
36
37 -- Global variables are declared here
38
39 -- Table to store the list of events already registered is declared below.
40 g_Events_Tbl Event_Tbl_Type;
41 -- Table to store allocated lock details.
42 g_Lock_Tbl Lock_Tbl_Type;
43
44 -- Lock declaration
45 g_Event_LockHandle VARCHAR2(128);
46 g_Event_LockName VARCHAR2(128);
47
48 FUNCTION Get_Event_Index(
49 p_Owner IN VARCHAR2 ,
50 p_Event_Name IN VARCHAR2 ,
51 p_Context IN VARCHAR2 )
52 RETURN NUMBER
53 IS
54 BEGIN
55 --
56 -- The function returns 0 if there are no events registered
57 -- The function returns 0 if there is no matching event registered so far.
58 --
59 IF ( g_Events_Tbl.COUNT = 0 ) THEN
60 RETURN 0;
61 END IF ;
62 --
63 -- Now check in the Global event list whether an event with the same name
64 -- Owner and Context is already registered. If so return the index of the
65 -- same. If not return 0.
66 --
67 FOR i IN g_Events_Tbl.FIRST .. g_Events_Tbl.LAST LOOP
68 IF ( g_Events_Tbl(i).Owner = p_Owner AND
69 g_Events_Tbl(i).Event_Name = p_Event_Name AND
70 g_Events_Tbl(i).Event_Context = p_Context ) THEN
71 RETURN i;
72 END IF ;
73 END LOOP ;
74 -- If there are no matching events return 0.
75 RETURN 0;
76 END Get_Event_Index;
77
78 PROCEDURE Validate_Globals(
79 p_Owner IN VARCHAR2 ,
80 p_Event_Name IN VARCHAR2 ,
81 p_Context IN VARCHAR2 )
82 IS
83 l_Event_Index NUMBER ;
84 BEGIN
85 --
86 -- Get the index of the event from the global event list.
87 -- index value 0 means that the event is not registered in the session.
88 --
89 l_Event_Index := Get_Event_Index(
90 p_Owner ,
91 p_Event_Name ,
92 p_Context );
93
94 IF ( l_Event_Index = 0 ) THEN
95 raise_application_error(-20010,'Event Not Initialized');
96 END IF ;
97
98 END Validate_Globals;
99
100 PROCEDURE Request_Lock(
101 p_Owner IN VARCHAR2 ,
102 p_Event_Name IN VARCHAR2 )
103 IS
104 l_LockName VARCHAR2(128);
105 l_Found BOOLEAN ;
106 l_Temp_Index BINARY_INTEGER ;
107 BEGIN
108
109 -- Construct the lock name.
110 l_LockName := p_Owner||'_'||p_Event_Name;
111
112 l_Found := FALSE ;
113 IF ( g_Lock_Tbl.COUNT <> 0 ) THEN
114 FOR i IN g_Lock_Tbl.FIRST .. g_Lock_Tbl.LAST LOOP
115 IF ( g_Lock_Tbl(i).Lock_Name = l_LockName ) THEN
116 g_Event_LockHandle := g_Lock_Tbl(i).Lock_Handle;
117 l_Found := TRUE ;
118 EXIT ;
119 END IF ;
120 END LOOP ;
121 END IF ;
122
123 -- Get the named lock.
124 AD_LOCK_UTILS_PKG.Get_Lock(
125 p_LockName => l_LockName,
126 p_LockMode => EVENT_LOCK_MODE,
127 p_Release_On_Commit => RELEASE_COMMIT_CONST,
128 x_LockHandle => g_Event_LockHandle);
129
130 IF ( l_Found = FALSE ) THEN
131 l_Temp_Index := NVL(g_Lock_Tbl.LAST,0) + 1;
132 g_Lock_Tbl(l_Temp_Index).Lock_Name := l_LockName;
133 g_Lock_Tbl(l_Temp_Index).Lock_Handle := g_Event_LockHandle;
134 END IF ;
135 END Request_Lock;
136
137 --------------------------------
138 -- This is the function to initialize the worker status string.
139 -- The string will be initialized to Num_workers number of 'N's
140 --------------------------------
141 FUNCTION Initialize_worker_string(
142 p_Num_Workers IN NUMBER )
143 RETURN VARCHAR2
144 IS
145 BEGIN
146 RETURN LPAD ('N',p_Num_Workers,'N');
147 END Initialize_worker_string;
148
149 ---------------------------------
150 -- This function will set the worker status string, i.e. it will
151 -- put a 'Y' indicating that the worker has completed
152 ---------------------------------
153 PROCEDURE Set_Worker_Status_String(
154 p_Worker_Id IN NUMBER ,
155 p_Worker_Status_String IN OUT NOCOPY VARCHAR2 )
156 IS
157 BEGIN
158 p_Worker_Status_String :=
159 SUBSTR (p_Worker_Status_String, 1, (p_Worker_Id-1)) ||
160 'Y'||
161 SUBSTR (p_Worker_Status_String, (p_Worker_Id+1));
162 END Set_Worker_Status_String;
163
164
165 ---------------------------------
166 -- This procedure will insert data into the AD_EVENT_TRANSITIONS table.
167 --
168 ---------------------------------
169 PROCEDURE Insert_Event_Transitions(
170 p_Event_Id IN NUMBER ,
171 p_Version IN NUMBER ,
172 p_New_Status IN VARCHAR2 ,
173 p_Old_Status IN VARCHAR2 := NULL ,
174 p_Module_Name IN VARCHAR2 ,
175 p_Worker_Id IN NUMBER := NULL )
176 AS
177 BEGIN
178 INSERT INTO ad_event_transitions(
179 transition_id,
180 event_id,
181 version,
182 new_status,
183 prior_status,
184 transition_time,
185 module_name,
186 worker_id)
187 VALUES (
188 ad_event_transitions_s.NEXTVAL,
189 p_Event_Id,
190 p_Version,
191 p_New_Status,
192 p_Old_Status,
193 SYSDATE ,
194 p_Module_Name,
195 p_Worker_Id);
196
197 END Insert_Event_Transitions;
198 --------------------------------
199 -- This procedure is used to perform Basic validations on the parameters
200 --------------------------------
201 PROCEDURE Validate_Parameters(
202 p_Owner IN VARCHAR2 ,
203 p_Event_Name IN VARCHAR2 ,
204 p_Event_Type IN VARCHAR2 ,
205 p_Num_Workers IN NUMBER )
206 IS
207 BEGIN
208 IF (p_Owner IS NULL ) THEN
209 RAISE_APPLICATION_ERROR(-20010,
210 'Error: Value for Owner is passed as NULL.');
211 END IF ;
212 IF (p_Event_Name IS NULL ) THEN
213 RAISE_APPLICATION_ERROR(-20010,
214 'Error: Value for Evant Name is passed as NULL .');
215 END IF ;
216 IF ( p_Event_Type <> BOOLEAN_TYPE AND p_Event_Type <> MULTI_TYPE ) THEN
217 RAISE_APPLICATION_ERROR(-20010,
218 'Error: Invalid event type : '||p_Event_Type );
219 END IF;
220
221 --
222 -- If the number of workers is greater than 999 raise an error.
223 --
224 IF ( NVL(p_Num_Workers ,1 ) > MAX_ALLOWED_WORKERS ) THEN
225 RAISE_APPLICATION_ERROR(-20010,
226 'Error: Number of workers exceeds the maximum limit.');
227 END IF ;
228
229 --
230 -- Check whether a multi worker event is initialized with
231 -- multiple workers. If so raise an error. This block will
232 -- handle all the cases, already registered and new events.
233 --
234 IF ( p_Event_Type = MULTI_TYPE AND nvl(p_Num_Workers,1) > 1 ) THEN
235 RAISE_APPLICATION_ERROR(-20010,'Multi State Events Cannot be '||
236 'Initialized with multiple Workers');
237 END IF;
238
239 END Validate_Parameters;
240 --------------------------------------------------------------------------
241 PROCEDURE Initialize_Event(
242 p_Owner IN VARCHAR2,
243 p_Event_Name IN VARCHAR2,
244 p_Module_Name IN VARCHAR2,
245 p_Event_Type IN VARCHAR2 := NULL ,
246 p_Context IN VARCHAR2 := NULL ,
247 p_Version IN NUMBER := NULL ,
248 p_Worker_Id IN NUMBER := NULL ,
249 p_Num_Workers IN NUMBER := NULL )
250 IS
251 -- Local variable declarations
252 l_Version ad_events.last_version%TYPE;
253 l_Event_Id ad_events.event_id%TYPE;
254 l_Event_Type ad_events.event_type%TYPE;
255 l_Event_Status ad_event_versions.status%TYPE;
256 l_Worker_Status_String ad_event_versions.worker_status%TYPE;
257 l_Event_Index NUMBER(3) ;
258 l_Commit_Flag BOOLEAN := FALSE ;
259
260 -- The following variables will be used to store the IN parameter value
261 l_p_Event_Type ad_events.event_type%TYPE;
262 l_p_Context ad_events.event_context%TYPE ;
263 l_p_Version ad_events.last_version%TYPE;
264
265 -- The following variable holds the value of number of workers in the last
266 -- call, in case the event fails and restarted this value will be useful.
267 l_Prev_Max_Workers ad_event_versions.num_workers%TYPE ;
268 l_Error_Message VARCHAR2 (100);
269
270 BEGIN -- Begin Procedure
271 --
272 -- The following block is used to take care of the default values
273 -- Change done for file.sql.35
274 --
275 l_p_Event_Type := nvl(p_Event_Type, BOOLEAN_TYPE);
276 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
277 l_p_Version := nvl(p_Version, DEFAULT_VERSION);
278
279 --
280 -- Call the procedure for validating the parameters
281 --
282 Validate_Parameters(
283 p_Owner => p_Owner ,
284 p_Event_Name => p_Event_Name ,
285 p_Event_Type => l_p_Event_Type,
286 p_Num_Workers => p_Num_Workers);
287
288 --
289 -- Request for the lock
290 --
291 Request_Lock(p_Owner,
292 p_Event_Name);
293
294 BEGIN -- Block 1
295
296 -- Select the details of the event for the passed parameters.
297 SELECT last_version,
298 event_id,
299 event_type
300 INTO l_Version,
301 l_Event_Id,
302 l_Event_Type
303 FROM ad_events
304 WHERE owner = p_Owner
305 AND event_name = p_Event_Name
306 AND event_context = l_p_Context;
307
308 --
309 -- Compare the event type in the database with that in the call.
310 -- If both doesn't match raise an error.
311 --
312 IF ( l_Event_Type <> l_p_Event_Type ) THEN
313 RAISE_APPLICATION_ERROR (-20010,'Event initialized with a '||
314 'wrong Type');
315 END IF ;
316
317 --
318 -- The following block is to make sure that an already registered
319 -- multi state event is not initialized with multiple workers.
320 --
321 IF ( l_Event_Type = MULTI_TYPE AND nvl(p_Num_Workers,0) > 1 ) THEN
322 RAISE_APPLICATION_ERROR (-20010,'Multi State Event Cannot be '||
323 'Initialized with multiple Workers');
324 END IF;
325
326 IF ( l_Version < l_p_Version ) THEN -- If 1 to check version
327 --
328 -- Get the worker status string
329 --
330 l_Worker_Status_String := Initialize_Worker_String(p_Num_Workers);
331 --
332 -- I.e. A version with a lesser version number exists.
333 -- A higher version is being registered.
334 -- It has to be inserted into AD_EVENT_VERSIONS.
335 --
336 INSERT INTO ad_event_versions(
337 event_id,
338 version,
339 status,
340 creation_date,
341 last_update_date,
342 num_workers,
343 worker_status)
344 VALUES( l_Event_Id,
345 l_p_Version,
346 INITIALIZED_STATUS,
347 SYSDATE ,
348 SYSDATE,
349 p_Num_Workers,
350 l_Worker_Status_String)
351 RETURNING status
352 INTO l_Event_Status ;
353
354 --
355 -- If event with lesser version number exists; version and last
356 -- update date are updated in AD_EVENTS
357 --
358 UPDATE ad_events
359 SET last_version = l_p_Version,
360 last_update_date = SYSDATE
361 WHERE event_id = l_Event_Id;
362
363 l_Commit_Flag := TRUE ; -- Committing the transaction here.
364 l_Prev_Max_Workers := p_Num_Workers;
365 ELSE -- else to check version
366 BEGIN --Block 2
367 --
368 -- The control comes here when the event is already
369 -- registered and the version is greater than or equal to p_Version
370 --
371 SELECT status,
372 worker_status,
373 num_workers
374 INTO l_Event_Status,
375 l_Worker_Status_String,
376 l_Prev_Max_Workers
377 FROM ad_Event_Versions
378 WHERE event_id = l_Event_Id
379 AND version = l_p_Version;
380 EXCEPTION
381 WHEN OTHERS THEN
382 RAISE_APPLICATION_ERROR (-20010,'Event with version '||
383 l_p_Version||' not registered. ');
384 END; --End of Block2
388 WHEN NO_DATA_FOUND THEN
385 END IF; -- End If 1 to check version
386
387 EXCEPTION
389 -- No Events with the same Owner,Name,Context are registered so far.
390 -- New Event can be registered Now.
391 INSERT INTO ad_events(
392 event_id,
393 owner,
394 event_name,
395 event_context,
396 event_type,
397 last_version,
398 creation_date,
399 last_update_date)
400 VALUES(
401 ad_events_s.NEXTVAL,
402 p_Owner,
403 p_Event_Name,
404 l_p_Context,
405 l_p_Event_Type,
406 l_p_Version,
407 SYSDATE,
408 SYSDATE )
409 RETURNING event_id
410 INTO l_Event_Id;
411
412 -- Insert the version details into AD_EVENT_VERSIONS
413 --
414 -- Form the worker status string
415 --
416 l_Worker_Status_String := Initialize_Worker_String(p_Num_Workers);
417
418 INSERT INTO ad_event_versions(
419 event_id,
420 version,
421 status,
422 creation_date,
423 last_update_date,
424 num_workers,
425 worker_status)
426 VALUES(
427 l_Event_Id,
428 l_p_Version,
429 INITIALIZED_STATUS,
430 SYSDATE,
431 SYSDATE,
432 p_Num_Workers,
433 l_Worker_Status_String);
434
435 l_Commit_Flag := TRUE ;
436 -- Argument event type will be the type of the new event.
437 l_Event_Type := l_p_Event_Type;
438 -- For new event, status will be initialized status
439 l_Event_Status := INITIALIZED_STATUS;
440 l_Prev_Max_Workers := p_Num_Workers;
441 WHEN OTHERS THEN
442 RAISE ;
443 END; -- End of Block 1
444
445 --
446 -- If the event is already initialized in the session it is not required
447 -- to add the same to the global event list again.
448 -- The below function call gets the index of the event from the
449 -- global event list, if the event is already registered in the session.
450 -- Ideally the function call should return 0.
451 --
452 l_Event_Index := Get_Event_Index(
453 p_Owner => p_Owner,
454 p_Event_Name => p_Event_Name ,
455 p_Context => l_p_Context);
456
457 --
458 -- For new events add the event details to the end of
459 -- the global event list. For already registered events update the
460 -- details in the global event list.
461 --
462 -- For new events the index value returned by the above call will be 0
463 IF ( l_Event_Index = 0 ) THEN
464 l_Event_Index := NVL(g_Events_Tbl.LAST,0) + 1;
465 g_Events_Tbl(l_Event_Index).Owner := p_Owner;
466 g_Events_Tbl(l_Event_Index).Event_Name := p_Event_Name;
467 g_Events_Tbl(l_Event_Index).Event_Context := l_p_Context;
468 g_Events_Tbl(l_Event_Index).Event_Id := l_Event_Id;
469 g_Events_Tbl(l_Event_Index).Event_Status := l_Event_Status;
470 g_Events_Tbl(l_Event_Index).Worker_Status := l_Worker_Status_String;
471 g_Events_Tbl(l_Event_Index).Event_Version := l_p_Version;
472 g_Events_Tbl(l_Event_Index).Event_Type := l_Event_Type;
473 g_Events_Tbl(l_Event_Index).Module_Name := p_Module_Name;
474 g_Events_Tbl(l_Event_Index).Worker_Id := p_Worker_Id;
475 g_Events_Tbl(l_Event_Index).Max_Workers := p_Num_Workers;
476 ELSE
477 g_Events_Tbl(l_Event_Index).Event_Version := l_p_Version;
478 g_Events_Tbl(l_Event_Index).Event_Status := l_Event_Status; -- check this
479 g_Events_Tbl(l_Event_Index).Module_Name := p_Module_Name;
480 g_Events_Tbl(l_Event_Index).Worker_Id := p_Worker_Id;
481 g_Events_Tbl(l_Event_Index).Max_Workers := p_Num_Workers;
482 END IF ;
483
484 --
485 -- If the multi-worker event fails before completion,
486 -- and gets re-started with a different number of workers then
487 -- the worker information in the db and session has to be updated.
488 --
489 IF ( l_Event_Status <> COMPLETED_STATUS -- If worker re-schedule
490 AND NVL (l_Prev_Max_Workers,1) <> NVL (p_Num_Workers,1) ) THEN
491 --
492 -- Form the worker status string
493 --
494 g_Events_Tbl(l_Event_Index).Worker_Status :=
495 Initialize_Worker_String(p_Num_Workers);
496 -- reset the worker status string and number_workers
497 -- in ad_event_versions
498 UPDATE ad_event_versions
499 SET num_workers = p_Num_Workers,
500 worker_status = g_Events_Tbl(l_Event_Index).Worker_Status ,
501 last_update_date = SYSDATE
502 WHERE event_id = l_Event_Id
503 AND version = l_p_Version ;
504
505 -- Inserting a record into the transition record to show that
506 -- a rescheduling has happened for the event.
507 Insert_Event_Transitions(
508 p_Event_Id => l_Event_Id,
509 p_Version => l_p_Version,
510 p_New_Status => 'RE-SCHEDULED',
511 p_Old_Status => l_Event_Status,
512 p_Module_Name => p_Module_Name,
516 l_Commit_Flag := TRUE ;
513 p_Worker_Id => p_Worker_Id);
514
515 -- Set the commit flag
517 END IF ; -- End If worker re-schedule
518 -- Commit the transaction if the commit flag is set
519 IF ( l_Commit_Flag = TRUE ) THEN
520 COMMIT ;
521 END IF ;
522
523 --
524 -- Release the lock
525 --
526 AD_LOCK_UTILS_PKG.Release_Lock(
527 p_LockHandle => g_Event_LockHandle);
528
529 g_Event_LockHandle := NULL ;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 IF ( g_Event_LockHandle IS NOT NULL ) THEN
534 BEGIN -- begin for release lock
535 AD_LOCK_UTILS_PKG.Release_Lock(
536 p_LockHandle => g_Event_LockHandle);
537 g_Event_LockHandle := NULL ;
538 EXCEPTION
539 WHEN OTHERS THEN
540 NULL ;
541 END ; -- end for release lock
542 END IF ;
543 RAISE ;
544 END Initialize_Event;
545 --------------------------------------------------------------------------
546
547 PROCEDURE Start_Event(
548 p_Owner IN VARCHAR2,
549 p_Event_Name IN VARCHAR2,
550 p_Context IN VARCHAR2 := NULL
551 )
552 IS
553 l_Event_Status ad_event_versions.status%TYPE;
554 l_Event_type ad_events.event_type%TYPE;
555 l_Event_Id ad_events.event_id%TYPE;
556 l_Version ad_event_versions.version%TYPE;
557 l_Worker_Id ad_event_transitions.worker_id%TYPE ;
558 l_Worker_Status_String ad_event_versions.worker_status%TYPE ;
559 l_Module_Name ad_event_transitions.module_name%TYPE ;
560 l_Event_Index NUMBER ;
561 -- this variable is used to hold in the IN parameter
562 l_p_Context ad_events.event_context%TYPE ;
563 BEGIN
564 --
565 -- The below block checks the value of parameter p_Contect
566 -- if the value is null, it is assumed that the value is default value.
567 -- the change was done for file.sql.35
568 --
569 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
570
571 --
572 -- The following call checks whether the event is initialized
573 -- Compares the global variable values with the values in
574 -- the current call also.
575 --
576 Validate_Globals(p_Owner,
577 p_Event_Name,
578 l_p_Context);
579 --
580 -- Now get the index of the current event from the global event list.
581 -- Use this index value to access the other event details.
582 --
583 l_Event_Index := Get_Event_Index(
584 p_Owner,
585 p_Event_Name,
586 l_p_Context);
587 BEGIN -- Block 1
588 --
589 -- we are not going to select the event details from db.
590 -- get it from the global event list in the session.
591 --
592 l_Event_Status := g_Events_Tbl(l_Event_Index).Event_Status;
593 l_Event_type := g_Events_Tbl(l_Event_Index).Event_Type;
594 l_Event_Id := g_Events_Tbl(l_Event_Index).Event_id;
595 l_Version := g_Events_Tbl(l_Event_Index).Event_Version;
596 l_Worker_Id := g_Events_Tbl(l_Event_Index).Worker_Id;
597 l_Worker_Status_String := g_Events_Tbl(l_Event_Index).Worker_Status;
598 l_Module_Name := g_Events_Tbl(l_Event_Index).Module_Name;
599
600 IF ( NVL(g_Events_Tbl(l_Event_Index).Max_Workers,1) > 1
601 AND SUBSTR(l_Worker_Status_String, l_Worker_Id,1) = 'Y' ) THEN
602 -- If worker status
603 --
604 -- The worker has already completed.
605 --
606 RAISE_APPLICATION_ERROR(-20010,'Worker has completed already.');
607 END IF ; --end if worker status
608
609 IF ( l_Event_Status = COMPLETED_STATUS ) THEN -- if event status
610 --
611 -- The Event is already completed, so it is not possible
612 -- to start the same.
613 --
614 RAISE_APPLICATION_ERROR(-20010,'Event is Already Completed');
615 ELSIF ( l_Event_Status = INITIALIZED_STATUS ) THEN --else if event status
616 --
617 -- Update the start time and last update date in AD_EVENT_VERSIONS.
618 --
619 UPDATE ad_event_versions
620 SET start_time = SYSDATE ,
621 last_update_date = SYSDATE
622 WHERE event_id = l_Event_Id
623 AND version = l_Version;
624 --
625 -- Whenver an Event is getting started
626 -- an entry is inserted into AD_EVENT_TRANSITIONS with
627 -- old status NULL and new status as Initialized status.
628 --
629 Insert_Event_Transitions(
630 p_Event_Id => l_Event_Id,
631 p_Version => l_Version,
632 p_New_Status => INITIALIZED_STATUS,
633 p_Module_Name => l_Module_Name,
634 p_Worker_Id => l_Worker_Id);
635
636 ELSE -- else if event status
637 --
638 -- The event is in some status other than completed and initialized.
639 -- It means that event is already started, so it is not possible to
640 -- start the same once again.
641 --
642 RAISE_APPLICATION_ERROR (-20010,'Invalid Event Status. '||
646 COMMIT ;
643 'Not possible to start.');
644 END IF; -- end if event status
645 END; -- End Block 1
647 END Start_Event;
648
649 ---------------------------------------------------------------------------
650
651 Procedure End_Event(
652 p_Owner IN VARCHAR2,
653 p_Event_Name IN VARCHAR2,
654 p_Context IN VARCHAR2 := NULL
655 )
656 IS
657 l_Event_Status ad_event_versions.status%TYPE;
658 l_New_Status ad_event_versions.status%TYPE;
659 l_Event_type ad_events.event_type%TYPE;
660 l_Event_Id ad_events.event_id%TYPE;
661 l_Version ad_event_versions.version%TYPE;
662 l_Worker_Status_String ad_event_versions.worker_status%TYPE;
663 l_End_Time DATE ;
664 l_Max_Workers ad_event_versions.num_workers%TYPE ;
665 l_Worker_Id ad_event_transitions.worker_id%TYPE ;
666 l_Module_Name ad_event_transitions.module_name%TYPE ;
667 -- the following variable stores the index that points to the current
668 -- event's record in the event list
669 l_Event_Index NUMBER ;
670 -- this variable is used to hold in the IN parameter
671 l_p_Context ad_events.event_context%TYPE ;
672
673 BEGIN -- begin procedure
674 --
675 -- The below block checks the value of parameter p_Contect
676 -- if the value is null, it is assumed that the value is default value.
677 -- the change was done for file.sql.35
678 --
679 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
680
681 --
682 -- The following call checks whether the event is initialized
683 -- in the current session
684 --
685 Validate_Globals(p_Owner,
686 p_Event_Name,
687 l_p_Context);
688 --
689 -- Get the index of the event from the global event list
690 --
691 l_Event_Index := Get_Event_Index(p_Owner,
692 p_Event_Name,
693 l_p_Context);
694
695 -- Now get the details from the global event list
696 l_Max_Workers := g_Events_Tbl(l_Event_Index).Max_Workers;
697 l_Worker_Id := g_Events_Tbl(l_Event_Index).Worker_Id;
698 l_Module_Name := g_Events_Tbl(l_Event_Index).Module_Name;
699
700 BEGIN -- Block 1
701 --
702 -- Request for the lock.
703 --
704 Request_Lock(p_Owner,
705 p_Event_Name);
706
707 --
708 -- Select the details of the event joining AD_EVENTS and
709 -- AD_EVENT_VERSIONS by joining using the version.
710 --
711
712 SELECT status,
713 event_type,
714 ae.event_id,
715 av.version,
716 worker_status
717 INTO l_Event_Status,
718 l_Event_type,
719 l_Event_Id,
720 l_Version,
721 l_Worker_Status_String
722 FROM ad_events ae, ad_event_versions av
723 WHERE ae.event_id = av.event_id
724 AND ae.last_version = av.version
725 AND ae.owner = p_Owner
726 AND ae.event_name = p_Event_Name
727 AND ae.event_context = l_p_Context;
728
729 IF ( l_Event_Status = COMPLETED_STATUS ) THEN -- if status check
730 --
731 -- The event is already completed so no need to end the same again.
732 --
733 RAISE_APPLICATION_ERROR(-20010,'Event is Already Completed.');
734 ELSE -- else status check
735 --
736 -- The below if block is for checking multi-worker events.
737 --
738 IF ( NVL(l_Max_Workers,0) > 1 ) THEN -- if max workers
739
740 --
741 -- Copy the current status of the event to l_New_Status.
742 --
743 l_New_Status := l_Event_Status;
744
745 --
746 -- Worker status uses positional characters to indicate
747 -- status of a worker.
748 -- Form the new worker status string by changing 'N' to 'Y'
749 -- at the proper position for the worker in the string.
750 --
751 Set_Worker_Status_String (l_Worker_Id,
752 l_Worker_Status_String);
753
754 --
755 -- Check whether all the workers are completed.
756 -- If there are no 'N's in the new worker status string it means
757 -- that all the workers have completed.
758 -- So set the new worker status as completed status.
759 -- otherwise status remains as the old status.
760 --
761 IF ( INSTR(l_Worker_Status_String,'N') = 0 ) THEN
762 l_New_Status := COMPLETED_STATUS;
763 l_End_Time := SYSDATE;
764 END IF;
765
766 UPDATE ad_event_versions
767 SET status = l_New_Status,
768 end_time = l_End_Time,
769 last_update_date = SYSDATE ,
770 worker_status = l_Worker_Status_String
771 WHERE event_id = l_Event_Id
772 AND version = l_Version;
773
774 --
775 -- The below block is for inserting the event transition records
776 -- for multi-worker events.
777 -- The transition records will be inserted only when the event is
781 IF ( l_New_Status = COMPLETED_STATUS )THEN -- if for completed status
778 -- completed. In case of multi-worker events, Event gets completed
779 -- only when all the workers are completed.
780 --
782 Insert_Event_Transitions(
783 p_Event_Id => l_Event_Id,
784 p_Version => l_Version,
785 p_New_Status => l_New_Status,
786 p_Old_Status => l_Event_Status,
787 p_Module_Name => l_Module_Name,
788 p_Worker_Id => l_Worker_Id);
789
790 END IF; -- end if for completed status
791
792 -- Commit the transaction here
793 COMMIT;
794
795 ELSE -- else max workers
796 --
797 -- Single worker events.
798 -- New status will be - completed status.
799 --
800 l_New_Status := COMPLETED_STATUS;
801
802 UPDATE ad_event_versions
803 SET status = l_New_Status,
804 end_time = SYSDATE ,
805 last_update_date = SYSDATE
806 WHERE event_id = l_Event_Id
807 AND version = l_Version;
808
809 --
810 -- The below block is for inserting the event transition records
811 -- for single-worker events.
812 -- The transition records will be inserted only when the event is
813 -- completed.
814 --
815 Insert_Event_Transitions(
816 p_Event_Id => l_Event_Id,
817 p_Version => l_Version,
818 p_New_Status => l_New_Status,
819 p_Old_Status => l_Event_Status,
820 p_Module_Name => l_Module_Name);
821
822 -- Commit the transaction here
823 COMMIT;
824
825 END IF; -- End if max workers
826
827 --
828 -- Set the global variables
829 --
830 g_Events_Tbl(l_Event_Index).Event_Status := l_New_Status;
831 g_Events_Tbl(l_Event_Index).Worker_Status := l_Worker_Status_String;
832
833
834 END IF; -- End if status check
835 --
836 -- Release the lock.
837 -- The lock is released only after doing all the DMLs
838 -- and the commit
839 --
840 AD_LOCK_UTILS_PKG.Release_Lock(
841 p_LockHandle => g_Event_LockHandle);
842
843 g_Event_LockHandle := NULL ;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 RAISE ;
848 END; -- End Block 1
849 EXCEPTION
850 WHEN OTHERS THEN
851 IF ( g_Event_LockHandle IS NOT NULL ) THEN
852 BEGIN -- begin for release lock
853 AD_LOCK_UTILS_PKG.Release_Lock(
854 p_LockHandle => g_Event_LockHandle);
855 g_Event_LockHandle := NULL ;
856 EXCEPTION
857 WHEN OTHERS THEN
858 NULL ;
859 END ; -- end for release lock
860 END IF ;
861 RAISE ;
862 END End_Event;
863 --------------------------------------------------------------------------
864 FUNCTION Check_Min_Completed_Version(
865 p_Owner IN VARCHAR2 ,
866 p_Event_Name IN VARCHAR2 ,
867 p_Min_Completed_Version IN NUMBER ,
868 p_Context IN VARCHAR2 := NULL )
869 RETURN BOOLEAN
870 IS
871 -- Local Variables
872 l_Temp_Var NUMBER ;
873 l_Return_Status BOOLEAN ;
874 -- this variable is used to hold in the IN parameter
875 l_p_Context ad_events.event_context%TYPE ;
876
877 BEGIN
878 --
879 -- If NULL is passed as the context assume the value to be DEFAULT
880 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
881
882 --
883 -- Set the return status to false
884 l_Return_Status := FALSE ;
885
886 --
887 -- Select the status, version of the versions higher than or equal to
888 -- the p_Min_Completed_Version.
889 -- if any of these versions are completed, it means that the event is
890 -- completed to atleast that level. hence return TRUE
891 -- Else return FALSE
892 --
893 SELECT 1
894 INTO l_Temp_Var
895 FROM ad_events ae,
896 ad_Event_Versions av
897 WHERE ae.owner = p_Owner
898 AND ae.event_name = p_Event_Name
899 AND ae.event_context = l_p_Context
900 AND ae.event_id = av.event_id
901 AND av.version >= p_Min_Completed_Version
902 AND av.status = COMPLETED_STATUS
903 AND ROWNUM < 2;
904
905 l_Return_Status := TRUE ;
906
907 RETURN l_Return_Status;
908
909 EXCEPTION
910 --
911 -- When the select returns no rows it means that there are no versions
912 -- matching the IN parameters. Return FALSE
913 --
914 WHEN NO_DATA_FOUND THEN
915 RETURN l_Return_Status ;
916 END Check_Min_Completed_Version;
917 --------------------------------------------------------------------------
918
919 FUNCTION Is_Event_Done(
920 p_Owner IN VARCHAR2 ,
921 p_Event_Name IN VARCHAR2 ,
922 p_Context IN VARCHAR2 := NULL ,
923 p_Min_Version IN NUMBER := NULL ,
927 RETURN BOOLEAN
924 p_Specific_Version IN NUMBER := NULL ,
925 p_Worker_Id IN NUMBER := NULL ,
926 p_Num_Workers IN NUMBER := NULL )
928 IS
929 -- Local variable declarations.
930 l_Event_Status ad_event_versions.status%TYPE;
931 l_Worker_Status_String ad_event_versions.worker_status%TYPE;
932 l_Num_Workers ad_event_versions.num_workers%TYPE;
933 l_Return_Status BOOLEAN;
934 l_Event_Version ad_event_versions.version%TYPE;
935 -- this variable is used to hold in the IN parameter
936 l_p_Context ad_events.event_context%TYPE ;
937
938 BEGIN
939 --
940 -- The below block checks the value of parameter p_Contect
941 -- if the value is null, it is assumed that the value is default value.
942 -- the change was done for file.sql.35
943 --
944 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
945
946 --
947 -- The user can specify either a Minimum version or a Specific version
948 -- only. Both should not be used in the same call.
949 -- Raise an error if both are specified.
950 --
951 IF ( p_Min_Version IS NOT NULL AND p_Specific_Version IS NOT NULL ) THEN
952 RAISE_APPLICATION_ERROR(-20010,'Minimum version and Specific version '||
953 'should not be used together.');
954 END IF ;
955
956 --
957 -- Select the details of the event based on the input details.
958 -- Since the event id is available in AD_EVENTS only AD_EVENTS and
959 -- AD_EVENT_VERSIONS are joined on this column.
960 -- If a specific version is supplied use that version in the where
961 -- clause otherwise use the last_version column of ad_events.
962 --
963 SELECT av.status,
964 av.worker_status,
965 av.version,
966 av.num_workers
967 INTO l_Event_Status,
968 l_Worker_Status_String,
969 l_Event_Version,
970 l_Num_Workers
971 FROM ad_events ae,
972 ad_event_versions av
973 WHERE ae.owner = p_Owner
974 AND ae.event_name = p_Event_Name
975 AND ae.event_context = l_p_Context
976 AND av.event_id = ae.event_id
977 AND av.version = NVL(p_Specific_Version,ae.last_version);
978
979 --
980 -- If the current version of the event is less than
981 -- the min version required, return FLASE.
982 --
983 IF ( l_Event_Version < NVL(p_Min_Version, l_Event_Version) ) THEN
984 RETURN FALSE;
985 END IF;
986
987 --
988 -- If the event status is completed return TRUE
989 -- for both multi-worker and single worker events.
990 --
991 IF ( l_Event_Status = COMPLETED_STATUS ) THEN -- if event completed
992 l_Return_Status := TRUE;
993 ELSE -- else event completed
994 IF ( NVL(l_Num_Workers,1) > 1 ) THEN -- if num workers
995 --
996 -- if number of workers does not match in the current run and
997 -- prior run, return FALSE irrespective of the individual
998 -- worker status.
999 --
1000 IF ( p_Worker_Id IS NOT NULL
1001 AND NVL(p_Num_Workers, 1) = NVL(l_Num_Workers, 1)
1002 AND SUBSTR(l_Worker_Status_String,p_Worker_Id,1) = 'Y' ) THEN
1003 -- If worker done
1004 --
1005 -- The control comes here when the number of workers is > 1
1006 -- and the the corresponding worker is completed.
1007 -- worker status 'Y' indicated that worker is completed.
1008 --
1009 l_Return_Status := TRUE;
1010 ELSE -- else worker done
1011 l_Return_Status := FALSE;
1012 END IF; -- end if worker done
1013 ELSE -- else num workers
1014 --
1015 -- The control comes here when the event is not completed and
1016 -- the number of workers is 1.
1017 -- So return false.
1018 --
1019 l_Return_Status := FALSE;
1020 END IF; -- end if num workers
1021 END IF; -- end if event completed
1022
1023 RETURN l_Return_Status;
1024
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 RETURN FALSE;
1028 END Is_Event_Done ;
1029
1030 --------------------------------------------------------------------------
1031
1032 PROCEDURE Assert_Event(
1033 p_Owner IN VARCHAR2 ,
1034 p_Event_Name IN VARCHAR2 ,
1035 p_Context IN VARCHAR2 := NULL ,
1036 p_Min_Version IN VARCHAR2 := NULL ,
1037 p_Specific_Version IN VARCHAR2 := NULL )
1038 IS
1039 v_Event_Status BOOLEAN ;
1040 -- this variable is used to hold in the IN parameter
1041 l_p_Context ad_events.event_context%TYPE ;
1042 BEGIN
1043 --
1044 -- The below block checks the value of parameter p_Contect
1045 -- if the value is null, it is assumed that the value is default value.
1046 -- the change was done for file.sql.35
1047 --
1048 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
1049 --
1050 -- Is_Event_Done function is called to check whether the event is done.
1054 p_Owner,
1051 -- If not raise an error.
1052 --
1053 v_Event_Status := Is_Event_Done(
1055 p_Event_Name,
1056 l_p_Context,
1057 p_Min_Version,
1058 p_Specific_Version,
1059 NULL,
1060 NULL );
1061 IF ( v_Event_Status = FALSE ) THEN
1062 RAISE_APPLICATION_ERROR(-20007,'The event is not completed');
1063 END IF ;
1064 END Assert_Event ;
1065
1066 --------------------------------------------------------------------------
1067
1068 FUNCTION Get_Event_Status(
1069 p_Owner IN VARCHAR2 ,
1070 p_Event_Name IN VARCHAR2 ,
1071 p_Context IN VARCHAR2 := NULL ,
1072 p_Min_Version IN NUMBER := NULL ,
1073 p_Specific_Version IN NUMBER := NULL )
1074 RETURN VARCHAR2
1075 IS
1076 l_Status ad_event_versions.status%TYPE;
1077 l_Event_Version ad_event_versions.version%TYPE;
1078 -- this variable is used to hold in the IN parameter
1079 l_p_Context ad_events.event_context%TYPE ;
1080 BEGIN
1081 --
1082 -- The below block checks the value of parameter p_Contect
1083 -- if the value is null, it is assumed that the value is default value.
1084 -- the change was done for file.sql.35
1085 --
1086 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
1087
1088 --
1089 -- The user can specify either a Minimum version or a Specific version
1090 -- only. Both should not be used in the same call.
1091 -- Raise an error if both are specified.
1092 --
1093 IF ( p_Min_Version IS NOT NULL AND p_Specific_Version IS NOT NULL ) THEN
1094 RAISE_APPLICATION_ERROR(-20010,'Minimum version and Specific version '||
1095 'should not be used together.');
1096 END IF ;
1097
1098 --
1099 -- Select the details from the db.
1100 -- If a specific version is supplied then select the details foe the same.
1101 -- Otherwise select the details of the version pointed by AD_EVENTS.
1102 --
1103 SELECT av.status,
1104 av.version
1105 INTO l_Status,
1106 l_Event_Version
1107 FROM
1108 ad_events ae,
1109 ad_event_versions av
1110 WHERE ae.owner = p_Owner
1111 AND ae.event_name = p_Event_Name
1112 AND ae.event_context = l_p_Context
1113 AND av.event_id = ae.event_id
1114 AND av.version = NVL(p_Specific_Version, ae.last_version);
1115
1116 --
1117 -- If the minimum version of the event required is greater than the
1118 -- current version, then return EVENT_NOT_DEFINED.
1119 -- Else return the current status.
1120 --
1121 IF ( l_Event_Version < p_Min_Version ) THEN
1122 RETURN EVENT_NOT_DEFINED;
1123 ELSE
1124 RETURN l_Status;
1125 END IF;
1126 EXCEPTION
1127 WHEN NO_DATA_FOUND THEN
1128 RETURN EVENT_NOT_DEFINED;
1129 END Get_Event_Status ;
1130
1131 --------------------------------------------------------------------------
1132
1133 PROCEDURE Set_Event_Status(
1134 p_Owner IN VARCHAR2,
1135 p_Event_Name IN VARCHAR2,
1136 p_Status IN VARCHAR2,
1137 p_Context IN VARCHAR2 := NULL )
1138 IS
1139 l_Event_Status ad_event_versions.status%TYPE;
1140 l_Num_Workers ad_event_versions.num_workers%TYPE;
1141 l_Event_Id ad_event_versions.event_id%TYPE;
1142 l_Version ad_event_versions.version%TYPE;
1143 l_Event_Index NUMBER ;
1144 l_Event_Type ad_events.event_type%TYPE ;
1145 l_Module_Name ad_event_transitions.module_name%TYPE ;
1146 l_Worker_Id ad_event_transitions.worker_id%TYPE ;
1147 -- this variable is used to hold in the IN parameter
1148 l_p_Context ad_events.event_context%TYPE ;
1149 BEGIN
1150 --
1151 -- The below block checks the value of parameter p_Contect
1152 -- if the value is null, it is assumed that the value is default value.
1153 -- the change was done for file.sql.35
1154 --
1155 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
1156
1157 IF ( p_Status = COMPLETED_STATUS ) THEN
1158 RAISE_APPLICATION_ERROR(-20010,'Set_Event_Status cannot be used to '||
1159 'complete an Event');
1160 END IF;
1161
1162 --
1163 -- The following call checks whether the event is initialized
1164 -- and compares the global variable values with the values in
1165 -- the current call also.
1166 --
1167 Validate_Globals(p_Owner,
1168 p_Event_Name,
1169 l_p_Context);
1170 --
1171 -- Now get the index value for the current event from the global
1172 -- event list
1173 --
1174 l_Event_Index := Get_Event_Index( p_Owner,
1175 p_Event_Name,
1176 l_p_Context);
1177
1178 --
1179 -- now get the details from the global events table
1180 --
1181 l_Event_Type := g_Events_Tbl(l_Event_Index).Event_Type;
1182 l_Event_Status := g_Events_Tbl(l_Event_Index).Event_Status;
1186 l_Module_Name := g_Events_Tbl(l_Event_Index).Module_Name;
1183 l_Num_Workers := g_Events_Tbl(l_Event_Index).Max_Workers;
1184 l_Event_Id := g_Events_Tbl(l_Event_Index).Event_Id;
1185 l_Version := g_Events_Tbl(l_Event_Index).Event_Version;
1187 l_Worker_Id := g_Events_Tbl(l_Event_Index).Worker_Id;
1188
1189 --
1190 -- If the type of the event is boolean raise an error.
1191 -- Because for boolean events there are no intermediate states to be set.
1192 --
1193 IF ( l_Event_Type = BOOLEAN_TYPE ) THEN
1194 RAISE_APPLICATION_ERROR (-20010,'Set_Event_Status not applicable to '||
1195 'boolean events.');
1196 END IF;
1197
1198 --
1199 -- This block checks whether the event is a multi-worker event.
1200 --
1201 IF ( NVL(l_Num_Workers,1) > 1 ) THEN
1202 RAISE_APPLICATION_ERROR (-20010,'Set_Event_Status not applicable to '||
1203 'multi-worker events.');
1204 END IF ;
1205
1206 --
1207 -- If already the status is completed return error.
1208 --
1209 IF ( l_Event_Status = COMPLETED_STATUS ) THEN
1210 RAISE_APPLICATION_ERROR(-20010,'Event is Already Completed');
1211 END IF;
1212
1213 --
1214 -- If number of workers is greater then 1 raise an error.
1215 --
1216 IF ( nvl(l_Num_Workers,1) > 1 ) THEN
1217 RAISE_APPLICATION_ERROR (-20010,'Set_Event_Status not applicable to '||
1218 'multi-worker events.');
1219 END IF;
1220
1221 -- Request for the lock
1222 Request_Lock(p_Owner,
1223 p_Event_Name);
1224 --
1225 -- Validations are thru. Now update the status.
1226 --
1227 UPDATE ad_event_versions
1228 SET status = p_Status,
1229 last_update_date = SYSDATE
1230 WHERE event_id = l_Event_Id
1231 AND version = l_Version;
1232
1233 --
1234 -- Insert the event transition entries
1235 --
1236 Insert_Event_Transitions(
1237 p_Event_Id => l_Event_Id,
1238 p_Version => l_Version,
1239 p_New_Status => p_Status,
1240 p_Old_Status => l_Event_Status,
1241 p_Module_Name => l_Module_Name,
1242 p_Worker_Id => l_Worker_Id);
1243
1244 --Set the global values here
1245 g_Events_Tbl(l_Event_Index).Event_Status := p_Status;
1246
1247 -- commit the transaction here
1248 COMMIT;
1249 -- Release the lock.
1250 AD_LOCK_UTILS_PKG.Release_Lock(
1251 p_LockHandle => g_Event_LockHandle);
1252
1253 g_Event_LockHandle := NULL ;
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 IF ( g_Event_LockHandle IS NOT NULL ) THEN
1258 BEGIN -- begin for release lock
1259 AD_LOCK_UTILS_PKG.Release_Lock(
1260 p_LockHandle => g_Event_LockHandle);
1261 g_Event_LockHandle := NULL ;
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 NULL ;
1265 END ; -- end for release lock
1266 END IF ;
1267 RAISE ;
1268 END Set_Event_Status ;
1269
1270
1271 --------------------------------------------------------------------------
1272
1273 PROCEDURE Reset_Event(
1274 p_Owner IN VARCHAR2 ,
1275 p_Event_Name IN VARCHAR2 ,
1276 p_Module_Name IN VARCHAR2 ,
1277 p_Context IN VARCHAR2 := NULL )
1278 IS
1279 l_Event_Id ad_event_versions.event_id%TYPE;
1280 l_Event_Version ad_event_versions.version%TYPE;
1281 l_Event_Status ad_event_versions.status%TYPE;
1282 l_Max_Workers ad_event_versions.num_workers%TYPE ;
1283 l_Worker_Status_String ad_event_versions.worker_status%TYPE;
1284 l_Worker_Id ad_event_transitions.worker_id%TYPE ;
1285 l_Module_Name ad_event_transitions.module_name%TYPE ;
1286 l_Event_Index NUMBER ;
1287 -- this variable is used to hold in the IN parameter
1288 l_p_Context ad_events.event_context%TYPE ;
1289
1290 BEGIN -- begin procedure
1291 --
1292 -- The below block checks the value of parameter p_Contect
1293 -- if the value is null, it is assumed that the value is default value.
1294 -- the change was done for file.sql.35
1295 --
1296 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
1297
1298 l_Module_Name := p_Module_Name;
1299 l_Worker_Id := NULL ;
1300
1301 --
1302 -- Request the lock.
1303 --
1304 Request_Lock(p_Owner,
1305 p_Event_Name);
1306
1307 --
1308 -- Select the event id and the version from AD_EVENTS.
1309 --
1310 SELECT ae.event_id,
1311 ae.last_version,
1312 av.status,
1313 av.num_workers
1314 INTO l_Event_Id,
1315 l_Event_Version,
1316 l_Event_Status,
1317 l_Max_Workers
1318 FROM ad_events ae, ad_event_versions av
1319 WHERE ae.owner = p_Owner
1320 AND ae.event_name = p_Event_Name
1321 AND ae.event_context = l_p_Context
1322 AND av.event_id = ae.event_id
1323 AND av.version = ae.last_version;
1327 'Initialized status, Not possible to reset');
1324
1325 IF ( l_Event_Status = INITIALIZED_STATUS ) THEN
1326 RAISE_APPLICATION_ERROR ( -20010,'The event is already in '||
1328 END IF ;
1329 --
1330 -- The validations are through, now reset the event information.
1331 --
1332 -- Form the worker status string
1333 l_Worker_Status_String := Initialize_Worker_String(l_Max_Workers);
1334
1335 UPDATE ad_event_versions
1336 SET status = INITIALIZED_STATUS,
1337 start_time = NULL ,
1338 end_time = NULL ,
1339 last_update_date = SYSDATE ,
1340 worker_status = LPAD('N',l_Max_Workers,'N')
1341 WHERE event_id = l_Event_Id
1342 AND version = l_Event_Version;
1343
1344 -- Insert the event transition entry.
1345 Insert_Event_Transitions(
1346 p_Event_Id => l_Event_Id,
1347 p_Version => l_Event_Version,
1348 p_New_Status => INITIALIZED_STATUS,
1349 p_Old_Status => l_Event_Status,
1350 p_Module_Name => l_Module_Name,
1351 p_Worker_Id => l_Worker_Id);
1352
1353 -- Commit the transaction here.
1354 COMMIT;
1355
1356 --
1357 -- Release the lock.
1358 --
1359 AD_LOCK_UTILS_PKG.Release_Lock(
1360 p_LockHandle => g_Event_LockHandle);
1361 g_Event_LockHandle := NULL ;
1362
1363 -- Get the index pointing to the event from the global event list
1364 -- This step will be useful in case an event is reset in the same
1365 -- session which has initialized the event.
1366 l_Event_Index := Get_Event_Index(p_Owner,
1367 p_Event_Name,
1368 l_p_Context);
1369 -- If the event is initialized in the session the index value
1370 -- will be non-zero.
1371 IF l_Event_Index <> 0 THEN
1372 -- Set the package global event list values
1373 g_Events_Tbl(l_Event_Index).Event_Status := INITIALIZED_STATUS;
1374 g_Events_Tbl(l_Event_Index).Worker_Status := LPAD('N',l_Max_Workers,'N');
1375 END IF ;
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 IF ( g_Event_LockHandle IS NOT NULL ) THEN
1379 BEGIN -- begin for release lock
1380 AD_LOCK_UTILS_PKG.Release_Lock(
1381 p_LockHandle => g_Event_LockHandle);
1382 g_Event_LockHandle := NULL ;
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 NULL ;
1386 END ; -- end for release lock
1387 END IF ;
1388 IF SQLCODE = 100 THEN
1389 RAISE_APPLICATION_ERROR(-20010,'Event not registered. '||
1390 'Not possible to reset.');
1391 ELSE
1392 RAISE ;
1393 END IF ;
1394 END Reset_Event ;
1395
1396 --------------------------------------------------------------------------
1397
1398 PROCEDURE Set_Event_As_Done(
1399 p_Owner IN VARCHAR2 ,
1400 p_Event_Name IN VARCHAR2 ,
1401 p_Module_Name IN VARCHAR2 ,
1402 p_Context IN VARCHAR2 := NULL ,
1403 p_Event_Type IN VARCHAR2 := NULL ,
1404 p_Version IN NUMBER := NULL ,
1405 p_Worker_Id IN NUMBER := NULL ,
1406 p_Num_Workers IN NUMBER := NULL )
1407 IS
1408 l_Event_Id ad_events.event_id%TYPE;
1409 l_Event_Version ad_event_versions.version%TYPE ;
1410 l_Event_Status ad_event_versions.status%TYPE ;
1411 l_New_Status ad_event_versions.status%TYPE ;
1412 l_Worker_Status_String ad_event_versions.worker_status%TYPE ;
1413 l_End_Time ad_event_versions.end_time%TYPE ;
1414 l_Worker_Id ad_event_transitions.worker_id%TYPE ;
1415 l_Prev_Max_Workers ad_event_versions.num_workers%TYPE ;
1416 l_Commit_Flag BOOLEAN ;
1417 l_event_completed_EXP EXCEPTION ;
1418 -- these variables are used to hold in the IN parameter
1419 l_p_Context ad_events.event_context%TYPE ;
1420 l_p_Event_Type ad_events.event_type%TYPE ;
1421 l_p_Version ad_events.last_version%TYPE ;
1422 BEGIN
1423 --
1424 -- The below block checks the value of parameter p_Contect
1425 -- if the value is null, it is assumed that the value is default value.
1426 -- the change was done for file.sql.35
1427 --
1428 l_p_Context := nvl(p_Context, DEFAULT_CONTEXT);
1429 l_p_Event_Type := nvl(p_Event_Type, BOOLEAN_TYPE);
1430 l_p_Version := nvl(p_Version, DEFAULT_VERSION);
1431
1432 l_Worker_id := p_Worker_Id;
1433 --
1434 -- Call the procedure for validating the parameters
1435 --
1436 Validate_Parameters(
1437 p_Owner => p_Owner ,
1438 p_Event_Name => p_Event_Name ,
1439 p_Event_Type => l_p_Event_Type,
1440 p_Num_Workers => p_Num_Workers);
1441
1442 -- Get the lock
1443 Request_Lock(p_Owner,
1444 p_Event_Name);
1445 BEGIN -- block 1
1446 --
1447 -- Select the details of the event from the registry.
1448 --
1449 SELECT status,
1450 ae.event_id,
1451 worker_status,
1452 num_workers,
1453 av.version
1454 INTO l_Event_Status,
1455 l_Event_Id,
1459 FROM ad_events ae, ad_event_versions av
1456 l_Worker_Status_String,
1457 l_Prev_Max_Workers,
1458 l_Event_Version
1460 WHERE ae.event_id = av.event_id
1461 AND ae.last_version = av.version
1462 AND ae.owner = p_Owner
1463 AND ae.event_name = p_Event_Name
1464 AND ae.event_context = l_p_Context;
1465
1466 --
1467 -- If there is an event in the db with the same version as that
1468 -- of the one being registered, do the following.
1469 --
1470 IF ( l_Event_Version = l_p_Version ) THEN -- if version check
1471 --
1472 -- If the event in the db is already in completed status
1473 --
1474 IF ( l_Event_Status = COMPLETED_STATUS ) THEN
1475 RAISE l_event_completed_EXP;
1476 END IF ;
1477 --
1478 -- Control goes to the following block, when
1479 -- the event is already registered, and there is a change in the
1480 -- number of workers.
1481 -- Here we compare the number of workers in this call with the number
1482 -- of workers from the registry. If there is any change we assume that
1483 -- the event is re-scheduled among a different set of workers.
1484 -- So, the worker details in ad_event_versions has to be updated
1485 -- and a special transition entry has to be inserted into
1486 -- ad_event_transitions.
1487 --
1488 IF ( NVL (l_Prev_Max_Workers,1) <> NVL (p_Num_Workers,1) ) THEN
1489 -- # if resch check
1490 -- Set the commit flag
1491 l_Commit_Flag := TRUE ;
1492
1493 -- Inserting a record into the transition record to show that
1494 -- a rescheduling has happened for the event.
1495 Insert_Event_Transitions(
1496 p_Event_Id => l_Event_Id,
1497 p_Version => l_p_Version,
1498 p_New_Status => 'RE-SCHEDULED',
1499 p_Old_Status => 'RE-SCHEDULED',
1500 p_Module_Name => p_Module_Name,
1501 p_Worker_Id => p_Worker_Id);
1502
1503 IF ( NVL (p_Num_Workers,1) > 1 ) THEN -- if # resch_worker
1504 --
1505 -- The control comes here when the event is re-scheduled
1506 -- and the number of workers is more than 1. So we have to form
1507 -- the worker status string, and the event status will be NULL.
1508 --
1509 -- form the worker status string
1510 l_Worker_Status_String := LPAD('N',p_Num_Workers,'N');
1511 --
1512 -- Form the new status string
1513 --
1514 Set_Worker_Status_String(l_Worker_Id, l_Worker_Status_String);
1515
1516 l_New_Status := NULL ;
1517 ELSE -- if # resch_worker ( number of workers = 1)
1518 --
1519 -- If the number of workers after the re-scheduling is 1, the
1520 -- worker status string will be null. And immediately we can
1521 -- say that the event has completed, so the event status will
1522 -- become COMPLETED_STATUS.
1523 --
1524 l_Worker_Status_String := NULL ;
1525 l_New_Status := COMPLETED_STATUS;
1526 l_End_Time := SYSDATE ;
1527 --
1528 -- Since the event has completed here, we have to insert an
1529 -- event transition entry as below.
1530 --
1531 Insert_Event_Transitions(
1532 p_Event_Id => l_Event_Id,
1533 p_Version => l_p_Version,
1534 p_New_Status => l_New_Status,
1538
1535 p_Old_Status => 'RE-SCHEDULED',
1536 p_Module_Name => p_Module_Name,
1537 p_Worker_Id => p_Worker_Id);
1539 END IF ; -- if # resch_worker
1540 --
1541 -- Now update ad_Event_Versions to make the changes happened as a
1542 -- result of the re-scheduling.
1543 --
1544 UPDATE ad_event_versions
1545 SET num_workers = p_Num_Workers,
1546 status = l_New_Status,
1547 worker_status = l_Worker_Status_String,
1548 start_time = DECODE(start_time,null,SYSDATE,
1549 start_time),
1550 last_update_date = SYSDATE ,
1551 end_time = l_End_Time
1552 WHERE event_id = l_Event_Id
1553 AND version = l_p_Version;
1554
1555 ELSE -- re-schedule check
1556 --
1557 -- If the event is multi-worker, then do the following.
1558 -- Multi-worker events require all the workers to complete for the
1559 -- event to get completed.
1560 -- So whenever a worker calls this API the corresponding character
1561 -- position in the worker status string will be updated. When the
1562 -- string is updated to all Y's the event status will be updated to
1563 -- completed.
1564 -- The control comes to this block only from the second call to this
1565 -- procedure. i.e from the second worker only. During the first call
1566 -- there wont be any event of the particular name and it will be
1567 -- caught in the exception handling section below.
1568 --
1569 IF ( NVL(p_Num_Workers,1) > 1 ) THEN -- if num workers
1570
1571 l_Commit_Flag := TRUE ;
1572
1573 --
1574 -- Form the new status string
1575 --
1576 Set_Worker_Status_String(l_Worker_Id, l_Worker_Status_String);
1577
1578 --
1579 -- If all the workers have completed update the event status to
1580 -- completed and the event end time to the current time.
1581 --
1582 IF ( INSTR(l_Worker_Status_String,'N') = 0 ) THEN
1583 l_New_Status := COMPLETED_STATUS;
1584 l_End_Time := SYSDATE;
1585 END IF;
1586
1587 --
1588 -- Update ad_event_versions with the new status
1589 --
1590 UPDATE ad_event_versions
1591 SET status = l_New_Status,
1592 end_time = l_End_Time,
1593 last_update_date = SYSDATE ,
1594 start_time = DECODE(start_time,null,SYSDATE,
1595 start_time),
1596 worker_status = l_Worker_Status_String
1597 WHERE event_id = l_Event_Id
1598 AND version = l_p_Version;
1599
1600 --
1601 -- The below block is for inserting the event transition records
1602 -- for multi-worker events.
1603 -- The transition records will be inserted only when the event is
1604 -- completed. In case of multi-worker events, Event gets completed
1605 -- only when all the workers are completed.
1606 --
1607 IF ( l_New_Status = COMPLETED_STATUS )THEN -- if for completed status
1608 Insert_Event_Transitions(
1609 p_Event_Id => l_Event_Id,
1610 p_Version => l_p_Version,
1611 p_New_Status => COMPLETED_STATUS,
1612 p_Old_Status => COMPLETED_STATUS,
1613 p_Module_Name => p_Module_Name,
1614 p_Worker_Id => p_Worker_Id);
1615
1616 END IF; -- end if for completed status
1617
1618 ELSE -- else for num workers
1619 --
1620 -- Here the number of workers is 1, the event is already registered
1621 -- and the status is something other than COMPLETED_STATUS
1622 -- This is for fixing bug no : 4260836
1623 --
1624 IF ( l_Event_Status <> COMPLETED_STATUS ) THEN
1628 --
1625 l_Commit_Flag := TRUE ;
1626 --
1627 -- Mark the event as completed here
1629 UPDATE ad_event_versions
1630 SET status = COMPLETED_STATUS,
1631 start_time = DECODE(start_time,null,SYSDATE,
1632 start_time),
1633 end_time = SYSDATE ,
1634 last_update_date = SYSDATE
1635 WHERE event_id = l_Event_Id
1636 AND version = l_p_Version;
1637 --
1638 -- Insert the transition entries here
1639 --
1640 Insert_Event_Transitions(
1641 p_Event_Id => l_Event_Id,
1642 p_Version => l_p_Version,
1643 p_New_Status => COMPLETED_STATUS,
1644 p_Old_Status => l_Event_Status,
1645 p_Module_Name => p_Module_Name);
1646 ELSE
1647 RAISE l_event_completed_EXP;
1648 END IF ;
1649 END IF ; -- end if num workers.
1650
1651 END IF ; -- re-schedule check
1652
1653 ELSIF ( l_Event_Version < l_p_Version ) THEN -- else version check
1654 --
1655 -- The following is a multi-worker event.
1656 -- Here an existing event with a higher version is being registered
1657 -- This is the first worker registering it. So set a new worker status
1658 -- string.
1659 --
1660 IF ( NVL(p_Num_Workers,1) > 1 ) THEN -- if num workers 2
1661 l_Worker_Status_String := LPAD('N',p_Num_Workers,'N');
1662 Set_Worker_Status_String(l_Worker_Id, l_Worker_Status_String);
1663
1664 ELSE
1665 l_Event_Status := COMPLETED_STATUS;
1666 l_New_Status := COMPLETED_STATUS;
1667 l_End_Time := SYSDATE ;
1668 l_Commit_Flag := TRUE ;
1669
1670 -- Insert the transition details.
1671 Insert_Event_Transitions(
1672 p_Event_Id => l_Event_Id,
1673 p_Version => l_p_Version,
1674 p_New_Status => l_New_Status,
1675 p_Old_Status => l_Event_Status,
1676 p_Module_Name => p_Module_Name,
1677 p_Worker_Id => p_Worker_Id);
1678
1679 END IF ; -- end if num workers 2
1680 --
1681 -- The control comes here when the version being registered is
1682 -- greater than the latest version and the event is single-worker.
1683 --
1684 l_Commit_Flag := TRUE ;
1685
1686 UPDATE ad_events
1687 SET last_version = l_p_Version,
1688 last_update_date = SYSDATE
1692 INSERT INTO ad_event_versions (
1689 WHERE event_id = l_Event_Id;
1690
1691 -- Insert the version details.
1693 event_id,
1694 version,
1695 status,
1696 start_time,
1697 end_time,
1698 creation_date,
1699 last_update_date,
1700 num_workers,
1701 worker_status)
1702 VALUES (
1703 l_Event_Id,
1704 l_p_Version,
1705 l_New_Status,
1706 SYSDATE ,
1707 l_End_Time ,
1708 SYSDATE ,
1709 SYSDATE ,
1710 p_Num_Workers ,
1711 l_Worker_Status_String );
1712 END IF ; -- end if Version check
1713
1714 EXCEPTION -- exceptions for block 1
1715
1716 WHEN l_event_completed_EXP THEN
1717 --
1718 -- It was decided to do nothing for this exception.
1719 --
1720 NULL ;
1721 WHEN NO_DATA_FOUND THEN
1722
1723 l_Commit_Flag := TRUE ;
1724
1725 -- For new events control comes here.
1726 INSERT INTO ad_events(
1727 event_id,
1728 owner,
1729 event_name,
1730 event_context,
1731 event_type,
1732 last_version,
1733 creation_date,
1734 last_update_date)
1735 VALUES(
1736 ad_events_s.NEXTVAL,
1737 p_Owner,
1738 p_Event_Name,
1739 l_p_Context,
1740 l_p_Event_Type,
1741 l_p_Version,
1742 SYSDATE,
1743 SYSDATE )
1744 RETURNING event_id
1745 INTO l_Event_Id;
1746 --
1747 -- If there is only a single worker it means that the event
1748 -- is completed here itself. So set the status as completed.
1749 --
1750 IF ( NVL(p_Num_Workers,1) > 1 ) THEN -- if num-workers
1751 --
1752 -- Form the worker status string for a multi worker event.
1753 -- The event status will remain as NULL here .
1754 -- The subsequent workers whoever is updating the event has
1755 -- to take care of updating the event status.
1756 --
1757 l_Worker_Status_String := LPAD('N',p_Num_Workers,'N');
1758 Set_Worker_Status_String(l_Worker_Id, l_Worker_Status_String);
1759
1760 ELSE -- else num-workers
1761 -- The control comes here for single-worker events.
1762 -- For single-worker event the status can be put as completed
1763 l_Event_Status := COMPLETED_STATUS;
1764 -- The end time also can be put as the current time.
1765 l_End_Time := SYSDATE ;
1766
1767 --
1768 -- Insert the transition records for single-worker events.
1769 --
1770
1771 Insert_Event_Transitions(
1772 p_Event_Id => l_Event_Id,
1773 p_Version => l_p_Version,
1774 p_New_Status => COMPLETED_STATUS,
1775 p_Old_Status => COMPLETED_STATUS,
1776 p_Module_Name => p_Module_Name,
1777 p_Worker_Id => p_Worker_Id);
1778
1779 END IF ; -- end if num-workers
1780 --
1781 -- Insert the event version details here
1782 -- This insertion is done for both single-worker and multi-worker cases
1783 -- but only for the first time.
1784 --
1785 INSERT INTO ad_event_versions(
1786 event_id,
1787 version,
1788 status,
1789 start_time,
1790 end_time,
1791 creation_date,
1792 last_update_date,
1793 num_workers,
1794 worker_status)
1795 VALUES(
1796 l_Event_Id,
1797 l_p_Version,
1798 l_Event_Status,
1799 SYSDATE,
1800 l_End_Time,
1801 SYSDATE,
1802 SYSDATE,
1803 p_Num_Workers,
1804 l_Worker_Status_String);
1805 END ; -- block 1
1806 --
1807 -- If the commit flag is set commit the txn here
1808 --
1809 IF ( l_Commit_Flag = TRUE ) THEN
1810 COMMIT ;
1811 END IF ;
1812 --
1813 -- Release the lock
1814 --
1815 AD_LOCK_UTILS_PKG.Release_Lock(
1816 p_LockHandle => g_Event_LockHandle);
1817 g_Event_LockHandle := NULL ;
1818
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 IF ( g_Event_LockHandle IS NOT NULL ) THEN
1822 BEGIN -- begin for release lock
1823 AD_LOCK_UTILS_PKG.Release_Lock(
1824 p_LockHandle => g_Event_LockHandle);
1825 g_Event_LockHandle := NULL ;
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 NULL ;
1829 END ; -- end for release lock
1830 END IF ;
1831 RAISE ;
1832 END Set_Event_As_Done ;
1833 --------------------------------------------------------------------------
1834
1835 END Ad_Event_Registry_Pkg;
[Home] [Help]