Fine Tuning SQL Developer

Someone recently told me they didn't know you could turn off extensions you don't use in sqldev so I thought a quick blog may be in order.

Since SQL Developer and jDeveloper are based on the concept of extensions, they can do things like check for updates or install new extensions very easily.  The other side of that is that you can disable the extensions you don't use.  A couple example of things we include by default but you may not use are the modeler, migrations, and times ten support.  All you have to do is goto Tools->Preferences in the main menu and then uncheck the ones you don't want.  The result is some menus don't get installed, some code is now not running at startup and you have a slimmer IDE since you turned off the parts you don't use anyway.

Apex Listener EA and more

In case anyone missed it, the new APEX Listener 1.1 EA is out.  The big new feature in there is the Resource Templates.  If you are interested in this feature and have not found it yet, you should really start following Colm's blog.  You can learn about it from Colm: here and my post last week: here.  It will let you get data out via REST with less development than ever before.  The Developer's Guide goes into all the details of this new set of features.

Another thing in this EA is that the APEX Export and Splitter have been combined in the apex.jar which is included.  Once the apex.war is deployed the apex.jar is expanded out however if you want to use just this issue this to pull it out.  I'll clean this step up in upcoming releases but for now here's how to test it out.

jar xvf  apex.war WEB-INF/lib/apex.jar

Then you just issue the commands as normal.  The new part is that they are joined and in one command line you can export and split.

$> java -cp apex.jar:/path/to/ojdbc6.jar  oracle.dbtools.apex.utilities.APEXExport

Usage APEXExport -db  -user  -password  -applicationid  -workspaceid -instance -skipExportDate -expSavedReports -debug  
 -db               : Database connect url in JDBC format 
-user             : Database username
-password         : Database password
-useAliasFileName : use alias.sql instead of f12345.sql
-application      : ID or name for application to be exported
-workspace        : Workspace ID or Name for which all applications to be exported
-outDir           : Directory to export to        
-instance         : Export all applications
-skipExportDate   : Exclude export date from application export files
-expSavedReports  : Export all user saved interactive reports
-split            : Split the export files
-flat             : Split into a Flat file structure
-update           : < create update file >
-nochecksum       : < don't check for changes >

Application Example: 
   APEXExport -db candy.us.oracle.com:1521:ORCL -user scott -password tiger -application 31500 
Workspace  Example: 
   APEXExport -db candy.us.oracle.com:1521:ORCL -user scott -password tiger -workspace 9999 
Instance Example:
   APEXExport -db candy.us.oracle.com:1521:ORCL -user flows_020200 -password apex -instance 

The other things new here are from a request from John Scott. There's a flag to name the files based on the alias instead of just the id. The application name or workspace name can be passed instead of the id.

The splitter has a few options:

-split
This is the main switch to turn on the splitting

-flat
Instead of directories it will use a single directory to put all the split up files.

-update
This will create an update.sql based on what changed from the last split that was done. It uses a checksum on the new and old to determine if there was a change in that section

-nochecksum
If you split f100.sql, you get all the split files. Normally, it will checksum that if page1.sql didn't change it will not update the timestamp on the file. If you turn checksum off it will always update the timestamp. The checksumming to update the timestamp is nice for things like checking into version control. Only the page/item/tab/.. sql scripts that changed will changed so you can see quickly what has and has not changed.

Four Stages of Learning

I went to a great set of classes this weekend and wanted to share one of the things I learned.  The classes had nothing at all to do with any form of technology, it was to get a hockey coaching license.  It was a very fun class since we got to meet Jamie Baker and Jay Woodcroft .  One slide was on the four stages of competence.  I don't think I've seen this before but it's on wikipedia and something that has been around for quite some time.   It breaks down the process into combination of 2 factors, Competence and Consciousness.

Taking those 2 factors there are 4 stages of learning.  Here's the process from the highest level to the lowest.

1) Competence and Unconscious
 - This is where you have become competent in the area you are learning to the level that you are unconsciously react to accomplish something.
 - How times do you do things on autopilot?  Your training has progress to the highest level and due to the number of repetitions it's been done it's just an automatic response now.

2) Competence and Conscious
 - You have the competence however are conscious of what you are doing.
 - How many times do you have to stop and think about what to do?  Then have a little thought it's easy using the skills you already have.

3) Incompetence and Conscious
 - You don't have the skills yet but are aware of what needed to accomplish the need.
 - How many times do you know how to get where you need to be yet have to breakout a manual/book/something to find the skills needed to get there?

4) Incompetence and Unconscious
 - You are just starting out so there's not a high skill level nor the awareness to know what needs to be done.
 - How often have you started a new project and not known where to go or how to start it?

SQL Developer 3.0 and Encrypted PDF

Another new thing in the EA is unloading the grid of data to a pdf file.  Plain PDF is one thing but what this will show is how to encrypt it.  While in the database the data is normally quite secure and you have to have proper permissions and knowledge on how to get to the data.  However, when it's loaded into excel or PDF or ... the security is far harder to enforce.  Here's one way to address it with encrypted PDFs.


First go into the Tools-> Preferences and setup a default title/author/subject.  These are changeable later.

Now simply go down to the security section and check a couple boxes and enter a password.  I'm just showing the simplest case but you can see from the checkboxes that you can give more granular permissions also.

