An Introduction to Oracle SQL Developer – Part I

Although you might not be a Database Administrator (DBA), sometimes in the IT industry we have to perform tasks that really aren’t part of our training or job description. If you ever end up working with Oracle tables and would like to use a GUI to make your life easier, this series is for you.

Rather than double your daily dose of Doctor prescribed “IT Tic-Tacs”, just relax and read the rest of this post regarding a very simple, yet very powerful GUI for working with Oracle tables. We will start this series with the simple things and progress to the advanced topics that will really WOW your colleagues.

Preparation

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’s 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.

Creating Connections

As you can see, upon launch you are presented with a Tip of the Day. You can choose to not have those displayed upon launch. Since we are working with a database GUI interface, we need to actually connect to a database. You will need to click on the green plus sign to add a new connection. The red arrow indicates the location of the green plus sign:

image_02

We are going to connect to our Oracle 10g Express Edition database. You should use your own appropriate information for the Oracle install that you have. Our connection information looks like this:

image_03

It’s advised that you use the Test button to make sure you can connect with your credentials and settings. If you are connecting to your corporate Oracle database server, you will probably use a service name instead of a SID.

Once you are connected you will see a list of your connections in the Connection Explorer along the left hand side. On the right, you will see a tab with the name of the connection.  This tab is the SQL Worksheet.

If you have multiple connections open, you will have multiple SQL Worksheets each with a tab denoting the connection name.  Right now we only have one connection, so our SQL Developer looks like this:

image_04

Creating A New Table

Having a database with no tables is like having a sail boat with no sails.  One of the joys of using a database management GUI is how easy it is to perform tasks. Most tasks are point and click operations.

To create a table, you simply click the plus sign to the left of the connection name in the Connection Explorer panel and you should see a drop down menu like the one we have here:

image_05

You want to right click on the Tables folder right below the connection name located in the Connections Explorer panel. You should see a drop down menu where you can choose New Table. Our looks like this:

image_06

Once you have clicked on New Table you will see the Create Table window. We are creating a simple 3 column table with id, first name and last name for columns. Our Create Table window looks like this right before we click the OK button:

image_07

At this point, under the Tables folder in the Connection Explorer, you should see the new table that we just created. Clicking the plus sign to the left of the new table listed in the Connection Explorer will display a tab next to the SQL Worksheet that lists a lot of details about the table. Our table tab looks like this:

image_08

As you can see, there is a lot of information presented along with a lot of options. Let’s add a row to our table. Click the Data tab under the table name tab. You will be presented with something similar to this:

image_09

A great thing about SQL Developer is that you can type right in the empty fields. We have supplied the following information as the first row of our table:

image_10

At this point, to save our new row, we must click on the commit button. The Commit button is the database symbol with a green checkmark in front of it. Once you click the commit button, SQL Developer will display information regarding the operation you are committing.

It will display Commit Success or it will display and error number with an error description. For those of you that want to make sure everything is working, feel free to use the old school method of an SQL query.

Click on the SQL Worksheet tab, for us that’s DBTEST and type in a generic SELECT * query and click the green arrow that points to the right (the Run Statement button). You should see the row we just created in a Query Result window/tab that appears below the SQL Worksheet. Ours looks like this:

image_11

If you want to add more rows to your table, click the Customers tab that should still be visible, then click on the data tab and finally, click on the icon of a piece of paper with a green plus sign on top of it. This will provide an additional empty row that you can propagate with the new information. Again, don’t forget to click the Commit button.

Wrap Up

This was the first post in a several post series regarding Oracle’s SQL Developer application. SQL Developer is free and it allows non-DBA’s access to databases via a GUI.

In this post we covered the basics of downloading and installing SQL Developer. We also covered how to create a table and how to add rows to a table.

Stay tuned for the next post in this series where we will cover how to get around one of Oracle’s policies about column attributes that always causes an argument between Oracle DBA’s and non-Oracle DBA’s. Good times!

Comments [3]

  1. [...] Welcome to the next installment of this Oracle SQL Developer series. You can check out Part I and Part II here. In this post, we will cover the creation of a database function using Oracle’s [...]

  2. [...] Welcome back to our series introducing you to Oracle’s SQL Developer application. Check out Part I, Part II and Part III. In this post we are going to cover how to backup your data using SQL [...]

  3. sethu says:

    sir/madam i have a problem in creating a new connection….. the error shown is "status:failure-test failure: IO Error: The Network could not establish the connection". what am i suppose to do for this….hoping for a solution soon… pls…

Leave a Reply