set define '^'

Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jkallman    06/09/2009 - Created for bug 8567757 



declare
  s varchar2(32767) := null;
  l_clob clob;
  l_length number := 1;
begin
  wwv_flow_security.g_security_group_id := 10;

s:=s||'select object_type, object_name, table_name, '||chr(10)||
'       decode(schema1,0,null,''<img src="#IMAGE_PREFIX#check_small_black.gif" border="0" height="15" width="15" alt="1"/>'') schema1, '||chr(10)||
'       decode(schema2,0,null,''<img src="#IMAGE_PREFIX#check_small_black.gif" border="0" height="15" width="15" alt="1"/>'') schema2'||chr(10)||
'from ('||chr(10)||
'select'||chr(10)||
'  ''TABLE.TRIGGER'' object_type,'||chr(10)||
'  table_name object_name,'||chr(10)||
'  TABLE_NAME||''.''||';

s:=s||'TRIGGER_NAME||''.''||TRIGGER_TYPE||''.''||'||chr(10)||
'    TRIGGERING_EVENT||''.''||WHEN_CLAUSE||''.''||ACTION_TYPE table_name,'||chr(10)||
'  sum(decode(OWNER,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(OWNER,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from sys.dba_triggers'||chr(10)||
'where (OWNER = :P1350_SCHEMA_1 or OWNER = :P1350_SCHEMA_2) and'||chr(10)||
'      TABLE_NAME not like ''BIN$%'' and'||chr(10)||
'      TABLE_NAME not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(u';

s:=s||'pper(TABLE_NAME)||''.''||upper(trigger_name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'(:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''TRIGGER'')'||chr(10)||
'group by table_name, TABLE_NAME||''.''||TRIGGER_NAME||''.''||TRIGGER_TYPE||''.''||'||chr(10)||
'    TRIGGERING_EVENT||''.''||WHEN_CLAUSE||''.''||ACTION_TYPE '||chr(10)||
'union all'||chr(10)||
'select'||chr(10)||
'  ''SEQUENCE'' object_type,'||chr(10)||
'  sequence_name object_name,'||chr(10)||
'  SEQUENCE_NAME||''.''||INCREMENT_BY||''.''||'||chr(10)||
'    CYCLE_FLAG||''.''||ORDER';

s:=s||'_FLAG||''.''||CACHE_SIZE table_name,'||chr(10)||
'  sum(decode(sequence_owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(sequence_owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from sys.dba_sequences'||chr(10)||
'where (sequence_owner = :P1350_SCHEMA_1 or sequence_owner = :P1350_SCHEMA_2) and'||chr(10)||
'      SEQUENCE_NAME not like ''BIN$%'' and'||chr(10)||
'      SEQUENCE_NAME not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(upper(SEQUENCE_NAME),upper(:P13';

