CODEWITHSUNDEEP
sqloracle-database
Bhavya Sheth
Bhavya Sheth

Reputation: 1

Compare same tables from different databases but exclude user_id and date in comparison

I am trying to compare same tables from two different database to achieve the difference rows based on table. For that I have written query:

[select table_name
   from all_tables
  where owner = 'MDWMP']
|
[select @table_name tbl,
        count(*)
   from((select *
        from mdwmp. @table_name:raw 
          minus
          select *
            from mdwmd. @table_name:raw)
         union
         (select *
            from mdwmd. @table_name:raw
          minus
          select *
            from mdwmp. @table_name:raw))] catch(-1403);

where mdwmp is my 1st database and mdwmd is my 2nd database.

This query is giving me an error:

ORA-00932: inconsistent datatypes: expected - got CLOB.

Tables may contain modified_user_id, date&time column. I want to exclude such columns from the comparison and get the unique values.

Upvotes: 0

Views: 46

Answers (1)

Danny James
Danny James

Reputation: 254

Just take out any Clob data type columns out of your selects as these cant be compared against or manipulated. or you can try a dynamic way below:

DECLARE
CURSOR table_cursor IS
    SELECT table_name
    FROM all_tables
    WHERE owner = 'MDWMP';
DECLARE
table_name VARCHAR2(100);
diff_count NUMBER;
BEGIN
FOR table_rec IN (SELECT table_name FROM all_tables WHERE owner = 'MDWMP') LOOP
    table_name := table_rec.table_name;
    
    EXECUTE IMMEDIATE '
        SELECT COUNT(*) INTO :diff_count FROM (
            SELECT * FROM ' || 'mdwmp.' || table_name || '
            MINUS
            SELECT * FROM ' || 'mdwmd.' || table_name || '
            UNION
            SELECT * FROM ' || 'mdwmd.' || table_name || '
            MINUS
            SELECT * FROM ' || 'mdwmp.' || table_name || '
        )' INTO diff_count;
    
    DBMS_OUTPUT.PUT_LINE('Table: ' || table_name || ', Differences: ' || diff_count);
END LOOP;
END;

Upvotes: 0

Related Questions