APPS.AD_ZD_MVIEW SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 144

 |             FOR UPDATE                                          |
 |             ENABLE QUERY REWRITE                                |
 |             DISABLE QUERY REWRITE                               |
 |                                                                 |
 +-----------------------------------------------------------------*/
function INCLUDE_PREBUILT(p_string clob)
  return clob
is
  l_retstring clob;

Line: 169

               l_pos := instr(p_string, 'FOR UPDATE');

Line: 177

                  end if; -- FOR UPDATE

Line: 202

  l_exp_select_query clob;

Line: 205

   dbms_sql2.expand_sql_text(p_query, l_exp_select_query);

Line: 206

   return l_exp_select_query;

Line: 272

   SELECT log_table
   INTO   l_ctblname
   FROM   dba_mview_logs
   WHERE  master=p_mview_name
   AND    log_owner=p_owner;

Line: 291

   exec(c_module, 'create or replace view "'||p_owner||'"."'||l_mvloglname||'" as select * from dual');

Line: 295

      exec(c_module, 'create or replace view "'||p_owner||'"."'||l_mvlogmname||'" as select * from dual');

Line: 311

   SELECT count(1)
   INTO   l_exist
   FROM   dba_mview_logs
   WHERE  log_owner=p_owner
   AND    master=p_mview_name;

Line: 319

      SELECT count(1)
      INTO   l_exist
      FROM   dba_tab_comments
      WHERE  owner=p_owner
      AND    table_name=get_mvq_name(p_mview_name, g_mvl_char)
      AND    table_type='VIEW';

Line: 328

         SELECT nvl(trim(comments), ' ')
         INTO   l_mvlogddlpart1
         FROM   dba_tab_comments
         WHERE  owner=p_owner
         AND    table_name=get_mvq_name(p_mview_name, g_mvl_char)
         AND    table_type='VIEW';

Line: 336

      SELECT count(1)
      INTO   l_exist
      FROM   dba_tab_comments
      WHERE  owner=p_owner
      AND    table_name=get_mvq_name(p_mview_name, g_mvm_char)
      AND    table_type='VIEW';

Line: 345

         SELECT nvl(trim(comments), ' ')
         INTO   l_mvlogddlpart2
         FROM   dba_tab_comments
         WHERE  owner=p_owner
         AND    table_name=get_mvq_name(p_mview_name, g_mvm_char)
         AND    table_type='VIEW';

Line: 362

   SELECT count(1)
   INTO   l_exist
   FROM   dba_views
   WHERE  owner=p_owner
   AND    view_name=get_mvq_name(p_mview_name, g_mvm_char);

Line: 412

SELECT owner,
       index_name
FROM   dba_indexes
WHERE  owner = p_owner
AND    table_name = p_mview_name
AND    index_name not like 'I_SNAP$%';

Line: 420

SELECT owner,
       trigger_name
FROM   dba_triggers
WHERE  owner = p_owner
AND    table_name = p_mview_name;

Line: 427

SELECT owner,
       constraint_name
FROM   dba_constraints
WHERE  owner = p_owner
AND    table_name = p_mview_name
AND    generated = 'USER NAME';

Line: 435

SELECT distinct grantee,
       privilege,
       grantable,
       hierarchy
FROM   dba_tab_privs
WHERE  owner = p_owner
AND    table_name = p_mview_name
AND    grantee <> 'SYSTEM';

Line: 499

      if (nvl(objs.hierarchy, 'NO') = 'YES' and objs.privilege='SELECT' ) then
        l_grant := l_grant || ' WITH HIERARCHY OPTION ';

Line: 534

  select count(1) into l_exist
  from   dba_mview_logs
  where  log_owner=p_owner and master=p_mview_name;

Line: 543

  select count(1) into l_exist
  from   dba_tables
  where  owner=p_owner and table_name=p_mview_name;

Line: 592

  select count(1) into l_exist
  from   dba_views
  where  owner=p_owner
  and    view_name=get_mvq_name(p_mview_name, g_mvl_char);

