CREATE OR REPLACE
PROCEDURE MGMT_R_CHECK_PEL(p_source_object_owner all_objects.owner%TYPE
                          ,p_source_object_name all_objects.object_name%TYPE
                          ,p_target_object_owner all_objects.owner%TYPE
                          ,p_target_object_name all_objects.object_name%TYPE
                          ,p_target_object_partition_name all_tab_partitions.partition_name%TYPE
                          ) IS
--******************************************************************************
--*
--*  Description
--*  ***********
--*  Measures
--* ----------------------------------------------------------------------------
--* Date         Author             Description
--* ===========  =================  ============================================
--* 01-APR-2006  Jeff Moss          Created
--* 12-FEB-2007  Jeff Moss          Added code to c_attributes cursor to not
--*                                 get partitions or subpartitions
--*                                 Added check to ensure correct number of
--*                                 partitions in source and subpartitions in
--*                                 target if exchanging a partitioned source with
--*                                 a composite partitioned target.
--* 29-MAR-2007  Jeff Moss          Changed Subpartition counting to realise that
--*                                 NONE is the phrase returned when the table is
--*                                 Not subpartitioned - not NULL.
--* 13-JUL-2007  Jeff Moss          Fixed bug on column comparison - now compares
--*                                 source with target as opposed to source!
--******************************************************************************
  CURSOR c_attributes IS
    SELECT ao_src.object_type src_object_type
    ,      at_src.cluster_owner src_cluster_owner
    ,      at_src.iot_type src_iot_type
    ,      ai_src.pct_threshold src_pct_threshold
    ,      ai_src.include_column src_include_column
    ,      apt_src.partitioning_type src_partitioning_type
    ,      apt_src.partition_count src_partition_count
    ,      apt_src.subpartitioning_type src_subpartitioning_type
    ,      ao_tgt.object_type tgt_object_type
    ,      at_tgt.cluster_owner tgt_cluster_owner
    ,      at_tgt.iot_type tgt_iot_type
    ,      ai_tgt.pct_threshold tgt_pct_threshold
    ,      ai_tgt.include_column tgt_include_column
    ,      apt_tgt.partitioning_type tgt_partitioning_type
    ,      apt_tgt.partition_count tgt_partition_count
    ,      apt_tgt.subpartitioning_type tgt_subpartitioning_type
    ,      atp.subpartition_count tgt_subpartition_count
    FROM   all_objects ao_src
    ,      all_tables at_src
    ,      all_indexes ai_src
    ,      all_part_tables apt_src
    ,      all_objects ao_tgt
    ,      all_tables at_tgt
    ,      all_indexes ai_tgt
    ,      all_part_tables apt_tgt
    ,      (SELECT table_owner
            ,      table_name
            ,      subpartition_count
            FROM   all_tab_partitions
            WHERE  table_owner = p_target_object_owner
            AND    table_name = p_target_object_name
            AND    partition_name = p_target_object_partition_name
           ) atp
    WHERE  ao_src.owner = p_source_object_owner
    AND    ao_src.object_name = p_source_object_name
    AND    ao_src.owner = at_src.owner(+)
    AND    ao_src.object_name = at_src.table_name(+)
    AND    ao_src.owner = ai_src.owner(+)
    AND    ao_src.object_name = ai_src.table_name(+)
    AND    ao_src.owner = apt_src.owner(+)
    AND    ao_src.object_name = apt_src.table_name(+)
    AND    ao_tgt.owner = p_target_object_owner
    AND    ao_tgt.object_name = p_target_object_name
    AND    ao_tgt.owner = at_tgt.owner(+)
    AND    ao_tgt.object_name = at_tgt.table_name(+)
    AND    ao_tgt.owner = ai_tgt.owner(+)
    AND    ao_tgt.object_name = ai_tgt.table_name(+)
    AND    ao_tgt.owner = apt_tgt.owner(+)
    AND    ao_tgt.object_name = apt_tgt.table_name(+)
    AND    ao_tgt.owner = atp.table_owner(+)
    AND    ao_tgt.object_name = atp.table_name(+)
    AND    ao_src.object_type NOT IN('TABLE PARTITION','TABLE SUBPARTITION')
    AND    ao_tgt.object_type NOT IN('TABLE PARTITION','TABLE SUBPARTITION');

  CURSOR c_hakan IS
    WITH src AS
    (
    SELECT tab$.spare1 src_hakan_factor
    FROM   sys.tab$
    ,      all_objects ao
    WHERE  ao.object_id = tab$.obj#
    AND    ao.owner = p_source_object_owner
    AND    ao.object_name = p_source_object_name
    )
    , tgt AS
	(
    SELECT tab$.spare1 tgt_hakan_factor
    FROM   sys.tab$
    ,      all_objects ao
    WHERE  ao.object_id = tab$.obj#
    AND    ao.owner = p_target_object_owner
    AND    ao.object_name = p_target_object_name
    )
    SELECT *
    FROM   src ,tgt;

  CURSOR c_columns IS
    WITH src AS
    (
    SELECT atc_src.column_name src_column_name
    ,      atc_src.data_type src_data_type
    ,      atc_src.data_length src_data_length
    ,      atc_src.data_precision src_data_precision
    ,      atc_src.data_scale src_data_scale
    ,      atc_src.nullable src_nullable
    ,      atc_src.column_id src_column_id
    FROM   all_tab_columns atc_src
    WHERE  atc_src.owner = p_source_object_owner
    AND    atc_src.table_name = p_source_object_name
    )
    , tgt as
    (
    SELECT atc_tgt.column_name tgt_column_name
    ,      atc_tgt.data_type tgt_data_type
    ,      atc_tgt.data_length tgt_data_length
    ,      atc_tgt.data_precision tgt_data_precision
    ,      atc_tgt.data_scale tgt_data_scale
    ,      atc_tgt.nullable tgt_nullable
    ,      atc_tgt.column_id tgt_column_id
    FROM   all_tab_columns atc_tgt
    WHERE  atc_tgt.owner = p_target_object_owner
    AND    atc_tgt.table_name = p_target_object_name
    )
    SELECT *
    FROM   src FULL OUTER JOIN tgt
    ON    src.src_column_id = tgt.tgt_column_id
    ORDER BY src_column_id;

  CURSOR c_constraints IS
    WITH src AS
    (
	SELECT constraint_name src_constraint_name
    ,      constraint_type src_constraint_type
    ,      search_condition src_search_condition
    ,      r_owner src_r_owner
    ,      r_constraint_name src_r_constraint_name
    ,      delete_rule src_delete_rule
    ,      status src_status
    ,      deferrable src_deferrable
    ,      validated src_validated
    ,      generated src_generated
    ,      rely src_rely
    ,      invalid src_invalid
    FROM   all_constraints
    WHERE  table_name = p_source_object_name
    AND    owner = p_source_object_owner
    -- exclude NOT NULL constraints as we check them at the columns stage.
    AND NOT (constraint_type = 'C' AND generated = 'GENERATED NAME')
    )
    , tgt AS
    (
	SELECT constraint_name tgt_constraint_name
    ,      constraint_type tgt_constraint_type
    ,      search_condition tgt_search_condition
    ,      r_owner tgt_r_owner
    ,      r_constraint_name tgt_r_constraint_name
    ,      delete_rule tgt_delete_rule
    ,      status tgt_status
    ,      deferrable tgt_deferrable
    ,      deferred tgt_deferred
    ,      validated tgt_validated
    ,      generated tgt_generated
    ,      bad tgt_bad
    ,      rely tgt_rely
    ,      invalid tgt_invalid
    FROM   all_constraints
    WHERE  table_name = p_target_object_name
    AND    owner = p_target_object_owner
    -- exclude NOT NULL constraints as we check them at the columns stage.
    AND NOT (constraint_type = 'C' AND generated = 'GENERATED NAME')
    )
    SELECT *
    FROM   src FULL OUTER JOIN tgt
    ON    src.src_constraint_name = tgt.tgt_constraint_name
    ORDER BY src.src_constraint_name;

  CURSOR c_indexes IS
    WITH src AS
    (
    SELECT ai.index_name src_index_name
    ,      ai.index_type src_index_type
    ,      ai.uniqueness src_uniqueness
    ,      ai.compression src_compression
    ,      ai.pct_threshold src_pct_threshold
    ,      ai.include_column src_include_column
    ,      ai.status src_status
    ,      aic.column_name src_column_name
    ,      aic.column_position src_column_position
    ,      aic.descend src_descend
    FROM   all_indexes ai
    ,      all_ind_columns aic
    WHERE  ai.table_name = p_source_object_name
    AND    ai.table_owner = p_source_object_owner
    AND    ai.index_name = aic.index_name
    AND    ai.owner = aic.index_owner
    )
    , tgt AS
    (
    SELECT ai.index_name tgt_index_name
    ,      ai.index_type tgt_index_type
    ,      ai.uniqueness tgt_uniqueness
    ,      aip.compression tgt_compression
    ,      ai.pct_threshold tgt_pct_threshold
    ,      ai.include_column tgt_include_column
    ,      aip.status tgt_status
    ,      aic.column_name tgt_column_name
    ,      aic.column_position tgt_column_position
    ,      aic.descend tgt_descend
    FROM   all_indexes ai
    ,      all_tab_partitions atp
    ,      all_ind_partitions aip
    ,      all_ind_columns aic
    WHERE  ai.table_name = p_target_object_name
    AND    ai.table_owner = p_target_object_owner
    AND    atp.partition_name = p_target_object_partition_name
    AND    atp.table_name = ai.table_name
    AND    atp.table_owner = ai.table_owner
    AND    atp.partition_position = aip.partition_position
    AND    ai.index_name = aic.index_name
    AND    ai.owner = aic.index_owner
    AND    ai.index_name = aip.index_name
    AND    ai.owner = aip.index_owner
    -- only get partitioned indexes on the target - they are the only ones we can handle.
    AND    ai.partitioned = 'YES'
    )
    SELECT *
    FROM   src FULL OUTER JOIN tgt
    ON     src.src_index_name = tgt.tgt_index_name
	AND    src.src_column_position = tgt.tgt_column_position
    ORDER BY src.src_index_name
    ,        src.src_column_position;

  r_attributes c_attributes%ROWTYPE;
  r_hakan c_hakan%ROWTYPE;

  l_bad_column_exists BOOLEAN;
  l_bad_constraint_exists BOOLEAN;
  l_bad_index_exists BOOLEAN;

