create or replace procedure P_SYS_GEN_DIFF_DEV_AND_TEST
(i_vc_srcowner in varchar2,
 i_vc_srcownerdefaulttbs in varchar2,
 i_vc_srcindextbs in varchar2,
 i_vc_tarowner in varchar2,
 i_vc_tarownerdefaulttbs in varchar2,
 i_vc_tarindextbs in varchar2,
 i_vc_defvalue_comm_flag in varchar2,
 o_vc_return_flag out varchar2) is

/*
功能： 比较IPRA开发环境和IPRA测试环境指定schema间差异，并生成消除这些差异的同步脚本
作者： cuihua
创建日期：2009-03-19

输入参数：
i_vc_srcowner：IPRA开发环境的用于比较差异的指定schema名称
i_vc_srcownerdefaulttbs: IPRA开发环境的用于比较差异的指定schema所在的default tablespace
i_vc_srcindextbs: IPRA开发环境的用于比较差异的指定schema的index所在的tablespace
i_vc_tarowner: IPRA测试环境的用于比较差异的指定schema名称
i_vc_tarownerdefaulttbs: IPRA测试环境的用于比较差异的指定schema所在的default tablespace
i_vc_tarindextbs：IPRA测试环境的用于比较差异的指定schema的index所在的tablespace
i_vc_defvalue_comm_flag: 是否比较default值和table comment及column comment的差异
                         如果它的值为N，则表示不比较
                         如果它的值为Y，则表示比较

输出参数：
o_vc_return_flag：用于判断该存储过程是否成功执行，并在出错后存储相应的错误原因。
                 如果它的值的第一位为S，则表明该存储过程成功执行，没有错误。
                如果它的值的第一位为E，则表明该存储过程执行失败，具体错误原因为它的值的第二位到最后一位。
输入输出参数：
无

调用到的存储过程：
p_sys_print_ddl
*/

type typtablename is table of dba_tables.table_name%type index by binary_integer;
tablenamessrc typtablename;
tablenamestar typtablename;
tablenames typtablename;

type typcolumnname is table of dba_tab_columns.COLUMN_NAME%type index by binary_integer;
columnnamessrc typcolumnname;
columnnamestar typcolumnname;

type typdatatype is table of dba_tab_columns.DATA_TYPE%type index by binary_integer;
datatypessrc typdatatype;
datatypestar typdatatype;

type typdatalength is table of dba_tab_columns.DATA_LENGTH%type index by binary_integer;
datalengthssrc typdatalength;
datalengthstar typdatalength;

type typdataprecision is table of dba_tab_columns.DATA_PRECISION%type index by binary_integer;
dataprecisionssrc typdataprecision;
dataprecisionstar typdataprecision;

type typdatascale is table of dba_tab_columns.DATA_SCALE%type index by binary_integer;
datascalessrc typdatascale;
datascalestar typdatascale;

type typconstraintname is table of dba_constraints.constraint_name%type index by binary_integer;
constraintnamessrc typconstraintname;
constraintnamestar typconstraintname;
constraintnames typconstraintname;

type typindexname is table of dba_indexes.index_name%type index by binary_integer;
indexnamessrc typindexname;
indexnamestar typindexname;
indexnames typindexname;

type typviewname is table of dba_views.view_name%type index by binary_integer;
viewnamessrc typviewname;
viewnamestar typviewname;
viewnames typviewname;

type typsequencename is table of dba_sequences.sequence_name%type index by binary_integer;
sequencenamessrc typsequencename;
sequencenamestar typsequencename;

type typtypename is table of dba_types.type_name%type index by binary_integer;
typenamessrc typtypename;
typenamestar typtypename;
typenames typtypename;

type typtypebodyname is table of dba_types.type_name%type index by binary_integer;
typebodynamessrc typtypebodyname;
typebodynamestar typtypebodyname;
typebodynames typtypebodyname;

type typfunctionname is table of varchar2(30) index by binary_integer;
functionnamessrc typfunctionname;
functionnamestar typfunctionname;
functionnames typfunctionname;

type typprocedurename is table of varchar2(30) index by binary_integer;
procedurenamessrc typprocedurename;
procedurenamestar typprocedurename;
procedurenames typprocedurename;

type typpackagename is table of varchar2(30) index by binary_integer;
packagenamessrc typpackagename;
packagenamestar typpackagename;
packagenames typpackagename;

type typpackagebodyname is table of varchar2(30) index by binary_integer;
packagebodynamessrc typpackagebodyname;
packagebodynamestar typpackagebodyname;
packagebodynames typpackagebodyname;

type typtriggername is table of varchar2(30) index by binary_integer;
triggernamessrc typtriggername;
triggernamestar typtriggername;
triggernames typtriggername;

type typlastddltime is table of date index by binary_integer;
lastddltimessrc typlastddltime;
lastddltimestar typlastddltime;

type typsynonymname is table of varchar2(30) index by binary_integer;
synonymnamessrc typsynonymname;
synonymnamestar typsynonymname;
synonymnames typsynonymname;

bln_temp boolean;
c_ddl clob;
n_idx number;
vc_temp varchar2(4000);

