I have two tables with same structure. Let it be 3 columns and a primary key, which are all number values.

Both tables should have similar values, but there are some different values present in the tables. I need to find out these values and at which positions they exist. How can I write the script for this in Oracle SQL Developer?

I tried it using joins and cursors. I'm new to this PL/SQL scripting, thus its not easy for me to understand it. so any kind of help is appreciated! Happy coding!

CREATE OR REPLACE PACKAGE mismatch_finder IS
  PROCEDURE find_mismatch_values;
  FUNCTION row_finder_tb1(pkey number) RETURN table1%rowtype;
  FUNCTION row_finder_tb2(pkey number) RETURN table2%rowtype;
END mismatch_finder;

CREATE OR REPLACE PACKAGE BODY mismatch_finder AS

PROCEDURE find_mismatch_values AS
CURSOR CUR IS
select pk from(select * from table1 minus select * from table2); REC CUR%rowtype; t1 table1%rowtype; t2 table2%rowtype; col_count number := 1;

BEGIN
OPEN CUR; LOOP FETCH CUR into REC; EXIT when CUR%NOTFOUND;

t1 := row_finder_tb1(REC.pk); t2 := row_finder_tb2(REC.pk);

IF (t1.column_1 != t2.column_1) THEN dbms_output.put_line('Value missmatch at key value' || REC.pk || ' column number ' || col_count || ' Table 1 value is : ' || t1.column_1 || ' and Table 2 value is : ' || t2.column_1);
END IF; col_count := col_count   1;

IF (t1.column_2 != t2.column_2) THEN dbms_output.put_line('Value missmatch at key value' || REC.pk || ' column number ' || col_count || ' Table 1 value is : ' || t1.column_2 || ' and Table 2 value is : ' || t2.column_2);
END IF; col_count := col_count   1;

IF (t1.column_3 != t2.column_3) THEN dbms_output.put_line('Value missmatch at key value' || REC.pk || ' column number ' || col_count || ' Table 1 value is : ' || t1.column_3 || ' and Table 2 value is : ' || t2.column_3);
END IF; col_count := 1;
END LOOP; CLOSE CUR;
END find_mismatch_values;

FUNCTION row_finder_tb1(p_key number) RETURN table1%rowtype IS
TEMP table1%rowtype;
BEGIN
select * into TEMP from table1 where table1.pk = p_key; RETURN(TEMP);
END row_finder_tb1;

FUNCTION row_finder_tb2(p_key number) RETURN table2%rowtype IS
TEMP table2%rowtype;
BEGIN
select * into TEMP from table2 where table2.pk = p_key; RETURN(TEMP);
END row_finder_tb2;

END mismatch_finder;

CodePudding user response：

Here's one way to do it:

1. Pivot all columns into rows with added column ID (c_id in cte_tb1, cte_tb2), which makes (id, c_id) the primary key of both tables.
2. Assume both tables have the same number of IDs and rows (change the inner join to outer join if not)
3. Join both pivoted CTE tables and compare values.

create table tb1 (
    id  int,
    c1  int,
    c2  int,
    c3  int
);

create table tb2 (
    id  int,
    c1  int,
    c2  int,
    c3  int
);
 

insert into tb1 values (1, 11, 12, 13), (2, 21, 22, 23), (3, 31, 32, 33), (4, 41, 42, 43);

insert into tb2 values (1, 11, 12, 13), (2, 22, 22, 23), (3, 31, 33, 35), (4, 42, 43, 44);


with cte_tb1 as (
select id, 1 as c_id, c1 as value from tb1 
union all
select id, 2 as c_id, c2 as value from tb1 
union all
select id, 3 as c_id, c3 as value from tb1),
cte_tb2 as (
select id, 1 as c_id, c1 as value from tb2 
union all
select id, 2 as c_id, c2 as value from tb2 
union all
select id, 3 as c_id, c3 as value from tb2)
select t1.id    as id,
       t1.c_id  as col_id,
       t1.value as t1_value,
       t2.value as t2_value
  from cte_tb1 t1
  join cte_tb2 t2
 using (id, c_id)
 where t1.value != t2.value
 order by 1,2;

Result:

id|col_id|t1_value|t2_value|
-- ------ -------- -------- 
 2|     1|      21|      22|
 3|     2|      32|      33|
 3|     3|      33|      35|
 4|     1|      41|      42|
 4|     2|      42|      43|
 4|     3|      43|      44|

CodePudding user response：

You can join the tables and use the CASE expressions to show differences. With sample data like:

WITH
    tbl_1 AS
        (
            Select 1 "ID", 10 "A", 100 "B", 1000 "C" From Dual Union All
            Select 2 "ID", 11 "A", 101 "B", 1001 "C" From Dual Union All
            Select 3 "ID", 12 "A", 102 "B", 1002 "C" From Dual Union All
            Select 4 "ID", 13 "A", 103 "B", 1003 "C" From Dual Union All
            Select 5 "ID", 14 "A", 104 "B", 1004 "C" From Dual 
        ),
    tbl_2 AS
        (
            Select 1 "ID", 10 "A", 100 "B", 1000 "C" From Dual Union All
            Select 2 "ID", 11 "A", 201 "B", 2001 "C" From Dual Union All
            Select 3 "ID", 12 "A", 102 "B", 1002 "C" From Dual Union All
            Select 4 "ID", 23 "A", 103 "B", 1003 "C" From Dual Union All
            Select 5 "ID", 14 "A", 104 "B", 1004 "C" From Dual 
        )

Here is the code:

SELECT
    t1.ID, 
    CASE When t1.A = t2.A THEN 0 ELSE t1.A END "T1_A",
    CASE When t1.A = t2.A THEN 0 ELSE t2.A END "T2_A",
    CASE When t1.B = t2.B THEN 0 ELSE t1.B END "T1_B",
    CASE When t1.B = t2.B THEN 0 ELSE t2.B END "T2_B",
    CASE When t1.C = t2.C THEN 0 ELSE t1.C END "T1_C",
    CASE When t1.C = t2.C THEN 0 ELSE t2.C END "T2_C"
FROM
    tbl_1 t1
Inner Join
    tbl_2 t2 ON(t2.ID = t1.ID)

The result shows just different values while those that are equal are set to 0. You can change it to Null or anything else you want including the actual values.

Regards...