s:=s||'50_SEARCH))>0) and'||chr(10)||
'(:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''SEQUENCE'')'||chr(10)||
'group by sequence_name, SEQUENCE_NAME||''.''||INCREMENT_BY||''.''||CYCLE_FLAG||''.''||ORDER_FLAG||''.''||CACHE_SIZE'||chr(10)||
'union all'||chr(10)||
'select type object_type, '||chr(10)||
'       object_name,'||chr(10)||
'       table_name, '||chr(10)||
'       sum(decode(owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'       sum(decode(owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from ('||chr(10)||
'select type, name object_na';

s:=s||'me, name||''.''||type||'' ''||t.l_lines||''=''||max(line)||'' ''||t.l_size||''=''||sum(length(text)) table_name, owner'||chr(10)||
'from sys.dba_source s,'||chr(10)||
'     (select wwv_flow_lang.system_message(''LINES'') l_lines,'||chr(10)||
'             wwv_flow_lang.system_message(''SIZE'') l_size'||chr(10)||
'      from dual) t'||chr(10)||
'where owner in (:P1350_SCHEMA_1,:P1350_SCHEMA_2) and'||chr(10)||
'      name not like ''BIN$%'' and'||chr(10)||
'      name not like ''DR$%'' and'||chr(10)||
'      (:P1350_SE';

s:=s||'ARCH is null or instr(upper(name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'      (:P1350_COMPARE = ''0'' or :P1350_COMPARE = type)'||chr(10)||
'group by owner, name,type, t.l_lines, t.l_size ) plsql'||chr(10)||
'group by type, object_name, table_name'||chr(10)||
'union all'||chr(10)||
'select'||chr(10)||
'  ''TABLE'' object_type,'||chr(10)||
'  t.table_name object_name,'||chr(10)||
'  t.table_name,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'fr';

s:=s||'om sys.dba_tables t'||chr(10)||
'where (owner = :P1350_SCHEMA_1 or owner = :P1350_SCHEMA_2) and'||chr(10)||
'      table_name not like ''BIN$%'' and'||chr(10)||
'      table_name not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(upper(table_name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'(:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''TABLE'')'||chr(10)||
'group by t.table_name'||chr(10)||
'union all'||chr(10)||
'select'||chr(10)||
'  ''VIEW'' object_type,'||chr(10)||
'  t.view_name object_name,'||chr(10)||
'  t.view_name table_name';

s:=s||','||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from sys.dba_views t'||chr(10)||
'where (owner = :P1350_SCHEMA_1 or owner = :P1350_SCHEMA_2) and'||chr(10)||
'      view_name not like ''BIN$%'' and'||chr(10)||
'      view_name not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(upper(view_name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'(:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''VIEW'')'||chr(10)||
'group by t';

s:=s||'.view_name'||chr(10)||
'union all'||chr(10)||
'select'||chr(10)||
'  ''TABLE.COLUMN'' object_type,'||chr(10)||
'  t.table_name||''.''||t.column_name object_name,'||chr(10)||
'  t.table_name||''.''||t.column_name table_name,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from sys.dba_tab_columns t'||chr(10)||
'where (owner = :P1350_SCHEMA_1 or owner = :P1350_SCHEMA_2) and'||chr(10)||
'      table_name not like ''BIN$%'' and'||chr(10)||
'      table_name';

s:=s||' not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(upper(table_name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'(:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''COLUMN'')'||chr(10)||
'group by t.table_name||''.''||t.column_name'||chr(10)||
'union all'||chr(10)||
'select'||chr(10)||
'  ''TABLE.COLUMN.DATATYPE'' object_type,'||chr(10)||
'  t.table_name||''.''||t.column_name||''.''||data_type object_name,'||chr(10)||
'  t.table_name||''.''||t.column_name||''.''||data_type||''.''||'||chr(10)||
'   data_length||''.''||data_sc';

s:=s||'ale||''.''||DATA_PRECISION||''.''||nullable table_name,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'  sum(decode(t.owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from sys.dba_tab_columns t'||chr(10)||
'where (owner = :P1350_SCHEMA_1 or owner = :P1350_SCHEMA_2) and'||chr(10)||
'      table_name not like ''BIN$%'' and'||chr(10)||
'      table_name not like ''DR$%'' and'||chr(10)||
'(:P1350_SEARCH is null or instr(upper(table_name),upper(:P1350_SEARCH))>0) and'||chr(10)||
'(';

s:=s||':P1350_COMPARE = ''0'' or :P1350_COMPARE = ''DATATYPE'')'||chr(10)||
'group by t.table_name||''.''||t.column_name||''.''||data_type,'||chr(10)||
'         t.table_name||''.''||t.column_name||''.''||data_type||''.''||'||chr(10)||
'          data_length||''.''||data_scale||''.''||DATA_PRECISION||''.''||nullable'||chr(10)||
'union all'||chr(10)||
'select ''TABLE.INDEX'' object_type,'||chr(10)||
'       object_name,'||chr(10)||
'       table_name,'||chr(10)||
'       sum(decode(table_owner,:P1350_SCHEMA_1,1,0)) schema1,'||chr(10)||
'    ';

s:=s||'   sum(decode(table_owner,:P1350_SCHEMA_2,1,0)) schema2'||chr(10)||
'from ('||chr(10)||
'    select table_owner,'||chr(10)||
'           table_name||'' ''||uniqueness||'' (''||'||chr(10)||
'           max(decode(position,1 ,column_name))||'||chr(10)||
'           max(decode(position,2 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,3 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,4 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,5 ,'',''||column_name)';

s:=s||')||'||chr(10)||
'           max(decode(position,6 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,7 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,8 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,9 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,10,'',''||column_name))||'')'''||chr(10)||
'           object_name,'||chr(10)||
'           table_name||''.''||'||chr(10)||
'           decode(substr(index_name,1,4),''SYS_'',''SYS'',index_name'||chr(10)||
'';

s:=s||'           )||''.''||uniqueness||'':''||'||chr(10)||
'           max(decode(position,1 ,column_name))||'||chr(10)||
'           max(decode(position,2 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,3 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,4 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,5 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,6 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,7 ,'',''|';

s:=s||'|column_name))||'||chr(10)||
'           max(decode(position,8 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,9 ,'',''||column_name))||'||chr(10)||
'           max(decode(position,10,'',''||column_name))'||chr(10)||
'           table_name'||chr(10)||
'     from('||chr(10)||
'        select dc.table_name, dc.table_owner,dc.index_name,di.uniqueness, di.status,'||chr(10)||
'               di.index_type, di.temporary, di.partitioned,di.funcidx_status, di.join_index,'||chr(10)||
'         ';

s:=s||'      dc.column_name,dc.column_position position'||chr(10)||
'          from sys.dba_ind_columns dc,sys.dba_indexes di'||chr(10)||
'         where dc.table_owner in (:P1350_SCHEMA_1,:P1350_SCHEMA_2)'||chr(10)||
'           and dc.table_name not like ''BIN$%'''||chr(10)||
'           and dc.table_name not like ''DR$%'''||chr(10)||
'           and dc.index_name = di.index_name'||chr(10)||
'           and dc.index_owner = di.owner '||chr(10)||
'           and (:P1350_SEARCH is null or instr(up';

s:=s||'per(dc.table_name),upper(:P1350_SEARCH))>0)'||chr(10)||
'           and (:P1350_COMPARE = ''0'' or :P1350_COMPARE = ''INDEX'')'||chr(10)||
'           )'||chr(10)||
'    group by table_name,table_owner,index_name,uniqueness, status,'||chr(10)||
'             index_type, temporary, partitioned,funcidx_status, join_index'||chr(10)||
') idx'||chr(10)||
'group by object_name, table_name)'||chr(10)||
'full_query'||chr(10)||
'where ('||chr(10)||
'      nvl(:P1350_SHOW_DELTA,''N'')=''N'' or '||chr(10)||
'      (nvl(:P1350_SHOW_DELTA,''N'')=''';

s:=s||'Y'' and schema1 != schema2)'||chr(10)||
'      )'||chr(10)||
'order by 1, 2';

    for c1 in (select id
                 from wwv_flow_page_plugs
                where flow_id between 4500 and 4509
                  and page_id >= 1350
                  and page_id < 1350+1
                  and id >= 14654103993650366
                  and id < 14654103993650366 +1) loop
        --
        select plug_source into l_clob
          from wwv_flow_page_plugs
         where id = c1.id
           for update;
           
        l_length := length(s);
        dbms_lob.write( l_clob, l_length, 1, s );       
    end loop;
                     
    commit;

end;
/