Friday, October 14, 2011

Schema Diff - Part 2

After showing the new Schema Diff in SQL Developer 3.1 @ddelmoli asked about separate files per object diff instead of the monolith file of all changes.  In the next EA when it comes out, you'll get prompted for what kind of file(s) to make if any at all.  There's several new options

1. Single File - what's there now, 1 file with everything

Along with the 1 file per object is a generated file which runs all the files.
2. Separate Files - 1 file per object

3. Type Files - 1 file per type i.e. tables.sql,views.sql,...

4. Separate Directories - 1 directory for each object type with

5. Worksheet - A new worksheet is created and the sql is put in.

6. Clipboard - As it suggests, straight into the clipboard for pasting into anything else.

Please check out the exchange and log any requests you may have.  We do listen !

Tuesday, October 11, 2011

SQL Developer 3.1 EA1 - Drill Down Report

I got a question on twitter from @goryunov about how to do drill down reports.  I plan on doing a few posts on reports since so much of the dialogs have changed.  That make this the first of a few blogs on reports.

Step 1.  Right click on the User Defined Reports and choose New Report

Step 2. Create a "Departments" report by naming it and entering the SQL.  This dialog has drastically changed from what was in the previous versions.  More on that later.

Step 3. Create a "Employees By Department" report.  This report will use a bind variable from the Departments report which is the DEPARTMENT_ID column from the query.

Step 4. Now edit the Departments report and choose the Drill Down item in the left options.  Click "Add Report"  This makes a new row in the table.  The Name part is whatever name you like.  The second will be a drop list of all reports in the tool.  In this case, I scrolled down and choose my "Employees By Departments" report which is using the bind variable.

Step 5. That's all there is to it. Now when I run the Departments report there's a new menu option for all drillable reports defined in Step 4.

Step 6.  When I choose that menu option, the values require are passed along.

Monday, October 10, 2011

SQL Developer 3.1 EA1 - Schema Diff

It was great showing everyone last week at OOW what was coming.  The only thing better is giving people the software to start using.  Now that it's out, I'll do some blogging to get some of the new areas pointed out.

One of the major areas of work in SQL Developer 3.1 is the diff generation between 2 schemas.  This is completely rewritten from the ground up.  That's interesting but maybe more interesting to you is that it no longer requires the change management pack.  That means you are free to use this feature now without any worries of what is licensed and on which databases.

It's still in the normal place in the Tools menu.  The first screen now gives you more control over what you are going to want to consider is a difference.  For example, matching constraints based on the name or the definition.  If I have a check contraint in two databases both doing the same things but have different names, it will not be flagged if I choose contrants by definition.

At the end of the wizard, there is a new tree representation of the differences between the schemas.  The tree shows everything possible.  Unchecking the "Show Equal Objects" will restrict to just the ones with changes.  Then in the lower portion of the screen, you get the standard diff window that highlights the changes in the objects.

Clicking the script tab you'll see the alter statement.

The finally clicking the SQL icon in the toolbar will generate the script to sync the two connections.