Skip to Main Content
  • Questions

  • Oracle procedure or fucntion to compare(minus) multiple (say 5) tables and log the number of changes for each table into a different table say temp.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: September 21, 2015 - 5:00 am UTC

Last updated: September 21, 2015 - 10:01 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

I have 5 tables at dev database and 5 tables at test database with same structure and data. I want to compare that is do a minus for all the tables using a procedure or function by passing a database name as parameter. I want the output as a table containing two columns table_name and the number of differences found for each table.


Can Anyone share the code Oracle procedure or fucntion to compare(doing a minus) multiple tables (say 5) and log the number of changes for each table into a different table say temp So that the difference can be known.

and Connor said...

You could do it in SQL or PLSQL, but why bother if someone has already done that work for you. I'd just use SQL Developer:

http://www.thatjeffsmith.com/archive/2012/09/sql-developer-database-diff-compare-objects-from-multiple-schemas/

http://www.thatjeffsmith.com/archive/2014/06/oracle-sql-developer-single-object-compare/

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rahul kumar, September 21, 2015 - 7:39 am UTC

Hi,

Thanks for the response.

But I cannot use this as I want to compare only specific columns of these tables to filter the result. I want to pass the Table names and their corresponding column_names (to be compared) via a table.

Thanks in advance.

With best regards,
Rahul
Connor McDonald
September 21, 2015 - 10:01 am UTC

Take a look at

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1004115105172

http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2151582681236

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2151582681236#15364732017705

Structure or Data?

Stew Ashton, September 21, 2015 - 11:16 am UTC

SQL Developer lets you compare table structures (DDL).

I think the questioner wants to compare the data itself.

I have blogged about Tom Kyte's method of comparing table data using GROUP BY:
https://stewashton.wordpress.com/list-of-my-posts-about-comparing-and-synchronizing-tables/
(I link back to Tom's article and to the original discussion on asktom.)

I also wrote a helper tool to generate the SQL needed for the comparison:
https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/

If the questioner had provided sample data, I would have demonstrated the technique directly here.

Best regards, Stew
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library