Installation and a review of some new features
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 is beefed up as well as shown.
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.
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 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.
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].
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.
Here is an example of a SQL Server 2005 TSQL statement that has a syntax problem during translation.