[Home] [Help]
PACKAGE BODY: APPS.AD_ZD_MVIEW
Source
1 PACKAGE BODY AD_ZD_MVIEW AS
2 -- $Header: ADZDMVB.pls 120.38.12020000.12 2013/05/09 14:49:45 rraam ship $
3
4
5 /*******************************************************************
6 * *
7 * UTILITY APIS *
8 * *
9 *******************************************************************/
10
11 type ddl_defs is table of CLOB index by BINARY_INTEGER;
12
13 -- Diagnostic Log shortcut
14 procedure LOG(p_module varchar2, p_log_type varchar2, p_message varchar2)
15 is
16 begin
17 AD_ZD_LOG.Message('ad.plsql.ad_zd_mview.'||p_module, p_log_type, p_message);
18 end;
19
20
21 /*-----------------------------------------------------------------+
22 | |
23 | SPLITNAMEOWNER |
24 | Return owner, name without quotes |
25 | |
26 | If quotes = 0, then extowner and extname will not contain |
27 | double quotes (") |
28 | Else if quotes > 0, then these will contain double |
29 | quotes (") |
30 | |
31 +-----------------------------------------------------------------*/
32 procedure SPLITNAMEOWNER(
33 name in varchar2,
34 extowner out nocopy varchar2,
35 extname out nocopy varchar2,
36 quotes in number)
37 is
38 pos number;
39 begin
40 pos := instr(name, '.');
41 if (pos > 0)
42 THEN
43 -- Owner also present.
44 extowner := substr(upper(name), 1, pos-1);
45 extowner := trim(extowner);
46 extowner := trim(both '"' from extowner);
47 extname := substr(upper(name), pos+1);
48 extname := trim(extname);
49 extname := trim(both '"' from extname);
50 ELSE
51 extowner := null;
52 extname := trim(name);
53 extname := trim(both '"' from extname);
54 END IF;
55
56 if (quotes > 0)
57 then
58 if (extowner is not null)
59 then
60 extowner := '"' || extowner || '"';
61 end if;
62 if (extname is not null)
63 then
64 extname := '"' || extname || '"';
65 end if;
66
67 end if;
68 end;
69
70 /*-----------------------------------------------------------------+
71 | |
72 | CONVERT_NAME |
73 | Translates Name from one form to another |
74 | flag = 1 translates from mv to mvq format |
75 | any other translates from mvq to mv format |
76 | ext = # extension character for mvq |
77 | = T extension character for trigger view |
78 | = L extension character for mvlog view |
79 | |
80 +-----------------------------------------------------------------*/
81 function CONVERT_NAME(name varchar2, flag number, ext varchar2)
82 return varchar2
83 is
84 ch varchar2(1);
85 ow varchar2(32);
86 nm varchar2(32);
87 l_tmpname varchar2(65);
88 retname varchar2(65);
89 begin
90 l_tmpname := trim(name);
91 ch := substr(l_tmpname, 1, 1);
92 if(ch = '"')
93 then
94 l_tmpname := trim(both '"' from l_tmpname);
95 end if;
96
97 splitnameowner(l_tmpname, ow, nm, 0);
98
99 if (flag = 1)
100 then
101 retname := substr(nm, 1, (30-length(ext)))||ext;
102 else
103 retname := regexp_replace(nm, ext||'$', '', 1, 1, 'i');
104 -- Hack for MTH_ENTITY_PLANNED_USAGE_SM_MV and MTH_ENTITY_PLANNED_USAGE_HR_MV mviews
105 -- Because these two mviews are having length 30 so attach V at the end after conversion
106 -- if after conversion ended with _M
107 if (length(retname) = 29 and retname like '%_M')
108 then
109 retname := retname||'V';
110 end if;
111
112 end if;
113
114 if (ch = '"')
115 then
116 if (ow is not null)
117 then
118 retname := '"'||ow||'"."'||retname||'"';
119 else
120 retname := '"'||retname||'"';
121 end if;
122 else
123 if (ow is not null)
124 then
125 retname := ow||'.'||retname;
126 end if;
127 end if;
128 return retname;
129 end;
130
131 /*-----------------------------------------------------------------+
132 | |
133 | INCLUDE_PREBUILT |
134 | |
135 | Tasks :- |
136 | Process the input string and returns the modified string |
137 | contains prebuilt syntax |
138 | |
139 | Order of checking the words should match with the proper |
140 | syntax |
141 | USING (NO )?INDEX |
142 | NEVER REFRESH |
143 | REFRESH |
144 | FOR UPDATE |
145 | ENABLE QUERY REWRITE |
146 | DISABLE QUERY REWRITE |
147 | |
148 +-----------------------------------------------------------------*/
149 function INCLUDE_PREBUILT(p_string clob)
150 return clob
151 is
152 l_retstring clob;
153 l_pos number;
154 l_prebuilt_clause varchar2(25) := ' ON PREBUILT TABLE ';
155 begin
156
157 l_pos := instr(p_string, 'PREBUILT');
158 if (l_pos = 0)
159 then
160 l_pos := regexp_instr(p_string, 'USING (NO )?INDEX');
161 if (l_pos = 0)
162 then
163 l_pos := instr(p_string, 'NEVER REFRESH');
164 if (l_pos = 0)
165 then
166 l_pos := instr(p_string, 'REFRESH');
167 if (l_pos = 0)
168 then
169 l_pos := instr(p_string, 'FOR UPDATE');
170 if (l_pos = 0)
171 then
172 l_pos := instr(p_string, 'ENABLE QUERY REWRITE');
173 if (l_pos = 0)
174 then
175 l_pos := instr(p_string, 'DISABLE QUERY REWRITE');
176 end if; -- ENABLE QUERY REWRITE
177 end if; -- FOR UPDATE
178 end if; -- REFRESH
179 end if; -- NEVER REFRESH
180 end if; -- USING (NO )?INDEX
181
182 l_retstring := substr(p_string, l_pos);
183 else
184 l_retstring := p_string;
185 end if;
186 l_retstring := l_prebuilt_clause || l_retstring;
187 return l_retstring;
188 end;
189
190 /*-----------------------------------------------------------------+
191 | |
192 | EXPAND_QUERY |
193 | Takes the command as input and executes it. |
194 | Execute Immediate is more efficient than dbms_sql.parse |
195 | Refer :- http://download.oracle.com/docs/cd/B28359_01/ |
196 | appdev.111/b28370/dynamic.htm |
197 | |
198 +-----------------------------------------------------------------*/
199 function EXPAND_QUERY(p_query clob)
200 return clob
201 is
202 l_exp_select_query clob;
203 begin
204 -- Expand the select query
205 dbms_sql2.expand_sql_text(p_query, l_exp_select_query);
206 return l_exp_select_query;
207 end;
208
209
210 -- Execute SQL
211 --GB: why is there an OWNER parameter
212 procedure EXEC(
213 X_MODULE varchar2,
214 X_COMMAND clob,
215 X_OWNER varchar2 default null)
216 IS
217 L_STMT clob;
218 L_CUR integer;
219 ROWS_PROCESSED integer;
220 e_rowid_prebuilt exception;
221 PRAGMA EXCEPTION_INIT(e_rowid_prebuilt, -12058);
222 e_udt_prebuilt exception;
223 PRAGMA EXCEPTION_INIT(e_udt_prebuilt, -32304);
224 e_shape_mismatch exception;
225 PRAGMA EXCEPTION_INIT(e_shape_mismatch, -12060);
226 e_table_already_referenced exception;
227 PRAGMA EXCEPTION_INIT(e_table_already_referenced, -32334);
228 begin
229 l_cur := dbms_sql.open_cursor;
230
231 if (x_owner is null) then
232 log(x_module, 'STATEMENT', 'SQL: '||substr(x_command, 1, 3900));
233 dbms_sql.parse(l_cur, x_command, dbms_sql.native);
234 else
235 l_stmt := 'begin '||x_owner||'.apps_ddl.apps_ddl(:stmt); end;';
236 log(x_module, 'STATEMENT', 'SQL['||x_owner||']: '||substr(x_command, 1, 3900));
237 dbms_sql.parse(l_cur, l_stmt, dbms_sql.native);
238 dbms_sql.bind_variable(l_cur, 'stmt', x_command);
239 end if;
240
241 rows_processed := dbms_sql.execute(l_cur);
242 dbms_sql.close_cursor(l_cur);
243 EXCEPTION
244 WHEN e_udt_prebuilt or
245 e_rowid_prebuilt or
246 e_shape_mismatch or
247 e_table_already_referenced THEN
248 dbms_sql.close_cursor(l_cur);
249 log(x_module, 'EVENT', SQLERRM||substr(x_command, 1, 3900));
250 raise;
251 WHEN OTHERS THEN
252 dbms_sql.close_cursor(l_cur);
253 log(x_module, 'ERROR', SQLERRM||substr(x_command, 1, 3900));
254 raise;
255 end;
256
257
258 procedure install_mvlog(p_owner varchar2, p_mview_name varchar2)
259 is
260 c_module varchar2(25) := 'install_mvlog';
261 l_mvloglname varchar2(32);
262 l_mvlogmname varchar2(32);
263 l_ctblname varchar2(30);
264 l_mvlogddl clob;
265 l_commentpart1 clob;
266 l_commentpart2 clob;
267 begin
268 log(c_module, 'EVENT', 'Saving MV Log definition');
269
270 l_mvloglname := get_mvq_name(p_mview_name, g_mvl_char);
271 l_mvlogmname := get_mvq_name(p_mview_name, g_mvm_char);
272 SELECT log_table
273 INTO l_ctblname
274 FROM dba_mview_logs
275 WHERE master=p_mview_name
276 AND log_owner=p_owner;
277
278 l_mvlogddl := dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG',
279 l_ctblname,
280 p_owner);
281
282 -- The max length of comment can be 4000
283 if (length(l_mvlogddl) > 4000)
284 then
285 l_commentpart1 := 'COMMENT ON TABLE "'||p_owner||'"."'||l_mvloglname||'" is '''||substr(l_mvlogddl, 1, 4000)||'''';
286 l_commentpart2 := 'COMMENT ON TABLE "'||p_owner||'"."'||l_mvlogmname||'" is '''||substr(l_mvlogddl, 4001)||'''';
287 else
288 l_commentpart1 := 'COMMENT ON TABLE "'||p_owner||'"."'||l_mvloglname||'" is '''||l_mvlogddl||'''';
289 end if;
290
291 exec(c_module, 'create or replace view "'||p_owner||'"."'||l_mvloglname||'" as select * from dual');
292 exec(c_module, l_commentpart1);
293 if (length(l_mvlogddl) > 4000)
294 then
295 exec(c_module, 'create or replace view "'||p_owner||'"."'||l_mvlogmname||'" as select * from dual');
296 exec(c_module, l_commentpart2);
297 end if;
298 end install_mvlog;
299
300
301 procedure recreate_mvlog(p_owner varchar2, p_mview_name varchar2)
302 IS
303 c_module varchar2(25) := 'recreate_mvlog';
304 l_mvlogddlpart1 clob := ' ';
305 l_mvlogddlpart2 clob := ' ';
306 l_mvlogddl clob;
307 l_exist number;
308 begin
309 log(c_module, 'EVENT', 'Recreating MV Log');
310
311 SELECT count(1)
312 INTO l_exist
313 FROM dba_mview_logs
314 WHERE log_owner=p_owner
315 AND master=p_mview_name;
316
317 if (l_exist = 0)
318 then
319 SELECT count(1)
320 INTO l_exist
321 FROM dba_tab_comments
322 WHERE owner=p_owner
323 AND table_name=get_mvq_name(p_mview_name, g_mvl_char)
324 AND table_type='VIEW';
325
326 if (l_exist > 0)
327 then
328 SELECT nvl(trim(comments), ' ')
329 INTO l_mvlogddlpart1
330 FROM dba_tab_comments
331 WHERE owner=p_owner
332 AND table_name=get_mvq_name(p_mview_name, g_mvl_char)
333 AND table_type='VIEW';
334 end if;
335
336 SELECT count(1)
337 INTO l_exist
338 FROM dba_tab_comments
339 WHERE owner=p_owner
340 AND table_name=get_mvq_name(p_mview_name, g_mvm_char)
341 AND table_type='VIEW';
342
343 if (l_exist > 0)
344 then
345 SELECT nvl(trim(comments), ' ')
346 INTO l_mvlogddlpart2
347 FROM dba_tab_comments
348 WHERE owner=p_owner
349 AND table_name=get_mvq_name(p_mview_name, g_mvm_char)
350 AND table_type='VIEW';
351 end if;
352
353 l_mvlogddl := l_mvlogddlpart1||l_mvlogddlpart2;
354
355 if (l_mvlogddl <> ' ')
356 then
357 exec(c_module, l_mvlogddl);
358 end if;
359 end if;
360 exec(c_module, 'drop view "'||p_owner||'"."'||get_mvq_name(p_mview_name, g_mvl_char)||'"');
361
362 SELECT count(1)
363 INTO l_exist
364 FROM dba_views
365 WHERE owner=p_owner
366 AND view_name=get_mvq_name(p_mview_name, g_mvm_char);
367
368 if (l_exist > 0)
369 then
370 exec(c_module, 'drop view "'||p_owner||'"."'||get_mvq_name(p_mview_name, g_mvm_char)||'"');
371 end if;
372 end recreate_mvlog;
373
374 function FETCH_DDL(p_type IN varchar2,
375 p_owner IN varchar2,
376 p_object_name IN varchar2) return CLOB
377 is
378 c_module varchar2(25) := 'fetch_ddl';
379 l_ddls sys.ku$_ddls;
380 l_ddl CLOB;
381 l_open_handle number;
382 l_transform_handle number;
383
384 begin
385 log(c_module, 'PROCEDURE', 'begin: '||p_type||'.'||p_owner||'.'||p_object_name);
386
387 l_open_handle := dbms_metadata.open(p_type);
388 dbms_metadata.set_filter(l_open_handle, 'SCHEMA', p_owner);
389 dbms_metadata.set_filter(l_open_handle, 'NAME', p_object_name);
390 l_transform_handle := dbms_metadata.add_transform(l_open_handle, 'DDL');
391 dbms_metadata.set_transform_param(l_transform_handle, 'SQLTERMINATOR', false);
392 l_ddls := dbms_metadata.fetch_ddl(l_open_handle);
393 dbms_metadata.close(l_open_handle);
394
395 if (l_ddls.count > 0) then
396 l_ddl := dbms_lob.substr(l_ddls(1).ddltext);
397 end if;
398
399 log(c_module, 'PROCEDURE', 'end');
400 return l_ddl;
401
402 end FETCH_DDL;
403
404 procedure STORE_DEFS(p_owner IN varchar2,
405 p_mview_name IN varchar2,
406 p_ddl_defs IN OUT NOCOPY ddl_defs)
407 IS
408 c_module varchar2(25) := 'store_defs';
409 l_grant varchar2(32000);
410
411 cursor dep_indexes is
412 SELECT owner,
413 index_name
414 FROM dba_indexes
415 WHERE owner = p_owner
416 AND table_name = p_mview_name
417 AND index_name not like 'I_SNAP$%';
418
419 cursor dep_triggers is
420 SELECT owner,
421 trigger_name
422 FROM dba_triggers
423 WHERE owner = p_owner
424 AND table_name = p_mview_name;
425
426 cursor dep_constraints is
427 SELECT owner,
428 constraint_name
429 FROM dba_constraints
430 WHERE owner = p_owner
431 AND table_name = p_mview_name
432 AND generated = 'USER NAME';
433
434 cursor dep_grants is
435 SELECT distinct grantee,
436 privilege,
437 grantable,
438 hierarchy
439 FROM dba_tab_privs
440 WHERE owner = p_owner
441 AND table_name = p_mview_name
442 AND grantee <> 'SYSTEM';
443
444 BEGIN
445 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mview_name);
446
447 log(c_module, 'EVENT', 'Getting Index Definitions');
448 for objs in dep_indexes
449 loop
450 begin
451 p_ddl_defs(p_ddl_defs.count + 1) := fetch_ddl('INDEX', p_owner, objs.index_name);
452
453 exception
454 when others then
455 log(c_module, 'ERROR', 'Strange Exception for index '||objs.index_name||' of '||p_owner||'.'||p_mview_name||' '||SQLCODE);
456 raise;
457 end;
458 end loop;
459
460 log(c_module, 'EVENT', 'Getting Trigger Definitions');
461 for objs in dep_triggers
462 loop
463 begin
464 p_ddl_defs(p_ddl_defs.count + 1) := fetch_ddl('TRIGGER', p_owner, objs.trigger_name);
465
466 exception
467 when others then
468 log(c_module, 'ERROR', 'Strange Exception for trigger '||objs.trigger_name||' of '||p_owner||'.'||p_mview_name||' '||SQLCODE);
469 raise;
470 end;
471 end loop;
472
473 log(c_module, 'EVENT', 'Getting Constraint Definitions');
474 for objs in dep_constraints
475 loop
476 begin
477 p_ddl_defs(p_ddl_defs.count + 1) := fetch_ddl('CONSTRAINT', p_owner, objs.constraint_name);
478
479 exception
480 when others then
481 log(c_module, 'ERROR', 'Strange Exception for constraint '||objs.constraint_name||' of '||p_owner||'.'||p_mview_name||' '||SQLCODE);
482 raise;
483 end;
484 end loop;
485
486 log(c_module, 'EVENT', 'Getting Grant Definitions');
487 for objs in dep_grants
488 loop
489 l_grant := 'GRANT ';
490 begin
491 l_grant := l_grant|| objs.privilege || ' ON "' ||
492 p_owner || '"."'||p_mview_name || '" TO "' ||
493 objs.grantee || '" ' ;
494
495 if (nvl(objs.grantable, 'NO') = 'YES' ) then
496 l_grant := l_grant || ' WITH GRANT OPTION ';
497 end if;
498
499 if (nvl(objs.hierarchy, 'NO') = 'YES' and objs.privilege='SELECT' ) then
500 l_grant := l_grant || ' WITH HIERARCHY OPTION ';
501 end if;
502
503 p_ddl_defs(p_ddl_defs.count + 1) := l_grant;
504
505 exception
506 when others then
507 log(c_module, 'ERROR', 'Strange Exception for grant of '||p_owner||'.'||p_mview_name||' '||SQLCODE||' '||l_grant);
508 raise;
509 end;
510 end loop;
511
512 log(c_module, 'PROCEDURE', 'end: '||p_owner||'.'||p_mview_name);
513
514 END STORE_DEFS;
515
516 -- Drop MV Container Table and recreate MV from MVQ
517 procedure DROP_RECREATE(
518 p_owner varchar2,
519 p_mview_name varchar2)
520 is
521 c_module varchar2(25) := 'drop_recreate';
522 l_exp_mvdef clob;
523 l_exist number;
524 l_phase varchar2(80) := ad_zd_parallel_exec.C_PHASE_CUTOVER;
525 l_ddl_defs ddl_defs;
526 begin
527 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mview_name);
528
529 if (ad_zd.get_edition('PATCH') is null) then
530 l_phase := ad_zd_parallel_exec.C_PHASE_UPGRADE_MVIEW;
531 end if;
532
533 -- If MVLog exist then install mvlog marker
534 select count(1) into l_exist
535 from dba_mview_logs
536 where log_owner=p_owner and master=p_mview_name;
537
538 if (l_exist > 0) then
539 install_mvlog(p_owner, p_mview_name);
540 end if;
541
542 -- If Container Table exists, then drop it
543 select count(1) into l_exist
544 from dba_tables
545 where owner=p_owner and table_name=p_mview_name;
546
547 if (l_exist > 0) then
548 -- Backup secondary object definitions of container table
549 log(c_module, 'EVENT', 'Backing up dependent object DDLs of container table '||p_owner||'.'||p_mview_name);
550 store_defs(p_owner, p_mview_name, l_ddl_defs);
551
552 log(c_module, 'EVENT', 'Dropping container table');
553 exec(c_module, 'drop table '||p_owner||'."'||p_mview_name||'"');
554 end if;
555
556 -- Generate MV definition
557 l_exp_mvdef := generate(p_owner, p_mview_name);
558 log(c_module, 'EVENT', 'Creating MV Definition with empty container table');
559 begin
560 exec(c_module, l_exp_mvdef);
561 exception
562 when others then
563 log(c_module, 'ERROR', 'MV creation failed. Storing all dependent DDLs in AD_ZD_DDL_HANDLER table');
564 for idx in 1..l_ddl_defs.count
565 loop
566 ad_zd_parallel_exec.load(
567 x_phase => l_phase,
568 x_sql => l_ddl_defs(idx),
569 x_unique => true);
570 log(c_module, 'ERROR', l_ddl_defs(idx));
571 end loop;
572 raise;
573 end;
574
575 log(c_module, 'EVENT', 'Creating dependent objects of container table');
576 for idx in 1..l_ddl_defs.count
577 loop
578 begin
579 exec(c_module, l_ddl_defs(idx));
580 exception
581 when others then
582 log(c_module, 'ERROR', 'Dependent object creation failed. Storing DDL in AD_ZD_DDL_HANDLER table');
583 ad_zd_parallel_exec.load(
584 x_phase => l_phase,
585 x_sql => l_ddl_defs(idx),
586 x_unique => true);
587 log(c_module, 'ERROR', l_ddl_defs(idx));
588 end;
589 end loop;
590
591 -- If saved MV Log data exists, recreate MV Log
592 select count(1) into l_exist
593 from dba_views
594 where owner=p_owner
595 and view_name=get_mvq_name(p_mview_name, g_mvl_char);
596
597 if (l_exist > 0) then
598 recreate_mvlog(p_owner, p_mview_name);
599 end if;
600
601 log(c_module, 'PROCEDURE', 'end');
602 end;
603
604
605 -- Get MVQ defintiion and comment
606 -- GB: why are we selecting from dual?
607 procedure get_mvq_definition(p_owner IN varchar2,
608 p_mvqname IN varchar2,
609 p_mvqdef OUT NOCOPY CLOB,
610 p_comment OUT NOCOPY CLOB)
611 IS
612 c_module varchar2(25) := 'get_mvq_definition';
613 BEGIN
614 log(c_module, 'STATEMENT', 'Getting Logical Definition from '||p_owner||'.'||p_mvqname);
615
616 begin
617 SELECT dbms_metadata.get_ddl('VIEW', p_mvqname, p_owner)
618 INTO p_mvqdef
619 FROM dual;
620 exception
621 when others then
622 log(c_module, 'ERROR', 'Strange Exception '||p_owner||'.'||p_mvqname||' '||SQLCODE);
623 p_mvqdef := null;
624 p_comment := null;
625 raise;
626 end;
627
628 begin
629 SELECT dbms_metadata.get_dependent_ddl('COMMENT', p_mvqname, p_owner)
630 INTO p_comment
631 FROM dual;
632 exception
633 when others then
634 if (SQLCODE = -31608) then
635 p_comment := null;
636 else
637 log(c_module, 'ERROR', 'Exception while calling
638 dbms_metadata.get_dependent_ddl to get the comment'||SQLCODE);
639 raise;
640 end if;
641 end;
642 end get_mvq_definition;
643
644 -- Regenerate MV from MVQ
645 procedure process_mv(p_owner varchar2,
646 p_mview_name varchar)
647 is
648 c_module varchar2(25) := 'process_mv';
649 l_exp_mvdef clob;
650 l_dropmv clob;
651 l_prebuilt number;
652 l_mvexist number;
653 l_syn_exist number;
654 l_owner varchar2(32);
655 l_appsname varchar2(30);
656 l_mvqdef clob;
657 l_mvqcomment clob;
658 l_mvq_name varchar2(30) := get_mvq_name(p_mview_name);
659
660 begin
661 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mview_name);
662
663 l_appsname := ad_zd.apps_schema;
664 l_owner := trim(nvl(p_owner, l_appsname));
665
666 -- Check if MV exists
667 select count(1) into l_mvexist
668 from dba_mviews
669 where owner=l_owner and mview_name=p_mview_name;
670
671 -- Drop existing MV if it exists
672 if (l_mvexist = 1) then
673 log(c_module, 'EVENT', 'Dropping old MV, preserve container table');
674 l_dropmv :=
675 'drop materialized view '||l_owner||'."'||p_mview_name||'" preserve table';
676 exec(c_module, l_dropmv);
677 end if;
678
679 get_mvq_definition(l_owner, l_mvq_name, l_mvqdef, l_mvqcomment);
680
681 -- Check if prebuilt table exists
682 select count(1) into l_prebuilt
683 from dba_tables
684 where owner=l_owner and table_name=p_mview_name;
685
686 if l_prebuilt = 0 then
687
688 -- create MV and table
689 drop_recreate(l_owner, p_mview_name);
690
691 else
692
693 -- create MV with pre-built table
694 l_exp_mvdef := generate(l_owner, p_mview_name);
695
696 declare
697 e_rowid_prebuilt exception;
698 PRAGMA EXCEPTION_INIT(e_rowid_prebuilt, -12058);
699 e_udt_prebuilt exception;
700 PRAGMA EXCEPTION_INIT(e_udt_prebuilt, -32304);
701 e_shape_mismatch exception;
702 PRAGMA EXCEPTION_INIT(e_shape_mismatch, -12060);
703 e_table_already_referenced exception;
704 PRAGMA EXCEPTION_INIT(e_table_already_referenced, -32334);
705 begin
706 log(c_module, 'EVENT', 'Creating revised MV Definition for original container table');
707 exec(c_module, l_exp_mvdef, l_owner);
708 exception
709 when e_udt_prebuilt or
710 e_rowid_prebuilt or
711 e_shape_mismatch or
712 e_table_already_referenced
713 then
714 drop_recreate(l_owner, p_mview_name);
715 end;
716
717 end if;
718
719
720 -- If the MV is not in APPS create synonym
721 if (upper(l_owner) <> l_appsname) then
722
723 -- test if synonym exists
724 select count(1) INTO l_syn_exist
725 from dba_synonyms
726 where synonym_name=p_mview_name
727 and owner=l_appsname
728 and table_owner=upper(l_owner)
729 and table_name=synonym_name;
730
731 if (l_syn_exist = 0) then
732 log(c_module, 'EVENT', 'Creating APPS synonym for non-APPS MV');
733 exec(c_module, 'CREATE OR REPLACE SYNONYM '||
734 l_appsname||'."'||p_mview_name||'" FOR "'||
735 l_owner||'"."'||p_mview_name || '"');
736 end if;
737
738 -- FUTURE: grant to APPS
739 -- exec(c_module, 'GRANT ALL ON "'||l_owner||'"."'||p_mview_name||
740 -- '" TO '||l_appsname||' WITH GRANT OPTION');
741
742 end if;
743
744 -- Execute automatic refresh if needed:
745 if (instr(l_mvqdef, '/*AUTOREFRESH*/')) > 0 then
746 dbms_mview.refresh(p_owner||'.'||p_mview_name,'?');
747 log(c_module, 'EVENT', 'Refreshed MV as AUTOREFRESH commentis present');
748 end if;
749
750 log(c_module, 'PROCEDURE', 'end');
751 end process_mv;
752
753
754 /*-----------------------------------------------------------------+
755 | |
756 | GET_COLUMN_ALIAS |
757 | |
758 | Tasks :- |
759 | Takes the input string and returns the column alias string |
760 | the column alias string |
761 | |
762 +-----------------------------------------------------------------*/
763 procedure get_column_alias(l_cmt1 IN CLOB,
764 l_cmt OUT NOCOPY CLOB,
765 l_column_alias OUT NOCOPY CLOB)
766 IS
767 c_module varchar2(25) := 'get_column_alias';
768 l_ctr number :=0;
769 l_dquote number :=0;
770 l_itr number :=1;
771 l_copy_cmt1 clob;
772 l_chr varchar2(1);
773 BEGIN
774
775 -- Trim all the new line, space and tab characters
776 l_copy_cmt1 := trim(leading fnd_const.newline from l_cmt1);
777 l_copy_cmt1 := trim(leading ' ' from l_copy_cmt1);
778 l_copy_cmt1 := trim(leading fnd_const.newline from l_copy_cmt1);
779 l_copy_cmt1 := trim(leading ' ' from l_copy_cmt1);
780 l_copy_cmt1 := trim(leading fnd_const.newline from l_copy_cmt1);
781 l_copy_cmt1 := trim(leading ' ' from l_copy_cmt1);
782 l_chr := substr(l_copy_cmt1, 1, 1);
783
784
785
786
787 l_copy_cmt1 := trim(leading ' ' from l_copy_cmt1);
788 -- log(c_module, 'Input string = ' || l_cmt1);
789 -- log(c_module, 'Modified Input string = ' || l_copy_cmt1);
790 -- log(c_module, 'l_chr = ' || l_chr);
791 if (l_chr = '(')
792 THEN
793 loop
794 -- log(c_module, 'Came into loop');
795 l_chr := substr(l_cmt1, l_itr, 1);
796 -- log(c_module, 'l_chr = ' || l_chr);
797 if (l_chr = '"')
798 THEN
799 if (l_dquote = 0)
800 THEN
801 l_dquote := 1;
802 ELSE
803 l_dquote := 0;
804 END IF;
805 END IF;
806 if (l_dquote = 0 and l_chr = '(')
807 THEN
808 l_ctr := l_ctr + 1;
809 -- log(c_module, 'Increased = ' || l_ctr);
810 END IF;
811
812 if (l_dquote = 0 and l_chr = ')')
813 THEN
814 l_ctr := l_ctr - 1;
815 -- log(c_module, 'Decreased = ' || l_ctr);
816 END IF;
817
818 l_itr := l_itr + 1;
819 exit when ((l_chr is null)or((l_ctr = 0) and (l_chr = ')')));
820 end loop;
821 l_column_alias := substr(l_cmt1, 1, l_itr);
822 l_cmt := substr(l_cmt1, l_itr+1);
823 ELSE
824 -- log(c_module, 'Didnot go into loop');
825 l_cmt := l_cmt1;
826 l_column_alias := null;
827 END IF;
828 -- log(c_module, 'Output column alias = ' || l_column_alias);
829 -- log(c_module, 'Output comment = ' || l_cmt);
830 END get_column_alias;
831
832 /*-----------------------------------------------------------------+
833 | |
834 | GENERATE_MVQDEF |
835 | This api tkaes the MV definition as input and generates |
836 | MVQ definition and its comment. |
837 | |
838 | Input :- "CREATE MATERIALIZED VIEW "owner"."mvname".... |
839 | statement |
840 | Output :- MVQ Definition and its comment |
841 | |
842 +-----------------------------------------------------------------*/
843 procedure generate_mvqdef(p_mvdef IN CLOB,
844 p_owner OUT nocopy VARCHAR2,
845 p_mvname OUT nocopy VARCHAR2,
846 p_mvqdef OUT NOCOPY CLOB,
847 p_comment OUT NOCOPY CLOB)
848 is
849 c_module varchar2(25) := 'generate_mvqdef';
850 pos number := 0;
851 text CLOB;
852 l_cmt CLOB;
853 l_column_alias CLOB := null;
854 l_cmt1 CLOB;
855 rempart CLOB;
856 l_mvdef CLOB;
857 mvname VARCHAR2(32);
858 mvnamequot VARCHAR2(32);
859 mvqname VARCHAR2(32);
860 owner varchar2(32);
861 ownerquot varchar2(32);
862 l_tmp varchar2(100);
863 l_table_Exist number;
864 begin
865 log(c_module, 'PROCEDURE', 'begin: parameter is of a datatype not suitable for logging');
866 l_mvdef := p_mvdef;
867
868 /*---------------------------------------------------------------------+
869 | |
870 | Step 1: |
871 | Remove CREATE MATERIALIZED VIEW from mv definition |
872 | |
873 | CREATE MATERIALIZED VIEW "APPS"."FINALMV" ("A", "C", "B", "D") |
874 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
875 | NOCOMPRESS LOGGING |
876 | TABLESPACE "SYSTEM" |
877 | BUILD IMMEDIATE |
878 | USING INDEX |
879 | REFRESH FORCE ON DEMAND |
880 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
881 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
882 | AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2 |
883 +---------------------------------------------------------------------*/
884 text := regexp_replace(l_mvdef, '[[:space:]]*CREATE[[:space:]]+' ||
885 '(MATERIALIZED[[:space:]]+VIEW|SNAPSHOT)' ||
886 '[[:space:]]+',
887 '', 1, 1, 'i');
888
889 /*---------------------------------------------------------------------+
890 | |
891 | Step 2: |
892 | Extract owner and mvname. |
893 | Hint: Search for first space or ( character. Till that letter is |
894 | the name. Care must be taken that sometimes owner might not |
895 | be there and quotes might not be there |
896 | |
897 | "APPS"."FINALMV" ("A", "C", "B", "D") |
898 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
899 | NOCOMPRESS LOGGING |
900 | TABLESPACE "SYSTEM" |
901 | BUILD IMMEDIATE |
902 | USING INDEX |
903 | REFRESH FORCE ON DEMAND |
904 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
905 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
906 | AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2 |
907 +---------------------------------------------------------------------*/
908 pos := regexp_instr(text, '[ ('||fnd_const.newline||']');
909
910 l_tmp := substr(text,1, pos-1);
911 l_tmp := regexp_replace(l_tmp, fnd_const.newline, ' ');
912
913 splitnameowner(l_tmp, ownerquot, mvnamequot, 0);
914 p_owner := ownerquot;
915 p_mvname := mvnamequot;
916 mvqname := get_mvq_name(mvnamequot);
917 mvqname := '"'||mvqname||'"';
918 owner := '"'||ownerquot||'"';
919 --log(c_module, 'STATEMENT', 'MVQ Name = '||mvqname);
920 --log(c_module, 'STATEMENT', 'mvnamequot= '||mvnamequot);
921 --log(c_module, 'STATEMENT', 'Ownerquot = '||ownerquot);
922
923 /*---------------------------------------------------------------------+
924 | |
925 | Step 3: |
926 | Remove owner and mvname. |
927 | |
928 | "APPS"."FINALMV" ("A", "C", "B", "D") |
929 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
930 | NOCOMPRESS LOGGING |
931 | TABLESPACE "SYSTEM" |
932 | BUILD IMMEDIATE |
933 | USING INDEX |
934 | REFRESH FORCE ON DEMAND |
935 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
936 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
937 | AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2 |
938 +---------------------------------------------------------------------*/
939 -- We are done with the naming stuff. Hence remove the mview name from the text
940
941 text := regexp_replace(text, '((")?'||ownerquot||'(")?.)?(")?'||mvnamequot||'(")?[[:space:]]*', '', 1, 1, 'i');
942 text := trim(text);
943
944 /*---------------------------------------------------------------------+
945 | |
946 | Step 4: |
947 | Extract mv attributes. |
948 | Hint: Extract till AS keyword |
949 | |
950 | ("A", "C", "B", "D") |
951 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
952 | NOCOMPRESS LOGGING |
953 | TABLESPACE "SYSTEM" |
954 | BUILD IMMEDIATE |
955 | USING INDEX |
956 | REFRESH FORCE ON DEMAND |
957 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
958 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
959 | AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2 |
960 +---------------------------------------------------------------------*/
961 pos := regexp_instr(text, '([[:space:]]|'||fnd_const.newline||
962 ')?AS('||
963 fnd_const.newline||'|[[:space:]])', 1, 1, 0, 'i');
964
965 --log(c_module, 'STATEMENT', 'step4 pos = '||pos);
966 --log(c_module, 'STATEMENT', 'regext_instr worked');
967 -- l_cmt1 doesn't really contain only the comment part or attributes
968 -- It may also contain the column alias.
969 l_cmt1 := substr(text, 1, pos-1);
970 text := substr(text, pos);
971 /*---------------------------------------------------------------------+
972 | |
973 | Step 5: |
974 | Extract column alias and comment from the attributes. |
975 | |
976 | ("A", "C", "B", "D") |
977 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
978 | NOCOMPRESS LOGGING |
979 | TABLESPACE "SYSTEM" |
980 | BUILD IMMEDIATE |
981 | USING INDEX |
982 | REFRESH FORCE ON DEMAND |
983 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
984 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
985 +---------------------------------------------------------------------*/
986 get_column_alias(l_cmt1, l_cmt, l_column_alias);
987
988 /*---------------------------------------------------------------------+
989 | |
990 | Step 6: |
991 | escape single quotes in the comment. |
992 | |
993 | ("A", "C", "B", "D") |
994 | ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 |
995 | NOCOMPRESS LOGGING |
996 | TABLESPACE "SYSTEM" |
997 | BUILD IMMEDIATE |
998 | USING INDEX |
999 | REFRESH FORCE ON DEMAND |
1000 | USING DEFAULT LOCAL ROLLBACK SEGMENT |
1001 | USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE |
1002 +---------------------------------------------------------------------*/
1003 -- Escape single quotes
1004 l_cmt := regexp_replace(l_cmt, '''', '''''');
1005
1006 /*---------------------------------------------------------------------+
1007 | |
1008 | Step 7: |
1009 | If comment contains PREBUILT option but table doesn't exist |
1010 | then remove it |
1011 | |
1012 +---------------------------------------------------------------------*/
1013 /*
1014
1015 Do not delete the below code. It may happen we need to reenable the code
1016 SELECT count(1)
1017 INTO l_table_exist
1018 FROM dba_tables
1019 WHERE owner=nvl(upper(owner), upper('APPS'))
1020 AND table_name=mvnamequot;
1021
1022 if (l_table_exist = 0)
1023 then
1024 l_cmt := regexp_replace(l_cmt, 'ON PREBUILT TABLE', ' ', 1, 1, 'i');
1025 l_cmt := regexp_replace(l_cmt, 'WITH REDUCED PRECISION', ' ', 1, 1, 'i');
1026 l_cmt := regexp_replace(l_cmt, 'WITHOUT REDUCED PRECISION', ' ', 1, 1, 'i');
1027 end if;
1028
1029 -- log(c_module, 'step7 l_cmt = '||l_cmt);
1030 */
1031 /*---------------------------------------------------------------------+
1032 | |
1033 | Step 8: |
1034 | Extract select query |
1035 | Hint: Extract the clause after the AS keyword from the remaining |
1036 | part |
1037 | |
1038 | AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2 |
1039 +---------------------------------------------------------------------*/
1040 -- Now right after ' as ' word this is the select query.
1041 pos := regexp_instr(text, 'AS', 1, 1, 0, 'i');
1042 --log(c_module, 'STATEMENT', 'step6 as position is = '||pos);
1043 rempart := substr(text, pos+3);
1044
1045 -- Now construct a MVQ ddl and its comment
1046 if (length(ownerquot) > 0) then
1047 -- log(c_module, 'STATEMENT', 'owner present');
1048 p_mvqdef := 'CREATE OR REPLACE VIEW '||owner||'.'||mvqname||l_column_alias||' AS '||rempart;
1049 -- log(c_module, 'STATEMENT', 'done string mvqdef');
1050 p_comment := 'COMMENT ON TABLE '||owner||'.'||mvqname||' is '''||l_cmt||'''';
1051 -- log(c_module, 'STATEMENT', 'done string comment');
1052 else
1053 -- log(c_module, 'STATEMENT', 'owner not present');
1054 p_mvqdef := 'CREATE OR REPLACE VIEW '||mvqname||l_column_alias||' AS '||rempart;
1055 -- log(c_module, 'STATEMENT', 'done string mvqdef');
1056 p_comment := 'COMMENT ON TABLE '||mvqname||' is '''||l_cmt||'''';
1057 -- log(c_module, 'STATEMENT', 'done string comment');
1058 end if;
1059
1060 log(c_module, 'PROCEDURE', 'end');
1061 END generate_mvqdef;
1062
1063
1064 -- Create MV based on MVQ
1065 procedure install_mv(p_owner varchar2,
1066 p_mvname varchar2)
1067 is
1068 c_module varchar2(25) := 'install_mv';
1069 l_mvq_name varchar2(30) := get_mvq_name(p_mvname);
1070 l_mv_exist number;
1071 l_mvq_exist number;
1072 l_mvl_exist number;
1073 l_mv_text clob;
1074 l_mvq_text clob;
1075 l_exp_mvdef clob;
1076 l_appsname varchar2(30);
1077 l_owner varchar2(30);
1078
1079 BEGIN
1080 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mvname);
1081
1082 l_appsname := ad_zd.apps_schema;
1083 l_owner := trim(nvl(p_owner, l_appsname));
1084
1085 -- Verify MVQ exists (it must)
1086 SELECT count(1)
1087 INTO l_mvq_exist
1088 FROM dba_views
1089 WHERE owner=l_owner and view_name=l_mvq_name;
1090
1091 if (l_mvq_exist = 0) then
1092 log(c_module, 'ERROR', 'Logical View '||l_mvq_name||' does not exist');
1093 RAISE_APPLICATION_ERROR(-20001, 'Logical View '||l_mvq_name||' does not exist');
1094 end if;
1095
1096 -- Check if MV exists (it might)
1097 SELECT count(1)
1098 INTO l_mv_exist
1099 FROM dba_mviews
1100 WHERE owner=l_owner and mview_name=p_mvname;
1101
1102 if (l_mv_exist = 0) then
1103
1104 log(c_module, 'STATEMENT', 'MV missing, create it');
1105 process_mv(l_owner, p_mvname);
1106
1107 else
1108
1109 log(c_module, 'STATEMENT', 'MV exists, checking definition');
1110
1111 delete from ad_zd_clob
1112 where owner=l_owner and name in
1113 (p_mvname, get_mvq_name(p_mvname));
1114
1115 -- Get MV query text into CLOB
1116 insert into ad_zd_clob(owner, name, query)
1117 select owner, mview_name, to_lob(query)
1118 from dba_mviews
1119 where owner=l_owner and mview_name=p_mvname;
1120
1121 select query into l_mv_text
1122 from ad_zd_clob
1123 where owner=l_owner and name=p_mvname;
1124
1125 -- Get MVQ query text into CLOB
1126 insert into ad_zd_clob(owner, name, query)
1127 select owner, view_name, to_lob(text)
1128 from dba_views
1129 where owner=l_owner and view_name=l_mvq_name;
1130
1131 select query into l_mvq_text
1132 from ad_zd_clob
1133 where owner=l_owner and name=l_mvq_name;
1134
1135 -- Compare query text, process if different
1136 if (dbms_lob.compare(expand_query(l_mvq_text), l_mv_text) <> 0) then
1137 log(c_module, 'STATEMENT', 'MV definition is out of date, revising...');
1138 process_mv(l_owner, p_mvname);
1139 else
1140 log(c_module, 'STATEMENT', 'MV definition is up to date');
1141 -- possibly compile to mark as up to date
1142 end if;
1143
1144 END IF;
1145
1146 -- test if MV Log exists
1147 -- TODO: do not create a junk object just to store one bit of information
1148 select count(1) into l_mvl_exist
1149 from dba_views
1150 where owner=l_owner and view_name=get_mvq_name(p_mvname, g_mvl_char);
1151
1152 if (l_mvl_exist > 0) then
1153 recreate_mvlog(l_owner, p_mvname);
1154 end if;
1155
1156
1157 -- If any exceptions then skip to next iteration
1158 log(c_module, 'PROCEDURE', 'end');
1159 END install_mv;
1160
1161
1162 -- Generate and Install MVQ
1163 procedure install_mvq(p_mvdef clob,
1164 p_owner out nocopy varchar2,
1165 p_mvname out nocopy varchar2)
1166 is
1167 c_module varchar2(25) := 'install_mvq';
1168 l_mvqdef clob;
1169 l_comment clob;
1170 begin
1171 generate_mvqdef(p_mvdef, p_owner, p_mvname, l_mvqdef, l_comment);
1172 exec(c_module, l_mvqdef);
1173 exec(c_module, l_comment);
1174 end;
1175
1176
1177 -- Returns MV definition based on MVQ
1178 function transform_to_mv(p_mvqdef CLOB,
1179 p_mvqcomment CLOB,
1180 p_prebuilt number) return CLOB
1181 IS
1182 c_module varchar2(25) := 'transform_to_mv';
1183 l_mvdef CLOB := null;
1184 l_modmvqdef CLOB := null;
1185 l_part1 CLOB;
1186 l_attributes CLOB;
1187 l_prebuilt_attributes CLOB;
1188 l_query CLOB;
1189 l_expquery CLOB;
1190 l_pos number :=0;
1191 l_mvqname VARCHAR2(65);
1192 l_mvname VARCHAR2(65);
1193 l_colstring CLOB;
1194 l_tmp1 clob;
1195 l_tmp2 clob;
1196 BEGIN
1197 l_modmvqdef := p_mvqdef;
1198
1199 /*-------------------------------------------------------------+
1200 | Step 1: |
1201 | Remove CREATE OR REPLACE FORCE VIEW from mvq definition |
1202 | |
1203 | CREATE OR REPLACE FORCE VIEW "APPS"."V1_MVQ" ("A", "B",|
1204 | "C", "D") AS |
1205 | select "A","B","C","D" from mv1 |
1206 +-------------------------------------------------------------*/
1207 l_part1 := regexp_replace(l_modmvqdef,
1208 '[[:space:]]*CREATE' ||
1209 '[[:space:]]+(OR[[:space:]]+REPLACE[[:space:]]+)?(FORCE[[:space:]]+)?VIEW[[:space:]]+',
1210 '', 1, 1, 'i');
1211
1212 /*-------------------------------------------------------------+
1213 | Step 2: |
1214 | The next word will be mvq name. Extract it and generate |
1215 | mv name out of it. |
1216 | |
1217 | "APPS"."V1_MVQ" ("A", "B", |
1218 | "C", "D") AS |
1219 | select "A","B","C","D" from mv1 |
1220 +--------------------------------------------------------------*/
1221 l_pos := regexp_instr(l_part1, ' |\(');
1222 l_mvqname := substr(l_part1,1, l_pos-1);
1223 l_mvname := get_mv_name(l_mvqname);
1224
1225 log(c_module, 'STATEMENT', 'Transforming Logical Definition to MV Definition: '||l_mvqname||' -> '||l_mvname);
1226
1227 /*-------------------------------------------------------------+
1228 | Step 3: |
1229 | We are done with the mvq name. Remove it |
1230 | |
1231 | ("A", "B", |
1232 | "C", "D") AS |
1233 | select "A","B","C","D" from mv1 |
1234 +--------------------------------------------------------------*/
1235 l_part1 := regexp_replace(l_part1, l_mvqname||'[[:space:]]*', '', 1, 1, 'i');
1236
1237 /*-------------------------------------------------------------+
1238 | Step 4: |
1239 | Extract the column string and remove it |
1240 | Hint : Extract it till AS keyword |
1241 | |
1242 | AS |
1243 | select "A","B","C","D" from mv1 |
1244 +--------------------------------------------------------------*/
1245 l_pos := regexp_instr(l_part1, '([[:space:]]|'||fnd_const.newline||
1246 ')?AS('||
1247 fnd_const.newline||'|[[:space:]])', 1, 1, 0, 'i');
1248 if (l_pos > 0)
1249 THEN
1250 l_colstring := substr(l_part1, 1, l_pos-1);
1251 ELSE
1252 l_colstring := ' ';
1253 END IF;
1254
1255 l_part1 := substr(l_part1, l_pos);
1256
1257 /*-------------------------------------------------------------+
1258 | Step 5: |
1259 | Extract the select query |
1260 | Hint : Extract it everything after AS keyword |
1261 | |
1262 | select "A","B","C","D" from mv1 |
1263 +-------------------------------------------------------------*/
1264 l_query := substr(l_part1, 4);
1265
1266 /*-------------------------------------------------------------+
1267 | Step 6: |
1268 | Expand the select query |
1269 | |
1270 | select "A","B","C","D" from mv1 |
1271 +-------------------------------------------------------------*/
1272 l_expquery := expand_query(l_query);
1273
1274 if (nvl(p_mvqcomment,'X') <> 'X') then
1275 l_attributes := regexp_replace(p_mvqcomment,
1276 '[[:space:]]*COMMENT' ||
1277 '[[:space:]]+ON' ||
1278 '[[:space:]]+TABLE' ||
1279 '[[:space:]]+(")?(.)+(")?' ||
1280 '[[:space:]]+IS' ||
1281 '[[:space:]]+''',
1282 '', 1, 1, 'i');
1283 l_attributes := trim(l_attributes);
1284
1285 -- Remove the last single quote
1286 l_attributes := substr(l_attributes, 1, length(l_attributes)-1);
1287 else
1288 l_attributes := ' ';
1289 end if;
1290
1291 if (p_prebuilt > 0) then
1292 l_prebuilt_attributes := include_prebuilt(l_attributes);
1293 else
1294 l_tmp1 := regexp_replace(l_attributes,
1295 'ON[[:space:]]+PREBUILT[[:space:]]+TABLE', ' ', 1, 1, 'i');
1296 l_tmp2 := regexp_replace(l_tmp1,
1297 'WITH[[:space:]]+REDUCED[[:space:]]+PRECISION', ' ', 1, 1, 'i');
1298 l_prebuilt_attributes := regexp_replace(l_tmp2,
1299 'WITHOUT[[:space:]]+REDUCED[[:space:]]+PRECISION', ' ', 1, 1, 'i');
1300 end if;
1301
1302 l_mvdef := 'CREATE MATERIALIZED VIEW '||l_mvname||' '||l_colstring||
1303 ' '||l_prebuilt_attributes||' AS '||l_expquery;
1304
1305 return l_mvdef;
1306 END transform_to_mv;
1307
1308
1309
1310 /*******************************************************************
1311 * *
1312 * PUBLIC APIS *
1313 * *
1314 *******************************************************************/
1315
1316
1317 /*=================================================================*
1318 * Below apis needed for xdf *
1319 *=================================================================*/
1320
1321 /*-----------------------------------------------------------------+
1322 | |
1323 | get_mvq_name |
1324 | Translates MV Name into MVQ Name |
1325 | |
1326 +-----------------------------------------------------------------*/
1327 function GET_MVQ_NAME(name varchar2, ext varchar2 default ' ')
1328 return varchar2
1329 IS
1330 BEGIN
1331 if (ext = ' ')
1332 then
1333 return convert_name(name, 1, g_mvq_char);
1334 else
1335 return convert_name(name, 1, ext);
1336 end if;
1337 end;
1338
1339 /*-----------------------------------------------------------------+
1340 | |
1341 | GET_MV_NAME |
1342 | Translates MVQ Name into MV Name |
1343 | |
1344 +-----------------------------------------------------------------*/
1345 function GET_MV_NAME(name varchar2, ext varchar2 default ' ')
1346 return varchar2
1347 IS
1348 BEGIN
1349 if (ext = ' ')
1350 then
1351 return convert_name(name, 0, g_mvq_char);
1352 else
1353 return convert_name(name, 0, ext);
1354 end if;
1355 end;
1356
1357
1358 /*=================================================================*
1359 * Below apis needed for ad_mv *
1360 *=================================================================*/
1361
1362 /*-----------------------------------------------------------------+
1363 | |
1364 | INSTALL_MVQ_ARCH |
1365 | |
1366 | p_mvdef => MV ddl |
1367 | p_skipmv => 1 Only installs MVQ. This mode will be used |
1368 | from patch |
1369 | 0 Installs both MVQ and MV. Assumes that MV |
1370 | doesn't exist |
1371 | Installs MVQ and expanded MV |
1372 | Called from patch and ad_mv |
1373 +-----------------------------------------------------------------*/
1374 procedure INSTALL_MVQ_ARCH(p_mvdef CLOB,
1375 p_skipmv number default 0)
1376 IS
1377 c_module varchar2(25) := 'install_mvq_arch';
1378 l_owner varchar2(65);
1379 l_mvname varchar2(65);
1380 BEGIN
1381 log(c_module, 'PROCEDURE', 'begin: parameter is of a datatype not suitable for logging');
1382 install_mvq(p_mvdef, l_owner, l_mvname);
1383
1384 if (p_skipmv = 0)
1385 then
1386 install_mv(l_owner, l_mvname);
1387 end if;
1388 log(c_module, 'PROCEDURE', 'end');
1389 end;
1390
1391 /*+----------------------------------------------------------------+
1392 | |
1393 | DROP_MVQ |
1394 | |
1395 | Takes the MV Owner and MV Name and drop its |
1396 | corresponding MVQ. |
1397 | Mainly this api is used in ad_mv package while |
1398 | droping the MView |
1399 +----------------------------------------------------------------+*/
1400
1401 procedure DROP_MVQ(p_owner varchar2 default ' ',
1402 p_mvname varchar2)
1403 is
1404 c_module varchar2(25) := 'drop_mvq';
1405 l_mvname varchar2(30);
1406 l_exist number;
1407 l_statement varchar2(200);
1408 l_appsname varchar2(30);
1409 l_owner varchar2(30);
1410 begin
1411 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mvname);
1412
1413 l_appsname := ad_zd.apps_schema;
1414 if(length(trim(nvl(p_owner, ' '))) > 0)
1415 then
1416 l_owner := upper(trim(p_owner));
1417 else
1418 l_owner := l_appsname;
1419 end if;
1420 SELECT count(1)
1421 INTO l_exist
1422 FROM dba_views
1423 WHERE owner=l_owner
1424 AND view_name=upper(get_mvq_name(p_mvname))
1425 AND EDITIONING_VIEW='N';
1426
1427 If (l_exist > 0)
1428 then
1429 log(c_module, 'EVENT', 'Dropping Logical View: '||get_mvq_name(p_mvname));
1430 l_statement := 'DROP VIEW '||l_owner||'."'||upper(get_mvq_name(p_mvname))||'" CASCADE CONSTRAINTS';
1431 exec(c_module, l_statement);
1432 end if;
1433
1434 SELECT count(1)
1435 INTO l_exist
1436 FROM dba_views
1437 WHERE owner=l_owner
1438 AND view_name=upper(get_mvq_name(p_mvname, g_mvl_char))
1439 AND EDITIONING_VIEW='N';
1440
1441 If (l_exist > 0)
1442 then
1443 l_statement := 'DROP VIEW '||l_owner||'."'||get_mvq_name(p_mvname, g_mvl_char)||'" CASCADE CONSTRAINTS';
1444 exec(c_module, l_statement);
1445 end if;
1446
1447 SELECT count(1)
1448 INTO l_exist
1449 FROM dba_views
1450 WHERE owner=l_owner
1451 AND view_name=upper(get_mvq_name(p_mvname, g_mvm_char))
1452 AND EDITIONING_VIEW='N';
1453
1454 If (l_exist > 0)
1455 then
1456 l_statement := 'DROP VIEW '||p_owner||'."'||get_mvq_name(l_mvname, g_mvm_char)||'" CASCADE CONSTRAINTS';
1457 exec(c_module, l_statement);
1458 end if;
1459 log(c_module, 'PROCEDURE', 'end');
1460 end;
1461
1462
1463 /*=================================================================*
1464 * Below apis given by GB *
1465 *=================================================================*/
1466
1467 /*-----------------------------------------------------------------+
1468 | |
1469 | PATCH |
1470 | This api would be called from two places. One from xdf file |
1471 | And other place is from FIX_MATERIALIZED_VIEWS procedure. |
1472 | |
1473 | Input :- "CREATE MATERIALIZED VIEW "owner"."mvname".... |
1474 | statement |
1475 | Database always gives the ddl in the above format. |
1476 | And xdf also should give in the same above format |
1477 | |
1478 | Tasks :- |
1479 | 1. Parse the given mv ddl and generate MVQ definition |
1480 | and its corresponding comment. |
1481 | 2. Execute both the above MVQ ddl and comment stmts. |
1482 | |
1483 | Notes :- MVQ is just a normal view in db |
1484 | All the Mview attributes will be added as |
1485 | comments to MVQ |
1486 | |
1487 +-----------------------------------------------------------------*/
1488 -- TODO: Try to use the dbms_metadata to convert the ddl into xml format
1489 -- always do execute immediate. Because it installs only MVQ
1490 procedure PATCH(p_mvdef CLOB)
1491 is
1492 c_module varchar2(25) := 'patch';
1493 l_mvqdef CLOB;
1494 l_comment CLOB;
1495 l_mvdef CLOB;
1496 BEGIN
1497 log(c_module, 'PROCEDURE', 'begin: parameter is of a datatype not suitable for logging');
1498 install_mvq_arch(p_mvdef => p_mvdef,
1499 p_skipmv => 1);
1500 log(c_module, 'PROCEDURE', 'end');
1501 end;
1502
1503 /*-----------------------------------------------------------------+
1504 | |
1505 | PATCH |
1506 | |
1507 | p_owner => owner |
1508 | p_name => object name |
1509 | p_type => object type. default is null |
1510 | |
1511 | |
1512 | Notes: This api will actualize the mvq for those mviews |
1513 | which are dependent on the given object |
1514 | |
1515 | Later in the cutover these mviews will be recreated |
1516 | so that the new/revised vpd policies will be involed |
1517 | in the expanded mview definition |
1518 +-----------------------------------------------------------------*/
1519 procedure PATCH(p_owner varchar2,
1520 p_name varchar2,
1521 p_type varchar2 default null)
1522 IS
1523 c_module varchar2(25) := 'patch';
1524
1525 cursor depobjs is
1526 SELECT owner,
1527 name
1528 FROM dba_dependencies
1529 WHERE referenced_name=upper(p_name)
1530 AND referenced_owner=upper(p_owner)
1531 AND referenced_type=upper(nvl(p_type, referenced_type))
1532 AND type='MATERIALIZED VIEW';
1533
1534 l_exist number;
1535 l_mvqname varchar2(30);
1536 BEGIN
1537 if (p_owner is null or p_name is null) then
1538 log(c_module, 'ERROR','Either p_owner or p_name is null');
1539 raise_application_error(-20001, 'null passed to one of the parameter');
1540 end if;
1541
1542 for obj in depobjs
1543 loop
1544 l_mvqname := get_mvq_name(obj.name);
1545 SELECT count(1)
1546 INTO l_exist
1547 FROM dba_views
1548 WHERE owner=obj.owner
1549 AND view_name=l_mvqname;
1550
1551 if (l_exist > 0)
1552 then
1553 exec(c_module,
1554 'alter view '||obj.owner||'.'||l_mvqname||' compile');
1555 end if;
1556
1557 end loop;
1558 end;
1559
1560
1561 -- Generate MV create statement from MVQ
1562 function GENERATE(p_owner varchar2,
1563 p_mvname varchar2) return clob
1564 is
1565 c_module varchar2(25) := 'generate';
1566 l_mvq_name varchar2(30) := get_mvq_name(p_mvname);
1567 l_mvqdef CLOB;
1568 l_mvdef CLOB := null;
1569 l_tmpmvdef CLOB := null;
1570 l_mvqcomment CLOB;
1571 l_mvq_exist number;
1572 l_prebuilt number;
1573 l_dummy1 varchar2(100);
1574 l_dummy2 varchar2(100);
1575 begin
1576 -- Get MVQ definition
1577 select count(1) into l_mvq_exist
1578 from dba_views
1579 where owner=p_owner and view_name=l_mvq_name;
1580
1581 if (l_mvq_exist > 0) then
1582 get_mvq_definition(p_owner, l_mvq_name, l_mvqdef, l_mvqcomment);
1583 else
1584 l_tmpmvdef := dbms_metadata.get_ddl('MATERIALIZED_VIEW', p_mvname, p_owner);
1585 generate_mvqdef(l_tmpmvdef, l_dummy1, l_dummy2, l_mvqdef, l_mvqcomment);
1586 end if;
1587
1588 -- Check for Prebuilt table
1589 select count(1) into l_prebuilt
1590 from dba_tables
1591 where owner=p_owner and table_name=p_mvname;
1592
1593 if ('X' <> nvl(l_mvqdef, 'X')) then
1594 l_mvdef := transform_to_mv(l_mvqdef, l_mvqcomment, l_prebuilt);
1595 end if;
1596
1597 return l_mvdef;
1598 end;
1599
1600
1601 /*-----------------------------------------------------------------+
1602 | |
1603 | PATCH_LOG |
1604 | Revise MVLogs |
1605 | |
1606 | Tasks :- |
1607 | This api would be called from finalize |
1608 | Refreshes all the patched tables if outdated |
1609 | |
1610 | TODO : Create MVLOG using PURGE syntax |
1611 | |
1612 +-----------------------------------------------------------------*/
1613 procedure PATCH_LOG(p_owner varchar2,
1614 p_tabname varchar2)
1615 is
1616 c_module varchar2(25) := 'patch_log';
1617
1618 cursor mvlogcols(X_TABLE_OWNER varchar2, X_TABLE_NAME varchar2) is
1619 select basecol, latestcol, mvlcol
1620 from
1621 (
1622 select
1623 ad_zd_table.ev_view_column(mvlc.column_name) as basecol
1624 , max(tabc.column_name) as latestcol
1625 , max(mvlc.column_name) as mvlcol
1626 from
1627 dba_tab_cols tabc
1628 , dba_mview_log_filter_cols mvlc
1629 where mvlc.owner = x_table_owner
1630 and mvlc.name = x_table_name
1631 and tabc.owner = mvlc.owner
1632 and tabc.table_name = mvlc.name
1633 and ad_zd_table.ev_view_column(tabc.column_name) =
1634 ad_zd_table.ev_view_column(mvlc.column_name)
1635 group by ad_zd_table.ev_view_column(mvlc.column_name)
1636 order by ad_zd_table.ev_view_column(mvlc.column_name)
1637 )
1638 where latestcol<>mvlcol;
1639
1640 l_first boolean := TRUE;
1641 l_query CLOB;
1642 l_latest_name_inmvlog DBA_MVIEW_LOG_FILTER_COLS.column_name%TYPE;
1643
1644 BEGIN
1645 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_tabname);
1646
1647 l_query := 'ALTER MATERIALIZED VIEW LOG FORCE ON '||p_owner||
1648 '.'||p_tabname||' ADD (';
1649 for mvlogcol in mvlogcols(p_owner, p_tabname)
1650 loop
1651 IF(l_first = TRUE)
1652 THEN
1653 l_query := l_query||mvlogcol.latestcol||' ';
1654 l_first := FALSE;
1655 ELSE
1656 l_query := l_query||', '||mvlogcol.latestcol;
1657 END IF;
1658 end loop;
1659
1660 IF(l_first = FALSE)
1661 THEN
1662 l_query := l_query||') INCLUDING NEW VALUES';
1663 exec(c_module, l_query);
1664 END IF;
1665 log(c_module, 'PROCEDURE', 'end');
1666 end;
1667
1668 /*-----------------------------------------------------------------+
1669 | |
1670 | FINALIZE |
1671 | This api would be called from adpatch. |
1672 | |
1673 | Refresh all the MV logs |
1674 | |
1675 +-----------------------------------------------------------------*/
1676 procedure FINALIZE
1677 IS
1678 c_module varchar2(25) := 'finalize';
1679 -- TODO: Need to analyze whether we need to put the where condition on status
1680 -- or not
1681 cursor ptbls is
1682 SELECT owner, name
1683 FROM ad_patched_tables;
1684 l_mvlog_exist number := 0;
1685 BEGIN
1686
1687 log(c_module, 'PROCEDURE', 'begin: no parameter(s)');
1688 for ptbl in ptbls
1689 loop
1690 SELECT count(1)
1691 INTO l_mvlog_exist
1692 FROM dba_mview_logs
1693 WHERE log_owner=ptbl.owner
1694 AND master=ptbl.name;
1695
1696 log(c_module, 'STATEMENT', 'Checking count for '||
1697 ptbl.owner||'.'||ptbl.name);
1698 log(c_module, 'STATEMENT', 'Count = '||l_mvlog_exist);
1699 IF (l_mvlog_exist > 0)
1700 THEN
1701 log(c_module, 'STATEMENT', 'Refreshing '||ptbl.owner||'.'||ptbl.name);
1702 patch_log(ptbl.owner, ptbl.name);
1703 END IF;
1704 end loop;
1705
1706 cutover(0);
1707
1708 log(c_module, 'PROCEDURE', 'end');
1709 commit;
1710 end;
1711
1712 /*
1713 ** CUTOVER
1714 **
1715 ** For each mvq actualized in the current edition
1716 ** a. Get the mvq ddl query and its comment
1717 ** b. Expand mvq's ddl using dbms_sql2.expand_sql_text
1718 ** c. Get the corresponding mv ddl sql query
1719 ** d. Compare, if there is any difference then re-install MV
1720 */
1721 procedure CUTOVER(x_execute number default 0)
1722 is
1723 c_module varchar2(25) := 'cutover';
1724 l_stmt clob;
1725
1726 -- out of date MVs
1727 cursor OOD_MVS is
1728 select
1729 emv.owner owner
1730 , emv.lv_name lv_name
1731 , emv.lv_status lv_status
1732 , emv.mv_name mv_name
1733 from
1734 ( select
1735 lvv.owner owner
1736 , lvv.view_name lv_name
1737 , decode(lvx.type#, 88, 'N', 'Y') lv_actual
1738 , decode(lvx.status, 1, 'VALID', 'INVALID') lv_status
1739 , case when lvx.type#=88 then
1740 case when lvx.status=1 then NULL else sysdate end
1741 else
1742 lvx.mtime
1743 end lv_changed
1744 , ad_zd_mview.get_mv_name(lvv.view_name) mv_name
1745 , mvo.created mv_changed
1746 from
1747 sys.obj$ lvx
1748 , ( select
1749 xusr.user#
1750 , xusr.ext_username user_name
1751 , ed.name edition_name
1752 from
1753 (select * from sys.user$ where type# = 2) xusr
1754 , (select * from sys.obj$ where owner# = 0 and type# = 57) ed
1755 where xusr.spare2 = ed.obj# ) eusr
1756 , dba_views lvv
1757 , dba_objects mvo
1758 where lvv.owner in
1759 ( select oracle_username from system.fnd_oracle_userid
1760 where read_only_flag in ('A', 'E', 'U', 'B') )
1761 and lvv.view_name like '%#'
1762 and lvv.editioning_view = 'N'
1763 and eusr.edition_name = sys_context('userenv', 'current_edition_name')
1764 and eusr.user_name = lvv.owner
1765 and lvx.owner# = eusr.user#
1766 and lvx.name = lvv.view_name
1767 and (lvx.type# = 4 or lvx.type# = 88)
1768 and mvo.owner(+) = lvv.owner
1769 and mvo.object_name(+) = substr(lvv.view_name, 1, length(lvv.view_name)-1)
1770 and mvo.object_type(+) = 'MATERIALIZED VIEW' ) emv
1771 where (emv.lv_status = 'INVALID' or
1772 (emv.lv_actual = 'Y' and (emv.mv_changed is null or emv.lv_changed > emv.mv_changed)));
1773
1774 BEGIN
1775 if x_execute = 1 then
1776 log(c_module, 'PROCEDURE', 'begin: execute ddl');
1777 else
1778 log(c_module, 'PROCEDURE', 'begin: store ddl');
1779 end if;
1780
1781 for row in ood_mvs loop
1782 begin
1783 if (x_execute = 1) then
1784 -- execute cutover action
1785 upgrade(row.owner, row.mv_name);
1786 else
1787 -- store cutover action
1788 l_stmt := 'begin '||ad_zd.apps_schema||'.ad_zd_mview.upgrade('''||
1789 row.owner||''','''||row.mv_name||'''); end;';
1790
1791 log(c_module, 'STATEMENT', 'Storing cutover action for: '||row.mv_name);
1792 ad_zd_parallel_exec.load(
1793 x_phase => ad_zd_parallel_exec.c_phase_cutover,
1794 x_sql => l_stmt,
1795 x_unique => true); /* there are channces for duplicacy */
1796
1797 end if;
1798 exception
1799 when others then
1800 -- log the error, then eat it
1801 log(c_module, 'ERROR', SQLERRM);
1802 end;
1803 end loop;
1804
1805 log(c_module, 'PROCEDURE', 'end');
1806 end;
1807
1808
1809 /*
1810 ** Upgrade MV for Online Patching
1811 **
1812 ** Note: can be called repeatedly during development
1813 ** for new or changed Logical Definitions (MV#)
1814 */
1815 procedure UPGRADE(p_owner varchar2,
1816 p_mview_name varchar2)
1817 is
1818 c_module varchar2(25) := 'upgrade';
1819 l_mvdef clob;
1820 l_mvq_exist number;
1821 l_dummy1 varchar2(100);
1822 l_dummy2 varchar2(100);
1823 BEGIN
1824 log(c_module, 'PROCEDURE', 'begin: '||p_owner||'.'||p_mview_name);
1825
1826 -- Check if MVQ exist or not
1827 select count(1)
1828 into l_mvq_exist
1829 from dba_views
1830 where owner=p_owner
1831 and view_name=get_mvq_name(p_mview_name);
1832
1833 -- if MVQ does not exist, create it
1834 if (l_mvq_exist = 0) then
1835 -- Dont get the query directly from data dictionary. Because
1836 -- from ad_mv if the mv is new then the api cannot get the sql query
1837 -- from data dictionary. It has to parse query only.
1838 l_mvdef := dbms_metadata.get_ddl('MATERIALIZED_VIEW', p_mview_name, p_owner);
1839 install_mvq(l_mvdef, l_dummy1, l_dummy2);
1840 end if;
1841
1842 -- Create MV Implementation from Logical Definition
1843 install_mv(p_owner, p_mview_name);
1844
1845 log(c_module, 'PROCEDURE', 'end');
1846 end;
1847
1848 /*+----------------------------------------------------------------+
1849 | |
1850 | UPGRADE_DB |
1851 | |
1852 | Main api to be called from AD_ZD_PREP |
1853 | |
1854 +----------------------------------------------------------------+*/
1855
1856 procedure UPGRADE_DB(x_execute number default 1)
1857 is
1858 C_MODULE varchar2(25) := 'upgrade_db';
1859
1860 -- MVs to upgrade
1861 cursor C_MVS is
1862 select m.owner owner,
1863 m.mview_name name
1864 from dba_mviews m
1865 where m.owner in
1866 ( select oracle_username from fnd_oracle_userid
1867 where read_only_flag in ('A', 'E', 'U', 'B') )
1868 and not exists
1869 ( select 'X' from dba_views v
1870 where v.owner = m.owner
1871 and v.view_name = ad_zd_mview.get_mvq_name(m.mview_name) )
1872 and not exists
1873 ( select 'X'
1874 from fnd_oracle_userid fou
1875 , fnd_product_installations fpi
1876 , ad_obsolete_objects aoo
1877 where fpi.application_id = aoo.application_id
1878 and fou.oracle_id = fpi.oracle_id
1879 and fou.oracle_username = m.owner
1880 and aoo.object_name = m.mview_name
1881 and aoo.object_type = 'MATERIALIZED VIEW' )
1882 union
1883 select v.owner owner,
1884 ad_zd_mview.get_mv_name(v.view_name) name
1885 from dba_views v
1886 where v.view_name like '%'||'#'
1887 and v.editioning_view = 'N'
1888 and v.owner in
1889 ( select oracle_username from fnd_oracle_userid
1890 where read_only_flag in ('A', 'E', 'U', 'B') )
1891 and not exists
1892 ( select 'X' from dba_objects m
1893 where m.owner = v.owner
1894 and m.object_name = ad_zd_mview.get_mv_name(v.view_name)
1895 and m.object_type = 'MATERIALIZED VIEW'
1896 and m.status = 'VALID' )
1897 and not exists
1898 ( select 'X'
1899 from fnd_oracle_userid fou
1900 , fnd_product_installations fpi
1901 , ad_obsolete_objects aoo
1902 where fpi.application_id = aoo.application_id
1903 and fou.oracle_id = fpi.oracle_id
1904 and fou.oracle_username = v.owner
1905 and aoo.object_name = v.view_name
1906 and aoo.object_type = 'VIEW' );
1907
1908 begin
1909 log(c_module, 'PROCEDURE', 'begin: '||x_execute);
1910
1911 for obj in c_mvs loop
1912 if (x_execute = 1) then
1913 upgrade(obj.owner, obj.name);
1914 else
1915 ad_zd_parallel_exec.load(
1916 ad_zd_parallel_exec.c_phase_upgrade_mview,
1917 'begin '||ad_zd.apps_schema||'.ad_zd_mview.upgrade('''||
1918 obj.owner||''','''||obj.name||'''); end;');
1919 end if;
1920 end loop;
1921
1922 log(c_module, 'PROCEDURE', 'end');
1923 end;
1924
1925 /*+----------------------------------------------------------------+
1926 | |
1927 | DROP_MV |
1928 | |
1929 | - Drops the given materialized view and its logical view |
1930 | If Running in PATCH edition |
1931 | Stores deferred DDL to drop MV in next CLEANUP |
1932 | If running in RUN edition |
1933 | Drops MV immediately |
1934 | |
1935 +----------------------------------------------------------------+*/
1936
1937 procedure DROP_MV (
1938 X_MVIEW_OWNER in varchar2,
1939 X_MVIEW_NAME in varchar2,
1940 X_DROP_STMT in varchar2,
1941 X_UPD_STMT in varchar2,
1942 X_DROPPED out nocopy varchar2 )
1943 is
1944 C_MODULE varchar2(80) := 'DROP_MV';
1945 l_mv_owner varchar2(30);
1946 l_lv_name varchar2(30);
1947 l_appsname varchar2(30);
1948 l_defer_stmt varchar2(2000);
1949 begin
1950 l_appsname := ad_zd.apps_schema;
1951 l_mv_owner := trim(nvl(x_mview_owner, l_appsname));
1952 log(C_MODULE, 'PROCEDURE', 'begin: '|| l_mv_owner ||'.'||x_mview_name);
1953
1954 -- Drop Logical view pointing to the materialized view
1955 ad_zd_mview.drop_mvq(l_mv_owner, x_mview_name);
1956
1957 -- (Deferred) drop materialized view logic
1958 if ad_zd.get_edition_type = 'RUN' then
1959 -- Drop materialized view
1960 log(c_module, 'EVENT', 'Drop MV '||l_mv_owner||'.'||x_mview_name);
1961 begin
1962 exec(c_module, x_drop_stmt);
1963
1964 -- Update dropped status
1965 if (x_upd_stmt is not null) then
1966 exec(c_module, x_upd_stmt);
1967 end if;
1968
1969 X_DROPPED := 'Y';
1970
1971 exception
1972 when others then
1973 -- ORA-12003 is materialized view doesn't exist.
1974 if (sqlcode <> -12003) then
1975 log(c_module, 'ERROR', 'Error while dropping MV '
1976 ||l_mv_owner||'.'||x_mview_name || ': ' || substr(sqlerrm, 1, 2000));
1977
1978 X_DROPPED := 'N';
1979
1980 else
1981 -- MV doesn't exist. Update dropped status to 'Y'
1982 if (x_upd_stmt is not null) then
1983 exec(c_module, x_upd_stmt);
1984 end if;
1985
1986 X_DROPPED := 'Y';
1987
1988 end if;
1989 end;
1990
1991 else -- If running from PATCH edition
1992
1993 -- Defer drop MV to next cleanup
1994 log (c_module, 'EVENT', 'Defer drop MV ' ||
1995 l_mv_owner||'.'||x_mview_name|| ' to next cleanup');
1996
1997 -- TODO: add update statement
1998 if (x_upd_stmt is not null) then
1999 l_defer_stmt := 'begin execute immediate '''|| regexp_replace(x_drop_stmt, '''', '''''') || '''; ' ||
2000 'execute immediate ''' || regexp_replace(x_upd_stmt, '''', '''''') ||
2001 '''; exception when others then null; end;';
2002
2003 ad_zd.load_ddl ('CLEANUP', l_defer_stmt);
2004 else
2005 ad_zd.load_ddl ('CLEANUP', x_drop_stmt);
2006 end if;
2007
2008 -- Return 'N' as the table object is not deleted yet.
2009 X_DROPPED := 'N';
2010 end if;
2011
2012 log( c_module, 'PROCEDURE', 'end');
2013 commit;
2014 end DROP_MV;
2015
2016 END AD_ZD_MVIEW;