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.

---

Oracle Tips & Tricks to
SKYROCKET Your Career!

---

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). 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.

For 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 .

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.

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).



From here you can create a new connection by clicking on the picture of the page of text with a little green plus sign or right click on the icon of the disk with a plug on it (the empty list of connections). This brings up a form for specifying the schema and the details of the database to which you want to connect as well as providing a label for this 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.

We mentioned earlier that 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.



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

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 but does not enable you to modify, compile or run it.



To edit, compile or run your pl/sql procedure or function you have to click on the edit icon (the pencil and sheet of paper) to bring up the pl/sql 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.



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 and functions can be created by right clicking on the root of the procedures or functions branch in the connections tree.

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 Ctrl-shift-w.



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.



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 articles and full documentation can be found on the Oracle Technology Network site.

Return from Oracle SQL Developer to Oracle utilities