Line: 617

    SELECT dbms_metadata.get_ddl('VIEW', p_mvqname, p_owner)
    INTO   p_mvqdef
    FROM   dual;

Line: 629

    SELECT dbms_metadata.get_dependent_ddl('COMMENT', p_mvqname, p_owner)
    INTO   p_comment
    FROM   dual;

Line: 667

  select count(1) into l_mvexist
  from   dba_mviews
  where  owner=l_owner and  mview_name=p_mview_name;

Line: 682

  select count(1) into l_prebuilt
  from   dba_tables
  where  owner=l_owner and table_name=p_mview_name;

Line: 724

    select count(1) INTO l_syn_exist
    from   dba_synonyms
    where  synonym_name=p_mview_name
      and  owner=l_appsname
      and  table_owner=upper(l_owner)
      and  table_name=synonym_name;

Line: 882

   |    AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2             |
   +---------------------------------------------------------------------*/
   text := regexp_replace(l_mvdef, '[[:space:]]*CREATE[[:space:]]+' ||
                                   '(MATERIALIZED[[:space:]]+VIEW|SNAPSHOT)' ||
                                   '[[:space:]]+',
                          '', 1, 1, 'i');

Line: 906

   |    AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2             |
   +---------------------------------------------------------------------*/
   pos := regexp_instr(text, '[ ('||fnd_const.newline||']');

Line: 937

   |    AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2             |
   +---------------------------------------------------------------------*/
-- We are done with the naming stuff. Hence remove the mview name from the text

   text := regexp_replace(text, '((")?'||ownerquot||'(")?.)?(")?'||mvnamequot||'(")?[[:space:]]*', '', 1, 1, 'i');

Line: 959

   |    AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2             |
   +---------------------------------------------------------------------*/
   pos := regexp_instr(text, '([[:space:]]|'||fnd_const.newline||
                             ')?AS('||
                              fnd_const.newline||'|[[:space:]])', 1, 1, 0, 'i');

Line: 1015

   Do not delete the below code. It may happen we need to reenable the code
   SELECT count(1)
   INTO   l_table_exist
   FROM   dba_tables
   WHERE  owner=nvl(upper(owner), upper('APPS'))
   AND    table_name=mvnamequot;

Line: 1034

   |  Extract select query                                               |
   |  Hint: Extract the clause after the AS keyword from the remaining   |
   |        part                                                         |
   |                                                                     |
   |    AS select t1.a, t1.c, t2.b, t2.d from mv1 t1, mv2 t2             |
   +---------------------------------------------------------------------*/
-- Now right after ' as ' word this is the select query.
   pos := regexp_instr(text, 'AS', 1, 1, 0, 'i');

Line: 1086

   SELECT count(1)
   INTO   l_mvq_exist
   FROM   dba_views
   WHERE  owner=l_owner and view_name=l_mvq_name;

Line: 1097

   SELECT count(1)
   INTO   l_mv_exist
   FROM   dba_mviews
   WHERE  owner=l_owner and mview_name=p_mvname;

Line: 1111

      delete from ad_zd_clob
      where  owner=l_owner and name in
               (p_mvname, get_mvq_name(p_mvname));

Line: 1116

      insert into ad_zd_clob(owner, name, query)
        select owner, mview_name, to_lob(query)
        from   dba_mviews
        where  owner=l_owner and mview_name=p_mvname;

Line: 1121

      select query into l_mv_text
      from   ad_zd_clob
      where  owner=l_owner and name=p_mvname;

Line: 1126

      insert into ad_zd_clob(owner, name, query)
        select owner, view_name, to_lob(text)
        from   dba_views
        where  owner=l_owner and view_name=l_mvq_name;

Line: 1131

      select query into l_mvq_text
      from   ad_zd_clob
      where  owner=l_owner and name=l_mvq_name;

Line: 1148

   select count(1) into l_mvl_exist
   from   dba_views
   where  owner=l_owner and view_name=get_mvq_name(p_mvname, g_mvl_char);

