1 PACKAGE BODY BIS_BIA_RSG_LOG_MGMNT AS
2 /*$Header: BISBRLMB.pls 120.4 2006/05/09 13:47:43 aguwalan noship $*/
3
4 PROCEDURE WRITELOG(P_TEXT VARCHAR2)
5 IS
6 BEGIN
7 --FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, 'Bis.BIS_BIA_RSG_LOG_MGMNT', P_TEXT);
8 BIS_COLLECTION_UTILITIES.put_line(P_TEXT);
9 --- dbms_output.put_line(substr(P_TEXT,1,250));
10 END;
11
12 FUNCTION duration(
13 p_duration number) return VARCHAR2 IS
14 BEGIN
15 return(to_char(floor(p_duration)) ||' Days '||
16 to_char(mod(floor(p_duration*24), 24))||':'||
17 to_char(mod(floor(p_duration*24*60), 60))||':'||
18 to_char(mod(floor(p_duration*24*60*60), 60)));
19 END duration;
20
21 PROCEDURE setTimer(
22 p_log_timstamp in out NOCOPY date)
23 IS
24 BEGIN
25 p_log_timstamp := sysdate;
26 END;
27
28
29 PROCEDURE logTime(
30 p_process varchar2,
31 p_log_timstamp date)
32 IS
33 l_duration number := null;
34 BEGIN
35 l_duration := sysdate - p_log_timstamp;
36 WRITELOG('Process Time for '|| p_process || ' : ' || duration(l_duration));
37 END;
38
39 Function form_triplet( att1 varchar2,
40 att2 varchar2,
41 att3 varchar2 ) return varchar2
42 IS
43 l_result varchar2(32767) := '( ';
44 BEGIN
45 l_result := l_result || att1 || ', ' || att2 || ', ' || att3 || ') ';
46 return l_result;
47 END;
48
49
50 Function is_mv_log_mangmnt_enabled
51 return boolean
52 is
53 begin
54 IF (fnd_profile.value('BIS_BIA_MVLOG_ENABLE') = 'Y') THEN
55 return true;
56 ELSE
57 WRITELOG('Runtime BIA MV Log Management feature is off');
58 return false;
59 END IF;
60 end;
61
62
63 PROCEDURE MV_LOG_API (
64 ERRBUF OUT NOCOPY VARCHAR2,
65 RETCODE OUT NOCOPY VARCHAR2,
66 P_API IN VARCHAR2,
67 P_OBJ_NAME IN VARCHAR2,
68 P_OBJ_TYPE IN VARCHAR2,
69 P_MODE IN VARCHAR2
70 ) IS
71 l_parameter_tbl BIS_BIA_RSG_PARAMETER_TBL := BIS_BIA_RSG_PARAMETER_TBL();
72 BEGIN
73 BIS_BIA_RSG_CUSTOM_API_MGMNT.INVOKE_CUSTOM_API(
74 ERRBUF, RETCODE,
75 P_API, BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_LOG_MGT,
76 P_OBJ_NAME, P_OBJ_TYPE, P_MODE);
77
78 END MV_LOG_API;
79
80
81 procedure build_statement
82 (ddl_text in varchar2,
83 row_num in integer)
84 is
85 begin
86 apps_array_ddl.glprogtext(row_num) := ddl_text;
87 exception
88 when others then
89 WRITELOG('error in build_statement('||ddl_text||', '||row_num||')');
90 raise;
91 end build_statement;
92
93
94 procedure update_mv_log_sql(p_base_object_name in varchar2,
95 p_base_object_type in varchar2,
96 p_snapshot_log_sql in CLOB) is
97 begin
98 update bis_obj_properties set snapshot_log_sql= p_snapshot_log_sql
99 where object_name=p_base_object_name
100 and object_type=p_base_object_type;
101 commit;
102 exception
103 when others then
104 raise;
105 end;
106
107
108 procedure update_mv_log_status(p_base_object_name in varchar2,
109 p_base_object_type in varchar2,
110 p_status in varchar2) is
111 begin
112 update bis_obj_properties
113 set mv_log_status=p_status,
114 status_time_stamp=sysdate
115 where object_name=p_base_object_name
116 and object_type=p_base_object_type;
117 commit;
118 exception
119 when others then
120 raise;
121 end;
122
123 function get_mv_creation_date_dd (p_base_object_name in varchar2,p_base_object_type in varchar2,p_base_object_schema in varchar2) return date is
124 cursor c_mv_creation_date_in_DD is
125 SELECT created mv_creation_date_dd
126 FROM all_objects
127 WHERE owner=p_base_object_schema
128 and object_name = p_base_object_name
129 and object_type=decode(p_base_object_type,'TABLE','TABLE','MV','MATERIALIZED VIEW') ;
130 l_date date;
131 begin
132 open c_mv_creation_date_in_DD;
133 fetch c_mv_creation_date_in_DD into l_date;
134 close c_mv_creation_date_in_DD;
135 return l_date;
136 exception
137 when others then
138 raise;
139 end;
140
141 ---this API check if MV patch has been applied after
142 ---last time the MV log being captured/dropped/created
143 ----please note that this API can't handle MV logs attached to
144 ---base summary tables, which are currently managed by individual
145 ---product teams by calling BIA custom API
146
147 function check_obj_patch_applied(p_base_object_name in varchar2,p_base_object_type in varchar2) return varchar2 is
148
149 cursor c_mv_patch_applied is
150 SELECT af.filename filename
151 , MAX(acf.creation_date) file_creation_date
152 FROM ad_check_files acf
153 , ad_files af
154 WHERE af.file_id = acf.file_id
155 AND (
156 filename =p_base_object_name || '.xdf'
157 OR filename = LOWER(p_base_object_name) || '.xdf'
158 )
159 and acf.creation_date>
160 (select STATUS_TIME_STAMP
161 from bis_obj_properties
162 where object_name=upper(p_base_object_name)
163 and object_type=p_base_object_type)
164 GROUP BY af.filename;
165
166 cursor c_log_patch_applied is
167 SELECT af.filename filename
168 , MAX(acf.creation_date) file_creation_date
169 FROM ad_check_files acf
170 , ad_files af
171 WHERE af.file_id = acf.file_id
172 AND (
173 filename =upper(p_base_object_name)||'_MLOG' || '.xdf'
174 OR filename = LOWER(p_base_object_name)||'_mlog'|| '.xdf'
175 )
176 and acf.creation_date>
177 (select STATUS_TIME_STAMP
178 from bis_obj_properties
179 where object_name=upper(p_base_object_name)
180 and object_type=p_base_object_type)
181 GROUP BY af.filename;
182
183
184 l_file_name varchar2(100);
185 l_file_creation_date date;
186
187 begin
188 if p_base_object_type='MV' then
189 open c_mv_patch_applied;
190 fetch c_mv_patch_applied into l_file_name,l_file_creation_date;
191 close c_mv_patch_applied;
192 else --'TABLE'
193 open c_log_patch_applied;
194 fetch c_log_patch_applied into l_file_name,l_file_creation_date;
195 close c_log_patch_applied;
196 end if;
197
198
199 if l_file_name is not null and l_file_creation_date is not null then
200 WRITELOG('Found patch being applied. File Name: '||l_file_name||'. File creation date '||to_char(l_file_creation_date,'DD-MON-YYYY HH24:MI:SS'));
201 return 'Y';
202 else
203 return 'N';
204 end if;
205 exception
206 when others then
207 raise;
208 end;
209
210 ---this api check if MV has been recreated after last
211 ---time the MV log being captured
212 function check_obj_recreated (p_base_object_name in varchar2,p_base_object_type in varchar2,P_base_object_schema in varchar2) return varchar2 is
213
214 cursor c_mv_creation_date_in_rsg is
215 select OBJECT_CREATION_DATE
216 from bis_obj_properties
217 where object_name=p_base_object_name
218 and object_type=p_base_object_type;
219
220 l_mv_creation_date_dd date;
221 l_mv_creation_date_rsg date;
222
223 begin
224 l_mv_creation_date_dd:=get_mv_creation_date_dd(p_base_object_name,p_base_object_type,p_base_object_schema);
225
226 open c_mv_creation_date_in_rsg;
227 fetch c_mv_creation_date_in_rsg into l_mv_creation_date_rsg;
228 close c_mv_creation_date_in_rsg;
229
230 WRITELOG('MV creation date in data dictionary '||to_char(l_mv_creation_date_dd,'DD-MON-YYYY HH24:MI:SS'));
231 WRITELOG('MV creation date captured in RSG '||to_char(l_mv_creation_date_rsg,'DD-MON-YYYY HH24:MI:SS'));
232 if l_mv_creation_date_dd>l_mv_creation_date_rsg then
233 return 'Y';
234 else
235 return 'N';
236 end if;
237 exception
238 when others then
239 raise;
240 end;
241
242
243 procedure update_obj_creation_date(p_base_object_name in varchar2,p_base_object_type in varchar2,p_base_object_schema in varchar2) is
244
245 begin
246 update bis_obj_properties
247 set OBJECT_CREATION_DATE=get_mv_creation_date_dd(p_base_object_name,p_base_object_type,p_base_object_schema)
248 where object_type=p_base_object_type
249 and object_name=p_base_object_name;
250
251 exception
252 when others then
253 raise;
254 end;
255
256 /*
257 * Function to return the name of the MV Log if any on a given MV,
258 * returns NULL otherwise
259 */
260 FUNCTION get_mview_log_name(p_mview_name IN VARCHAR2, p_object_schema IN VARCHAR2)
261 RETURN VARCHAR2
262 IS
263 CURSOR get_log_table IS
264 SELECT log_table
265 FROM All_SNAPSHOT_LOGS LOG
266 WHERE log.master = p_mview_name
267 AND log_owner = p_object_schema;
268 log_table_name VARCHAR2(30);
269 BEGIN
270 log_table_name := NULL;
271 OPEN get_log_table;
272 FETCH get_log_table INTO log_table_name;
273 CLOSE get_log_table;
274 RETURN log_table_name;
275 EXCEPTION WHEN OTHERS THEN
276 WRITELOG('Exception : get_mview_log_name ' || form_triplet(p_mview_name, p_object_schema, 'MVLog') );
277 raise;
278 END;
279
280 --AGUWALAN :: bug#4898446 :: api to capture Indexes on MVs
281 procedure capture_mv_log_index(p_mv_log_name in varchar2,
282 P_mv_log_schema in varchar2)
283 IS
284 l_stmt VARCHAR2(1000);
285 TYPE curType IS REF CURSOR ;
286 c_mv_log_index curType;
287 l_index_name VARCHAR2(30);
288 l_schema VARCHAR2(30);
289 l_ddl CLOB;
290 l_count NUMBER;
291 begin
292 l_count := 0;
293 l_stmt := 'SELECT INDEX_NAME, OWNER, TO_CHAR(sys.ad_dbms_metadata.GET_DDL(''INDEX'',INDEX_NAME,OWNER)) FROM '||
294 '(select index_name , OWNER from all_indexes where table_name = :1 and owner = :2)';
295
296 open c_mv_log_index for l_stmt USING p_mv_log_name, p_mv_log_schema;
297 loop
298 fetch c_mv_log_index into l_index_name, l_schema, l_ddl;
299 exit when c_mv_log_index%NOTFOUND;
300
301 if l_ddl is not null or l_ddl <> '' then
302 l_count := l_count+1;
303 update bis_obj_indexes set INDEX_SQL = l_ddl, LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.User_id, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID where OBJECT_NAME=p_mv_log_name and OBJECT_TYPE='MVLOG' and INDEX_NAME=l_index_name;
304 IF SQL%NOTFOUND THEN
305 INSERT INTO bis_obj_indexes( OBJECT_NAME, OBJECT_TYPE, INDEX_NAME, INDEX_SQL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
306 values(p_mv_log_name, 'MVLOG', l_index_name, l_ddl, sysdate, FND_GLOBAL.User_id, sysdate, FND_GLOBAL.User_id, fnd_global.LOGIN_ID);
307 END IF;
308 COMMIT;
309 WRITELOG('Captured Index '|| l_index_name || ' on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
310 end if;
311 end loop;
312 close c_mv_log_index;
313 if (l_count = 0) then
314 WRITELOG('No Index captured on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
315 else
316 WRITELOG(to_char(l_count)||' Index(s) captured on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
317 end if;
318 end;
319
320 function capture_mv_log_sql(p_base_object_name in varchar2,
321 P_base_object_schema in varchar2,
322 P_base_object_type in varchar2)
323
324 RETURN NUMBER
325 IS
326 l_snapshot_log_sql_handle clob := null;
327 l_time date;
328 l_length number := 0;
329 --bug#4704403
330 l_stmt varchar2(1000) := 'BEGIN
331 :snapshot_log_sql_handle := sys.ad_dbms_metadata.get_dependent_ddl(
332 ''MATERIALIZED_VIEW_LOG'',
333 :p_base_object_name,
334 :p_base_object_schema
335 ); END;
336 ';
337 cursor c_obj_properties is
338 select snapshot_log_sql,mv_log_status
339 from bis_obj_properties
340 where object_name=p_base_object_name
341 and object_type=P_base_object_type;
342
343 cursor log_exist is
344 select 'Y'
345 from dual
346 where exists(
347 select log_table
348 from all_snapshot_logs
349 where master=p_base_object_name
350 and log_owner=p_base_object_schema);
351
352 l_mv_log_status bis_obj_properties.mv_log_status%type;
353 l_mv_log_sql_stored bis_obj_properties.snapshot_log_sql%type;
354 l_program_status boolean:=true;
355 l_log_exist varchar2(1);
356 l_mv_log_name VARCHAR2(30);
357
358 BEGIN
359
360 open log_exist;
361 fetch log_exist into l_log_exist;
362 close log_exist;
363
364 setTimer(l_time);
365
366 WRITELOG('Capturing MV log for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) );
367 if l_log_exist='Y' then
368 execute immediate l_stmt
369 using OUT l_snapshot_log_sql_handle, IN p_base_object_name, IN P_base_object_schema;
370 end if;
371
372 open c_obj_properties;
373 fetch c_obj_properties into l_mv_log_sql_stored,l_mv_log_status;
374 close c_obj_properties;
375
376 l_length := DBMS_LOB.getlength(l_snapshot_log_sql_handle);
377
378 if (nvl(l_log_exist,'N')<>'Y' or l_snapshot_log_sql_handle is null or
379 l_length = 0 ) then
380 WRITELOG('No MV Log found for this object');
381
382 if l_mv_log_sql_stored is null then
383 ---either because first time capture or because last captured log is null
384 ---the corresponding log status column in RSG is either null or 'NOLOG'
385 update_mv_log_status(p_base_object_name ,
386 p_base_object_type ,
387 'NOLOG') ;
388 WRITELOG('MV log definition for this object in RSG is also null. Only update status to ''NOLOG''');
389 else ---RSG stored mv log is not null
390 if l_mv_log_status='RECREATED' then
391 ---MV log had been recreated successfully
392 ---We can conclude that mv log is dropped by patch
393 ---So wipe out the mv log definition stored in RSG
394 update_mv_log_sql(p_base_object_name ,
395 p_base_object_type,
396 l_snapshot_log_sql_handle);
397
398 update_mv_log_status(p_base_object_name ,
399 p_base_object_type ,
400 'NOLOG') ;
401 WRITELOG('Wipe out MV log definition in RSG because patch dropped mv log after it had been recreated by RSG. Set status to ''NOLOG''');
402 -- aguwalan: bug#4898446
403 l_mv_log_name := get_mview_log_name(p_base_object_name, P_base_object_schema);
404 DELETE bis_obj_indexes where OBJECT_NAME=l_mv_log_name and OBJECT_TYPE='MVLOG';
405 WRITELOG('Also wiped out definition of Indexes(if any) on the MV log.');
406 else ---other status 'DROPPED','CAPTURED'
407
408 ----We need to identify if the mv log is dropped by RSG or patch
409 ----before we decide to wipe out mv log definition in RSG or not
410 if check_obj_patch_applied(p_base_object_name,p_base_object_type)='Y' then
411 if check_obj_recreated (p_base_object_name,p_base_object_type,P_base_object_schema) ='Y' then
412 ---we can conclude that mv log is dropped by patch after last failed run
413 ---so wipe out the mv log definition stored in RSG
414 ---please note that this should be a corner case
415 ---According to BISREL, customer should not apply any patch
416 ---if the last run is not successful
417 update_mv_log_sql(p_base_object_name ,
418 p_base_object_type,
419 l_snapshot_log_sql_handle);
420
421 update_mv_log_status(p_base_object_name ,
422 p_base_object_type ,
423 'NOLOG') ;
424 WRITELOG('Wipe out MV log definition in RSG because patch dropped mv log after last failed run. Set status to ''NOLOG''');
425 -- aguwalan: bug#4898446
426 l_mv_log_name := get_mview_log_name(p_base_object_name, P_base_object_schema);
427 DELETE bis_obj_indexes where OBJECT_NAME=l_mv_log_name and OBJECT_TYPE='MVLOG';
428 WRITELOG('Also wiped out definition of Indexes(if any) on MV log.');
429 else
430 --though mv patch being applied but not sure if the MV log is dropped by patch
431 ---report warning and keep MV log definition in RSG
432 update_mv_log_status(p_base_object_name ,
433 p_base_object_type ,
434 'NOT_OVERWRITE_WITH_NULL') ;
435 WRITELOG('Keep MV log definition in RSG because we are not sure if the mv log is dropped by RSG or by patch');
436 WRITELOG('Set status to ''NOT_OVERWRITE_WITH_NULL''');
437 WRITELOG ('Report this as warning. Please contact system administrator');
438 WRITELOG ('If the log is dropped by patch, MV log definition stored in RSG should be cleaned up by system administrator manually');
439 l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
440
441 end if; ---end if check_mv_recreated
442
443 else
444 ----we can conclude that MV log is dropped by RSG. So keep mv log definition
445 ----in RSG.
446 update_mv_log_status(
447 p_base_object_name ,
448 p_base_object_type ,
449 'NOT_OVERWRITE_WITH_NULL') ;
450 WRITELOG('Keep MV log definition in RSG because the mv log is dropped by RSG');
451 WRITELOG('Set status to ''NOT_OVERWRITE_WITH_NULL''');
452 end if; --end if mv patch being applied
453 end if; ---end if mv log status is 'RECREATED'
454
455 end if; ---end if rsg stored mv log is null
456
457 else ---captured MV log not null
458 WRITELOG('MV Log length: ' || l_length);
459 update_mv_log_sql(p_base_object_name ,
460 p_base_object_type,
461 l_snapshot_log_sql_handle);
462
463 update_mv_log_status(p_base_object_name ,
464 p_base_object_type ,
465 'CAPTURED') ;
466
467 WRITELOG('Captured MV log definition for '|| form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) || 'is not null');
468 WRITELOG('Update BIS_OBJ_PROPERTIES TABLE with captured MV log definition');
469 -- aguwalan: bug#4898446 :Capture Index(if any) on MVLog
470 l_mv_log_name := get_mview_log_name(p_base_object_name, P_base_object_schema);
471 WRITELOG('Capturing Index on MV log '|| form_triplet(l_mv_log_name, P_base_object_schema, 'MVLog'));
472 capture_mv_log_index(l_mv_log_name, P_base_object_schema);
473 end if; ---end if captured MV log is null or not
474
475 logTime( 'Capturing MV Log for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) , l_time);
476
477 update_obj_creation_date(p_base_object_name,p_base_object_type,p_base_object_schema);
478
479 WRITELOG('************************************');
480 return l_length;
481 EXCEPTION WHEN OTHERS THEN
482 WRITELOG('Failed capturing MV Log for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) || ', ' ||sqlerrm);
483 raise;
484 END;
485
486 PROCEDURE drop_mv_log(p_base_object_name in varchar2,
487 P_base_object_schema in varchar2)
488 IS
489 l_time date;
490 l_dur number;
491 l_stmt varchar2(1000) := 'DROP MATERIALIZED VIEW LOG ON ' || P_base_object_schema || '.' ||
492 p_base_object_name;
493
494 BEGIN
495 setTimer(l_time);
496 WRITELOG('Executing ' || l_stmt);
497 execute immediate l_stmt;
498 logTime( 'Dropping mv log of ' || form_triplet(p_base_object_name, P_base_object_schema, null) , l_time);
499 WRITELOG('************************************');
500 EXCEPTION WHEN OTHERS THEN
501 WRITELOG('Failed droping ' || P_base_object_schema || '.' ||
502 p_base_object_name || sqlerrm);
503 raise;
504 END;
505
506 PROCEDURE capture_and_drop_log_by_name (
507 P_OBJ_NAME IN VARCHAR2,
508 P_OBJ_TYPE IN VARCHAR2,
509 P_OBJ_OWNER IN VARCHAR2
510 ) IS
511 l_length number;
512 BEGIN
513 l_length:= capture_mv_log_sql(P_OBJ_NAME, P_OBJ_OWNER, P_OBJ_TYPE);
514 if (l_length >0 ) then
515 drop_mv_log(P_OBJ_NAME,P_OBJ_OWNER);
516 update_mv_log_status(P_OBJ_NAME ,
517 P_OBJ_TYPE ,
518 'DROPPED') ;
519 end if;
520 EXCEPTION WHEN OTHERS THEN
521 WRITELOG('Error happened while capturing/dropping mv log for '
522 || form_triplet(P_OBJ_NAME, P_OBJ_OWNER, P_OBJ_TYPE));
523 RAISE;
524 END;
525
526 --aguwalan :: bug#4898446 :: api to recreate Indexes on MV Logs
527 PROCEDURE recreate_mv_log_index(p_mv_log_name in varchar2,
528 P_mv_log_schema in varchar2)
529 IS
530 l_stmt VARCHAR2(1000);
531 TYPE curType IS REF CURSOR ;
532 c_mv_log_index curType;
533 l_index_name VARCHAR2(30);
534 l_schema VARCHAR2(30);
535 l_index_ddl VARCHAR2(32767);
536 temp_index_ddl VARCHAR2(32767);
537 l_count NUMBER;
538 BEGIN
539 l_stmt := 'select INDEX_NAME, INDEX_SQL from BIS_OBJ_INDEXES where OBJECT_NAME='''
540 ||p_mv_log_name||
541 ''' and OBJECT_TYPE=''MVLOG'' and INDEX_SQL is not null';
542 l_count := 0;
543 open c_mv_log_index for l_stmt;
544 loop
545 fetch c_mv_log_index into l_index_name, l_index_ddl;
546 exit when c_mv_log_index%NOTFOUND;
547 IF (l_index_ddl IS NOT NULL) THEN
548 BEGIN
549 temp_index_ddl:= to_char(sys.ad_dbms_metadata.get_ddl('INDEX',l_index_name));
550 EXCEPTION WHEN OTHERS THEN --Index doesnt exists already . We can recreate now without ORA error.
551 execute immediate l_index_ddl;
552 l_count := l_count + 1;
553 WRITELOG('Recreated Index ' || l_index_name || ' on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
554 END;
555 end if;
556 end loop;
557 close c_mv_log_index;
558 if (l_count = 0) then
559 WRITELOG('No Index Recreated on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
560 else
561 WRITELOG(to_char(l_count)||' Index(s) re-created on MVLog ' || form_triplet(p_mv_log_name, P_mv_log_schema, 'MVLog') );
562 end if;
563 END;
564
565
566 PROCEDURE create_mv_log(p_base_object_name in varchar2,
567 P_base_object_schema in varchar2,
568 P_base_object_type in varchar2,
569 P_check_profile in varchar2 default 'Y')
570 IS
571 l_count integer := 0;
572 l_time date;
573 l_dur number;
574 l_snapshot_log_sql_handle clob := null;
575 l_amount integer := 256;
576 l_offset integer := 1;
577 l_output varchar2(256);
578 l_index integer := 0;
579 l_stmt varchar2(32767) := 'BEGIN
580 select count(*) into :l_count
581 from
582 bis_obj_properties PRP,
583 All_SNAPSHOT_LOGS LOG
584 where
585 log.master = PRP.object_name
586 and OBJECT_TYPE = :P_base_object_type
587 and OBJECT_NAME = :p_base_object_name
588 and log.log_owner = :P_base_object_schema;
589 -- BIS_CREATE_REQUESTSET.get_object_owner(object_name, object_type);
590 END;
591 ';
592 l_mv_log_name VARCHAR2(30);
593 BEGIN
594 if P_check_profile='Y' then
595 if ( NOT is_mv_log_mangmnt_enabled) then
596 WRITELOG('No further action performed!');
597 return;
598 end if;
599 end if;
600
601 if P_base_object_schema='NOTFOUND' then
602 WRITELOG('Base object '||p_base_object_name||' not exists.'||'No further action performed!');
603 return;
604 end if;
605
606 --WRITELOG('Executed ' || l_stmt || ' with ' || form_triplet(l_count, P_base_object_type, p_base_object_name));
607 execute immediate l_stmt
608 using OUT l_count, IN P_base_object_type, IN p_base_object_name, IN P_base_object_schema;
609
610 if(l_count > 0) then
611 WRITELOG('MV log for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) || 'exists.
612 Stop MV log recreating!');
613 return;
614 end if;
615
616 setTimer(l_time);
617 begin
618 select snapshot_log_sql into l_snapshot_log_sql_handle
619 from bis_obj_properties
620 where object_name = p_base_object_name
621 and object_type = P_base_object_type;
622 exception when no_data_found then
623 WRITELOG(form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) || ' not seeded in RSG,
624 skip MV log creating!!');
625 return;
626 end;
627
628 if (l_snapshot_log_sql_handle is null or
629 DBMS_LOB.getlength(l_snapshot_log_sql_handle) = 0 ) then
630 WRITELOG('Found no MV Log defined for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type)
631 || ', not need to recreate.');
632 return;
633 else
634 WRITELOG('building mv log sql statement array:');
635 WHILE(l_offset< DBMS_LOB.getlength(l_snapshot_log_sql_handle) )
636 LOOP
637 l_index := l_index + 1;
638 DBMS_LOB.READ(l_snapshot_log_sql_handle, l_amount, l_offset, l_output);
639 build_statement(l_output, l_index);
640 l_offset := l_offset + l_amount;
641 WRITELOG(l_output);
642 END LOOP;
643
644 WRITELOG('executing apps_array_ddl.apps_array_ddl');
645 apps_array_ddl.apps_array_ddl(1, l_index);
646
647 --aguwalan :: bug#4898446 :: Recreate Indexes, if any, on MVLog
648 WRITELOG('Recreate Indexes, if any, on MVLog');
649 l_mv_log_name := get_mview_log_name(p_base_object_name, P_base_object_schema);
650 recreate_mv_log_index(l_mv_log_name, P_base_object_schema);
651
652 logTime( 'Creating mv log for ' || form_triplet(p_base_object_name, P_base_object_schema, P_base_object_type) , l_time);
653
654 update_mv_log_status(p_base_object_name ,
655 p_base_object_type ,
656 'RECREATED');
657 end if;
658 EXCEPTION WHEN OTHERS THEN
659 WRITELOG('Failed creating MV log for ' || P_base_object_schema || '.' ||
660 p_base_object_name || ', ' ||sqlerrm);
661 raise;
662 END;
663
664 function on_check_prog_linkage (
665 p_object_name in varchar2,
666 p_object_type in varchar2
667 ) RETURN BOOLEAN
668 IS
669 l_count integer := 0;
670 BEGIN
671 if ( p_object_type <> 'MV') then
672 select count(*) into l_count
673 from bis_obj_prog_linkages lkg
674 where
675 object_type <> 'MV'
676 and lkg.enabled_flag = 'Y'
677 and lkg.refresh_mode in ( 'INIT', 'INIT_INCR')
678 and object_type = p_object_type
679 and object_name = p_object_name;
680 if (l_count > 0 ) then
681 WRITELOG('Found comcplete refresh program for ' || p_object_name);
682 return true;
683 else
684 WRITELOG('Found no comcplete refresh program for ' || p_object_name);
685 return false;
686 end if;
687 else
688 select count(*) into l_count
689 from bis_obj_prog_linkages lkg
690 where
691 object_type = 'MV'
692 and lkg.enabled_flag = 'Y'
693 and lkg.refresh_mode in ( 'INIT', 'INIT_INCR')
694 and conc_program_name <> 'BIS_MV_REFRESH'
695 and object_type = p_object_type
696 and object_name = p_object_name;
697 if (l_count > 0 ) then
698 WRITELOG('Found complete refresh program for MV ' || p_object_name||' hence do not perform capture and drop MV log.');
699 return false;
700 else
701 WRITELOG('Found no complete refresh program for MV, ' || p_object_name);
702 return true;
703 end if;
704 end if;
705 END;
706
707
708
709 PROCEDURE base_sum_mlog_recreate (
710 P_OBJ_NAME IN VARCHAR2
711 ) IS
712 l_parameter_tbl BIS_BIA_RSG_PARAMETER_TBL := BIS_BIA_RSG_PARAMETER_TBL();
713 l_root_request_id INTEGER;
714 l_rs_name varchar2(500);
715 l_is_force_full varchar2(100) := null;
716 l_refresh_mode varchar2(100) := null;
717 l_tmp varchar2(100) := null;
718 l_opt varchar2(100) := null;
719 l_schema varchar2(500);
720 cursor c_force_full(p_request_id INTEGER ) IS
721 select distinct
722 sets.request_set_name,
723 opt.option_name,
724 opt.option_value
725 from
726 fnd_run_requests req,
727 fnd_request_sets sets,
728 bis_request_set_options opt
729 where
730 req.parent_request_id = p_request_id
731 and sets.request_set_id = req.request_set_id
732 and req.application_id = 191
733 and sets.application_id = 191
734 and req.application_id = sets.application_id
735 and opt.request_set_name = sets.request_set_name
736 and opt.option_name IN ('FORCE_FULL', 'REFRESH_MODE');
737 l_impl varchar2(10):= null;
738
739 BEGIN
740
741 IF (Not BIS_COLLECTION_UTILITIES.setup(P_OBJ_NAME)) THEN
742 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
743 return;
744 END IF;
745
746 if ( NOT is_mv_log_mangmnt_enabled) then
747 WRITELOG('No further action performed!');
748 return;
749 end if;
750
751 select implementation_flag into l_impl
752 from bis_obj_properties
753 where object_name = P_OBJ_NAME
754 and object_type = 'TABLE';
755 if (l_impl <> 'Y') then
756 WRITELOG( P_OBJ_NAME || 'is not implemented, No further action performed!');
757 return;
758 end if;
759
760 l_root_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
761 BIS_COLLECTION_UTILITIES.put_line('FND_GLOBAL.CONC_PRIORITY_REQUEST: ' || l_root_request_id);
762 open c_force_full(l_root_request_id);
763 LOOP
764 fetch c_force_full into l_rs_name, l_opt, l_tmp;
765 exit when c_force_full%NOTFOUND;
766 if ( l_opt = 'FORCE_FULL') then
767 l_is_force_full := l_tmp;
768 elsif ( l_opt = 'REFRESH_MODE') then
769 l_refresh_mode := l_tmp;
770 end if;
771 END LOOP;
772 close c_force_full;
773 WRITELOG('Request Set: ' || l_rs_name
774 || ', ' || 'FORCE_FULL option: ' || l_is_force_full
775 || ', ' || 'REFRESH_MODE option: ' || l_refresh_mode
776 );
777 if (NOT
778 ( l_is_force_full = 'Y' AND l_refresh_mode = 'INIT')
779 ) then
780 WRITELOG('No action for MV Log recreation.');
781 RETURN;
782 end if;
783
784
785 WRITELOG( 'BIS RUNTIME MV LOG MANAGEMENT for ' ||form_triplet(P_OBJ_NAME, 'TABLE', null) || ' Starts!');
786 l_schema := BIS_CREATE_REQUESTSET.get_object_owner(P_OBJ_NAME, 'TABLE');
787 WRITELOG('Schema info:' || l_schema);
788 create_mv_log(P_OBJ_NAME,
789 l_schema,
790 'TABLE');
791
792 EXCEPTION WHEN OTHERS THEN
793 close c_force_full;
794 raise;
795 END base_sum_mlog_recreate;
796
797 PROCEDURE base_sum_mlog_capture_and_drop(
798 P_OBJ_NAME IN VARCHAR2 )
799 IS
800 l_owner varchar2(32767);
801 l_impl varchar2(10):= null;
802 l_exist number := 0;
803 BEGIN
804 if ( NOT is_mv_log_mangmnt_enabled) then
805 WRITELOG('No further action performed!');
806 return;
807 end if;
808
809 select implementation_flag into l_impl
810 from bis_obj_properties
811 where object_name = P_OBJ_NAME
812 and object_type = 'TABLE';
813 if (l_impl <> 'Y') then
814 WRITELOG( P_OBJ_NAME || 'is not implemented, no further action performed!');
815 return;
816 end if;
817
818 l_owner := BIS_CREATE_REQUESTSET.get_object_owner(P_OBJ_NAME, 'TABLE');
819
820 /** commented out per enhancement 4222518
821 select count(*) into l_exist
822 from
823 All_SNAPSHOT_LOGS LOG
824 where
825 log.master = P_OBJ_NAME
826 and log.log_owner = l_owner;
827
828 if(l_exist = 0 ) then
829 WRITELOG( 'MV log for ' || P_OBJ_NAME || ' does not exist, no further action performed!');
830 return;
831 end if;
832 **/
833
834 capture_and_drop_log_by_name ( P_OBJ_NAME, 'TABLE', l_owner);
835 END base_sum_mlog_capture_and_drop;
836
837
838
839 PROCEDURE capture_and_drop_log_by_set(
840 ERRBUF OUT NOCOPY VARCHAR2,
841 RETCODE OUT NOCOPY VARCHAR2,
842 p_request_set_name in varchar2)
843 IS
844 TYPE curType IS REF CURSOR ;
845 c_all_log_base_obj curType;
846 /*
847 * l_stmt was modified to consider MV type object only due to bug3901782
848 * 23-Mar-2005 l_stmt is modified per enhancement 4222518. Not join with
849 * all_snapshot_logs in this query
850 */
851 l_stmt varchar2(32767) := '
852 select DISTINCT PRP.object_name, PRP.object_type,
853 BIS_CREATE_REQUESTSET.get_object_owner(prp.object_name, prp.object_type) object_owner
854 from (
855 SELECT distinct DEPEND_OBJECT_TYPE OBJECT_TYPE
856 , DEPEND_OBJECT_NAME OBJECT_NAME
857 , DEPEND_OBJECT_OWNER OBJECT_OWNER
858 FROM BIS_OBJ_DEPENDENCY
859 WHERE DEPEND_OBJECT_TYPE = ''MV''
860 START WITH OBJECT_NAME in (
861 select object_name from bis_request_set_objects
862 where request_set_name = :p_request_set_name
863 and object_type = ''PAGE''
864 )
865 and enabled_flag = ''Y''
866 connect by object_name = prior depend_object_name
867 and object_type = prior depend_object_type
868 and enabled_flag = ''Y''
869 ) PRP,
870 BIS_OBJ_PROPERTIES P
871 where prp.object_name = p.object_name
872 and prp.object_type = p.object_type
873 and p.implementation_flag = ''Y''
874 order by PRP.object_type, PRP.object_name
875 ';
876
877 l_object_name bis_obj_properties.object_name%type;
878 l_object_type bis_obj_properties.object_type%type;
879 l_object_owner all_objects.owner%type;
880 BEGIN
881 errbuf := NULL;
882 retcode := '0';
883 IF (Not BIS_COLLECTION_UTILITIES.setup(p_request_set_name)) THEN
884 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || sqlerrm);
885 return;
886 END IF;
887
888 if ( NOT is_mv_log_mangmnt_enabled) then
889 WRITELOG('No further action performed!');
890 return;
891 end if;
892
893 --OPEN c_all_log_base_obj(p_request_set_name);
894 WRITELOG('Executing the following: ' || l_stmt || '; using ' || p_request_set_name);
895 open c_all_log_base_obj for l_stmt using p_request_set_name;
896 LOOP
897 FETCH c_all_log_base_obj into l_object_name, l_object_type, l_object_owner;
898 exit when c_all_log_base_obj%NOTFOUND;
899 if(l_object_owner <> 'NOTFOUND') then
900 if (on_check_prog_linkage(l_object_name, l_object_type )) then
901 WRITELOG('Perform Capture and Drop MV Log');
902 BEGIN
903 capture_and_drop_log_by_name(l_object_name, l_object_type, l_object_owner);
904 EXCEPTION WHEN OTHERS
905 -- Mask out exception
906 THEN NULL;
907 END;
908 else
909 WRITELOG('not to Perform Capture and Drop MV Log');
910 WRITELOG('************************************');
911 end if; -- on_check_prog_linkage
912 end if; -- l_object_owner <> 'NOTFOUND'
913 WRITELOG(' ');
914 END LOOP;
915 CLOSE c_all_log_base_obj;
916 EXCEPTION WHEN OTHERS THEN
917 errbuf := sqlerrm;
918 retcode := sqlcode;
919 CLOSE c_all_log_base_obj;
920 WRITELOG('Failed capture_all_mv_log_sql, ' || sqlerrm);
921 Raise;
922 END;
923
924 procedure restore_by_set(
925 ERRBUF OUT NOCOPY VARCHAR2,
926 RETCODE OUT NOCOPY VARCHAR2,
927 p_request_set_name varchar2
928 )
929 IS
930 TYPE curType IS REF CURSOR ;
931 c_all_log_base_obj curType;
932 l_stmt varchar2(32767) := '
933 select DISTINCT object_name, object_type,
934 BIS_CREATE_REQUESTSET.get_object_owner(object_name, object_type) object_owner
935 from (
936 SELECT distinct DEPEND_OBJECT_TYPE OBJECT_TYPE
937 , DEPEND_OBJECT_NAME OBJECT_NAME
938 , DEPEND_OBJECT_OWNER OBJECT_OWNER
939 FROM BIS_OBJ_DEPENDENCY
940 WHERE DEPEND_OBJECT_TYPE = ''MV''
941 START WITH OBJECT_NAME in (
942 select object_name from bis_request_set_objects
943 where request_set_name = :p_request_set_name
944 and object_type = ''PAGE''
945 )
946 connect by object_name = prior depend_object_name
947 and object_type = prior depend_object_type
948 ) PRP
949 order by object_type, object_name';
950
951 l_object_name bis_obj_properties.object_name%type;
952 l_object_type bis_obj_properties.object_type%type;
953 l_object_owner all_objects.owner%type;
954 BEGIN
955 --OPEN c_all_log_base_obj(p_request_set_name);
956 WRITELOG('Executing the following: ' || l_stmt || '; using ' || p_request_set_name);
957 OPEN c_all_log_base_obj for l_stmt using p_request_set_name;
958 LOOP
959 FETCH c_all_log_base_obj into l_object_name, l_object_type, l_object_owner;
960 exit when c_all_log_base_obj%NOTFOUND;
961 if(l_object_owner <> 'NOTFOUND') then
962 BEGIN
963 WRITELOG('Restoring MV log for ' || form_triplet(l_object_name, l_object_owner, l_object_type));
964 create_mv_log(l_object_name,l_object_owner,l_object_type);
965 WRITELOG('Restored MV log for ' || form_triplet(l_object_name, l_object_owner, l_object_type));
966 WRITELOG(' ');
967 EXCEPTION WHEN OTHERS THEN
968 WRITELOG('Encountered issue on restoring MV log for ' || form_triplet(l_object_name, l_object_owner, l_object_type)
969 || ', '
970 || sqlerrm);
971 END;
972 end if;
973 END LOOP;
974 CLOSE c_all_log_base_obj;
975 EXCEPTION WHEN OTHERS THEN
976 errbuf := sqlerrm;
977 retcode := sqlcode;
978 WRITELOG('Failed restoring MV logs ' || sqlerrm);
979 CLOSE c_all_log_base_obj;
980 raise;
981 END;
982
983 END; -- Package Body BIS_BIA_RSG_LOG_MGMNT
[Home] [Help]