BEGIN
  dbms_output.put_line(LPAD('*',100,'*'));
  dbms_output.put_line('Starting run of mgmt_r_check_pel...');
  dbms_output.put_line(LPAD('*',100,'*'));
  dbms_output.put_line('Parameters...');
  dbms_output.put_line('P_SOURCE_OBJECT_OWNER: '||p_source_object_owner);
  dbms_output.put_line('P_SOURCE_OBJECT_NAME: '||p_source_object_name);
  dbms_output.put_line('P_TARGET_OBJECT_OWNER: '||p_target_object_owner);
  dbms_output.put_line('P_TARGET_OBJECT_NAME: '||p_target_object_name);
  dbms_output.put_line('P_TARGET_OBJECT_PARTITION_NAME: '||p_target_object_partition_name);
  dbms_output.put_line(LPAD('*',100,'*'));
  dbms_output.put_line('Check object level compatibility...');
  dbms_output.put_line(LPAD('*',100,'*'));

  OPEN c_attributes;
  FETCH c_attributes INTO r_attributes;
  IF c_attributes%NOTFOUND OR c_attributes%NOTFOUND IS NULL THEN
    -- No attributes found for the supplied parameters
    CLOSE c_attributes;
    dbms_output.put_line('Sorry - parameters supplied do not match any objects in the dictionary');
  ELSE
    CLOSE c_attributes;
    -- Check that the objects can undertake a PEL...
    -- Heap Tables can be exchanged with Partitions or Subpartitions
    --  on Heap Tables or Materialized Views (including Prebuilt)
    -- Materialized Views can be exchanged with Partitions or Subpartitions
    --  on Heap Tables or Materialized Views (including Prebuilt)
    -- All other objects can not be exchanged.
    IF r_attributes.src_cluster_owner IS NOT NULL
      OR r_attributes.tgt_cluster_owner IS NOT NULL THEN
      dbms_output.put_line('FAILURE: Clustered Tables are incompatible with Partition Exchange');
    ELSIF r_attributes.src_object_type = 'IOT'
        AND r_attributes.tgt_object_type = 'IOT'
		AND (NVL(r_attributes.src_include_column,-1) != NVL(r_attributes.src_include_column,-1)
             OR NVL(r_attributes.src_pct_threshold,-1) != NVL(r_attributes.tgt_pct_threshold,-1)
			) THEN
      dbms_output.put_line('FAILURE: Index Organised Tables with different Overflow characteristics - incompatible for partition exchange');
    ELSIF (r_attributes.src_object_type = 'IOT'
           AND r_attributes.tgt_object_type = 'IOT'
           AND NVL(r_attributes.src_include_column,-1) = NVL(r_attributes.src_include_column,-1)
           AND NVL(r_attributes.src_pct_threshold,-1) = NVL(r_attributes.tgt_pct_threshold,-1)
          )
	  OR
	  (r_attributes.src_object_type IN('TABLE','MATERIALIZED_VIEW')
           AND r_attributes.tgt_object_type IN('TABLE','MATERIALIZED_VIEW')
	  ) THEN
      dbms_output.put_line('PASS: The source and target objects are ones which can be partition exchanged');
    ELSE
      dbms_output.put_line('FAILURE: The source and target objects are not ones which can be partition exchanged');
    END IF;

    -- Now check hakan factors...
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line('Check Hakan Factor compatability...');
    dbms_output.put_line(LPAD('*',100,'*'));
    OPEN c_hakan;
    FETCH c_hakan INTO r_hakan;
    IF c_hakan%NOTFOUND OR c_hakan%NOTFOUND IS NULL THEN
      CLOSE c_hakan;
      dbms_output.put_line('WARNING:Hakan Factors not found...');
	ELSE
      CLOSE c_hakan;
      IF r_hakan.src_hakan_factor != r_hakan.tgt_hakan_factor THEN
        dbms_output.put_line('FAILURE: Hakan Factors differ - Partition Exchange will fail');
      ELSE
        dbms_output.put_line('PASS: Hakan Factors same: '||TO_CHAR(r_hakan.src_hakan_factor));
	  END IF;
	END IF;

    -- Now check columns...
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line('Check column level compatability...');
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line(RPAD('SIDE',7)||
                         RPAD('COLUMN_ID',10)||
                         RPAD('COLUMN_NAME',31)||
                         RPAD('DATA_TYPE',9)||
                         'LENGTH '||
                         'PRECISION '||
                         'SCALE '||
                         'NULL?'
                         );

    dbms_output.put_line(RPAD('=',7,'=')||
                         RPAD('=',10,'=')||
                         RPAD('=',31,'=')||
                         RPAD('=',9,'=')||
                         RPAD('=',7,'=')||
                         RPAD('=',10,'=')||
                         RPAD('=',6,'=')||
                         RPAD('=',5,'=')
                         );

    -- set flag to keep track of whether we have any bad columns...
    l_bad_column_exists := FALSE;

    FOR r_columns IN c_columns LOOP
      IF r_columns.src_column_id = r_columns.tgt_column_id
        AND r_columns.src_column_name = r_columns.tgt_column_name
        AND r_columns.src_data_type = r_columns.tgt_data_type
        AND r_columns.src_data_length = r_columns.tgt_data_length
        AND NVL(r_columns.src_data_precision,-1) = NVL(r_columns.tgt_data_precision,-1)
        AND NVL(r_columns.src_data_scale,-1) = NVL(r_columns.tgt_data_scale,-1)
        AND r_columns.src_nullable = r_columns.tgt_nullable
      THEN
          dbms_output.put_line('SAME   '||
                               RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
                               RPAD(NVL(r_columns.src_column_name,'-'),31)||
                               RPAD(NVL(r_columns.src_data_type,'-'),9)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_length),'-'),7)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_precision),'-'),10)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_scale),'-'),6)||
                               RPAD(NVL(r_columns.src_nullable,'-'),6)
                              );
      ELSE
        l_bad_column_exists := TRUE;
        IF r_columns.src_column_id IS NULL THEN
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(TO_CHAR(r_columns.tgt_column_id),'-'),10)||
                               RPAD(NVL(r_columns.tgt_column_name,'-'),31)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
                               RPAD(NVL(r_columns.src_column_name,'-'),31)||
                               RPAD(NVL(r_columns.src_data_type,'-'),9)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_length),'-'),7)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_precision),'-'),10)||
                               RPAD(NVL(TO_CHAR(r_columns.src_data_scale),'-'),6)||
                               RPAD(NVL(r_columns.src_nullable,'-'),6)
                              );
        END IF;
        IF r_columns.tgt_column_id IS NULL THEN
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(TO_CHAR(r_columns.src_column_id),'-'),10)||
                               RPAD(NVL(r_columns.src_column_name,'-'),31)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(TO_CHAR(r_columns.tgt_column_id),'-'),10)||
                               RPAD(NVL(r_columns.tgt_column_name,'-'),31)||
                               RPAD(NVL(r_columns.tgt_data_type,'-'),9)||
                               RPAD(NVL(TO_CHAR(r_columns.tgt_data_length),'-'),7)||
                               RPAD(NVL(TO_CHAR(r_columns.tgt_data_precision),'-'),10)||
                               RPAD(NVL(TO_CHAR(r_columns.tgt_data_scale),'-'),6)||
                               RPAD(NVL(r_columns.tgt_nullable,'-'),6)
                              );
        END IF;
      END IF;
    END LOOP;

    dbms_output.put_line(LPAD('*',100,'*'));

    -- indicate whether any column checks failed
    IF l_bad_column_exists THEN
      dbms_output.put_line('FAILURE: At least one column is different - Partition Exchange will fail');
    ELSE
      dbms_output.put_line('PASS: All columns are the same');
    END IF;

    -- Now check constraints...
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line('Check constraint level compatability...');
    dbms_output.put_line(LPAD('*',100,'*'));

    dbms_output.put_line(RPAD('SIDE',7)||
                         RPAD('CONSTRAINT NAME',31)||
                         RPAD('TYPE',5)||
                         RPAD('R_OWNER',26)||
                         RPAD('R_CONSTRAINT_NAME',31)
                         );
    dbms_output.put_line('/  '||
	                     RPAD('DELETE_RULE',12)||
                         RPAD('STATUS',9)||
                         RPAD('DEFERRABLE',15)||
                         RPAD('VALIDATED',14)||
                         RPAD('GENERATED',15)||
                         RPAD('RELY',5)||
                         RPAD('INVALID',8)
                         );

    dbms_output.put_line(RPAD('=',100,'='));

    -- set flag to keep track of whether we have any bad columns...
    l_bad_constraint_exists := FALSE;

    FOR r_constraints IN c_constraints LOOP
      IF r_constraints.src_constraint_name = r_constraints.tgt_constraint_name
        AND NVL(r_constraints.src_constraint_type,'12') = NVL(r_constraints.tgt_constraint_type,'12')
        AND NVL(r_constraints.src_r_owner,'1234567890123456789012345678901') =
		        NVL(r_constraints.tgt_r_owner,'1234567890123456789012345678901')
        AND NVL(r_constraints.src_r_constraint_name,'1234567890123456789012345678901') =
		        NVL(r_constraints.tgt_r_constraint_name,'1234567890123456789012345678901')
        AND NVL(r_constraints.src_delete_rule,'1234567890') = NVL(r_constraints.tgt_delete_rule,'1234567890')
        AND NVL(r_constraints.src_status,'123456789') = NVL(r_constraints.tgt_status,'123456789')
        AND NVL(r_constraints.src_deferrable,'123456789012345') = NVL(r_constraints.tgt_deferrable,'123456789012345')
        AND NVL(r_constraints.src_validated,'12345678901234') = NVL(r_constraints.tgt_validated,'12345678901234')
        AND NVL(r_constraints.src_generated,'123456789012345') = NVL(r_constraints.tgt_generated,'123456789012345')
        AND NVL(r_constraints.src_rely,'12345') = NVL(r_constraints.tgt_rely,'12345')
        AND NVL(r_constraints.src_invalid,'12345678') = NVL(r_constraints.tgt_invalid,'12345678')
      THEN
          dbms_output.put_line('SAME   '||
                               RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
                               RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
                               RPAD(NVL(r_constraints.src_r_owner,'-'),26)||
                               RPAD(NVL(r_constraints.src_r_constraint_name,'-'),31)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(r_constraints.src_delete_rule,'-'),12)||
                               RPAD(NVL(r_constraints.src_status,'-'),9)||
                               RPAD(NVL(r_constraints.src_deferrable,'-'),15)||
                               RPAD(NVL(r_constraints.src_validated,'-'),14)||
                               RPAD(NVL(r_constraints.src_generated,'-'),15)||
                               RPAD(NVL(r_constraints.src_rely,'-'),5)||
                               RPAD(NVL(r_constraints.src_invalid,'-'),8)
                              );
      ELSE
        l_bad_constraint_exists := TRUE;
        IF r_constraints.src_constraint_name IS NULL THEN
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(r_constraints.tgt_constraint_name,'-'),31)||
                               RPAD(NVL(r_constraints.tgt_constraint_type,'-'),5)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
                               RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
                               RPAD(NVL(r_constraints.src_r_owner,'-'),26)||
                               RPAD(NVL(r_constraints.src_r_constraint_name,'-'),31)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(r_constraints.src_delete_rule,'-'),12)||
                               RPAD(NVL(r_constraints.src_status,'-'),9)||
                               RPAD(NVL(r_constraints.src_deferrable,'-'),15)||
                               RPAD(NVL(r_constraints.src_validated,'-'),14)||
                               RPAD(NVL(r_constraints.src_generated,'-'),15)||
                               RPAD(NVL(r_constraints.src_rely,'-'),5)||
                               RPAD(NVL(r_constraints.src_invalid,'-'),8)
                              );
        END IF;
        IF r_constraints.tgt_constraint_name IS NULL THEN
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(r_constraints.src_constraint_name,'-'),31)||
                               RPAD(NVL(r_constraints.src_constraint_type,'-'),5)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(r_constraints.tgt_constraint_name,'-'),31)||
                               RPAD(NVL(r_constraints.tgt_constraint_type,'-'),5)||
                               RPAD(NVL(r_constraints.tgt_r_owner,'-'),26)||
                               RPAD(NVL(r_constraints.tgt_r_constraint_name,'-'),31)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(r_constraints.tgt_delete_rule,'-'),12)||
                               RPAD(NVL(r_constraints.tgt_status,'-'),9)||
                               RPAD(NVL(r_constraints.tgt_deferrable,'-'),15)||
                               RPAD(NVL(r_constraints.tgt_validated,'-'),14)||
                               RPAD(NVL(r_constraints.tgt_generated,'-'),15)||
                               RPAD(NVL(r_constraints.tgt_rely,'-'),5)||
                               RPAD(NVL(r_constraints.tgt_invalid,'-'),8)
                              );
        END IF;
      END IF;
    END LOOP;

    dbms_output.put_line(LPAD('*',100,'*'));

    -- indicate whether any constraint checks failed
    IF l_bad_constraint_exists THEN
      dbms_output.put_line('FAILURE: At least one constraint is different - Partition Exchange will fail');
    ELSE
      dbms_output.put_line('PASS: All constraints are the same');
    END IF;

    dbms_output.put_line(LPAD('*',100,'*'));

    -- Now check indexes...
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line('Check Index level compatability...');
    dbms_output.put_line('NOTE - If WITHOUT INDEXES is used in the exchange then the following may not be relevant.');
    dbms_output.put_line(LPAD('*',100,'*'));

    dbms_output.put_line(RPAD('SIDE',7)||
                         RPAD('INDEX NAME',31)||
                         RPAD('INDEX TYPE',28)||
                         RPAD('UNIQUENESS',11)||
                         RPAD('COMPRESSION',12)||
                         RPAD('PCT_THRESHOLD',14)
                         );
    dbms_output.put_line('/  '||
                         RPAD('INCLUDE_COLUMN',15)||
                         RPAD('STATUS',9)||
                         RPAD('COLUMN_NAME',31)||
                         RPAD('COLUMN POSITION',16)||
                         RPAD('DESCEND',8)
                         );

    dbms_output.put_line(RPAD('=',100,'='));

    -- set flag to keep track of whether we have any bad columns...
    l_bad_index_exists := FALSE;

    FOR r_indexes IN c_indexes LOOP
      IF r_indexes.src_index_name = r_indexes.tgt_index_name
        AND NVL(r_indexes.src_index_type,'1234567890123456789012345678') =
		        NVL(r_indexes.tgt_index_type,'1234567890123456789012345678')
        AND NVL(r_indexes.src_uniqueness,'1234567890') = NVL(r_indexes.tgt_uniqueness,'1234567890')
        AND NVL(r_indexes.src_compression,'123456789') = NVL(r_indexes.tgt_compression,'123456789')
        AND NVL(r_indexes.src_pct_threshold,-1) = NVL(r_indexes.tgt_pct_threshold,-1)
        AND NVL(r_indexes.src_include_column,-1) = NVL(r_indexes.tgt_include_column,-1)
        AND NVL(r_indexes.src_status,'123456789') =
		        (CASE WHEN NVL(r_indexes.tgt_status,'123456789') = 'USABLE'
				      THEN 'VALID'
				      ELSE 'INVALID'
				       END)
        AND NVL(r_indexes.src_column_name,'1234567890123456789012345678901') =
		        NVL(r_indexes.tgt_column_name,'1234567890123456789012345678901')
        AND NVL(r_indexes.src_column_position,-1) = NVL(r_indexes.tgt_column_position,-1)
        AND NVL(r_indexes.src_descend,'12345') = NVL(r_indexes.tgt_descend,'12345')
      THEN
          dbms_output.put_line('SAME   '||
                               RPAD(NVL(r_indexes.src_index_name,'-'),31)||
                               RPAD(NVL(r_indexes.src_index_type,'-'),28)||
                               RPAD(NVL(r_indexes.src_uniqueness,'-'),11)||
                               RPAD(NVL(r_indexes.src_compression,'-'),12)||
                               RPAD(NVL(TO_CHAR(r_indexes.src_pct_threshold),'-'),14)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(TO_CHAR(r_indexes.src_include_column),'-'),15)||
                               RPAD(NVL(r_indexes.src_status,'-'),9)||
                               RPAD(NVL(r_indexes.src_column_name,'-'),31)||
                               RPAD(NVL(TO_CHAR(r_indexes.src_column_position),'-'),16)||
                               RPAD(NVL(r_indexes.src_descend,'-'),8)
                              );
      ELSE
        l_bad_index_exists := TRUE;
        IF r_indexes.src_index_name IS NULL THEN
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(r_indexes.tgt_index_name,'-'),31)||
                               RPAD(NVL(r_indexes.tgt_index_type,'-'),28)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('SOURCE '||
                               RPAD(NVL(r_indexes.src_index_name,'-'),31)||
                               RPAD(NVL(r_indexes.src_index_type,'-'),28)||
                               RPAD(NVL(r_indexes.src_uniqueness,'-'),11)||
                               RPAD(NVL(r_indexes.src_compression,'-'),12)||
                               RPAD(NVL(TO_CHAR(r_indexes.src_pct_threshold),'-'),14)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(TO_CHAR(r_indexes.src_include_column),'-'),15)||
                               RPAD(NVL(r_indexes.src_status,'-'),9)||
                               RPAD(NVL(r_indexes.src_column_name,'-'),31)||
                               RPAD(NVL(TO_CHAR(r_indexes.src_column_position),'-'),16)||
                               RPAD(NVL(r_indexes.src_descend,'-'),8)
                              );
        END IF;
        IF r_indexes.tgt_index_name IS NULL THEN
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(r_indexes.src_index_name,'-'),31)||
                               RPAD(NVL(r_indexes.src_index_type,'-'),28)||
                               'MISSING'
                              );
        ELSE
          dbms_output.put_line('TARGET '||
                               RPAD(NVL(r_indexes.tgt_index_name,'-'),31)||
                               RPAD(NVL(r_indexes.tgt_index_type,'-'),28)||
                               RPAD(NVL(r_indexes.tgt_uniqueness,'-'),11)||
                               RPAD(NVL(r_indexes.tgt_compression,'-'),12)||
                               RPAD(NVL(TO_CHAR(r_indexes.tgt_pct_threshold),'-'),14)
                              );
          dbms_output.put_line('/  '||
                               RPAD(NVL(TO_CHAR(r_indexes.tgt_include_column),'-'),15)||
                               RPAD(NVL(r_indexes.tgt_status,'-'),9)||
                               RPAD(NVL(r_indexes.tgt_column_name,'-'),31)||
                               RPAD(NVL(TO_CHAR(r_indexes.tgt_column_position),'-'),16)||
                               RPAD(NVL(r_indexes.tgt_descend,'-'),8)
                              );
        END IF;
      END IF;
    END LOOP;

    dbms_output.put_line(LPAD('*',100,'*'));

    -- indicate whether any index checks failed
    IF l_bad_index_exists THEN
      dbms_output.put_line('FAILURE: At least one index is different - Partition Exchange will fail');
    ELSE
      dbms_output.put_line('PASS: All indexes are the same');
    END IF;

    -- Now check sub/partitioning...
    dbms_output.put_line(LPAD('*',100,'*'));
    dbms_output.put_line('Check partitioning/subpartitioning types/counts');
    dbms_output.put_line(LPAD('*',100,'*'));

    IF r_attributes.src_partitioning_type IS NULL
      AND NVL(r_attributes.tgt_subpartitioning_type,'NONE') != 'NONE' THEN
      dbms_output.put_line('FAILURE: Source is not partitioned but target is subpartitioned');
    ELSIF NVL(r_attributes.src_partitioning_type,'X') = 'LIST'
      AND NVL(r_attributes.tgt_subpartitioning_type,'X') != 'LIST' THEN
      dbms_output.put_line('FAILURE: Source is LIST partitioned but target is: '||NVL(r_attributes.tgt_subpartitioning_type,'UNSET'));
    ELSIF NVL(r_attributes.src_partitioning_type,'X') = 'RANGE'
      AND NVL(r_attributes.tgt_subpartitioning_type,'X') != 'RANGE' THEN
      dbms_output.put_line('FAILURE: Source is RANGE partitioned but target is not');
    ELSIF NVL(r_attributes.src_partitioning_type,'X') = NVL(r_attributes.tgt_subpartitioning_type,'X')
      AND NVL(r_attributes.src_partition_count,-1) != NVL(r_attributes.tgt_subpartition_count,-1) THEN
      dbms_output.put_line('FAILURE: '||r_attributes.src_partitioning_type||' partitioned source has '||r_attributes.src_partition_count||' partitions, but '||
                           r_attributes.tgt_subpartitioning_type||' subpartitioned target has '||r_attributes.tgt_subpartition_count||' subpartitions');
    ELSE
      dbms_output.put_line('PASS: Source and target partitioning and subpartitioning are acceptable');
    END IF;

  END IF;
  dbms_output.put_line(LPAD('*',100,'*'));

EXCEPTION
  WHEN OTHERS THEN
    IF c_hakan%ISOPEN THEN
      CLOSE c_hakan;
    END IF;
    IF c_attributes%ISOPEN THEN
      CLOSE c_attributes;
    END IF;
    RAISE;
END mgmt_r_check_pel;
/
grant execute on mgmt_r_check_pel to public;
--exec mgmt_r_check_pel('AE_STG_AML','STG_AML_T_BAD_MONTHLY_PIVOT','AE_AML','AML_T_BAD_MONTHLY_PIVOT','AML_T_BAF_P0001');