8/23/2019 Getting Started With Oracle SQL Developer

Getting Started with Oracle SQL Developer

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a
browser>

Purpose
This tutorial shows introduces Oracle SQL Developer and shows you how to manage your database objects.

Time to Complete
Approximately 50 minutes

Overview
Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database
development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and
debug PL/SQL statements and run reports, whether provided or created.

Developed in Java, SQL Developer runs on Windows, Linux and the Mac OS X. This is a great advantage to
the increasing numbers of developers using alternative platforms. Multiple platform support also means that
users can install SQL Developer on the Database Server and connect remotely from their desktops, thus
avoiding client server network traffic.

Default connectivity to the database is through the JDBC Thin driver, so no Oracle Home is required. To
install SQL Developer simply unzip the downloaded file. With SQL Developer users can connect to any
supported Oracle Database, for all Oracle database editions including Express Edition.

Prerequisites
Before starting this tutorial, you should:

Install Oracle SQL Developer 2.1 early adopter from OTN here. Follow the readme instructions here.
Install the Oracle Database 10g and later.
Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
alter user hr identified by hr account unlock;
Download and unzip the sqldev_mngdb.zip file that contains all the files you need to perform this
tutorial.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer is to create a database connection.
Perform the following steps:

1 . Open Oracle SQL Developer.

2 . In the Connections navigator, right-click Connections and select New Connection.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 1/38
8/23/2019 Getting Started With Oracle SQL Developer

3 . Enter HR_ORCL for the Connection Name (or any other name that identifies your connection),
hr for the Username and Password, specify your localhost for the Hostname and enter ORCL
for the SID. Click Test.

4 . The status of the connection was tested successfully. The connection was not saved however.
Click Save to save the connection, and then click Connect.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 2/38
8/23/2019 Getting Started With Oracle SQL Developer

5 . The connection was saved and you see the database in the list.

6 . Expand HR_ORCL.

Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL
Worksheet allows you to execute SQL against the connection you just created.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 3/38
8/23/2019 Getting Started With Oracle SQL Developer

7 . Expand Tables.

8 . Select the Table EMPLOYEES to view the table definition. Then click the Data tab.

9 . The data is shown. In the next topic, you create a new table and populate the table with data.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 4/38
8/23/2019 Getting Started With Oracle SQL Developer

Adding a New Table Using the Create Table Dialog Box

You create a new table called DEPENDENTS which has a foreign key to the EMPLOYEES table. Perform
the following steps:

1 . Right-click Tables and select New TABLE...

2 . Enter DEPENDENTS for the Table Name and click the Advanced check box.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 5/38
8/23/2019 Getting Started With Oracle SQL Developer

3 . Enter ID for the Name, select NUMBER for the Data type and enter 6 for the Precision. Select
the Cannot be NULL check box. Then click the Add Column icon.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 6/38
8/23/2019 Getting Started With Oracle SQL Developer

4 . Enter FIRST_NAME for the Name, leave type as VARCHAR2 and 20 for the Size. Then click
the Add Column icon.

5 . Enter LAST_NAME for the Name, leave type as VARCHAR2 and enter 25 for the Size. Select
the Cannot be NULL check box. Then click the Add Column icon.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 7/38
8/23/2019 Getting Started With Oracle SQL Developer

6 . Enter BIRTHDATE for the Name, select DATE for the Data type. Then click the Add Column
icon.

7 . Enter RELATION for the Name, leave type as VARCHAR2 and enter 25 for the Size. Click
OK to create the table.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 8/38
8/23/2019 Getting Started With Oracle SQL Developer

8 . Your new table appears in the list of tables.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 9/38
8/23/2019 Getting Started With Oracle SQL Developer

Changing a Table Definition

Oracle SQL Developer makes it very easy to make changes to database objects. In this topic, you add a
column to the DEPENDENTS table you just created. Perform the following steps:

1 . Select the DEPENDENTS table.

