Htykuut

Lets say i have table 1 and table 2. Table 2 is the updated version of table one. It can have same or updated structure, columns and data. I want to compare those two tables.

If you want to compare all columns in 2 tables then try below query

`select * from table1 t1,table2 t2 where t1.id = t2.id`

But if you want compare some specified columns then try below query

`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id

 group by t1.column,t2.column`

It should be work for your requirement.

My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.

For data comparison:

OPEN p_store FOR SELECT * FROM customers@dblink2;

OPEN p_store2 FOR SELECT * FROM customers2@dblink2;

ut.expect(p_store).to_equal(p_store2);

For column comparison:

OPEN p_store FOR

SELECT

    A.COLUMN_NAME,

    A.DATA_TYPE,

    A.DATA_LENGTH

FROM

    (SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME  = 'CUSTOMERS') A;

OPEN p_store2 FOR

SELECT

  B.COLUMN_NAME,

  B.DATA_TYPE,

  B.DATA_LENGTH

FROM

  (SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;

ut.expect(p_store).to_equal(p_store2);

I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.

=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:

SELECT

    t1.table_name

   ,t2.table_name

   ,t1.column_name

   ,t2.column_name

FROM

    (

        SELECT

            *

        FROM

            all_tab_cols

        WHERE

            table_name = 'TEMP1'

    ) t1

    FULL OUTER JOIN (

        SELECT

            *

        FROM

            all_tab_cols

        WHERE

            table_name = 'TEMP2'

    ) t2 ON t1.owner = t2.owner

            AND t1.column_name = t2.column_name;

2. 
   

   To Compare data :

   
   
   
   
   
   1. 
      

      You can Use a similar query with join different types of join to compare like left and right join.

      
      
      

      SELECT
      *
      FROM
      temp1 t1
      FULL JOIN temp2 t2 ON t1.id = t2.id;

      
      
   
   

   2. You can Use Set operations like union ,unionall and intersect to compare further use distinct.