Line: 1205

   |       select "A","B","C","D" from mv1                       |
   +-------------------------------------------------------------*/
   l_part1 := regexp_replace(l_modmvqdef,
       '[[:space:]]*CREATE' ||
       '[[:space:]]+(OR[[:space:]]+REPLACE[[:space:]]+)?(FORCE[[:space:]]+)?VIEW[[:space:]]+',
       '', 1, 1, 'i');

Line: 1219

   |       select "A","B","C","D" from mv1                       |
   +--------------------------------------------------------------*/
   l_pos := regexp_instr(l_part1, ' |\(');

Line: 1233

   |       select "A","B","C","D" from mv1                       |
   +--------------------------------------------------------------*/
   l_part1 := regexp_replace(l_part1, l_mvqname||'[[:space:]]*', '', 1, 1, 'i');

Line: 1243

   |       select "A","B","C","D" from mv1                       |
   +--------------------------------------------------------------*/
   l_pos := regexp_instr(l_part1, '([[:space:]]|'||fnd_const.newline||
                             ')?AS('||
                              fnd_const.newline||'|[[:space:]])', 1, 1, 0, 'i');

Line: 1259

   |    Extract the select query                                 |
   |    Hint : Extract it everything after AS keyword            |
   |                                                             |
   |       select "A","B","C","D" from mv1                       |
   +-------------------------------------------------------------*/
   l_query := substr(l_part1, 4);

Line: 1268

   |    Expand the select query                                  |
   |                                                             |
   |       select "A","B","C","D" from mv1                       |
   +-------------------------------------------------------------*/
   l_expquery := expand_query(l_query);

Line: 1420

   SELECT count(1)
   INTO   l_exist
   FROM   dba_views
   WHERE  owner=l_owner
   AND    view_name=upper(get_mvq_name(p_mvname))
   AND    EDITIONING_VIEW='N';

Line: 1434

   SELECT count(1)
   INTO   l_exist
   FROM   dba_views
   WHERE  owner=l_owner
   AND    view_name=upper(get_mvq_name(p_mvname, g_mvl_char))
   AND    EDITIONING_VIEW='N';

Line: 1447

   SELECT count(1)
   INTO   l_exist
   FROM   dba_views
   WHERE  owner=l_owner
   AND    view_name=upper(get_mvq_name(p_mvname, g_mvm_char))
   AND    EDITIONING_VIEW='N';

Line: 1526

SELECT owner,
       name
FROM   dba_dependencies
WHERE  referenced_name=upper(p_name)
AND    referenced_owner=upper(p_owner)
AND    referenced_type=upper(nvl(p_type, referenced_type))
AND    type='MATERIALIZED VIEW';

Line: 1545

      SELECT count(1)
      INTO   l_exist
      FROM   dba_views
      WHERE  owner=obj.owner
      AND    view_name=l_mvqname;

Line: 1577

  select count(1) into l_mvq_exist
  from   dba_views
  where  owner=p_owner and view_name=l_mvq_name;

Line: 1589

  select count(1) into l_prebuilt
  from   dba_tables
  where  owner=p_owner and table_name=p_mvname;

Line: 1619

  select basecol, latestcol, mvlcol
  from
    (
      select
          ad_zd_table.ev_view_column(mvlc.column_name) as basecol
        , max(tabc.column_name) as latestcol
        , max(mvlc.column_name) as mvlcol
      from
          dba_tab_cols tabc
        , dba_mview_log_filter_cols mvlc
      where mvlc.owner   = x_table_owner
        and mvlc.name    = x_table_name
        and tabc.owner      = mvlc.owner
        and tabc.table_name = mvlc.name
        and ad_zd_table.ev_view_column(tabc.column_name) =
              ad_zd_table.ev_view_column(mvlc.column_name)
      group by ad_zd_table.ev_view_column(mvlc.column_name)
      order by ad_zd_table.ev_view_column(mvlc.column_name)
    )
  where latestcol<>mvlcol;

Line: 1682

SELECT owner, name
FROM   ad_patched_tables;

Line: 1690

     SELECT count(1)
     INTO   l_mvlog_exist
     FROM   dba_mview_logs
     WHERE  log_owner=ptbl.owner
     AND    master=ptbl.name;

