Oracle SQL Developer

Oracle SQL Developer is a program used to interact with and manage the database just like SQLPlus. Unlike SQLPlus, SQL Developer runs on a full-sized window comprising various interactive components—such as forms, buttons, etc.— which makes database management much easier for developers and administrators.

This page will introduce the fundamentals of Oracle SQL Developer that can be oftentimes used in everyday database management tasks.

Installation

Note: Skip this section if you already have SQL Developer installed, unless you're trying it on your own computer.

SQL Developer downloads are located at this link. Look for the desired platform, then click its corresponding download link to download the archive. For 64-bit Windows, you can download SQL Developer with or without the JDK 17. Conversely, for 32-bit Windows, you can only download it without JDK 17, so JDK installation is required.

After downloading it, open it with the archiver that is installed and available. Click Extract and specify the path where you want to put the folder in.

This will extract all the necessary files from the archive.

After extracting the archive, find the sqldeveloper.exe file. Then right click on it, hover the mouse cursor to Send To (or click), and click Desktop (create shortcut). This will create a shortcut to the executable file on the desktop, so you don't have to browse the explorer again just for that file.

After creating a desktop shortcut for SQL Developer, then happy learning about it!

This photo would look like once SQL Developer is fully loaded.

Creating a Connection

To create a connection, at the Connections window at the left, click the green plus button to show the window for creating a database connection.

A connection will show up on that window after you created it.

Enter the name you want of the connection, and enter our account's username and password.

You don't need to change other details. These are already configured or filled up by default for ease.

To ensure you entered the correct account credentials, click Test, then at the bottom left, if the status shows "Success," click Connect to create the connection.

Each connection is listed in Oracle Connections and contains lists of database objects that you have or created.

It is allowed to create a distinct connection that uses the same credentials as the another connection, but it is usually not a good practice to create connections with different names yet share the same credentials.

SQL Worksheet

A SQL Worksheet is a text area where you enter SQL (and PL/SQL) commands or statements and execute them within it.

To open the SQL Worksheet, right-click on the desired connection, then just click Open SQL Worksheet. This will show up the SQL Worksheet without any problem.

Alternatively, press both Alt and F10 keys simultaneously, then select the connection where you want the SQL Worksheet to work on.

After selecting the connection, it should look like the figure at the left, which is the SQL worksheet itself.

You can type any SQL statement and try to run it for yourself.

Creating a Table

To create a table using SQL Developer, follow the steps below:

Right click on Tables (Filtered), then click New Table.

This will show the Create Table window, where columns can be customized by editing the attributes of each column created before creating the table.

This is where you customize the table by giving it a name, adding columns, moving columns, and edit columns.

To provide a name for the table, simply enter the desired name next to "Name:".

To add a column, just click the green plus to insert it after the preceding (or selected) column.

To delete a column, select (click) a single or multiple columns you want to delete, then click the red cross and the selected column will be gone.

Moving the Columns

You can arrange the columns by moving them. However, after you created the table, you cannot move those columns somewhere within the table.

On the right side, is a set of buttons used to move the columns. By enumerating each button's purpose, the first button moves the selected columns to the very top (up arrow with the bar above). The second button moves them by an upward step (up arrow). The third button moves them by a downward step (down arrow). Lastly, the fourth button moves them to the very bottom (down arrow with the bar below).

Modifying the Attributes

Additionally, you can modify the attributes of each column. It is done by clicking a cell within the respective row of the corresponding column.

Check or uncheck the first column of the column editor to indicate if a corresponding column is a primary key or not. The columns from second to fourth correspond to the respective column's name, data type, and size. Check or uncheck the fifth column to indicate if a column will not allow a null value to be stored. The sixth column contains the default value of the column in case no value is passed in the INSERT statement. The seventh column contains the comment of the column, which is usually helpful for describing the column.

After finished creating the columns and finalizing the entire table's structure, just click OK to create the table. Once created, you should see the new table in Tables (filtered) by expanding it to display all the tables you have.

Inserting Data

To insert data into the table, click the table to insert the data in.

Go to the Data tab, and press the icon next to the blue one. Or alternatively, press Ctrl and I keys to do the same. This will show a blank record where you input the values in each cell.

Double click each cell to edit the value of each column. Once done, click the encircled icon to commit changes or apply changes to the database.

The success message should be shown at the Messages - Log view after successful insertion.

Editing a Table

If you want to change something in a set of attributes of a table, you can directly edit the table. By right clicking the table, and click Edit.

It should look something like the photo at the left, where you can change the attributes or the name of the table.

You can add or delete columns, but you cannot move them somewhere within the table. Moving the columns requires recreation of the table; by dropping and creating the table again with different order of columns.

Also, you can apply constraints to some of the columns, and it will be covered in a separate article.

Once done editing, click OK to apply the changes.

Modifying and Deleting the Data

Browse the data of the table by clicking it, then go to the Data tab to view all the data of the corresponding table.

You can modify each value in the corresponding cell, by double clicking it and entering the new value for that cell.

Additionally, you can also resize columns whenever lengthy values are necessary, or click the pencil icon at the figure below to show the larger text area.

Click this icon once done modifying to apply the changes, or alternatively press F11.

Otherwise, click this icon to undo (rollback) the changes, or alternatively press F12.

After clicking any of these icons, the message will be shown below the editor (the Messages - Log view), to determine whether the execution is successful or not.

To delete a record (or more), select a single or multiple records by clicking the cell (doesn't matter which column) that is belong in the record that will be deleted, or by pressing Ctrl or Shift together with clicking the cell to do multiple selection of columns.

To apply or undo the changes, click either of those icons mentioned above.

Executing SQL (soon)

If you already opened your SQL Worksheet, this is the place where you execute SQL (or PL/SQL) statements and display the results (more commonly for SELECT statements).

Page updated

Google Sites

Report abuse