This tutorial introduces Oracle SQL Developer 4.0 and shows you
how to manage your database objects.
Time to Complete
Approximately 50 minutes
Introduction
Oracle SQL Developer is a free graphical tool that enhances
productivity and simplifies database development tasks. Using SQL
Developer, you 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 number 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.
Prerequisites
Before starting this tutorial, you should:
Install Oracle SQL Developer 4.0 from OTN.
Follow the readme instructions here
.
Install Oracle Database 12c with the Sample schema
Unlock the HR user. Login to SQL Developer as the SYS user and
execute the following command: alter user hr
identified by hr account unlock;
Download and unzip the files.zip
to a local folder on your file system. In this tutorial, we use
the C:\sqldev3.0
folder.
Note: This tutorial was created in a Linux
environment. If you are using a Windows environment, the
screenshots may be slightly different.
Creating a Database Connection
The first step to managing database objects using Oracle SQL
Developer 4.0 is to create a database connection. Perform the
following steps:
If you installed the SQL Developer icon on your desktop,
click the icon to start your SQL Developer and move to Step 4.
If you do not have the icon located on your desktop, perform
the following steps to create a shortcut to launch SQL
Developer 4.0 directly from your desktop.
In Windows, open the directory where the SQL Developer 4.0 is
located, right-click sqldeveloper.exe and
select Send to > Desktop (create shortcut).
On the desktop, you will find an icon named Shortcut to
sqldeveloper.exe (in Windows) or SQL Developer4.0 (in
Linux). Double-click the icon to open SQL Developer 4.0.
The New / Select Database Connection dialog opens. Enter the
connection details as follows and click Test.
Connection Name: HR_ORCL
User Name: hr
Password: <your_password> (Select the Save
Password checkbox)
Connection Color: Select the red color. (The color is
set as the border of any SQL Worksheet and other windows
associated with the connection. Setting the color connection is
useful when working with multiple connections.)
Hostname: localhost
SID: <your_own_SID>View Image
Note: In this tutorial the Service Name is
specified instead of SID.
Check for the status of the connection on the left-bottom
side (above the Help button). It should read Success.
Click Connect. Then click Save.
Note: When a connection is opened, a SQL
Worksheet is opened automatically. The SQL Worksheet allows
you to execute SQL against the connection you have opened.
You can sort the EMPLOYEES
table data by double clicking the arrow icon next to the
column you want to sort on. Here the table data is sorted on EMP_ID.
Note that the DEPARTMENTS
table has 4 constraints. Each constraint has a unique name.
The CONSTRAINT_TYPE identifies the type of constraint on the
table.
Review the Foreign Keys constraints. Note that the DEPARTMENTS
table has two foreign keys, one named DEPT_LOC_FK
and the other named DEPT_MGR_FK.
When you select one of the names in the Foreign Keys field,
the details about that foreign key appear.
Adding a New Table Using the Create Table Dialog Box
In this section, you create a new table called
DEPENDENTS. You create columns in the DEPENDENTS
table. Your table will have the columns ID,
FIRST_NAME, LAST_NAME, BIRTHDATE, and RELATION.
Later, you create a foreign key on the
DEPENDENTS table to associate the column's values with
the EMPLOYEES.
Perform the following steps:
For the first column in your table, enter ID
for the Name, select NUMBER for the Datatype
and enter 6 for the Precision (length of the
number). Select Cannot be NULL, then click
Add Column .
Note: By checking Cannot be NULL, you are adding a constraint
on the table that specifies the column must hold values.
For the next column, enter FIRST_NAME
for the Name, leave the type as VARCHAR2 and enter 20
for the Size to specify the length of the VARCHAR2. Then click
Add Column .
For the next column, enter LAST_NAME
for the Name, leave the type as VARCHAR2 and enter 25
for the Size. Select the Cannot be NULL
check box. Then click Add Column .
Oracle SQL Developer makes it very easy to make changes to
database objects. In this topic, you add a column called
RELATIVE_ID to the
DEPENDENTS table you just created. Perform the following
steps:
In the Connections navigator, select the DEPENDENTS
table.
The RELATIVE_ID
column is added to the table definition.
Adding Table Constraints
In this topic, you create the Primary and Foreign Key Constraints
for the table. Constraints are a way of validating data. A Primary
Key constraint ensures that a column's values are unique and
present (not null).
A Foreign Key constraints maintains data integrity between two
different columns (one of which may be in a separate table).
Perform the following steps:
This identifies that the ID
column in the DEPENDENTS
table has a Primary Key constraint. All of the values in
this column must be unique and must be present.
Next, you add a Foreign Key constraint. Click the drop down
arrow next to the Add Constraint icon and select
New Foreign Key Constraint
You will link the RELATIVE_ID
column in the DEPENDENTS
table to the ID
column in the
EMPLOYEES table. This will ensure that any value
added in the the RELATIVE_ID
column must match an existing value in the ID
column of the EMPLOYEES
table.
Under the Referenced Constraint section,
ensure the schema selected is HR. Select
EMPLOYEES
from Table drop list. Ensure that the Constraint field holds
the value EMP_EMP_ID_PK.
In the Associations field, select RELATIVE_ID
for the Local Column field and click OK.
Click on the drop down next to Format and select the format
you want to export the data into, such as csv. Note that you
have various format options available.
If you need to execute a SQL statement 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.
Bind variables enable you to run your query multiple times and
pass in different values. Perform the following steps:
Highlight the SQL statement in the HR_ORCL SQL Worksheet that
you executed, right-click and select Create Report...
You can also run a Data Dictionary report. Data dictionary
reports hold meta data about your objects. This includes
information such as your table definitions.
Expand Data Dictionary Reports > Data Dictionary.
Then select Dictionary Views.
Note: in the output shown above, the
ALL_CONS_COLUMNS would hold information on
constraints on columns. The ALL_COLL_TYPES
holds information on the data types for your columns.
Creating and Executing PL/SQL
Oracle SQL Developer contains extensive PL/SQL editing
capabilities. In this topic, you create a Package Specification
and Package Body. This package adjusts an employee's salary.
Perform the following steps:
The package specification and the package body compile
successfully.
Note: The script first drops the package, so the first time
you run this script, you will see ora errors. Ignore the
errors and observe the output at the bottom that the package
is created.
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.
You can split the code editor display either horizontally or
vertically. To do so, right click HR_PACK
Body and select Split Vertically
for a vertical split or Split Horizontally
for a horizontal split.
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.
Input parameters are values that you are passing into the
program at runtime.
Set the P_ID to 102 and
P_INCREMENT to 1.2. This
means for 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.
.
. 
and select New Primary Key Constraint.
to shuttle the value to the Selected Columns
window.
.
to save the row to the database.
.
to show all the data.
. 
.
Overview