/*cursor cur_defaultvalue(vc_srcowner varchar2) is
  select * from dba_tab_columns where owner=vc_srcowner and data_default is not null;
rec_defaultvalue dba_tab_columns%rowtype;*/

cursor cur_defaultvalue(vc_srcowner varchar2) is
  select * from dba_tab_columns where owner=vc_srcowner and data_default is not null 
    and TABLE_NAME in 
    (select OBJECT_NAME from dba_objects 
       where owner=vc_srcowner and object_type='TABLE' and last_ddl_time > sysdate - 7
         and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=vc_srcowner and object_type in ('TABLE','ALL')));
rec_defaultvalue dba_tab_columns%rowtype;

/*cursor cur_tablecomment(vc_srcowner varchar2) is
  select * from dba_tab_comments where owner=vc_srcowner;
rec_tablecomment dba_tab_comments%rowtype;*/

cursor cur_tablecomment(vc_srcowner varchar2) is
  select * from dba_tab_comments where owner=vc_srcowner
     and TABLE_NAME in 
     (select OBJECT_NAME from dba_objects 
       where owner=vc_srcowner and object_type='TABLE' and last_ddl_time > sysdate - 7
         and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=vc_srcowner and object_type in ('TABLE','ALL')));
rec_tablecomment dba_tab_comments%rowtype;

/*cursor cur_columncomment(vc_srcowner varchar2) is
  select * from dba_col_comments where owner=vc_srcowner;
rec_columncomment dba_col_comments%rowtype;*/

cursor cur_columncomment(vc_srcowner varchar2) is
  select * from dba_col_comments where owner=vc_srcowner
     and TABLE_NAME in 
     (select OBJECT_NAME from dba_objects 
       where owner=vc_srcowner and object_type='TABLE' and last_ddl_time > sysdate - 7
         and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=vc_srcowner and object_type in ('TABLE','ALL')));
rec_columncomment dba_col_comments%rowtype;

