DIFFs and Alter Scripts via Oracle SQL Developer Data Modeler

thatjeffsmith SQL Developer 27 Comments

Tell Others About This Story:

This post is not a ‘How to Version Your Models’ tutorial. We have that in movie form in the Oracle Learning Library (OLL.) There’s a series of several 2-3 minute videos that step you through how to add a model to source control via Subversion, how to make changes, commit said changes, and how someone else can do a Check Out to see and continue your work.

I want to concentrate on a small but critical component of this process. Once you’ve made changes to a model, you’re going to want more information about said changes and you’re going to want to see what the impact on the database will be if you ever decide to synchronize your model back to the live system.

Show me what’s different, and show me the code to make those changes happen in the database!

SQL Developer Data Modeler (SQLDev) has the subversion client interface built into the tool. You can view your trunk and branches plus what’s versioned in each of those locations. Once you open a model, you can see what incoming and outgoing changes are in play. Let’s take a second to investigate that.

Pending Changes – Incoming and Outgoing

In order to make changes to a versioned model, you’ll need to ensure first that:

  • You have a Subversion system defined with proper access
  • You have checked out a model
  • You have made changes to the model
  • You have saved the changes to your local copy

Once you’ve made changes to the local copy, these will appear as ‘Outgoing Changes.’

You’ll want to make sure the ‘Pending Changes’ panel is opened via the Versioning – Pending Changes menu item.

SQLDev will tell you what you're sending out and what's outstanding from the server

The integrated Subversion interface is showing me what will be going out if I choose to ‘Commit’ the changes. Since the models are composed of a collection of individual XML files representing each data modelling element, I can commit my changes at a per object level. Or I can commit all the changes at the model level. If someone else was make changes to the model in the Subversion repository, I would see those changes in the ‘Incoming Changes’ panel.

If I mouse-right-click on a pending change, I can get the details. You have the option of comparing the model to the latest local copy or to what’s on the server.

Compare with

I want to see what changes are about to be committed to the Subversion repository when I do a ‘commit.’ So I can choose the table I want to investigate.

How does the latest file differ from what had been checked out from Subversion?

The results are delivered in a pop-up box that looks like this

I've changed the column from a CLOB to a VARCHAR(4000)

But Wait Jeff, I want an ALTER DDL Script!

In order to generate ALTER scripts – that is, to get a DDL script that will ALTER the objects in the database to reflect what is in the current version of the model, you need to do a model ‘Compare.’ SQLDev makes this very easy when it comes to treating the live database as a model.

The main toolbar has two buttons which allow you to do this compare on the fly.

The most popular buttons in the tool?

Synching with the data dictionary will allow me to generate an ALTER script that updates the database to reflect what’s in the model.

The resulting dialog shows me all of the affected model and database objects with the details for each listed side-by-side.

Compare Model Results

Now instead of showing you the same old ‘this column is a apple, and this column is an orange’ I navigated to the ‘Data Type Conversion’ panel. Here I can determine how the ALTER script will be generated. Maybe I am OK with a simple

 ALTER TABLE SH.PROMOTIONS 
    MODIFY ( PROMO_SUBCATEGORY VARCHAR2 (30 BYTE) ) 
;

But maybe this will result in data loss! Instead I want a temp copy of the table created and then I want the data moved over, etc. By checking the ‘Recreate table’ option, I am telling SQLDev to use this strategy. Here’s what the resulting ALTER script looks like instead:

