An Introduction to Oracle SQL Developer – Part V

Welcome to the final installment of the Oracle SQL Developer series. In this post, we will discuss how to connect Oracle’s SQL Developer with Non-Oracle databases. Oracle’s SQL Developer application is a very powerful GUI interface for working with Oracle databases or as in the case with this post, just about any brand of database. Working with SQL Developer’s GUI interface puts everything a click away.

Preparation

As we stated in the previous posts, you will need to download Oracle’s SQL Developer application. It’s free, but you will have to create an Oracle.com account to actually download the software.

At the time of this post, the current version is 2.1 and is available at

http://www.oracle.com/technology/software/products/sql/index.html.

The software is available for several types of platforms. Also, since it’s based on Java, there is a download version that includes the proper JDK. Once you uncompress the zip file, SQL Developer is ready to be used. Find the sqldeveloper.exe file and double click it to get started.

You will also need the third party database driver for your Non-Oracle database. The third party database drivers for use with SQL Developer can be found on this website:

http://www.oracle.com/technology/products/database/sql_developer/files/thirdparty.htm

Getting Started

Once you have everything installed and are ready to proceed, launch SQL Developer. On the left hand side, in the connection explorer panel, click on the green plus sign to create a new connection. This should present you with the new connection wizard. Our new connection wizard looks something like this:

image_01

As you can see, we have already clicked on the MySQL tab. SQL Developer has already provided the hostname and the port. Next you need to provide the Connection Name, Username and Password for your MySQL database.

These credentials can be from a user of the database or your database root account. Our advice is that you connect with a user’s credentials. Remember, once you have provided all of the information it’s always a good idea to click on the Test button to test your connection information. If all goes well, you should see Status: Success in the lower left area of the connection wizard.

We have connected to our MySQL database and clicked the plus sign next to our connection to reveal all of our MySQL databases. Here’s what our SQL Developer currently looks like:

image_02

Seeing Is Believing

If you have read the other posts in this series, I’m sure you realize by now that everything in SQL Developer is just a click away. So, to view the tables in a database, just click the plus sign next to the database name within the Connection explorer panel.

As you can see from the picture, we are viewing the list of procedures in our sakila database. You can also see the other database objects that are listed (functions, triggers, views and indexes).

image_03

Feel free to play around with the new connection and all of its “child” elements.

The Limits and Purpose

After reviewing the information available with your new non-Oracle database connection, you probably realized that several features are missing. In SQL Developer, Non-Oracle database connections offer you the ability to view, update, select, insert and delete data.

You can use SQL statements that are specific to the database type you are using. For example, in Oracle, if you wanted to limit the number of records returned with a statement your SQL syntax would look like this:

SELECT col FROM tbl WHERE ROWNUM<=20;

While you are connected to a MySQL database, you can use the typical MySQL syntax:

SELECT col FROM tbl LIMIT 20;

Being able to use the native syntax for the database type you are using in Oracle’s SQL Developer is a big convenience.

The main purpose of connecting to non-Oracle databases with SQL Developer is the ability to migrate data from non-Oracle databases into Oracle databases. SQL Developer makes this process very simple. Under the Tools menu there is a choice titled Quick Migration and it uses a 6 step wizard to walk you through the migration process.

image_04

The other option is to migrate the data manually. The subject of migrating data is too lengthy for this post. In fact, it could be a series of its own. The manual process involves capturing data, converting it, massaging SQL scripts to your liking, running a few scripts and finally the actual migration of data.

For a more in depth reading in regards to SQL Developer and data migration, please visit the Oracle tech net page regarding SQL Developer and migrations:

http://www.oracle.com/technology/tech/migration/workbench/files/omwb_getstarted.html

Wrap It Up

In this post we covered how to connect to non-Oracle databases using the free Oracle SQL Developer application. We also covered a few topics regarding what you can do once you are connected. This series was meant to assist those performing Database Administrator (DBA) tasks but weren’t trained as DBA’s.

In this series we covered how to perform the following with SQL Developer, connect with Oracle databases, backup your data, create functions, connect with non-Oracle databases and one or two other tricks of the trade. We hope this series was as enjoyable for you as it was for us.

Comments [1]

  1. Well done…

    I was looking around for something like this…

    Thanks alot… though if u could make a new one in more details.

    Coz his method did not work for me… I had to modify several things

    in order for it to work..

Leave a Reply

Your email address will not be published. Required fields are marked *