The easiest part is next , write your sql statement to get the data you need.  Once you have the data in a grid, it's as easy as right click -> Unload.

Once you have the dialog to unload, Choose PDF in the drop list of formats.  This will bring a section to change the defaults for report title,subject, and keywords.

Lastly, open the PDF and you'll get prompted for a password.  This screenshot is using Apple's Preview which just the default viewer for Apple and all PDF viewer will function the same.

SQL Developer 3.0 EA

Monday we released the SQL Developer Datamodeler 3.0 EA .  Today the SQL Developer 3.0 EA is out.  There's lots of new things in this version. Here's a quick index to some of the larger features that have been added. There are also too many smaller things to list which include things like a Table API generator.  Some of the features have online demos already more are on the way.  I will continue to blog on some of the demos of the others.

1. DBMS Scheduler -- Online Demo here
2. DBA Functionality  -- Online Demo here
3. Files and Version Control
4. Migrations
5. PDF
6. PL/SQL Support
7. Query Builder -- Online Demo here
8. Schema Browser
9. SQL Plus Commands
10. Spatial
11. Tuning
12. Unit Testing
13. Unloading and Uploading  -- Data Export / Import was renamed to be more accurately load/unload

SQLDeveloper Datamodeler 3.0 EA

In case anyone missed it this morning, there's an Early Adopter build of the Modeler released today.  You can go and download it from the normal place on Oracle.com http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html


There's been some great new features added to this release and Sue has some webcasts which range from  an overview to more detailed webcasts on version control and design rules. These webcasts are all under 15 minutes and are a great way to see what's new int he product.

Also, Here is the quick list of new features that have been added.  

Browser Navigator	The browser navigator supports the ability to have multiple open designs. Existing physical models automatically appear in the browser.
Custom Design Rules	Create user-defined Design Rules. Group collections of design rules in Design Rule sets. Build libraries of Design Rules. Create used defined transformations.
Diagrams	Settings are available at the diagram level - Show labels (for relationships) - Add diagram legend - Straighten all lines - Show grid - Resize objects to visible size. (This is useful if you change the View Details settings)
Import	Importing CA Erwin Data Modeler files has been extended to include the import of Erwin 7.x files. Import from Erwin supports the import of Functions, Packages and Stored Procedures to the Physical Model. Data Dictionary Import supports the import of Functions and Packages. Importing for Oracle Designer: You can select the checked in branch. Incremental import of used domains in Designer
Physical Model	Support for the import and DDL generation of packages and functions. Changed "snapshot" support to "materialized view" support. Full support for Oracle 11g partitioning (table and materialized view).
Reporting.	Reporting provided through either: - Reporting Repository. Users export the design to the reporting repository and use SQL Developer to run reports. - Local Reports: Run reports from the tool itself. Reports are word xml documents and as such can be opened in MS Word. These reports include entities tables, domains and glossary.
Reporting Repository	Support extended to include data types models.
Subversion Integration	Integrated version control using Subversion for complete design. Allows you to compare and merge objects. Designs added to versioned folders are placed under version control. Users can see pending incocoming and outgoing changes. Data Modeler recognizes versioned designs. Changes made to a versioned design and saved, are revealed in the pending changes dialog. Collaborative access and support is provided through tight integration with Subversion.

3.0 Modeler Sneak Peak - Transformations

The newly free modeler has some great new features coming as well.  This is an example of the new scriptable transformations.  This is a fairly simple example but I think it shows of how powerful this form of transforming the models can be.

First, we start by importing the HR schema to have something to work with.  

 To invoke a Transformation or create a new one, simply choose under Design Rules.

I'm going to use the first one in the list which changes all table names to lowercase.  The part of this I like is that it's using Java's scripting engine support which allows you to plugin any engine you like programming in.  The default is javascript which is what this example is.

The end result is as expected, the table names are lower case now.

3.0 Sneak Peek #2 - Schema Browser

I'm going to try and do some short blog entries that are showcasing some of the up and coming features of 3.0.  We showed lots of people at Open World but for those of you that couldn't be there, stay tuned and I'll put a few things out.


Another new thing being added is an alternative view of the objects in the schema. Simply right click on the connections and choose Schema Browser.

Then you are presented with a drop list of the schemas and object type.

Then as with the tree based navigator, there is filtering.  This view has 2 types of filtering.  The first is when you click the down arrow as shown in the screen shot.  This takes you to the normal filter dialog and filters by adding a predicate to the sql retrieving a list of objects.  The second is the binoculars at the bottom of the list.  This does a very fast/simple client filter of the objects listed. As seen below with "jo" in the search.

REST/JSON access to your data in 2 clicks

There never seems to be enough time at oow to show all the new things coming.  Some new things in the listener is exactly the case this year.  A few people found me and asked some questions but we didn't have a good setup for demoing it there.  Here's one of the new things coming....


Here's how to get REST / JSON access to your data in 2 mouse clicks.

1. Click Add Resource Template

2. Fill in the fields. Second Click Save.
    
   You can tell that the {id} is the bind that is passed into the query.  Where it's referenced with the same syntax to use as a bind.

Done.  Now here's the JSON output from all that hard work :)

Obviously this is a very simple example and there are more samples and documentation on the possibilities coming.