Line: 1728

select
        emv.owner     owner
      , emv.lv_name   lv_name
      , emv.lv_status lv_status
      , emv.mv_name   mv_name
    from
      ( select
            lvv.owner     owner
          , lvv.view_name lv_name
          , decode(lvx.type#, 88, 'N', 'Y') lv_actual
          , decode(lvx.status, 1, 'VALID', 'INVALID') lv_status
          , case when lvx.type#=88 then
                 case when lvx.status=1 then NULL else sysdate end
            else
                lvx.mtime
            end lv_changed
          , ad_zd_mview.get_mv_name(lvv.view_name) mv_name
          , mvo.created   mv_changed
        from
            sys.obj$ lvx
          , ( select
                  xusr.user#
                , xusr.ext_username user_name
                , ed.name edition_name
              from
                  (select * from sys.user$ where type# = 2) xusr
                , (select * from sys.obj$  where owner# = 0 and type# = 57) ed
              where xusr.spare2 = ed.obj# ) eusr
          , dba_views lvv
          , dba_objects mvo
        where lvv.owner in
                ( select oracle_username from system.fnd_oracle_userid
                  where  read_only_flag in ('A', 'E', 'U', 'B') )
          and lvv.view_name like '%#'
          and lvv.editioning_view = 'N'
          and eusr.edition_name  = sys_context('userenv', 'current_edition_name')
          and eusr.user_name     = lvv.owner
          and lvx.owner# = eusr.user#
          and lvx.name   = lvv.view_name
          and (lvx.type# = 4 or lvx.type# = 88)
          and mvo.owner(+)       = lvv.owner
          and mvo.object_name(+) = substr(lvv.view_name, 1, length(lvv.view_name)-1)
          and mvo.object_type(+) = 'MATERIALIZED VIEW' ) emv
    where (emv.lv_status = 'INVALID' or
            (emv.lv_actual = 'Y' and (emv.mv_changed is null or emv.lv_changed > emv.mv_changed)));

Line: 1827

  select count(1)
  into   l_mvq_exist
  from   dba_views
  where  owner=p_owner
  and    view_name=get_mvq_name(p_mview_name);

Line: 1862

    select m.owner owner,
           m.mview_name name
    from   dba_mviews m
    where m.owner in
            ( select oracle_username from fnd_oracle_userid
              where  read_only_flag in ('A', 'E', 'U', 'B') )
      and not exists
            ( select 'X' from dba_views v
              where  v.owner = m.owner
              and    v.view_name = ad_zd_mview.get_mvq_name(m.mview_name) )
      and not exists
            ( select 'X'
              from   fnd_oracle_userid fou
                   , fnd_product_installations fpi
                   , ad_obsolete_objects aoo
              where fpi.application_id  = aoo.application_id
                and fou.oracle_id       = fpi.oracle_id
                and fou.oracle_username = m.owner
                and aoo.object_name     = m.mview_name
                and aoo.object_type     = 'MATERIALIZED VIEW' )
    union
    select v.owner owner,
           ad_zd_mview.get_mv_name(v.view_name) name
    from   dba_views v
    where v.view_name like '%'||'#'
      and v.editioning_view = 'N'
      and v.owner in
            ( select oracle_username from fnd_oracle_userid
              where  read_only_flag in ('A', 'E', 'U', 'B') )
      and not exists
            ( select 'X' from dba_objects m
              where m.owner      = v.owner
                and m.object_name = ad_zd_mview.get_mv_name(v.view_name)
                and m.object_type = 'MATERIALIZED VIEW'
                and m.status      = 'VALID' )
      and not exists
            ( select 'X'
              from   fnd_oracle_userid fou
                   , fnd_product_installations fpi
                   , ad_obsolete_objects aoo
              where fpi.application_id  = aoo.application_id
                and fou.oracle_id       = fpi.oracle_id
                and fou.oracle_username = v.owner
                and aoo.object_name     = v.view_name
                and aoo.object_type     = 'VIEW' );