Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
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 22.214.171.124, IDE Version: 126.96.36.199.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
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].
eBook Price: £14.99
Book Price: £24.99
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.
eBook Price: £14.99
Book Price: £24.99
About the Author :
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.
Books From Packt