1 PACKAGE BODY ad_patch_analysis_engine
2 /* $Header: adpaengb.pls 120.10.12010000.2 2008/12/05 11:37:34 mkumandu ship $ */
3 AS
4
5 ----------------------------------------------------------------------------
6 -- Declaration of the global variables.
7 ----------------------------------------------------------------------------
8
9 ghashLevel typeHashVarchar;
10 ghashIfUsed typeHashVarchar;
11 ghashIfLoad typeHashVarchar;
12
13 ghashLevelIntr typeHashVarchar;
14 ghashBaselineIntr typeHashVarchar;
15 ghashRequires typeHashVarchar;
16
17
18 ----------------------------------------------------------------------------
19 -- Procedure to print the usage of this package.
20 -- This intializes the following
21 -- * Caching of CodeLevels on customer instance
22 -- * Caching of Baselines on customer instance
23 -- * Caching of IfLoad on customer instance
24 -- * Caching of IfUsed on customer instance
25 ----------------------------------------------------------------------------
26
27 PROCEDURE usage
28 IS
29
30 BEGIN
31 debugPrint ('--------------------------------------------------');
32 debugPrint ('------------Compare 2 Codelevels------------------');
33 debugPrint ('--------------------------------------------------');
34 debugPrint (' ad_patch_analysis_engine.compareLevel(<codelevel1>,<codelevel2>);');
35 debugPrint ('--------------------------------------------------');
36 debugPrint ('------------ Run the Analysis Engine ------------------');
37 debugPrint ('--------------------------------------------------');
38 debugPrint (' ad_patch_analysis_engine.getPatchStatus( '
39 ||'<bug_number>,'
40 ||'<baseline>,'
41 ||'<release>,'
42 ||'<OUT error_message>'
43 ||');');
44 debugPrint ('RETURNS patch_status ');
45 debugPrint ('--------------------------------------------------');
46 debugPrint ('----- Run the Analysis Engine and Update database------');
47 debugPrint ('--------------------------------------------------');
48 debugPrint (' ad_patch_analysis_engine.getPatchStatus( '
49 ||'<bug_number>,'
50 ||'<baseline>,'
51 ||'<release>,'
52 ||'<OUT error_message>,'
53 ||'<analysis_run_id>,'
54 ||'<user_id>,'
55 ||'<overwrite_database>'
56 ||');');
57 debugPrint ('RETURNS patch_status ');
58
59 END;
60
61 ----------------------------------------------------------------------------
62 -- Function to print messages on console
63 ----------------------------------------------------------------------------
64 PROCEDURE debugPrint
65 ( p_message IN VARCHAR2
66 )
67 IS
68 BEGIN
69 NULL;
70 -- DBMS_OUTPUT.put_line (p_message);
71 END;
72
73
74
75 ----------------------------------------------------------------------------
76 -- Get the value from desired hash
77 ----------------------------------------------------------------------------
78 FUNCTION getValueFromHash
79 ( p_key IN VARCHAR2,
80 p_hash IN typeHashVarchar
81 )
82 RETURN VARCHAR2
83 IS
84 l_Value VARCHAR2(50);
85 BEGIN
86
87 l_Value := p_hash(p_key);
88
89 RETURN l_Value;
90
91 EXCEPTION
92
93 WHEN OTHERS THEN
94 RETURN NULL;
95
96 END;
97
98
99
100 ----------------------------------------------------------------------------
101 -- Procedure to initialize the global variables.
102 -- This intializes the following
103 -- * Caching of CodeLevels on customer instance
104 -- * Caching of Baselines on customer instance
105 -- * Caching of IfLoad on customer instance
106 -- * Caching of IfUsed on customer instance
107 ----------------------------------------------------------------------------
108
109 PROCEDURE initialize
110 IS
111
112 BEGIN
113 FOR rec IN
114 (SELECT
115 abbreviation abbr,
116 NVL(used_flag, 'Y') used_flag,
117 NVL(load_flag, 'N') load_flag,
118 codelevel te_level
119 FROM ad_trackable_entities)
120 LOOP
121 ghashLevel(rec.abbr) := rec.te_level;
122 ghashIfUsed(rec.abbr) := rec.used_flag;
123 ghashIfLoad(rec.abbr) := rec.load_flag;
124
125
126 END LOOP;
127 debugPrint ('ghashLevel: '|| ghashLevel.COUNT || ' rows defined.');
128 debugPrint ('ghashIfUsed: '|| ghashIfUsed.COUNT || ' rows defined.');
129 debugPrint ('ghashIfLoad: '|| ghashIfLoad.COUNT || ' rows defined.');
130
131 ghashLevelIntr.DELETE;
132 ghashBaselineIntr.DELETE;
133 ghashRequires.DELETE;
134
135 END;
136
137 ----------------------------------------------------------------------------
138 -- Function to compare the the 2 inputs codelevels.
139 -- Currently assuming the codelevels are numeric.
140 -- Return 1 if (input1 > input2),
141 -- 2 if (input1 < input2)
142 -- 0 if (input1 = input2)
143 -- -1 in case of Error
144 ----------------------------------------------------------------------------
145 FUNCTION compareLevel
146 ( p_level_1 IN VARCHAR2 DEFAULT '',
147 p_level_2 IN VARCHAR2 DEFAULT ''
148 )
149 RETURN NUMBER
150 IS
151 TYPE arrVar IS TABLE OF VARCHAR2(10);
152 l_n NUMBER := 0;
153 l_str VARCHAR2(100);
154 -- As specified in the doc max decimal places can be 10. So initializing the
155 -- table with 10 values of 0 each.
156 l_arrLevel_1 arrVar := arrVar('0','0','0','0','0','0','0','0','0','0');
157 l_arrLevel_2 arrVar := arrVar('0','0','0','0','0','0','0','0','0','0');
158 l_arrLevel_1_size NUMBER := 0;
159 l_arrLevel_2_size NUMBER := 0;
160 BEGIN
161
162 -- Compare if the two levels are same (string comparison)
163 IF (p_level_1 = p_level_2) THEN
164 RETURN 0;
165 END IF;
166
167 -- Storing all the decimal values for Level 1 in plsql table
168 -- Also calculate the real size of the table.
169 -- If input level is empty replace with 0.
170 CASE
171 WHEN length(p_level_1) = 0 THEN l_str := '0';
172 WHEN length(p_level_1) > 0 THEN l_str := LOWER(p_level_1);
173 ELSE l_str := '0';
174 END CASE;
175
176 LOOP
177 l_n := instr(l_str, '.' );
178 IF ( length(l_str) > 0 AND l_n = 0) THEN
179 l_arrLevel_1_size := l_arrLevel_1_size +1;
180 l_arrLevel_1(l_arrLevel_1_size) := l_str;
181 END IF;
182 EXIT WHEN (nvl(l_n,0) = 0);
183 l_arrLevel_1_size := l_arrLevel_1_size +1;
184 l_arrLevel_1(l_arrLevel_1_size) := substr(l_str, 1, l_n-1);
185 l_str := ltrim( substr( l_str, l_n+1 ) );
186 END LOOP;
187
188 -- Storing all the decimal values for Level 2 in plsql table
189 -- Also calculate the real size of the table.
190 -- If input level is empty replace with 0.
191 CASE
192 WHEN length(p_level_2) = 0 THEN l_str := '0';
193 WHEN length(p_level_2) > 0 THEN l_str := LOWER(p_level_2);
194 ELSE l_str := '0';
195 END CASE;
196
197 LOOP
198 l_n := instr(l_str, '.' );
199 IF ( length(l_str) > 0 AND l_n = 0) THEN
200 l_arrLevel_2_size := l_arrLevel_2_size +1;
201 l_arrLevel_2(l_arrLevel_2_size) := l_str;
202 END IF;
203 EXIT WHEN (nvl(l_n,0) = 0);
204 l_arrLevel_2_size := l_arrLevel_2_size +1;
205 l_arrLevel_2(l_arrLevel_2_size) := substr(l_str, 1, l_n-1);
206 l_str := ltrim( substr( l_str, l_n+1 ) );
207 END LOOP;
208
209 --debugPrint(' 1::::'|| l_arrLevel_1_size);
210 --debugPrint(' 2::::'|| l_arrLevel_2_size);
211
212
213 -- Compare the each decimal value of one codelevel with the corresponding
214 -- decimal level of other codelevel for the same place. If one is
215 DECLARE
216 l_tmpSize NUMBER := 0;
217 l_tmpFlg NUMBER := 0;
218 l_tmpVar VARCHAR2(10);
219 l_value_1 VARCHAR2(10);
220 l_value_2 VARCHAR2(10);
221
222 BEGIN
223 IF(l_arrLevel_1_size >= l_arrLevel_2_size) THEN
224 l_tmpSize := l_arrLevel_1_size;
225 ELSE
226 l_tmpSize := l_arrLevel_2_size;
227 END IF;
228
229 --debugPrint(' '|| l_tmpSize);
230
231 -- Loop for each of the decimal places
232 FOR i IN 1..l_tmpSize LOOP
233 BEGIN
234 l_value_1 := l_arrLevel_1(i);
235 l_value_2 := l_arrLevel_2(i);
236
237 -- remove the keyword 'delta' before processing
238 l_value_1 := REPLACE(l_value_1,'delta','');
239 l_value_2 := REPLACE(l_value_2,'delta','');
240
241 -- Check that the first segment for both the levels should match.
242 -- If not return error
243 -- ababkuma 26-JUN-2006 Bug#5357093 commented the code below
244 -- IF(i = 1 AND l_value_1 <> l_value_2) THEN
245 -- RETURN -1;
246 -- END IF;
247
248 -- debugPrint(l_value_1||' '||l_value_2);
249 -- In loop when the segments are same then no action.
250 -- if one is greater than then return the index of the higher level
251 -- The segments 2 onwards could be varchar as well as number.
252 CASE
253 WHEN (TO_NUMBER(l_value_1) = TO_NUMBER(l_value_2) ) THEN NULL;
254 WHEN (TO_NUMBER(l_value_1) > TO_NUMBER(l_value_2) ) THEN RETURN 1;
255 WHEN (TO_NUMBER(l_value_1) < TO_NUMBER(l_value_2) ) THEN RETURN 2;
256 END CASE;
257 EXCEPTION
258 WHEN VALUE_ERROR THEN
259 SELECT GREATEST(l_value_1, l_value_2) INTO l_tmpVar FROM DUAL;
260 CASE
261 WHEN ((l_tmpVar = l_value_2) AND (l_tmpVar = l_value_1)) THEN NULL;
262 WHEN (l_tmpVar = l_value_1) THEN RETURN 1;
263 WHEN (l_tmpVar = l_value_2) THEN RETURN 2;
264 END CASE;
265 END;
266 END LOOP;
267 END;
268
269 CASE
270 WHEN length(p_level_1) = 0 THEN RETURN 2;
271 WHEN length(p_level_1) IS NULL THEN RETURN 2;
272 WHEN length(p_level_2) = 0 THEN RETURN 1;
273 WHEN length(p_level_2) IS NULL THEN RETURN 1;
274 ELSE RETURN 0;
275 END CASE;
276 EXCEPTION
277 WHEN OTHERS THEN
278 RETURN -1;
279 END;
280
281 ----------------------------------------------------------------------------
282 -- Procedure to add the pre-req found to the global pre-req associative array.
283 -- This also validates if the pre-req is
284 -- * present on customer's instance
285 -- * present in the pre-req associative array
286 ----------------------------------------------------------------------------
287 PROCEDURE addPrereq
288 ( p_te_abbr IN VARCHAR2 ,
289 p_te_level IN VARCHAR2 ,
290 p_hashRequires IN OUT NOCOPY typeHashVarchar
291 )
292 IS
293 l_te_level VARCHAR2(50);
294 BEGIN
295
296 -- Check level in ghashLevel for TE_NAME
297 IF (ghashLevel.EXISTS(p_te_abbr)) THEN
298 -- check level from hashRequires for TE_NAME
299 IF (p_hashRequires.EXISTS(p_te_abbr)) THEN
300 IF (compareLevel(p_te_level, p_hashRequires(p_te_abbr)) = 1) THEN
301 -- add to hashPrereq TE_NAME, TE_LEVEL
302 p_hashRequires(p_te_abbr) := p_te_level;
303 -- debugPrint (p_te_abbr||' '||p_te_level );
304 END IF;
305 ELSE
306 IF (compareLevel(p_te_level, ghashLevel(p_te_abbr)) = 1 ) THEN
307 -- add to hashPrereq TE_NAME, TE_LEVEL
308 p_hashRequires(p_te_abbr) := p_te_level;
309 -- debugPrint (p_te_abbr||' '||p_te_level );
310 END IF;
311 END IF;
312 ELSE
313 -- get level from hashPrereq for TE_NAME
314 IF (p_hashRequires.EXISTS(p_te_abbr)) THEN
315 IF ( compareLevel(p_te_level, p_hashRequires(p_te_abbr)) = 1) THEN
316 p_hashRequires(p_te_abbr) := p_te_level;
317 -- debugPrint (p_te_abbr||' '||p_te_level );
318 END IF;
319 ELSE
320 p_hashRequires(p_te_abbr) := p_te_level;
321 -- debugPrint (p_te_abbr||' '||p_te_level );
322 END IF;
323 END IF;
324 END;
325
326
327 ----------------------------------------------------------------------------
328 -- Procedure to determine the status of the patch asked by user and also
329 -- determines the reasons for output status.
330 -- This procedure is exposed to user.
331 ----------------------------------------------------------------------------
332
333 FUNCTION getPatchStatus (
334 p_bug_number IN NUMBER,
335 p_baseline IN VARCHAR2,
336 p_release IN VARCHAR2,
337 p_err_message OUT NOCOPY VARCHAR2,
338 p_analysis_run_id IN NUMBER,
339 p_user_id IN NUMBER,
340 p_overwrite IN BOOLEAN DEFAULT FALSE
341 )
342 RETURN VARCHAR2 IS
343 l_analysis_run_bug_id NUMBER := 0;
344 l_tmpCnt NUMBER := 0;
345 l_tmpVar VARCHAR2(30);
346 l_patch_status VARCHAR2(100) := 'ERROR';
347 l_tmpLevel VARCHAR2(30) := NULL;
348 l_tmpBaseline VARCHAR2(30) := NULL;
349 BEGIN
350
351 debugPrint ('Start - Calling pre-req');
352 l_patch_status := getPatchStatus (p_bug_number, p_baseline, p_release, p_err_message);
353 debugPrint ('End - Pre-req is ');
354
355 -- Perform the insertion in the Patch Wizard tables if analysis_id is specified
356 IF (p_analysis_run_id > 0) THEN
357 SELECT analysis_run_bug_id INTO l_analysis_run_bug_id
358 FROM ad_pa_analysis_run_bugs
359 WHERE analysis_run_id = p_analysis_run_id
360 AND bug_number = p_bug_number
361 AND baseline = p_baseline;
362
366 WHERE analysis_run_id = p_analysis_run_id
363 IF (l_analysis_run_bug_id > 0) THEN
364 UPDATE ad_pa_analysis_run_bugs
365 SET analysis_status = l_patch_status
367 AND bug_number = p_bug_number
368 AND baseline = p_baseline;
369
370 -- Insert the pre-req data in ad_pa_anal_run_bug_prereqs
371 debugPrint ('PREREQ CNT:'|| ghashRequires.COUNT);
372
373 IF(ghashRequires.COUNT > 0) THEN
374 l_tmpVar := ghashRequires.FIRST; -- get subscript of first element
375 WHILE l_tmpVar IS NOT NULL LOOP
376 debugPrint ('Pre-req is '|| l_tmpVar ||' Level: '|| ghashRequires(l_tmpVar));
377
378 IF (p_overwrite) THEN
379 DELETE FROM ad_pa_anal_run_bug_prereqs
380 WHERE analysis_run_bug_id = l_analysis_run_bug_id
381 AND prereq_te_abbr = l_tmpVar;
382 END IF;
383
384 INSERT INTO ad_pa_anal_run_bug_prereqs
385 (analysis_run_bug_id, prereq_te_abbr, prereq_te_level,
386 created_by, creation_date, last_updated_by , last_update_date)
387 SELECT l_analysis_run_bug_id,
388 l_tmpVar ,
389 ghashRequires(l_tmpVar),
390 p_user_id,
391 sysdate,
392 p_user_id,
393 sysdate
394 FROM DUAL
395 WHERE NOT EXISTS
396 ( SELECT 'x' FROM ad_pa_anal_run_bug_prereqs
397 WHERE analysis_run_bug_id = l_analysis_run_bug_id
398 AND prereq_te_abbr = l_tmpVar
399 );
400
401 l_tmpVar := ghashRequires.NEXT(l_tmpVar);
402 END LOOP;
403 END IF;
404
405 -- Insert the introducing codelevel/baseline data in ad_pa_anal_run_bug_codelevels
406 IF (ghashLevelIntr.COUNT > 0) THEN
407 debugPrint ('Introducing :'|| ghashLevelIntr.COUNT );
408 l_tmpVar := ghashLevelIntr.FIRST; -- get subscript of first element
409
410 WHILE l_tmpVar IS NOT NULL LOOP
411 debugPrint ('Introducing for '|| l_tmpVar || ' Level: '|| getValueFromHash(l_tmpVar, ghashLevelIntr)||' Baseline:'||getValueFromHash(l_tmpVar, ghashBaselineIntr) );
412
413 IF (p_overwrite) THEN
414 DELETE FROM ad_pa_anal_run_bug_codelevels
415 WHERE analysis_run_bug_id = l_analysis_run_bug_id
416 AND intr_te_abbr = l_tmpVar;
417 END IF;
418
419 l_tmpLevel := getValueFromHash(l_tmpVar, ghashLevelIntr);
420 l_tmpBaseline := getValueFromHash(l_tmpVar, ghashBaselineIntr);
421
422 INSERT INTO ad_pa_anal_run_bug_codelevels
423 (analysis_run_bug_id, intr_te_abbr, intr_te_level,
424 intr_te_baseline, intr_te_type,
425 created_by, creation_date, last_updated_by , last_update_date)
426 SELECT l_analysis_run_bug_id,
427 l_tmpVar ,
428 l_tmpLevel,
429 l_tmpBaseline,
430 null,
431 p_user_id,
432 sysdate,
433 p_user_id,
434 sysdate
435 FROM DUAL
436 WHERE NOT EXISTS
437 ( SELECT 'x' FROM ad_pa_anal_run_bug_codelevels
438 WHERE analysis_run_bug_id = l_analysis_run_bug_id
439 AND intr_te_abbr = l_tmpVar
440 );
441
442 l_tmpVar := ghashLevelIntr.NEXT(l_tmpVar);
443 END LOOP;
444 END IF;
445
446 COMMIT;
447
448 END IF;
449 END IF;
450
451 RETURN l_patch_status;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 ROLLBACK;
456 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
457 RETURN l_patch_status;
458
459 END;
460
461 ----------------------------------------------------------------------------
462 -- Procedure to determine the status of the patch asked by user and also
463 -- determines the reasons for output status.
464 -- This procedure is exposed to user.
465 ----------------------------------------------------------------------------
466
467 FUNCTION getPatchStatus (
468 p_bug_number IN NUMBER,
469 p_baseline IN VARCHAR2,
470 p_release IN VARCHAR2,
471 p_err_message OUT NOCOPY VARCHAR2
472 )
473 RETURN VARCHAR2 IS
474 l_patch_status VARCHAR2(100) := 'READY';
475 l_patch_type VARCHAR2(100) :='';
476 l_count NUMBER := 0;
477 l_patch_id NUMBER := 0;
478 l_te_abbr VARCHAR2(30) ;
479 l_te_baseline VARCHAR2(30) ;
480
481 BEGIN
482
483 debugPrint ('Calling Initiallize.... ');
484 initialize();
485 debugPrint ('End Calling Initiallize.... ');
486
487 -- Check for the existence of BUG_NUMBER, BASELINE in Snapshot tables for global snapshot.
488 -- If found then set output status as 'APPLIED'
489 -- msailoz bug#5505349
490 -- If baseline is not provided or patch is baseline independent only search
491 -- for the existence of bug_number in AD_BUGS
492
496 FROM ad_snapshot_bugfixes
493 IF ( length(trim(p_baseline)) = 0 OR p_baseline = 'R12') THEN
494
495 SELECT count(SNAPSHOT_BUG_ID) INTO l_count
497 WHERE bugfix_id in
498 (SELECT bug_id FROM AD_BUGS
499 WHERE bug_number = to_char(p_bug_number)
500 AND ARU_RELEASE_NAME = p_release)
501 AND snapshot_id =
502 (SELECT snapshot_id
503 FROM ad_snapshots
504 WHERE snapshot_name = 'GLOBAL_VIEW'
505 and snapshot_type = 'G');
506 ELSE
507
508 SELECT count(SNAPSHOT_BUG_ID) INTO l_count
509 FROM ad_snapshot_bugfixes
510 WHERE bugfix_id in
511 (SELECT bug_id FROM AD_BUGS
512 WHERE bug_number = to_char(p_bug_number)
513 AND baseline_name = p_baseline
514 AND ARU_RELEASE_NAME = p_release)
515 AND snapshot_id =
516 (SELECT snapshot_id
517 FROM ad_snapshots
518 WHERE snapshot_name = 'GLOBAL_VIEW'
519 and snapshot_type = 'G');
520 END IF;
521
522 debugPrint ('Count'|| l_count);
523
524 IF (l_count > 0) THEN
525 l_patch_status := 'APPLIED';
526 RETURN l_patch_status;
527 ELSE
528 debugPrint ('No records found in Snapshot');
529 NULL;
530 END IF;
531
532 -- Check whether this patch has been uploaded
533 debugPrint ('... Checking uploading of patch');
534 DECLARE
535 l_upload_id NUMBER := 0;
536 BEGIN
537
538 SELECT entity_abbr,
539 patch_id,
540 NVL(upload_run_id,-1),
541 NVL(patch_type,'')
542 INTO l_te_abbr,
543 l_patch_id,
544 l_upload_id,
545 l_patch_type
546 FROM ad_pm_patches
547 WHERE bug_number = p_bug_number
548 AND baseline = p_baseline;
549
550 --- 09-Aug-2006 ababkuma bug#5357552 handling obsoleted patch
551 IF (l_patch_id = 0 ) THEN
552 l_patch_status := 'ERROR';
553 p_err_message := 'PATCH NOT UPLOADED';
554 RETURN l_patch_status;
555 ELSIF (l_upload_id <= 0 ) THEN
556 l_patch_status := 'ERROR';
557 p_err_message := 'PATCH METADATA NOT UPLOADED';
558 RETURN l_patch_status;
559 ELSIF (l_patch_type = 'obsoleted' ) THEN
560 l_patch_status := 'OBSOLETED';
561 p_err_message := 'PATCH IS OBSOLETED';
562 RETURN l_patch_status;
563 END IF;
564
565 EXCEPTION
566 WHEN no_data_found THEN
567 l_patch_status := 'ERROR';
568 p_err_message := 'PATCH NOT UPLOADED';
569 RETURN l_patch_status;
570 END;
571
572 -- Compare the baseline of patch with that on customer instance
573 debugPrint ('... Checking uploading of patch');
574
575 -- if the patch is generic patch then no need of comparing with baseline on customer instance
576 IF (p_baseline <> 'R12' AND LENGTH(TRIM(p_baseline)) > 0) THEN
577 BEGIN
578
579 SELECT baseline
580 INTO l_te_baseline
581 FROM ad_trackable_entities
582 WHERE abbreviation = l_te_abbr;
583
584 -- if the patch is not generic and doesn't match with the baseline
585 -- on customer instance then error out
586 IF (compareLevel(l_te_baseline, p_baseline) <> 0 ) THEN
587 l_patch_status := 'ERROR';
588 p_err_message := 'PATCH BASELINE NOT MATCHING';
589 END IF;
590
591 EXCEPTION
592 WHEN no_data_found THEN
593 l_patch_status := 'READY';
594 END;
595 END IF;
596
597 -- Check all the baselines introduced are higher or equal to that on the customer instance.
598 -- Also mark the new CodeLevels introduced
599 DECLARE
600 l_tmpVar VARCHAR2(100) := NULL;
601 BEGIN
602 debugPrint ('... Checking levels of patch');
603 FOR rec IN
604 (SELECT appei.te_abbr,
605 nvl(ate.baseline,0) curr_baseline,
606 nvl(appei.baseline,0) intr_baseline,
607 nvl(ate.codelevel,0) curr_level,
608 nvl(appei.te_level,0) intr_level
609 FROM ad_pa_patch_entity_info appei,
610 ad_trackable_entities ate
611 WHERE appei.te_abbr = ate.abbreviation(+)
612 AND appei.patch_id = l_patch_id)
613 LOOP
614 debugPrint (rec.te_abbr ||' BASELINE (current,intr) ('|| rec.curr_baseline||', '||rec.intr_baseline
615 ||') CODELEVEL (current,intr) ('|| rec.curr_level||', '|| rec.intr_level||')');
616 IF( compareLevel(rec.intr_baseline, rec.curr_baseline) <> 2) THEN
617 IF( compareLevel(rec.intr_level, rec.curr_level) = 1 ) THEN
618 ghashLevelIntr(rec.te_abbr) := rec.intr_level;
619 END IF;
620 IF( compareLevel(rec.intr_baseline, rec.curr_baseline) = 1 ) THEN
621 ghashBaselineIntr(rec.te_abbr) := rec.intr_level;
622 END IF;
623 END IF;
624 END LOOP;
625
626 -- IF(l_tmpStatus != NULL) THEN
627 -- l_patch_status := 'ERROR';
628 -- p_err_message := 'Found higher '||l_tmpStatus||' baselines than in Patch';
629 -- RETURN l_patch_status;
630 --END IF;
631
635 l_tmpVar := ghashLevelIntr.FIRST;
632 -- Store the introducing levels in output string
633 IF ( ghashLevelIntr.COUNT > 0) THEN
634 p_err_message := p_err_message || 'INTRODUCES ';
636 WHILE l_tmpVar IS NOT NULL LOOP
637 p_err_message := p_err_message || l_tmpVar ||':'|| ghashLevelIntr(l_tmpVar) ||' ';
638 l_tmpVar := ghashLevelIntr.NEXT(l_tmpVar);
639 END LOOP;
640 END IF;
641 END;
642
643 debugPrint ('New Levels Introduced: '|| ghashLevelIntr.COUNT );
644 debugPrint ('New Baselines Introduced: '|| ghashBaselineIntr.COUNT );
645
646
647 -- Check all the requires and conditional requires (PRE_REQUISITES )specified in patch driver.
648 DECLARE
649 l_tmpVar VARCHAR2(100) := NULL;
650 BEGIN
651 debugPrint ('... Checking Requires of patch');
652 debugPrint ('PATCH ID: '|| l_patch_id);
653 FOR rec IN
654 ( SELECT appri.patch_requires_id,
655 appri.te_abbr,
656 appri.requires_te_abbr,
657 appri.requires_te_level ,
658 appcri.condition_type,
659 appcri.condition_te_abbr,
660 appcri.condition_te_level
661 FROM ad_pa_patch_requires_info appri,
662 ad_pa_patch_cond_requires_info appcri
663 WHERE appri.patch_id = l_patch_id
664 AND appri.patch_requires_id = appcri.patch_requires_id (+) )
665
666 LOOP
667 IF (rec.condition_type IS NULL) THEN
668 addPrereq(rec.requires_te_abbr, rec.requires_te_level, ghashRequires);
669 ELSIF( UPPER(rec.condition_type) = 'IFUSED') THEN
670 IF( ghashIfUsed.EXISTS(rec.condition_te_abbr)) THEN
671 IF(ghashIfUsed(rec.condition_te_abbr) = 'Y'
672 AND (compareLevel(rec.condition_te_level, ghashLevel(rec.condition_te_abbr)) = 1
673 OR compareLevel(rec.condition_te_level, ghashLevel(rec.condition_te_abbr)) = 0 )
674 ) THEN
675
676 addPrereq(rec.requires_te_abbr, rec.requires_te_level, ghashRequires);
677 END IF;
678 END IF;
679 ELSIF( UPPER(rec.condition_type) = 'IFLOAD') THEN
680 IF(ghashIfLoad.EXISTS(rec.condition_te_abbr)) THEN
681 IF(ghashIfLoad(rec.condition_te_abbr) = 'Y'
682 AND (compareLevel(rec.condition_te_level, ghashLevel(rec.condition_te_abbr)) = 1
683 OR compareLevel(rec.condition_te_level, ghashLevel(rec.condition_te_abbr)) = 0 )
684 ) THEN
685 addPrereq(rec.requires_te_abbr, rec.requires_te_level, ghashRequires);
686 END IF;
687 END IF;
688 END IF;
689 END LOOP;
690
691 debugPrint ('Total Requires: '|| ghashRequires.COUNT );
692 -- Store the requires in output string
693 IF ( ghashRequires.COUNT > 0) THEN
694 l_patch_status := 'MISSING';
695 p_err_message := p_err_message || 'REQUIRES ';
696 l_tmpVar := ghashRequires.FIRST;
697 WHILE l_tmpVar IS NOT NULL LOOP
698 p_err_message := p_err_message || UPPER(l_tmpVar) ||'.'|| ghashRequires(l_tmpVar) ||' ';
699
700 l_tmpVar := ghashRequires.NEXT(l_tmpVar);
701 END LOOP;
702
703 RETURN l_patch_status;
704 END IF;
705 END;
706
707 RETURN l_patch_status;
708
709 END;
710
711 -- var p varchar2(100)
712 -- var c varchar2(100)
713 --- exec :p := ad_patch_analysis_engine.getPatchStatus(9000002,'R12','R12', :c);
714 --- exec :p := ad_patch_analysis_engine.getPatchStatus(7000002,'AD.1.0','R12', :c, 4652175, 4);
715 --- exec :p := ad_patch_analysis_engine.getPatchStatus(8000004,'R12','R12', :c);
716 --- exec :p := ad_patch_analysis_engine.compareLevel('AD.1.2','AD.1')
717 -- select ad_patch_analysis_engine.compareLevel('120.22','33') from dual
718
719 END ad_patch_analysis_engine;
[Home] [Help]