[Home] [Help]
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)
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
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');
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
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);
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:
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:
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:
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)
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);
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') )
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
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'
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