begin

   o_vc_return_flag := 'S' || '_' || '已经成功产生IPRA开发环境的' || i_vc_srcowner || '和IPRA测试环境的' || i_vc_tarowner || '间的同步脚本！';


  ----------------处理table的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into tablenamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='TABLE' and object_name not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TABLE','ALL'));
   select object_name,last_ddl_time bulk collect into tablenamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='TABLE' and object_name not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TABLE','ALL'));

   --先处理那些source db有但target db没有的表
   if( tablenamessrc.count > 0 ) then
     for i in tablenamessrc.first .. tablenamessrc.last loop
       bln_temp := false;

       if ( tablenamestar.count > 0 ) then
         for j in tablenamestar.first .. tablenamestar.last loop
           if ( tablenamessrc(i) = tablenamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('TABLE',tablenamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的表
   if( tablenamestar.count > 0 ) then
     for i in tablenamestar.first .. tablenamestar.last loop
       bln_temp := false;

       if ( tablenamessrc.count > 0 ) then
         for j in tablenamessrc.first .. tablenamessrc.last loop
           if ( tablenamestar(i) = tablenamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop table ' || i_vc_tarowner || '.' || tablenamestar(i) || ' purge;');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   /*--首先过滤掉那些last_ddl_time没有被更新的表
   n_idx := 1;
   if( tablenamessrc.count > 0 ) then
     for i in tablenamessrc.first .. tablenamessrc.last loop
       for j in tablenamestar.first .. tablenamestar.last loop
         if (  tablenamessrc(i) = tablenamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           tablenames(n_idx) := tablenamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;   */

   --接着处理同名表间字段的差异
   --正向比较一次
   if( tablenamessrc.count > 0 ) then
    for i in tablenamessrc.first .. tablenamessrc.last loop
      select column_name,data_type,data_length,data_precision,data_scale bulk collect into columnnamessrc,datatypessrc,datalengthssrc,dataprecisionssrc,datascalessrc from dba_tab_columns where owner=i_vc_srcowner and table_name=tablenamessrc(i);
      select column_name,data_type,data_length,data_precision,data_scale bulk collect into columnnamestar,datatypestar,datalengthstar,dataprecisionstar,datascalestar from dba_tab_columns@caipratest where owner=i_vc_tarowner and table_name=tablenamessrc(i);

      if( columnnamessrc.count > 0 and columnnamestar.count > 0 ) then
        for j in columnnamessrc.first .. columnnamessrc.last loop
          bln_temp := false;

          for k in columnnamestar.first .. columnnamestar.last loop
            if( columnnamessrc(j) = columnnamestar(k) ) then
              bln_temp := true;
            end if;

            if( datatypessrc(j) = 'VARCHAR2' ) then
              if( columnnamessrc(j) = columnnamestar(k) ) then
                if( (datalengthssrc(j) <> datalengthstar(k)) or datatypestar(k) <> 'VARCHAR2' ) then
                  dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '.' || columnnamessrc(j) || ',类型为VARCHAR2,长度为' || datalengthssrc(j) );
                  dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '.' || columnnamestar(k) || ',类型为' || datatypestar(k) || ',长度为' || datalengthstar(k) );
                  dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' modify (' || columnnamestar(k) || ' VARCHAR2(' || datalengthssrc(j) || '));');
                  dbms_output.put_line('REM ');
                end if;
              end if;
            elsif ( datatypessrc(j) = 'NUMBER' ) then
              if( columnnamessrc(j) = columnnamestar(k) ) then
                if( (dataprecisionssrc(j) <> dataprecisionstar(k)) or (datascalessrc(j) <> datascalestar(k)) or (datatypestar(k) <> 'NUMBER')) then
                  dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '.' || columnnamessrc(j) || ',类型为NUMBER,长度为' || dataprecisionssrc(j) || '.' || datascalessrc(j) );
                  dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '.' || columnnamestar(k) || ',类型为' || datatypestar(k) || ',长度为' || dataprecisionstar(k) || '.' || datascalestar(k) );
                  dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' modify (' || columnnamestar(k) || ' NUMBER(' || dataprecisionssrc(j) || ',' || datascalessrc(j) || '));');
                  dbms_output.put_line('REM ');
                end if;
              end if;
            else
              if( columnnamessrc(j) = columnnamestar(k) ) then
                if( datalengthssrc(j) <> datalengthstar(k) ) then
                  dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '.' || columnnamessrc(j) || ',类型为' || datatypessrc(j) || ',长度为' || datalengthssrc(j) );
                  dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '.' || columnnamestar(k) || ',类型为' || datatypestar(k) || ',长度为' || datalengthstar(k) );
                  dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' modify (' || columnnamestar(k) || ' ' || datatypessrc(j) || '(' || datalengthssrc(j) || '));');
                  dbms_output.put_line('REM ');
                end if;
              end if;
            end if;

          end loop;

          if( bln_temp = false ) then
            if( datatypessrc(j) = 'VARCHAR2' ) then
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '有字段' || columnnamessrc(j) || ',类型为VARCHAR2,长度为' || datalengthssrc(j));
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '没有字段' || columnnamessrc(j));
              dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' add (' || columnnamessrc(j) || ' VARCHAR2(' || datalengthssrc(j) || '));');
              dbms_output.put_line('REM ');
            elsif ( datatypessrc(j) = 'NUMBER') then
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '有字段' || columnnamessrc(j) || ',类型为NUMBER,长度为' || dataprecisionssrc(j) || '.' || datascalessrc(j));
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '没有字段' || columnnamessrc(j));
              if( dataprecisionssrc(j) is not null ) then
                dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' add (' || columnnamessrc(j) || ' NUMBER(' || dataprecisionssrc(j) || ',' || datascalessrc(j) || '));');
                dbms_output.put_line('REM ');
              else
                dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' add (' || columnnamessrc(j) || ' NUMBER);');
                dbms_output.put_line('REM ');
              end if;
            else
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '有字段' || columnnamessrc(j) || ',类型为' || datatypessrc(j) || ',长度为' || datalengthssrc(j));
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '没有字段' || columnnamessrc(j));
              dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' add (' || columnnamessrc(j) || ' ' || datatypessrc(j) || '(' || datalengthssrc(j) || '));');
              dbms_output.put_line('REM ');
            end if;
          end if;
        end loop;
      end if;

    end loop;
  end if;


  --最后反向再比较一次，反向比较的时候已经无需比较长度的差异
  if( tablenamessrc.count > 0 ) then
    for i in tablenamessrc.first .. tablenamessrc.last loop
      select column_name,data_type,data_length,data_precision,data_scale bulk collect into columnnamessrc,datatypessrc,datalengthssrc,dataprecisionssrc,datascalessrc from dba_tab_columns where owner=i_vc_srcowner and table_name=tablenamessrc(i);
      select column_name,data_type,data_length,data_precision,data_scale bulk collect into columnnamestar,datatypestar,datalengthstar,dataprecisionstar,datascalestar from dba_tab_columns@caipratest where owner=i_vc_tarowner and table_name=tablenamessrc(i);

      if( columnnamestar.count > 0 and columnnamessrc.count > 0) then
        for j in columnnamestar.first .. columnnamestar.last loop
          bln_temp := false;

          for k in columnnamessrc.first .. columnnamessrc.last loop
            if( columnnamestar(j) = columnnamessrc(k) ) then
              bln_temp := true;
              exit;
            end if;
          end loop;

          if( bln_temp = false ) then
            if( datatypestar(j) = 'VARCHAR2' ) then
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '有字段' || columnnamestar(j) || ' '  || ',类型为VARCHAR2,长度为' || datalengthstar(j));
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '没有字段' || columnnamestar(j));
              dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' drop (' || columnnamestar(j) || ');');
              dbms_output.put_line('REM ');
            elsif ( datatypestar(j) = 'NUBMER' ) then
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '有字段' || columnnamestar(j) || ' '  || ',类型为NUBMER,长度为' || dataprecisionstar(j) || '.' || datascalestar(j));
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '没有字段' || columnnamestar(j));
              dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' drop (' || columnnamestar(j) || ');');
              dbms_output.put_line('REM ');
            else
              dbms_output.put_line('REM IPRA测试环境' || i_vc_tarowner || '.' || tablenamessrc(i) || '有字段' || columnnamestar(j) || ' '  || ',类型为' || datatypestar(j) || ',长度为' || datalengthstar(j));
              dbms_output.put_line('REM IPRA开发环境' || i_vc_srcowner || '.' || tablenamessrc(i) || '没有字段' || columnnamestar(j));
              dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamessrc(i) || ' drop (' || columnnamestar(j) || ');');
              dbms_output.put_line('REM ');
            end if;
          end if;
        end loop;
      end if;

    end loop;
  end if;
 ----------------处理table的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


 ----------------处理constraint的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select constraint_name,table_name,last_change bulk collect into constraintnamessrc,tablenamessrc,lastddltimessrc from dba_constraints where owner=i_vc_srcowner and CONSTRAINT_TYPE in ('C','P') and TABLE_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TABLE','ALL'));
   select constraint_name,table_name,last_change bulk collect into constraintnamestar,tablenamestar,lastddltimestar from dba_constraints@caipratest where owner=i_vc_tarowner and CONSTRAINT_TYPE in ('C','P') and TABLE_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TABLE','ALL'));

   --先处理那些source db有但target db没有的constraint
   if( constraintnamessrc.count > 0 ) then
     for i in constraintnamessrc.first .. constraintnamessrc.last loop
       bln_temp := false;

       if ( constraintnamestar.count > 0 ) then
         for j in constraintnamestar.first .. constraintnamestar.last loop
           if ( constraintnamessrc(i) = constraintnamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         --不处理哪些系统自己命名的constraint
         if ( substr(constraintnamessrc(i),1,3) != 'SYS') then
           c_ddl := dbms_metadata.get_ddl('CONSTRAINT',constraintnamessrc(i),i_vc_srcowner);
           p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
           dbms_output.put_line('/');
           dbms_output.put_line(chr(10));
         end if;         
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的constraint   
   if( constraintnamestar.count > 0 ) then
     for i in constraintnamestar.first .. constraintnamestar.last loop
       bln_temp := false;

       if ( constraintnamessrc.count > 0 ) then
         for j in constraintnamessrc.first .. constraintnamessrc.last loop
           if ( constraintnamestar(i) = constraintnamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       --不处理哪些系统自己命名的constraint
       if ( substr(constraintnamestar(i),1,3) != 'SYS') then
         if( bln_temp = false ) then
           dbms_output.put_line('alter table ' || i_vc_tarowner || '.' || tablenamestar(i) || ' drop constraint ' || constraintnamestar(i) || ';');
           dbms_output.put_line(chr(10));
         end if;
       end if;       

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的constraint
   n_idx := 1;
   if( constraintnamessrc.count > 0 and constraintnamestar.count > 0) then
     for i in constraintnamessrc.first .. constraintnamessrc.last loop
       for j in constraintnamestar.first .. constraintnamestar.last loop
         if (  constraintnamessrc(i) = constraintnamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           constraintnames(n_idx) := constraintnamessrc(i);
           tablenames(n_idx) := tablenamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的constraint
   if ( constraintnames.count > 0 ) then
     for i in constraintnames.first .. constraintnames.last loop
       --不处理哪些系统自己命名的constraint
       if ( substr(constraintnames(i),1,3) != 'SYS') then
         dbms_output.put_line( 'alter table ' || i_vc_tarowner || '.' || tablenames(i) || ' drop constraint ' || constraintnames(i) || ';');
         c_ddl := dbms_metadata.get_ddl('CONSTRAINT',constraintnames(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;     
     end loop;
   end if;   
 ----------------处理constraint的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 
 
 if ( i_vc_defvalue_comm_flag = 'Y' ) then
   ---------------处理default值的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    open cur_defaultvalue(i_vc_srcowner);
    fetch cur_defaultvalue into rec_defaultvalue;
    while (cur_defaultvalue%found) loop
  
      if( rec_defaultvalue.DATA_DEFAULT is not null ) then
         vc_temp := 'alter table ' || i_vc_tarowner || '.' || rec_defaultvalue.TABLE_NAME || ' modify (' || rec_defaultvalue.COLUMN_NAME || ' default ' || rec_defaultvalue.DATA_DEFAULT || ');';
         if ( instr(vc_temp,'null') = 0 ) then
           --dbms_output.put_line(vc_temp);
  
           --这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制
           loop
           exit when vc_temp is null;
           dbms_output.put_line( substrb( vc_temp, 1, 254 ) );
           vc_temp := substrb( vc_temp, 255 );
           end loop;
         end if;
      end if;
  
      fetch cur_defaultvalue into rec_defaultvalue;
    end loop;
    close cur_defaultvalue;
   ---------------处理default值的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  
   ---------------处理table comment值的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    open cur_tablecomment(i_vc_srcowner);
    fetch cur_tablecomment into rec_tablecomment;
    while (cur_tablecomment%found) loop
  
      if ( rec_tablecomment.comments is not null ) then
        vc_temp := 'comment on table ' || i_vc_tarowner || '.' || rec_tablecomment.TABLE_NAME || ' is ' || '''' || rec_tablecomment.comments || '''' ||';';
        --dbms_output.put_line(vc_temp);
  
        --这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制
        loop
        exit when vc_temp is null;
        dbms_output.put_line( substrb( vc_temp, 1, 254 ) );
        vc_temp := substrb( vc_temp, 255 );
        end loop;
      end if;
  
      fetch cur_tablecomment into rec_tablecomment;
    end loop;
    close cur_tablecomment;
   ---------------处理table comment值的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  
   ---------------处理column comment值的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    open cur_columncomment(i_vc_srcowner);
    fetch cur_columncomment into rec_columncomment;
    while (cur_columncomment%found) loop
  
      if ( rec_columncomment.comments is not null ) then
        vc_temp := 'comment on column ' || i_vc_tarowner || '.' || rec_columncomment.table_name || '.' ||rec_columncomment.column_name || ' is ' || '''' || rec_columncomment.comments || '''' ||';';
        --dbms_output.put_line(vc_temp);
  
        --这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制
        loop
        exit when vc_temp is null;
        dbms_output.put_line( substrb( vc_temp, 1, 254 ) );
        vc_temp := substrb( vc_temp, 255 );
        end loop;
      end if;
  
      fetch cur_columncomment into rec_columncomment;
    end loop;
    close cur_columncomment;
   ---------------处理column comment值的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 end if;

 

 ----------------处理index的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into indexnamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='INDEX' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('INDEX','ALL'));
   select object_name,last_ddl_time bulk collect into indexnamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='INDEX' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('INDEX','ALL'));

   --先处理那些source db有但target db没有的index
   if( indexnamessrc.count > 0 ) then
     for i in indexnamessrc.first .. indexnamessrc.last loop
       bln_temp := false;

       if ( indexnamestar.count > 0 ) then
         for j in indexnamestar.first .. indexnamestar.last loop
           if ( indexnamessrc(i) = indexnamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         --不处理主键所带的unique index
         --不处理由系统自己命名的index
         if ( instr(indexnamessrc(i),'PK') = 0  and substr(indexnamessrc(i),1,3) != 'SYS' ) then
           c_ddl := dbms_metadata.get_ddl('INDEX',indexnamessrc(i),i_vc_srcowner);
           p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
           dbms_output.put_line('/');
           dbms_output.put_line(chr(10));
         end if;         
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的index
   if( indexnamestar.count > 0 ) then
     for i in indexnamestar.first .. indexnamestar.last loop
       bln_temp := false;

       if ( indexnamessrc.count > 0 ) then
         for j in indexnamessrc.first .. indexnamessrc.last loop
           if ( indexnamestar(i) = indexnamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         --不处理主键所带的unique index
         --不处理由系统自己命名的index
         if ( instr(indexnamestar(i),'PK') = 0  and substr(indexnamestar(i),1,3) != 'SYS' ) then
           dbms_output.put_line('drop index ' || i_vc_tarowner || '.' || indexnamestar(i) || ';');
           dbms_output.put_line(chr(10));
         end if;         
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的index
   n_idx := 1;
   if( indexnamessrc.count > 0 and indexnamestar.count > 0 ) then
     for i in indexnamessrc.first .. indexnamessrc.last loop
       for j in indexnamestar.first .. indexnamestar.last loop
         if (  indexnamessrc(i) = indexnamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           indexnames(n_idx) := indexnamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的index
   if ( indexnames.count > 0 ) then
     for i in indexnames.first .. indexnames.last loop
       --不处理主键所带的unique index
       --不处理由系统自己命名的index
       if ( instr(indexnames(i),'PK') = 0  and substr(indexnames(i),1,3) != 'SYS' ) then
         dbms_output.put_line('drop index ' || i_vc_tarowner || '.' || indexnames(i) || ';');
         c_ddl := dbms_metadata.get_ddl('INDEX',indexnames(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;      
     end loop;
   end if;   
 ----------------处理index的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 ----------------处理view的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into viewnamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='VIEW' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('VIEW','ALL'));
   select object_name,last_ddl_time bulk collect into viewnamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='VIEW' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('VIEW','ALL'));

   --先处理那些source db有但target db没有的view
   if( viewnamessrc.count > 0 ) then
     for i in viewnamessrc.first .. viewnamessrc.last loop
       bln_temp := false;

       if ( viewnamestar.count > 0 ) then
         for j in viewnamestar.first .. viewnamestar.last loop
           if ( viewnamessrc(i) = viewnamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('VIEW',viewnamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的view
   if( viewnamestar.count > 0 ) then
     for i in viewnamestar.first .. viewnamestar.last loop
       bln_temp := false;

       if ( viewnamessrc.count > 0 ) then
         for j in viewnamessrc.first .. viewnamessrc.last loop
           if ( viewnamestar(i) = viewnamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop view ' || i_vc_tarowner || '.' || viewnamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的view
   n_idx := 1;
   if( viewnamessrc.count > 0  and viewnamestar.count > 0 ) then
     for i in viewnamessrc.first .. viewnamessrc.last loop
       for j in viewnamestar.first .. viewnamestar.last loop
         if (  viewnamessrc(i) = viewnamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           viewnames(n_idx) := viewnamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的view
   if ( viewnames.count > 0 ) then
     for i in viewnames.first .. viewnames.last loop
       c_ddl := dbms_metadata.get_ddl('VIEW',viewnames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;   
 ----------------处理view的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


 ----------------处理sequence的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into sequencenamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='SEQUENCE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('SEQUENCE','ALL'));
   select object_name,last_ddl_time bulk collect into sequencenamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='SEQUENCE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('SEQUENCE','ALL'));

   --先处理那些source db有但target db没有的sequence
   if( sequencenamessrc.count > 0 ) then
     for i in sequencenamessrc.first .. sequencenamessrc.last loop
       bln_temp := false;
       
       if ( sequencenamestar.count > 0 ) then
         for j in sequencenamestar.first .. sequencenamestar.last loop
           if ( sequencenamessrc(i) = sequencenamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('SEQUENCE',sequencenamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的sequence
   if( sequencenamestar.count > 0 ) then
     for i in sequencenamestar.first .. sequencenamestar.last loop
       bln_temp := false;

       if ( sequencenamessrc.count > 0 ) then
         for j in sequencenamessrc.first .. sequencenamessrc.last loop
           if ( sequencenamestar(i) = sequencenamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop sequence ' || i_vc_tarowner || '.' || sequencenamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;
 ----------------处理sequence的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 
 
 ----------------手工处理synonym的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into synonymnamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='SYNONYM' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('SYNONYM','ALL'));
   select object_name,last_ddl_time bulk collect into synonymnamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='SYNONYM' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('SYNONYM','ALL'));

   --先处理那些source db有但target db没有的synonym
   if( synonymnamessrc.count > 0 ) then
     for i in synonymnamessrc.first .. synonymnamessrc.last loop
       bln_temp := false;

       if ( synonymnamestar.count > 0 ) then
         for j in synonymnamestar.first .. synonymnamestar.last loop
           if ( synonymnamessrc(i) = synonymnamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('SYNONYM',synonymnamessrc(i),i_vc_srcowner);
         p_sys_print_ddl_for_syn(c_ddl);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的synonym
   if( synonymnamestar.count > 0 ) then
     for i in synonymnamestar.first .. synonymnamestar.last loop
       bln_temp := false;

       if ( synonymnamessrc.count > 0 ) then
         for j in synonymnamessrc.first .. synonymnamessrc.last loop
           if ( synonymnamestar(i) = synonymnamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop synonym ' || i_vc_tarowner || '.' || synonymnamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的synonym
   n_idx := 1;
   if( synonymnamessrc.count > 0 and synonymnamestar.count > 0 ) then
     for i in synonymnamessrc.first .. synonymnamessrc.last loop
       for j in synonymnamestar.first .. synonymnamestar.last loop
         if (  synonymnamessrc(i) = synonymnamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           synonymnames(n_idx) := synonymnamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的synonym
   if( synonymnames.count > 0 ) then
     for i in synonymnames.first .. synonymnames.last loop
       c_ddl := dbms_metadata.get_ddl('SYNONYM',synonymnames(i),i_vc_srcowner);
       p_sys_print_ddl_for_syn(c_ddl);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;
 ----------------手工处理synonym的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 


 ----------------处理type的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into typenamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='TYPE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TYPE','ALL'));
   select object_name,last_ddl_time bulk collect into typenamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='TYPE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TYPE','ALL'));

   --先处理那些source db有但target db没有的type
   if( typenamessrc.count > 0 ) then
     for i in typenamessrc.first .. typenamessrc.last loop
       bln_temp := false;

       if ( typenamestar.count > 0 ) then
         for j in typenamestar.first .. typenamestar.last loop
           if ( typenamessrc(i) = typenamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('TYPE',typenamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的type
   if( typenamestar.count > 0 ) then
     for i in typenamestar.first .. typenamestar.last loop
       bln_temp := false;

       if ( typenamessrc.count > 0 ) then
         for j in typenamessrc.first .. typenamessrc.last loop
           if ( typenamestar(i) = typenamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop type ' || i_vc_tarowner || '.' || typenamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的type
   n_idx := 1;
   if( typenamessrc.count > 0 and typenamestar.count > 0 ) then
     for i in typenamessrc.first .. typenamessrc.last loop
       for j in typenamestar.first .. typenamestar.last loop
         if (  typenamessrc(i) = typenamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           typenames(n_idx) := typenamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的type
   if( typenames.count > 0 ) then
     for i in typenames.first .. typenames.last loop
       c_ddl := dbms_metadata.get_ddl('TYPE',typenames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;   
 ----------------处理type的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ----------------处理type body的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into typebodynamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='TYPE BODY' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TYPE BODY','ALL'));
   select object_name,last_ddl_time bulk collect into typebodynamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='TYPE BODY' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TYPE BODY','ALL'));

   --首先过滤掉那些last_ddl_time没有被更新的typebody
   n_idx := 1;
   if( typebodynamessrc.count > 0 and typebodynamestar.count > 0) then
     for i in typebodynamessrc.first .. typebodynamessrc.last loop
       for j in typebodynamestar.first .. typebodynamestar.last loop
         if (  typebodynamessrc(i) = typebodynamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           typebodynames(n_idx) := typebodynamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的type body
   if( typebodynames.count > 0 ) then
     for i in typebodynames.first .. typebodynames.last loop
       c_ddl := dbms_metadata.get_ddl('TYPE',typebodynames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;   
 ----------------处理type body的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


 ----------------处理function的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into functionnamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='FUNCTION' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('FUNCTION','ALL'));
   select object_name,last_ddl_time bulk collect into functionnamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='FUNCTION' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('FUNCTION','ALL'));

   --先处理那些source db有但target db没有的function
   if( functionnamessrc.count > 0 ) then
     for i in functionnamessrc.first .. functionnamessrc.last loop
       bln_temp := false;
       
       if ( functionnamestar.count > 0 ) then
         for j in functionnamestar.first .. functionnamestar.last loop
           if ( functionnamessrc(i) = functionnamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('FUNCTION',functionnamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的function
   if( functionnamestar.count > 0 ) then
     for i in functionnamestar.first .. functionnamestar.last loop
       bln_temp := false;

       if ( functionnamessrc.count > 0 ) then
         for j in functionnamessrc.first .. functionnamessrc.last loop
           if ( functionnamestar(i) = functionnamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop function ' || i_vc_tarowner || '.' || functionnamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的function
   n_idx := 1;
   if( functionnamessrc.count > 0 and functionnamestar.count > 0 ) then
     for i in functionnamessrc.first .. functionnamessrc.last loop
       for j in functionnamestar.first .. functionnamestar.last loop
         if (  functionnamessrc(i) = functionnamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           functionnames(n_idx) := functionnamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的function
   if( functionnames.count > 0 ) then
     for i in functionnames.first .. functionnames.last loop
       c_ddl := dbms_metadata.get_ddl('FUNCTION',functionnames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;   
 ----------------处理function的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 ----------------处理package的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into packagenamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='PACKAGE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PACKAGE','ALL'));
   select object_name,last_ddl_time bulk collect into packagenamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='PACKAGE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PACKAGE','ALL'));

   --先处理那些source db有但target db没有的package
   if( packagenamessrc.count > 0 ) then
     for i in packagenamessrc.first .. packagenamessrc.last loop
       bln_temp := false;

       if ( packagenamestar.count > 0 ) then
         for j in packagenamestar.first .. packagenamestar.last loop
           if ( packagenamessrc(i) = packagenamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('PACKAGE',packagenamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));

        /* dbms_output.put_line('REM ' || packagenamessrc(i) || ' needs to be created in ' || i_vc_tarowner || '!' );
         dbms_output.put_line(chr(10)); */
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的package
   if( packagenamestar.count > 0 ) then
     for i in packagenamestar.first .. packagenamestar.last loop
       bln_temp := false;

       if ( packagenamessrc.count > 0 ) then
         for j in packagenamessrc.first .. packagenamessrc.last loop
           if ( packagenamestar(i) = packagenamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop package ' || i_vc_tarowner || '.' || packagenamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的package
   n_idx := 1;
   if( packagenamessrc.count > 0 and packagenamestar.count > 0 ) then
     for i in packagenamessrc.first .. packagenamessrc.last loop
       for j in packagenamestar.first .. packagenamestar.last loop
         if (  packagenamessrc(i) = packagenamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           packagenames(n_idx) := packagenamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的package
   if( packagenames.count > 0 ) then
     for i in packagenames.first .. packagenames.last loop
       c_ddl := dbms_metadata.get_ddl('PACKAGE',packagenames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
  
       /*dbms_output.put_line('REM ' || packagenames(i) || ' updated, needs synchronize!');
       dbms_output.put_line(chr(10));*/ 
     end loop;
   end if;   
 ----------------处理package的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ----------------处理package body的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into packagebodynamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='PACKAGE BODY' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PACKAGE BODY','ALL'));
   select object_name,last_ddl_time bulk collect into packagebodynamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='PACKAGE BODY' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PACKAGE BODY','ALL'));

   --首先过滤掉那些last_ddl_time没有被更新的package body
   n_idx := 1;
   if( packagebodynamessrc.count > 0 and packagebodynamestar.count > 0 ) then
     for i in packagebodynamessrc.first .. packagebodynamessrc.last loop
       for j in packagebodynamestar.first .. packagebodynamestar.last loop
         if (  packagebodynamessrc(i) = packagebodynamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           packagebodynames(n_idx) := packagebodynamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的packagebody
   if( packagebodynames.count > 0 ) then
     for i in packagebodynames.first .. packagebodynames.last loop
       c_ddl := dbms_metadata.get_ddl('PACKAGE',packagebodynames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
  
       /*dbms_output.put_line('REM ' || packagebodynames(i) || '''s body updated, needs synchronize!');
       dbms_output.put_line(chr(10));*/
     end loop;
   end if;   
 ----------------处理package body的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




 ----------------处理procedure的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into procedurenamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='PROCEDURE' and object_name <> 'P_SYS_GEN_DIFF_DEV_AND_TEST' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PROCEDURE','ALL'));
   select object_name,last_ddl_time bulk collect into procedurenamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='PROCEDURE' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('PROCEDURE','ALL'));

   --先处理那些source db有但target db没有的procedure
   if( procedurenamessrc.count > 0 ) then
     for i in procedurenamessrc.first .. procedurenamessrc.last loop
       bln_temp := false;

       if ( procedurenamestar.count > 0 ) then
         for j in procedurenamestar.first .. procedurenamestar.last loop
           if ( procedurenamessrc(i) = procedurenamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('PROCEDURE',procedurenamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的procedure
   if( procedurenamestar.count > 0 ) then
     for i in procedurenamestar.first .. procedurenamestar.last loop
       bln_temp := false;

       if ( procedurenamessrc.count > 0 ) then
         for j in procedurenamessrc.first .. procedurenamessrc.last loop
           if ( procedurenamestar(i) = procedurenamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop procedure ' || i_vc_tarowner || '.' || procedurenamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的procedure
   n_idx := 1;
   if( procedurenamessrc.count > 0 and procedurenamestar.count > 0 ) then
     for i in procedurenamessrc.first .. procedurenamessrc.last loop
       for j in procedurenamestar.first .. procedurenamestar.last loop
         if (  procedurenamessrc(i) = procedurenamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           procedurenames(n_idx) := procedurenamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的procedure
   if( procedurenames.count > 0 ) then
     for i in procedurenames.first .. procedurenames.last loop
       c_ddl := dbms_metadata.get_ddl('PROCEDURE',procedurenames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;   
 ----------------处理procedure的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




 ----------------处理trigger的差异------------begin---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select object_name,last_ddl_time bulk collect into triggernamessrc,lastddltimessrc from dba_objects where owner=i_vc_srcowner and object_type='TRIGGER' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TRIGGER','ALL'));
   select object_name,last_ddl_time bulk collect into triggernamestar,lastddltimestar from dba_objects@caipratest where owner=i_vc_tarowner and object_type='TRIGGER' and OBJECT_NAME not in (select object_name from ipra.object_not_sync where owner=i_vc_srcowner and object_type in ('TRIGGER','ALL'));

   --先处理那些source db有但target db没有的trigger
   if( triggernamessrc.count > 0 ) then
     for i in triggernamessrc.first .. triggernamessrc.last loop
       bln_temp := false;

       if ( triggernamestar.count > 0 ) then
         for j in triggernamestar.first .. triggernamestar.last loop
           if ( triggernamessrc(i) = triggernamestar(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         c_ddl := dbms_metadata.get_ddl('TRIGGER',triggernamessrc(i),i_vc_srcowner);
         p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
         dbms_output.put_line('/');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --再处理那些target db有但source db没有的trigger
   if( triggernamestar.count > 0 ) then
     for i in triggernamestar.first .. triggernamestar.last loop
       bln_temp := false;

       if ( triggernamessrc.count > 0 ) then
         for j in triggernamessrc.first .. triggernamessrc.last loop
           if ( triggernamestar(i) = triggernamessrc(j) ) then
             bln_temp := true;
             exit;
           end if;
         end loop;
       end if;       

       if( bln_temp = false ) then
         dbms_output.put_line('drop trigger ' || i_vc_tarowner || '.' || triggernamestar(i) || ';');
         dbms_output.put_line(chr(10));
       end if;

     end loop;
   end if;

   --首先过滤掉那些last_ddl_time没有被更新的trigger
   n_idx := 1;
   if( triggernamessrc.count > 0 and triggernamestar.count > 0 ) then
     for i in triggernamessrc.first .. triggernamessrc.last loop
       for j in triggernamestar.first .. triggernamestar.last loop
         if (  triggernamessrc(i) = triggernamestar(j) and lastddltimessrc(i) >= lastddltimestar(j) ) then
           triggernames(n_idx) := triggernamessrc(i);
           n_idx := n_idx + 1;
           exit;
         end if;
       end loop;
     end loop;
   end if;

   --接着修改这些更新过的trigger
   if( triggernames.count > 0 ) then
     for i in triggernames.first .. triggernames.last loop
       c_ddl := dbms_metadata.get_ddl('TRIGGER',triggernames(i),i_vc_srcowner);
       p_sys_print_ddl(c_ddl,i_vc_srcowner,i_vc_srcownerdefaulttbs,i_vc_srcindextbs,i_vc_tarowner,i_vc_tarownerdefaulttbs,i_vc_tarindextbs);
       dbms_output.put_line('/');
       dbms_output.put_line(chr(10));
     end loop;
   end if;
   
 ----------------处理trigger的差异------------end---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


exception
  when others then
    o_vc_return_flag := 'E' || '_' || sqlcode || '_' || sqlerrm;
    return;

end P_SYS_GEN_DIFF_DEV_AND_TEST;
/