2 . Right-click, select Column then Add.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 10/38
8/23/2019 Getting Started With Oracle SQL Developer

3 . Enter RELATIVE_ID, select NUMBER from the droplist, set the Precision to 6 and Scale to 0.

Click Apply.

4 . The confirmation verifies that a column has been added.

Click OK.

5 . Expand the DEPENDENTS table to review the updates.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 11/38
8/23/2019 Getting Started With Oracle SQL Developer

Adding Table Constraints

In this topic, you create the Primary and Foreign Key Constraints for the DEPENDENTS table. Perform the
following steps:

1 . Right-click DEPENDENTS table and select Edit...

2 . Click the Primary Key node in the tree.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 12/38
8/23/2019 Getting Started With Oracle SQL Developer

3 . Select the ID column and click > to shuttle the value to the Selected Columns window.

4 . Select the Foreign Key node in the tree and click Add.
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 13/38
8/23/2019 Getting Started With Oracle SQL Developer

5 . Select EMPLOYEES for the Referenced Table and select RELATIVE_ID for the Local
Column and click OK.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 14/38
8/23/2019 Getting Started With Oracle SQL Developer

Adding Data to a Table

You can add data to the DEPENDENTS table by performing the following steps:

1 . With the DEPENDENTS table still selected, you should have the Data tab already selected. If
not, select it.

2 . Then click the Insert Row icon.

3 . Enter the following data and then click the Commit icon to commit the row to the database.

ID: 209
FIRST_NAME: Sue
LAST_NAME: Littlefield
BIRTHDATE: 01-JAN-
97
RELATION: Daughter
RELATIVE_ID: 110

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 15/38
8/23/2019 Getting Started With Oracle SQL Developer

4 . The outcome of the commit action displays in the log window.

5 . You can also load multiple rows at one time using a script. Click File Open...

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 16/38
8/23/2019 Getting Started With Oracle SQL Developer

6 . Navigate to the directory where you unzipped the files from the Prerequisites, select the
load_dep.sql file and click Open.

7 . Select the HR_ORCL connection in the connection drop list to the right of the SQL Worksheet.

8 . The SQL from the script is shown. Click the Run Script icon.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 17/38
8/23/2019 Getting Started With Oracle SQL Developer

The data was inserted. Click the DEPENDENTS tab.

9 . To view the data, make sure the Data tab is selected and click the Refresh icon to show all
the data.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 18/38
8/23/2019 Getting Started With Oracle SQL Developer

All the data is displayed

10 . You can export the data so it can be used in another tool, for example, Excel. Right-click on one
of the values in any column, select Export and then one of the file types, such as csv.

11 . Specify the directory and name of the file and click Apply.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 19/38
8/23/2019 Getting Started With Oracle SQL Developer

12 . If you review the DEPENDENTS.CSV file, you should see the following:

Accessing Data
One way to access DEPENDENTS data is to generate a SELECT statement on the DEPENDENTS table and
add a WHERE clause. Perform the following steps:

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 20/38
8/23/2019 Getting Started With Oracle SQL Developer

1 . Select the HR_ORCL Database Connection, right-click and select Open SQL Worksheet.

2 . Drag and Drop the DEPENDENTS table from the list of database objects to the SQL statement
area.

3 . A dialog window appears. You can specify what type of SQL statement to create. Accept the
default to create a SELECT statement and click Apply.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 21/38
8/23/2019 Getting Started With Oracle SQL Developer

4 . Your SELECT statement is displayed. You can modify it in the SQL Worksheet and run it.

5 . Add the WHERE clause where relative_id > 110 to the end of the SELECT statement BEFORE
the ';'.

Click the Run Statement icon.

6 . The results are shown.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 22/38
8/23/2019 Getting Started With Oracle SQL Developer

Creating Reports
As the SQL you just ran in the previous topic needs to be executed frequently, you can create a custom report
based on the SQL. In addition, you can run a report of your database data dictionary using bind variables.
Perform the following steps:

