logo for asktheoracle.net
leftimage for asktheoracle.net

Oracle SQL Developer Overview

This introduction to Oracle SQL Developer is designed to give you a feel for the product and to show you a few shortcuts to make it easier to use. First we'll have a look at what it is, then we'll see how to install it before we dive into the product itself.

What is Oracle SQL Developer?

n a nutshell Oracle SQL Developer is a Java-based, multi-platform, SQL and PL/SQL development environment with a language-sensitive editor and a tree-like, graphical interface to the database.

With it you can:
  • View, create and modify data 
  • Build queries visually using drag and drop
  • Create and manage database objects
  • Build and run SQL statements
  • Build, run and debug PL/SQL code
  • Integrate with CVS or Subversion for source control

Oracle Tips & Tricks
to SKYROCKET Your Career!

If you're not already a subscriber to our newsletter you're missing out on a myriad of tips and tricks you need to become a better, faster, smarter developer. Subscribe now and ignite your career.

Downloading and Installing SQL Developer

Now that we know what it is and on the assumption that you've decided to at least give it a go, the next step is obviously to download it and install it.

Oracle SQL Developer is available from OTN (the Oracle Technology Network), subject to you agreeing with the licensing conditions.

For the Windows version there is a choice of downloading it with or without the required JDK (Java Development Kit). If you've already installed the required JDK or want to install it separately, pick the file without the JDK. On other platforms you have no choice, the JDK has to be installed separately.

Oracle Sql Developer download page on OTN 

On Windows the installation process is just a matter of unzipping the file to the required directory and then navigating to the directory and running sqldeveloper.exe.

How To Use Oracle SQL Developer

There is a wide variety of tasks you can perform with SQL Developer and usually more than one way of doing each of them. There isn't the space to cover them all in this overview so we'll just have a look at the basics to give you an idea of what you can do.

For a fast start watch our SQL Developer video tutorial.

Creating a Database Connection

Once you've installed Oracle SQL Developer, the first thing you need to do is to define a database connection using the connections tab. If the connections tab is not showing you can display it by selecting View|Connections from the menu (or use the shortcut keys Alt-v c).

sql developer define connection

From here you can create a new connection by clicking on the big green plus sign or right click on the icon of the disk with a plug on it (the empty list of connections) and select "New Connection". This brings up a form for specifying the details of the database to which you want to connect as well as providing a label for this connection.

SQL Dev - new/select database connection

If you're likely to be connecting multiple databases it's a good idea to include the database name in the connection name.

If you chose the "Basic" connection type from the drop-down list you have to supply all the connection details, whereas if you choose "TNS" you're given a drop down list of available connections which makes things a bit easier (assuming you're using TNS). The other choices are "LDAP" or "Advanced".

Once you've created a connection, a little disk icon labeled with the connection name appears as a new node in your connections tree and the SQL window pops up. Defining the connection and connecting to the database can be done separately and any connections defined in one session are automatically remembered by future sessions - unless, of course, you delete them!

connection tree

Viewing/Modifying Table Data

We learned earlier that Oracle SQL Developer provides a graphical interface to the database and this provides you with one way of viewing and modifying data.

When you expand your connection node (by clicking on the plus sign next to it), you're presented with a list of all the possible types of objects that could exist in the selected schema.

schema tree

Expanding any of these branches causes the list of all of the objects of that type in your schema to be retrieved. This may take a while if you have a schema with a large number of objects.

To view and modify data in a table it's just a matter of selecting the "Tables" branch in the connection tree to load the list of tables owned by the current schema, then selecting the table of interest. This brings up the table definition in the view window.

Clicking on the "Data" tab in the view window displays the table data in a grid enabling you to add, delete and modify the data in situ. To commit changes, you simply click on the icon of the disk with the green tick or to rollback you just click on the icon of the disk with the red curved arrow.

table data

Using PL/SQL

Selecting a pl/sql procedure or function from the "Procedures" or "Functions" branch in the connections tree brings up the pl/sql view window which enables you to view the code, modify, compile and run it.

plsql edit window

When you run your code (by clicking the green arrow or using Ctrl+F11), Oracle SQL Developer automatically builds an anonymous block for you complete with the appropriate variable declarations and the call to your procedure or function with the correct parameters. It even adds a dbms_output statement for you if it's a function that's being tested.

plsql run window

The only thing you need to do is to modify the code to initialise the parameters with the correct values and then run it by clicking the OK button. Results are displayed in a message pane which pops up automatically when you click OK.

New procedures, functions and packages can be created by right clicking on the root of the procedures or functions branch in the connections tree.

How To Run Your Own SQL Code

We've seen how to view and modify raw table data using the data grid but if your needs are more sophisticated than that you can enter and run your own sql code in the Sql Edit window. To access this just select Tools|SQL Worksheet from the menu or use the shortcut Alt-F10.

sql edit window

As you type in your code a list of possible items is automatically (and very quickly) displayed. By using the up and down arrow keys you can navigate to the item of interest and select it by pressing <return> or <enter>.

There is also a snippets side bar which provides access to small pieces of SQL code such as conversion functions and date formats to save you having to enter them manually and for when you're unsure of the syntax.

sql edit window

This was just a quick look at Oracle SQL Developer - it has far too many features to be covered in a short overview like this. More details can be found on the Oracle Technology Network site.

Looking for more Oracle training?

Learn quickly and easily from highly experienced consultants with skills honed over many years in the real world from designing, building and testing myriad, complex systems. Contact our partner Smartsoft for Oracle training in the UK. All courses are taught by experts, can be customised to your exact requirements and are offered on or off site.