How to compare 2 tables (data)

--create table tmp (sql_text long);
CREATE OR REPLACE PROCEDURE compareTabs
(
p_c OUT SYS_REFCURSOR --output
, p_tab1 VARCHAR2     --table name 1
, p_tab2 VARCHAR2     -- table name 2
, p_count NUMBER DEFAULT 300 --count of output rows
, p_round NUMBER DEFAULT 9 -- how to round numbers
, p_cols VARCHAR2 DEFAULT '#' --columns to NOT show separated by #, all by default #
, p_owner1 VARCHAR2 DEFAULT USER --owner of table 1
, p_owner2 VARCHAR2 DEFAULT USER --owner of table 2
, p_where1 VARCHAR2 DEFAULT NULL -- where clause for table 1
, p_where2 VARCHAR2 DEFAULT NULL -- where clause for table 2
, p_list1 VARCHAR2 DEFAULT NULL -- list of columns from table 1
, p_list2 VARCHAR2 DEFAULT NULL -- list of columns from table 2
, p_column VARCHAR2 DEFAULT NULL --first "key" column to show and sort by
)
IS
-- Original idea of Marco Stefanetti with Tom Kyte's minor addition
-- http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
-- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2151582681236
-- Created: 26.12.2006
-- Update: 17.01.2013
-- Autor: Andrey Smirnov
-- Using in sqlplus:
-- variable vc refcursor
-- set autoprint on
-- set linesize 32767
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst'); 

-- exclued several columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_cols => 'created#data_object_id#edition_name);
-- compare only required columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_list1 => 'object_id, object_name');
-- compare different set of columns with expressions
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_list1 => 'owner, object_name, object_type, object_id', p_list2 => 'owner, object_name, object_type, NVL(data_object_id, object_id)');
-- compare starting and sorting by special columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_column => 'object_id, data_object_id');  v_select_list1 VARCHAR2(32767);
v_select_list2 VARCHAR2(32767);
v_group_list1 VARCHAR2(32767);
v_where_clause1 VARCHAR2(32767);
v_where_clause2 VARCHAR2(32767);
v_table1 VARCHAR2(32767);
v_table2 VARCHAR2(32767);
v_sql VARCHAR2(32767);
FUNCTION get_cols
(
a_table VARCHAR2
, a_table_other VARCHAR2
, a_owner VARCHAR2
, a_owner_other VARCHAR2
, a_round NUMBER
, a_cols VARCHAR2
, a_group NUMBER DEFAULT 0
)
RETURN VARCHAR2
IS
v_select_list VARCHAR2(32767);
vComma VARCHAR2(32767);
BEGIN
FOR r IN
(
SELECT
cols
FROM
(
SELECT
(DECODE(a_group, 0, DECODE(NVL2(a_round, data_type, NULL), 'NUMBER', 'ROUND('||column_name||', '||a_round||')')||' '||column_name, column_name)) cols
FROM
all_tab_columns
WHERE
owner = UPPER(TRIM(a_owner))
AND table_name = UPPER(TRIM(a_table))
AND UPPER(TRIM(NVL(a_cols, '#')))
NOT LIKE '%#'||column_name||'#%'
AND (UPPER(TRIM(p_column)) NOT LIKE '%'||column_name||'%' OR p_column IS NULL)
AND column_name IN
(
SELECT t.column_name FROM all_tab_columns t WHERE t.owner = UPPER(TRIM(a_owner_other)) and t.table_name = UPPER(TRIM(a_table_other))
)
ORDER BY column_name
)
) LOOP
v_select_list := v_select_list || vComma || r.cols;
vComma := ',';
END LOOP;
IF p_column IS NOT NULL THEN
v_select_list := p_column||','||v_select_list;
END IF;
DBMS_OUTPUT.PUT_LINE(a_table||': '||v_select_list);
RETURN v_select_list;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'No table: '||a_table);
END get_cols;
BEGIN
--DBMS_OUTPUT.PUT_LINE('Start');
v_table1 := p_owner1||'.'||p_tab1;
v_table2 := p_owner2||'.'||p_tab2;
IF p_list1 IS NULL THEN
v_select_list1 := get_cols(p_tab1, p_tab2, p_owner1, p_owner2, p_round, p_cols);
v_group_list1 := get_cols(p_tab1, p_tab2, p_owner1, p_owner2, p_round, p_cols, 1);
ELSE
v_select_list1 := p_list1;
v_group_list1 := p_list1;
END IF;
IF p_list2 IS NULL THEN
v_select_list2 := /*get_cols(p_tab2, p_tab1, p_owner2, p_round, p_cols)*/v_select_list1;
ELSE
v_select_list2 := p_list2;
END IF;
IF p_where1 IS NULL THEN
v_where_clause1 := NULL;
ELSE
v_where_clause1 := ' AND ('||p_where1||')';
END IF;
IF p_where2 IS NULL THEN
v_where_clause2 := NULL;
ELSE
v_where_clause2 := ' AND ('||p_where2||')';
END IF;
BEGIN
v_sql :=
'
select *
from
(
select '||v_select_list1||',
count(src1) in_tab1,
count(src2) in_tab2
from
( select '||v_select_list1||',
1 src1,
to_number(null) src2
from '||v_table1||'
WHERE 1 = 1 '||v_where_clause1||'
union all
select '||v_select_list2||',
to_number(null) src1,
2 src2
from '||v_table2||'
WHERE 1 = 1 '||v_where_clause2||'
)
group by '||v_group_list1||'
having count(src1) <> count(src2)
order by '||v_group_list1||'
)
WHERE rownum <= '||p_count||'
'
;
--EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp';
$IF $$SHOWOUTPUT $THEN
--DBMS_OUTPUT.PUT_LINE('v_sql = '||v_sql);
$END
-- INSERT INTO tmp VALUES (v_sql);
-- COMMIT;
OPEN p_c FOR v_sql;
/*EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, length(v_sql)||' test'||SQLERRM);*/
END;
END compareTabs;
/
show err 

sample using in sqlplus here