2 Replies Latest reply on Jul 11, 2014 12:59 PM by Paavo

    How to diff table data and generate script?

    Paavo
    Paavo Jul 10, 2014 2:29 PM

      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

      • 884 Views
      • Tags:
        • 1. Re: How to diff table data and generate script?
          Gary Graham-Oracle
          Gary Graham-Oracle Jul 10, 2014 3:33 PM (in response to Paavo)

          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
          • 2. Re: How to diff table data and generate script?
            Paavo
            Paavo Jul 11, 2014 12:59 PM (in response to Gary Graham-Oracle)

            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