1 package body ad_patch_history_pkg as
2 /* $Header: adphmntb.pls 115.5 2002/12/11 23:10:57 wjenkins ship $ */
3
4 --
5 --
6 -- Private procedures
7 --
8
9 --
10 /*
11 NAME:
12 Procedure bld_execs_and_copy_acts_list():
13
14 DESCRIPTION:
15 Inserts into AD_PTCH_HST_EXE_COP_TMP records of 2 kinds (viz. executed
16 copy actions and all non-copy actions) in patches that have been uploaded
17 from applptch.txt
18
19 COLUMNS THAT MAY NEED EXPLANATION:
20 - ACTION: 1 for "copy" actions, 2 otherwise
21 - FILE_VERSION: Relevant only for "executed" "copy" actions . Holds the
22 patch-file-version
23 - MAX-COPY-DATE: Holds the run-date of the latest "copy" action on that
24 file in that appl-top, prior to (attempting to) running the action
25 in question.
26
27 LOGIC: For a particular action on a file in a specific appl-top (source),
28 we walk back in time starting from a some action, till we get a copy
29 action for that file on that appl-top (target). The version there (on
30 the target) can then be said to have been the "onsite" version when the
31 original action (the source) must have been attempted to be executed.
32
33 NOTE: The only reason dynamic sql is used is bcoz PL/SQL wouldn't let me use
34 analytic functions otherwise (an analytic windowing function is used
35 here, whose start point is fixed and the end point is the row just
36 prior to the current-row)
37
38 CAUTION: The windowing function has 2 implications related to MAX_COPY_DATE:
39 1. If the FIRST (ie. when walked back all the way to the beginning) patch
40 had 2 executed "copy" actions for the same file, then the first one
41 would have a null MAX_COPY_DATE (bcoz there is no "prior" record to walk
42 back to), and the second one would have the RUN-DATE of the first stored
43 in its MAX_COPY_DATE. But the RUN-DATE of both are the same (since they
44 are part of the same patch) Truly, we need to skip such cases. Its difficult
45 if not impossible to do it as part the single SQL, hence we delegate the
46 task of skipping such records to the caller.
47
48 2. On subsequent uploads of applptch.txt (ie. other than the first upload.
49 In other words, say if uploaded from an applptch.txt that was created bcoz
50 a patch was run in pre-install mode), the FIRST action in the temp table
51 is not truly the first. There could be prior executed "copy" actions for
52 that file in prior normal mode patches. But the walk-back logic would not
53 look at those. As a result, the on-site version during some actions in
54 patches applied in pre-install mode may not be known. This is a
55 limitation, and is hoped NOT to be a severe one.
56 */
57 --
58 procedure bld_execs_and_copy_acts_list
59 (p_min_run_date date)
60 is
61 l_str varchar2(2000);
62 begin
63 l_str := 'insert into ad_ptch_hst_exe_cop_tmp '||
64 '( '||
65 'row_id, action, appl_top_id, run_date, '||
66 'file_id, file_version_id, max_copy_date '||
67 ') '||
68 'select '||
69 'prba.rowid, '||
70 'decode(pca.action_code, ''copy'', 1, 2), '||
71 'pr.appl_top_id, '||
72 'pr.end_date, '||
73 'nvl(pca.loader_data_file_id, prba.file_id), '||
74 'decode(pca.action_code, ''copy'', decode(prba.executed_flag, '||
75 '''Y'', prba.patch_file_version_id, null), '||
76 'null), '||
77 'max(decode(pca.action_code, ''copy'', decode(prba.executed_flag, '||
78 '''Y'', pr.end_date, to_date(''01/01/1990'',''MM/DD/YYYY'')), '||
79 'to_date(''01/01/1990'',''MM/DD/YYYY''))) over '||
80 '(partition by nvl(pca.loader_data_file_id, prba.file_id), '||
81 'pr.appl_top_id '||
82 'order by pr.end_date, '||
83 'decode(pca.action_code, ''copy'', 1, 2) '||
84 'rows between unbounded preceding and 1 preceding) '||
85 'from ad_patch_runs pr, '||
86 'ad_patch_run_bugs prb, '||
87 'ad_files f, '||
88 'ad_patch_common_actions pca, '||
89 'ad_patch_run_bug_actions prba '||
90 'where prba.common_action_id = pca.common_action_id '||
91 'and prba.file_id = f.file_id '||
92 'and prba.patch_run_bug_id = prb.patch_run_bug_id '||
93 'and prb.patch_run_id = pr.patch_run_id '||
94 -- only look at rows uploaded from applptch.txt
95 'and pr.source_code = ''TXT'' '||
96 -- in sync with the actions considered in
97 -- adpvovGetFSVerAndAddToHash() (but excl fcopy)
98 'and pca.action_code in (''copy'', ''libin'', ''forcecopy'', '||
99 '''genfpll'', ''genform'', ''genrpll'', '||
100 '''genrep'', ''genmenu'', '||
101 '''sql'', ''exec'', ''exectier'') '||
102 -- If its a loader call but the parsing logic of
103 -- adpfilExtractFileFromArgs() failed to identify the file-id, skip it
104 'and not (pca.loader_data_file_id is null and f.filename in ( '||
105 '''FNDLOAD'', ''WFLOAD'', ''akload.class'')) '||
106 -- Only consider (1) exec(tier) calls of the ODF and loader
107 -- kinds, and (2) actions other than exec(tier).
108 'and ( '||
109 '(pca.action_code in (''exec'', ''exectier'') and '||
110 '(pca.loader_data_file_id is not null or '||
111 'pca.action_what_sql_exec like ''odf%'') '||
112 ') '||
113 'or '||
114 'pca.action_code not in (''exec'', ''exectier'') '||
115 ') '||
116 -- To allow multiple runs (incremental)
117 'and prba.onsite_file_version_id is null';
118
119 if p_min_run_date is not null then
120 l_str := l_str || ' and pr.end_date >= :min_end_date';
121
122 execute immediate l_str using p_min_run_date;
123 else
124 execute immediate l_str;
125 end if;
126
127 end bld_execs_and_copy_acts_list;
128
129
130 --
131 --
132 -- Public procedures
133 --
134
135 procedure backfill_onsite_versions
136 (p_min_run_date date)
137 is
138 cursor c_actions is
139 select rowidtochar(row_id)
140 from ad_ptch_hst_exe_cop_tmp
141 where max_copy_date is not null
142 and (action <> 1 or max_copy_date <> run_date);
143
144 type T_ROWID is varray(1000) of varchar2(18);
145 l_arr_size constant number := 1000;
146
147 l_rowid T_ROWID;
148
149 l_cur_fetch number := 0;
150 l_prev_fetch number := 0;
151 l_row number;
152 u number := 0;
153 begin
154 -- First, build the temp area
155
156 bld_execs_and_copy_acts_list(p_min_run_date);
157
158 -- Commit, to free up rollback segs
159
160 commit;
161
162 -- Now do the actual update
163
164 open c_actions;
165
166 l_prev_fetch := 0;
167
168 <<one_iter_per_limit_fetch>>
169 loop
170 fetch c_actions bulk collect into l_rowid limit l_arr_size;
171
172 l_cur_fetch := c_actions%rowcount - l_prev_fetch;
173 l_prev_fetch := c_actions%rowcount;
174
175 forall l_row in 1..l_cur_fetch
176 update ad_patch_run_bug_actions prba
177 set prba.onsite_file_version_id =
178 (
179 select
180 /*+ USE_NL(E)
181 INDEX (AD_PTCH_HST_EXE_COP_TMP AD_PTCH_HST_EXE_COP_TMP_U1)
182 INDEX(AD_PTCH_HST_EXE_COP_TMP AD_PTCH_HST_EXE_COP_TMP_N1)
183 */
184 distinct c.file_version_id
185 -- The "distinct" isn't really necessary. The values should
186 -- be the same. The distinct is just to throw up an error
187 -- if they aren't the same.
188 from ad_ptch_hst_exe_cop_tmp c, ad_ptch_hst_exe_cop_tmp e
189 where e.row_id = chartorowid(l_rowid(l_row))
190 and e.appl_top_id = c.appl_top_id
191 and e.file_id = c.file_id
192 and c.action = 1
193 and e.max_copy_date = c.run_date
194 )
195 where prba.rowid = chartorowid(l_rowid(l_row))
196 and prba.onsite_file_version_id is null;
197
198 exit when c_actions%notfound;
199 end loop one_iter_per_limit_fetch;
200
201 close c_actions;
202
203 --
204 commit;
205 --
206 end backfill_onsite_versions;
207
208
209 /*
210 NAME:
211 Procedure bld_cf_repos_using_upload_hist():
212
213 DESCRIPTION:
214 Inserts into AD_CHECK_FILES using information uploaded from applptch.txt.
215 Does something ONLY if the checkfile repository is empty AND is some patch
216 history information exists.
217
218 NOTE: The only reason dynamic sql is used is bcoz PL/SQL wouldn't let me use
219 analytic functions otherwise (an analytic ranking function is used here)
220 */
221 --
222 procedure bld_cf_repos_using_upload_hist
223 (anything_inserted out nocopy number)
224 is
225 dummy varchar2(1);
226 begin
227 anything_inserted := 0;
228
229 select 'x'
230 into dummy
231 from dual
232 where not exists (select 'x' from ad_check_files)
233 and exists (select 'x' from ad_patch_runs);
234 --
235 --
236 execute immediate 'insert into ad_check_files '||
237 '( '||
238 'check_file_id, file_id, '||
239 'file_version_id, distinguisher, '||
240 'creation_date '||
241 ') '||
242 'select '||
243 'ad_check_files_s.nextval, max_vers.file_id, '||
244 'max_vers.file_version_id, max_vers.distinguisher, '||
245 'sysdate '||
246 'from '||
247 '( '||
248 'select '||
249 'nvl(pca.loader_data_file_id, prba.file_id) file_id, '||
250 'fv.file_version_id file_version_id, '||
251 'decode(pca.action_code, ''sql'', null, '||
252 'decode(substr(pca.action_what_sql_exec, 1, 3), ''odf'', '||
253 'decode(substr(pca.action_phase, 1, '||
254 'decode(instr(pca.action_phase, ''+''), 0, '||
255 'length(pca.action_phase), '||
256 'instr(pca.action_phase, ''+'')-1)), '||
257 '''tab'', ''tab_tables'', '||
258 '''seq'', ''seq_sequences'', '||
259 '''vw'', ''vw_views'', '||
260 'null), '||
261 'null)) distinguisher, '||
262 'rank() over (partition by nvl(pca.loader_data_file_id, '||
263 'prba.file_id), '||
264 'decode(pca.action_code, ''sql'', null, '||
265 'decode(substr(pca.action_what_sql_exec, 1, 3), ''odf'', '||
266 'decode(substr(pca.action_phase, 1, '||
267 'decode(instr(pca.action_phase, ''+''), 0, '||
268 'length(pca.action_phase), '||
269 'instr(pca.action_phase, ''+'')-1)), '||
270 '''tab'', ''tab_tables'', '||
271 '''seq'', ''seq_sequences'', '||
272 '''vw'', ''vw_views'', '||
273 'null), '||
274 'null)) '||
275 'order by fv.version_segment1 desc, '||
276 'fv.version_segment2 desc, fv.version_segment3 desc, '||
277 'fv.version_segment4 desc, fv.version_segment5 desc, '||
278 'fv.version_segment6 desc, fv.version_segment7 desc, '||
279 'fv.version_segment8 desc, fv.version_segment9 desc, '||
280 'fv.version_segment10 desc, '||
281 'fv.translation_level desc) as rank1 '||
282 'from '||
283 'ad_file_versions fv, '||
284 'ad_patch_runs pr, '||
285 'ad_patch_run_bugs prb, '||
286 'ad_patch_common_actions pca, '||
287 'ad_patch_run_bug_actions prba '||
288 'where prba.common_action_id = pca.common_action_id '||
289 'and prba.patch_run_bug_id = prb.patch_run_bug_id '||
290 'and prb.patch_run_id = pr.patch_run_id '||
291 'and prba.onsite_file_version_id = fv.file_version_id '||
292 'and pca.action_code in (''sql'', ''exec'', ''exectier'') '||
293 'and prba.executed_flag = ''Y'' '||
294 'and pr.source_code = ''TXT'' '||
295 'group by '||
296 -- The group-by is necessary to suppress repeated entries if the
297 -- SAME version of the SAME file was (executed) in different
298 -- patches (or in different bugs in the same patch)
299 'nvl(pca.loader_data_file_id, prba.file_id), fv.file_version_id, '||
300 'decode(pca.action_code, ''sql'', null, '||
301 'decode(substr(pca.action_what_sql_exec, 1, 3), ''odf'', '||
302 'decode(substr(pca.action_phase, 1, '||
303 'decode(instr(pca.action_phase, ''+''), 0, '||
304 'length(pca.action_phase), '||
308 '''vw'', ''vw_views'', '||
305 'instr(pca.action_phase, ''+'')-1)), '||
306 '''tab'', ''tab_tables'', '||
307 '''seq'', ''seq_sequences'', '||
309 'null), '||
310 'null)), '||
311 'fv.version_segment1, fv.version_segment2, fv.version_segment3, '||
312 'fv.version_segment4, fv.version_segment5, fv.version_segment6, '||
313 'fv.version_segment7, fv.version_segment8, fv.version_segment9, '||
314 'fv.version_segment10, fv.translation_level '||
315 ') max_vers '||
316 'where max_vers.rank1 = 1';
317 --
318 anything_inserted := sql%rowcount;
319 --
320 commit;
321 --
322 --
323 exception when no_data_found then
324 -- If ad_check_files already has data, or patch-hist is NOT
325 -- populated, simply return.
326 --
327 null;
328 end bld_cf_repos_using_upload_hist;
329
330
331 end ad_patch_history_pkg;
[Home] [Help]