Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to diff table data and generate script?

PaavoJul 10 2014 — edited Jul 11 2014

How to diff data between 2 tables (in different schemas) which have identical structure with the CART functionality?

Now both database diff and CART diff functionality shows only differences between the DDL.

Also is it possible to generate script to replicate changes so that after executing the changes to destination table, it is identical with the source?

I am using

Java(TM) Platform1.7.0_55
Oracle IDE4.0.2.15.21

I am already able to use cart to generate script which drops, ddl + sqlldr table from scratch, but this is pretty heavy operation and I would like to see script which e.g. updates only and inserts so that optionally sequence is momentarily disabled (sort of identity insert on).

This type of functionality would be good because it is very time consuming to make update-sql by hand and keep that updated when the ddls change.

/rgrds Paavo

Comments

Gary Graham-Oracle

Within the mission scope of the SQL Developer tool, the "drops, ddl + sqlldr table from scratch" approach seems both expected and sufficient.  Expanding the scope to things like replication or data integration, then you would expect to use a different tool.

If your use case for the replicated table is only some kind of testing, look into SQL Developer's Unit Test feature with its Startup and TearDown processing.

Regards,
Gary

SQL Developer Team

1 person found this helpful
Paavo

Thank you very much for your fast response and couraging guidance to try Unit Test which I never tried before. SqlDeveloper and DataModeller are great service for developers!

Unit Test will certainly make testing much easier for me in the future

Of course it was a bit challenging to setup XE where I can act as sysdba and have privilegies to build Unit Test repository etc..

What I have now discovered is that the startup and tear down scripts can be stored to library which can then be copied and optionally subscribed in all unit tests.

Alone this is a very good feature and has pretty polished touch, but one idea came to my mind that could this library be linked with the cart generated export scripts?

That way the Unit Test tear down could contain also ddl-scripts which are not allowed in user plsql-scripts and those could be managed with the cart features of the tool.

The ddl drops and creates for tables, sequences, triggers are necessary because the tear down scripts omit restoring sequences as they were before the unit test took place e.g. for procedures inserting to the tables.

btw. the CART copy-functionality could have similar setup dialog like the export-functionality has, because the copy-functionality attempts to define 'storage' tablespaces and such by default which makes it fail when fiddling between databases.

rgrds Paavo


1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 8 2014
Added on Jul 10 2014
#sql-developer
2 comments
1,152 views