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.
-
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs. – APC Nov 21 '18 at 12:55
-
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you. – Success Shrestha Nov 21 '18 at 15:21
-
1If you have found a solution then please post it as an answer, It may help other Seekers in the future. – APC Nov 21 '18 at 16:33
3 Answers
=> 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;
To Compare data :
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;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.

- 169
- 7
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.

- 114,394
- 18
- 182
- 210

- 511
- 2
- 11
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.

- 433
- 1
- 4
- 19