1 . Select the SQL in the HR_ORCL SQL Worksheet that you executed, right-click and select
Create Report.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 23/38
8/23/2019 Getting Started With Oracle SQL Developer

2 . Enter a Name for the report and click Apply.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 24/38
8/23/2019 Getting Started With Oracle SQL Developer

3 . Select the Reports tab, expand User Defined Reports and select the report you just created.

4 . Select HR_ORCL from the drop list and click OK to connect to your database.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 25/38
8/23/2019 Getting Started With Oracle SQL Developer

5 . The results of your report are shown.

6 . You can also run a Data Dictionary report. Expand Data Dictionary Reports > Data
Dictionary. Then select Dictionary Views..

7 . Deselect the NULL check box, enter col for the Value and click Apply.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 26/38
8/23/2019 Getting Started With Oracle SQL Developer

8 . All the Data Dictionary views that contain 'col' in its name are displayed.

Creating and Executing PL/SQL

Oracle SQL Developer contains extensive PL/SQL editing capabilities. In this topic, you create a Package
Spec and Package Body that adjusts an employee's salary. Perform the following steps:

1 . Select File > Open using the main menu.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 27/38
8/23/2019 Getting Started With Oracle SQL Developer

2 . Browse to the directory where you unzipped the files from the Prerequisites, select
createHRpack.sql Click Open.

3 . Select the HR_ORCL database connection from the the drop list on the right.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 28/38
8/23/2019 Getting Started With Oracle SQL Developer

4 . Click the Run Script icon.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 29/38
8/23/2019 Getting Started With Oracle SQL Developer

5 . The package and package body compiled successfully. Click the Connections navigator.

6 . Expand HR_ORCL > Packages > HR_PACK and select HR_PACK to view the package
definition.

7 . Double-click HR_PACK BODY to view the package body definition.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 30/38
8/23/2019 Getting Started With Oracle SQL Developer

8 . Click on any one of the - to collapse the code or press + to expand the code.

9 . If your line numbers do not appear, you can right-click in the line number area and click Toggle
Line Numbers to turn them on. This is useful for debugging purposes.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 31/38
8/23/2019 Getting Started With Oracle SQL Developer

10 . In the Connections Navigator, select Packages > HR_PACK, right-click and select Run.

11 . A parameter window appears. Make sure that the GET_SAL target is selected. You need to set
the input parameters here for P_ID and P_INCREMENT.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 32/38
8/23/2019 Getting Started With Oracle SQL Developer

12 . Set the P_ID to 102 and P_INCREMENT to 1.2. What this means is that the Employee who has
the ID 102, their salary is increased by 20%. The current SALARY for EMPLOYEE_ID 102 is
17000. Click OK.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 33/38
8/23/2019 Getting Started With Oracle SQL Developer

13 . The value returned is 20400.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 34/38
8/23/2019 Getting Started With Oracle SQL Developer

14 . To test the Exception Handling, right-click on HR_PACK in the navigator and select Run.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 35/38
8/23/2019 Getting Started With Oracle SQL Developer

15 . This time, change the P_INCREMENT value to 5 and click OK.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 36/38
8/23/2019 Getting Started With Oracle SQL Developer

16 . In this case, an exception was raised with "Invalid increment amount" because the
P_INCREMENT value was greater than 1.5.

Summary
In this tutorial, you have learned how to:

Create a database connection

Add a new table using the Table Dialog Box
Change a table definition
Add data to a table
Access data
Generate a report
Create and execute PL/SQL

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 37/38
8/23/2019 Getting Started With Oracle SQL Developer

About Oracle |Oracle and Sun | | Careers | Contact

Us | Site Maps| Legal Notices | Terms of Use | Your
Privacy Rights

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/sqldev_mngdb/sqldev_mngdb_otn.htm 38/38