ALTER TABLE SH.PRODUCTS RENAME TO bcp_PRODUCTS 
;
CREATE TABLE SH.PRODUCTS 
    ( 
     PROD_ID NUMBER (6)  NOT NULL , 
     PROD_NAME VARCHAR2 (50 BYTE)  NOT NULL ,
...
INSERT INTO SH.PRODUCTS 
    (PROD_ID , PROD_NAME , PROD_DESC , PROD_SUBCATEGORY , PROD_SUBCATEGORY_ID , PROD_SUBCATEGORY_DESC ,
 PROD_CATEGORY , PROD_CATEGORY_ID , PROD_CATEGORY_DESC , PROD_WEIGHT_CLASS , PROD_UNIT_OF_MEASURE ,
 PROD_PACK_SIZE , SUPPLIER_ID , PROD_STATUS , PROD_LIST_PRICE , PROD_MIN_PRICE , PROD_TOTAL ,
 PROD_TOTAL_ID , PROD_SRC_ID , PROD_EFF_FROM , PROD_EFF_TO , PROD_VALID )
SELECT 
    PROD_ID , PROD_NAME , PROD_DESC , PROD_SUBCATEGORY , PROD_SUBCATEGORY_ID , PROD_SUBCATEGORY_DESC ,
 PROD_CATEGORY , PROD_CATEGORY_ID , PROD_CATEGORY_DESC , PROD_WEIGHT_CLASS , PROD_UNIT_OF_MEASURE ,
 PROD_PACK_SIZE , SUPPLIER_ID , PROD_STATUS , PROD_LIST_PRICE , PROD_MIN_PRICE , PROD_TOTAL ,
 PROD_TOTAL_ID , PROD_SRC_ID , PROD_EFF_FROM , PROD_EFF_TO , PROD_VALID 
FROM 
    bcp_PRODUCTS 
;
...

Regardless of the method you choose, I highly recommend you review ALL outstanding changes and their related ALTER scripts. If you want to run the script against the database, you’ll need to save the file and execute it outside the SQL Developer Data Modeler.

But Wait Jeff, I want an ALTER DDL script for 2 different versions of the model, not between the current and the database!

OK, now you’re just getting picky!

If I want to generate an ALTER between version 3 and version 4 of my model, I need to do a CHECK OUT of the older version, or revision.

On the check out dialog, be sure to toggle on the ‘use revision’ control, then tell us what revision you want to check out.

Tells us what revision to check out

Then use Tools – Compare/Merge Models, then point to the model file you just had copied down locally when you did the ‘Check Out with revision.’

The compare results navigation and the DDL scripting works just the same.

An idea going forward is to make this more convenient by making these DDL scripts available directly from the ‘Version History’ dialog instead of forcing you to do a check out and launching the compare dialog. Does this sound like a good idea?

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 27

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  1. I am trying to compare and preview DLL between two data models but the button is disabled when I choose any of the DB2 options. Do I have to setup something for DB2 to work?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  2. Hi Jeff,

    I am having trouble generating an ALTER script from a design that was created by importing a DDL file, not reverse-engineering data dictionary. When I try to sync it with an existing schema that was previously created with the same DDL file, I get a blank screen in DDL preview.

    https://community.oracle.com/thread/3600074

    However, reverse-engineering data dictionary, altering it and then syncing it back works as expected.

    Is this a bug?

    Thank you,
    Kirill

  3. Jeff,

    furst of all.. thanks for taking out time and answering all our queries and explaining how the data model work.. I am having an issue with the synchronize option.
    There are cases where the changes are made to productions and not reflected in datamodel. For that you had said that we can use synchronize option and the tool will be report the changes. we can merge those changes. Issue I am facing …
    For eg, in production there are 10 tables and in my datamodels there are only 8 tables.. Synchronize model from datamodel is giving me only changes for the 8 tables.. 2 tables which are not in my datamodels is not reflected in the report.

    What could be the reason?

    1. thatjeffsmith Post
      Author

      When you first imported those 8 tables – were the other 2 tables there too, and you just chose NOT to import them? Or were they added to the database AFTER you did the inital import from data dictionary?

      Nevermind, I see what you’re talking about now – answer to follow ASAP.

    2. thatjeffsmith Post
      Author

      The tables in the design define what will be compared. When you have tables added in the database, you need to run the Reverse Engineer/Import again. Your two new tables should be selected by default, as we’ll recognize the other 8 having already been imported from the previous session.

  4. Hi Jeff, I am following your blog to improve my knowledge of Sqldev Modeler. Now I am trying synchronize model with DB (Sql Server – JDBC).
    I to import from relational designer DB, make the changes but when I try to generate script changes , the button DDL PREVIEW is shadow, like without connection with DB.
    Would you do some tips ?
    Thank you.
    Ezequiel Marin РṢo Paulo РBrazil

    1. thatjeffsmith Post
      Author

      If you bring up the properties for your model, right click on the Relational in the Browser tree, check the Summary page. Does it list your ‘Source DB Connection’ accurately? If not, the ‘Synchronize Data Dictionary’ dialogs won’t be available b/c it doesn’t know what database to compare to.

      Is that what you’re saying, that the Sync wizards are greyed out? Or are you able to get to the Sync dialog and only that the DDL preview is not available?

      Also, what version of SQL Server and what version of Oracle SQL Developer Data Modeler are you using?

      1. I’m trying to use this for SQL Server 2008 R2. I can import the data dictionary no problem. I can make changes and it will generate the DDL for creating everything from scratch no issues. But it will NOT create change DDL. The connection string on the Summary page is perfect, it will open the compare dialog and show me all my changes, but the preview DDL button is greyed out no matter what I do.

        Is this just not compatiable with SQL Server? I’m using Oracle SQL Developer Modeler 4.0.3.853 and SQL Server 2008 R2. If this one feature will work, this looks to be an amazing tool, if not, it is pretty much useless…

        1. thatjeffsmith Post
          Author
          1. I suspected as much 🙁 Thanks for verifying, that’s a real bummer, maybe it’s time to switch to oracle 🙂 lol

  5. JeffS Post
    Author

    Alexandre – Your team will probably be building and managing their PL/SQL code directly in the IDE (SQL Developer), right?

    Since there is no concept of an ALTER SCRIPT for PL/SQL objects, I recommend you setup a separate Subversion project for your application code, then use SQL Developer to manage your code changes and versioning.

    Sorry if I misunderstand your question or intent!

    1. Thanks Jeff, you are right. There are two different “diff concepts”: a classical one (for source code, e.g. PL/SQL) and the one based on ALTER scripts. It makes sense to handle them separately. But may be one day SQL Developer Data Modeler can elegantly combine both 😉
      Cheers,
      Alexandre.

    2. HI Jeff,
      Can you think of a any decent solution (when using Data Modeler and SQL Developer as tools) for version controlling the schema and procedural code by using the same repository? I.e. being able to tie together versions of the model and the PL/SQL code using revision numbers without manually keeping track of the revision numbers that work well together…
      Thanks,
      Bert

      1. thatjeffsmith Post
        Author
  6. Hi Jeff,
    I’m currently planning to put our team’s database development under version control. Your article is very enlightening in this sense! Still one question.
    The model comparison works great for relational models, but not for physical ones. What should I do with the changes in stored procedures, triggers, sequences? I could export them in a DDL file and put it under version control in addition to the model files. Is this the best way? Or may be I miss a more elegant solution?
    Thanks,
    Alexandre.

  7. Jeff,

    When I run the Diff Report I can see the objects that are different. I can click each individiual object and get the DDL for it. How do I get the DDL for all the object so I can pass them along to the developers? I don’t want to copy/paste 150 times if I can avoid it.

    Great articles!
    Thanks,
    Tony

    1. JeffS Post
      Author

      The DDL Preview button in the Compare Models dialog should generate the entire DDL script for all selected objects above. Are you sure those other objects are toggled? If I just check 1 table, it will only generate the ALTER script required to ‘merge’ that table.

    1. JeffS Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *