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.
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.
The results are delivered in a pop-up box that looks like this
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.
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.
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.
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?
32 Comments
Hey Jeff
As I can see, it is possible to generate automatically scripts using “Data type conversion”. Very cool function.
Is there also a possibility to add columns at a certain position afterwards?
So can data modeler generate an automated script for this or exist a workaround to generate it?
I have integer as datatype for many columns. Whenever I do diff with the oracle database dictionary it gives a change to number(*,0). How can avoid that DDL modification ? Is it possible to configure datamodeler so it “knows” that oracle will use number(*,0) instead of integer?
Thank you,
the database supports the ansi sql type INTEGER, but it stores it as a NUMBER at the end of the day. In your data model, i’d use number(38,0) instead of integer to make the compares easier
Thanks, but How come this could be different from the “compare data model in sqldeveloper data modeller and generate the scripts for those changes”?
https://www.thatjeffsmith.com/archive/2012/03/diffs-and-alter-scripts-via-oracle-sql-developer-data-modeler/
It’s basically the same, what are you needing to do?
Hi Jeff,
Does Oracle SQL Developer provide compare model diff /alter script for MYSQL?
no the alter scripting is only offered for Oracle models – the MySQL team has their own IDE/Modeler, it’s the MySQL Workbench
is there an additional licensing to be aware of when doing the diff and creating the DDL scripts?
Nope! You can use this feature w/o any licensing concerns.
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?
the ddl generation on compares is avail for Oracle designs only
Hi Jeff,
The movie does not exist in OLL. Has it been removed? Can you please provide another link? Thanks.
Fixed the link, thanks for the heads-up Anthony.
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
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?
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.
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.
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 – São Paulo – Brazil
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?
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…
The DELTA SQL generation on compares isn’t available for SQL Server and DB2 – only for Oracle.
I suspected as much 🙁 Thanks for verifying, that’s a real bummer, maybe it’s time to switch to oracle 🙂 lol
Is it possible to generate DDL script from changes DB when using SQL SERVER 2008 R2 ?
I am using SQLDev Modeler 3.3.0.734 and JDBC, but cant compare live DB with model.
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!
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.
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
Are you an Oracle employee? If so, just email me.
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.
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
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 on putting them in the version history
Seems like a natural workflow to me, we’ll see how it goes Dominic!