Thursday, July 3, 2008

SQL Developer Revisited

I just found out yesterday that a new version of SQL Developer has just been released. Since I used it heavily, I thought I will share some insights on why I like the product so much.

The new version is 1.5. The following is the release history:


Initial release: SQL Developer 1.0 (March 2006)

Release SQL Developer 1.1 (Dec 2006)

Release SQL Developer 1.2 (June 2007)

Release SQL Developer 1.2.1 (Aug 2007)

Release SQL Developer 1.5 (April 2008)

The one I just download is 1.5.1. The product has undergone improvement and changes. Between 1.1 and 1.2, Oracle introduced support to MS Access, MySQL and SQLServer. I have not tried this feature since I don't use any of the other databases.

Yesterday, I tried to open the package, function and procedure using Version 1.1 on a 9.2 database and encountered a strange error of "Ora-00942 Table not found". I thought, it was a problem with my rights but turn out that it was a problem with SQL Developer - needed to use 1.2. I found my old downloaded copy and then later checked and found a new version of SQLDeveloper from Oracle's Website.

You can also find a tutorial (using SQLDeveloper 1.2) here.

Some interface changes between 1.2 and 1.5

Version 1.2

Version 1.5

Look has changed to slightly and some additional options added to the menu.

Additional objects like Queues, Java has been added.

Things I like about SQLDeveloper:

When browsing the table:
  1. It is easy to edit the data in the table, when you create a new row in the table, you can highlight multiple columns from an existing table and just paste it into the new row. When you browse the table.
  2. Just type in the condition to filter the rows.
  3. Right click on the table to analyze.
  4. View the indexes, partitions and also view the DML needed to recreate the table and all supporting objects.
When using the SQL Worksheet.
  1. Auto formatting on the sql statement. In 1.5, they have further enhanced this feature.
  2. After you query the data, you can just click on the column header to sort the output (don't have to requery); you can also drag and drop to rearrange the columns.
  3. When exporting the result to excel, the SQL Script is saved in one of the worksheet in the workbook.
  4. When you highlight the table name and press F4, you will be given the window equivalent to browsing the table.
Other useful features are:
  1. Ability to import and export data from Excel, CSV and other types of files.
  2. Easily change the structure of your table (gui interface to do that).
  3. Simplify the editing, checking and compiling of stored procedures, functions and packages.
  4. Can export the package and package body with one click to a file (for backup and storing into Source Safe or CSV.
  5. Explain plan (but only if you have the needed tables created).
  6. Analyze and verify tables with one click.
  7. Recompile invalid objects with one click.
  8. Create connection to databases without installing Oracle client (i.e. setting up SQLNet).
  9. Don't need to install, just unzip the zip file and start using. (comes with two type of packages - one with java bundled another without). As you have guessed, it a Java application.
  10. Easily configurable parameters.
  11. Provides third party add on and also allows for auto update (until a major release).
  12. Best of all it is Free!

No comments: