Oracle SQL Developer Tool is a stand alone program that connects to Oracle as well as third-party databases which can be used to perform a variety of tasks from running simple queries to migration of databases from third party vendor products to Oracle. Please review the following articles on the earlier versions 1.1 and 1.2: MS Access Queries with Oracle SQL Developer 1.2 Tool and Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2.

In the present article by Dr. Jayaram Krishnaswamy the latest version of this tool [Oracle SQL Developer 1.5.0.53, IDE Version: 11.1.1.0.22.49. 33] will be described and some details of how to use this tool with SQL Server 2005 will be discussed. While the default installation is ready to connect to Oracle and Access databases the other third-party products such as SQL Server 2005, MySQL and Sybase require additional JDBC drivers. This article describes how you may connect to SQL Server 2005 and SQLExpress servers using the JDBC drivers. The Scratch Editor interface which accepts an SQL Statement from a 3rd party and translates into PL/SQL code is described. This feature was also available in the earlier version.

Installation and a review of some new features

Installation

The program [EA2 download -Early Adapter] can be downloaded from the following URL. In the present case the Windows option that comes with JDK1.5.0_06 bundled was used. The downloaded ZIP file, sqldeveloper-5073 (100MB) can be unzipped to any suitable location and from within the sqldeveloper folder you can immediately start using the program. The program can be started by double clicking the executable which has an unambiguous fat green arrow.

Review of features

Adjustable Look and Feel

The look and feel is adjustable. You can choose between 'Windows' and 'Oracle'. After choosing 'oracle' you can choose a variety of themes. The one shown is for 'Desert Yellow'.

The View Menu

The View menu is better organized as shown compared to the previous version.

Tools Menu

Tools menu is beefed up as well as shown.

External Tools

The External Tools sub menu item can find existing tools (browsers, notepad, mdb files) and also using a 4 step wizard allows you to create tools, provided you know the details for accessing them.

Wizards

Diff Wizard allows comparing objects of same type between schema of source and destination as well update the destination based on source. Similarly the Copy Wizard allows you to copy objects from one database schema to another.

Versioning Support

Versioning support is another new feature in this version.SQL Developer provides integrated support for CVS [concurrent versions system] and Subversion in its source control. CVS allows repository creation on the local PC or, on a remote machine. Source files are held in folder modules. In the case of Subversion the access to the repository is by means of a connection and this is where the master copies are held, files are checked out to a local working folder.

Run menu item

The Run menu item also contains the debugging options as shown. In the previous version Run and Debug were two menus.

Migration Menu

The Microsoft Access Exporter can export from 97,2000,2002, and 2003 like in the previous version (1.2) and seems to be essentially the same as the previous version. This version can now create off line migration scripts to ASE 15 and Sybase 12 in addition to several versions of SQL Server 7,2000,2005 and MySQL (3.23,4,5)

Connecting to SQL 2005 databases

As described in the previous referenced articles at the beginning  you can establish a connection to the server by clicking on the Connection node (positive green sign) in the first figure. This opens New / Select Database Connection window where you will see only Oracle and Access. This is because, at this point no JDBC drivers have been specified for connecting to the other three servers, SQL Server, MySQL, and Sybase.

There are two ways you can register JDBC drivers for these databases. For SQL Servers you require the jtds.jar file from the SourceForge.com web site. In the first method you need to go through Tools|Preferences|Database|Third party JDBC Drivers| to find the path to the file as shown in the next figure and use the browse key to locate the driver and add it. The driver file should be in the correct path for the application to find.

In the other method that is used here, which in the opinion of the author is simpler, is to go through Help|Check Updates... This brings up the Step 1 of wizard as shown. Read the instructions in this window.

Now click Next. This takes you to the next window as shown.

The needed item is already checked. Click Next. The window that comes up next shows compatible drivers for the databases.

Choose items needed by placing check marks. In this tutorial both the SQL Server and MySQL drivers were chosen. Click Next.

In the window that shows up agree to the licensing[GNU Public] terms after reading the terms. Click on Next in the final window of Step 4.

When Step 5 "Download" windows opens the login window also opens. As these drivers are downloaded from the Oracle site, you will have to insert your Oracle login information. Step 5 screen shot is not shown.

You will be adding both the JDBC drivers on the final step. Click Finish. In order to install the updates you chose, the SQL Developer 1.5 needs to restart, and it restarts when you click on Yes in the Confirm Exit window.

Do you want to Migrate User Settings?  window shows up again. For this article it is a No again. The Oracle SQL Developer window gets displayed. Now you open the screen.  You will see all the five database tabs in the New / Select Database Connection with default connection to the Oracle 10G XE on the local machine[Screen shot not shown].

An ideal book for trainers who may want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.  Environment set up for Visual Studio 2005 with respect to SSIS and multiple tasking Connect to Microsoft Access, Text Files, Excel Spread Sheets Transform data from a source going to a destination Use the scripting support that the IDE provides and event handling Learn about tasks such as web service and XML, which are new in SQL Server 2005 http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

Connecting to SQL Server 2005

The SQL Server 2005 server installed on this machine is set for SQL Server authentication that requires a login name and a password to get connected to the databases. Click the Connection tab and open the New / Select Database Connection window as shown. Insert details as shown. Connection is a name of your choice[here it 2k5]. Insert login information and then place check mark for Save Password. Change the tab to SQL Server. The Host name can remain 'localhost' and the Port is the default port of SQL Server which is 1433 [unless it has been altered in the SQL Server Configuration Manager].

Now you can click on the Retrieve Database button. If everything is correct you should see a drop-down list of databases from the SQL Server 2005. However you can only use one database for a connection.

Connecting to SQLEXPRESS database

SQLEXPRESS is a junior version of the full product with some reduced functionality. By default it has been installed for Windows authentication. A procedure similar to the above resulted in unsuccessful connection. As suggested in the forums the entries to the HOST name and Port in the New / Select Database Connection are different. The next figure shows what you need to type into these fields to get the connection to succeed. For the HOST you need to specify the machine name(name of your pc) and for the port a somewhat non-standard entry [1433/pubsx;instance=SQLEXPRESS] as shown implying that a particular database pubs is chosen. You may both Test and Connect to the database. Again you will only be connecting to the named database in the port entry.

The next figure shows the expanded connections node with two for SQL Server 2005 and one for SQLEXPRESS. After you close the Oracle SQL Developer window the authentication information needs to be entered for each connection when you connect again.

Translation Scratch Editor

You can access this from the Migration Menu command and it can be useful during migration. You can translate a 3rd party SQL statement into PL/SQL using this item. In the following example the following SQL query against the pubs database in the Express connection made earlier will be shown.

Click on Migration|Translation Scratch Editor which opens up the window with two panels. One for the 3rd party SQL statement and the second which will display the generated PL/SQL as shown. Each of them can be executed in the context of the connection which needs to be specified while executing the code. The result of running the query will appear in the bottom panels

Now insert the statement (using the same statement in the MS SQL Server Management Studio) in the 3rd party's window and click on the green arrow in the same pane to execute. You may have to pick the connection [in this case EXPRESS]. This will result in running the sql statement and the result gets displayed a shown.

Now you can translate this 3rd party statement to PL/SQL by hitting the Translate button[[> >] between the panes. This will display the PL/SQL code in its window since the 10G XE's database schema does not have a pubs database, trying to execute this in the context of 10G will result in an Oracle error as shown.

Another example

Here is an example of a SQL Server 2005 TSQL statement that has a syntax problem during translation.

Summary

This article described some of the new features of Oracle SQL Developer Early Adapter version 1.5. This article also described the procedure to connect to SQL Server 2005 as well as SQLExpress. An example for using the Translation Scratch Editor was described using TSQL from SQL Server 2005.

An ideal book for trainers who may want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.  Environment set up for Visual Studio 2005 with respect to SSIS and multiple tasking Connect to Microsoft Access, Text Files, Excel Spread Sheets Transform data from a source going to a destination Use the scripting support that the IDE provides and event handling Learn about tasks such as web service and XML, which are new in SQL Server 2005 http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

About the Author

Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his web site or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net