Place the cursor over this icon
to load and view all the screenshots for this tutorial. (Caution: This
action loads all screenshots simultaneously, so response time may be slow depending
on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
Overview
Oracle SQL Developer is a free graphical tool that enhances
productivity and simplifies database development tasks. Using Oracle 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, Oracle 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. Oracle SQL Developer is built on an extensible
framework and, as such, is extensible itself. Users can create basic XML extensions
or more involved Java extensions to add utilities or other functionality to
the product.
To create a database connection, perform the following steps:
1.
Open Windows Explorer and double-click
<your_path>\sqldeveloper\sqldeveloper.exe.
Note: If you receive a dialog window asking whether
you want to migrate settings from a previous release, click No.
2.
In the Connections tab, right-click Connections
and select New Connection.
3.
Enter HR_ORCL
for the Connection Name (or any other name that identifies your connection),
HR for the Username and
Password, specify your <hostname>
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. To save the connection,
click Connect.
5.
The connection was saved and you see the
database in the list. Expand HR_ORCL.
6.
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.
Users who want to share user defined reports can add these
as an extension to Oracle SQL Developer. To do this perform the following steps:
1.
Select the Reports tab.
2.
Expand each of the nodes. Note that there are a selection
of shipped reports under the Data Dictionary Reports
node.
3.
There are also shipped Migration Workbench Reports
under the Shared Reports node. Any reports you create
are added under the User Defined Reports node.
4.
To add a shared reports extension, navigate to the
Tools-> Preferences menu.
5.
Expand the Database node and select
User Defined Extensions.
6.
Click Add Row
7.
Click in the field under Type and
select Report.
8.
Click in the field under Location
and Browse to your working directory. Select the employees_reports.xml
file.
9.
Ensure the location is selected, then click OK.
10.
Shutdown and restart Oracle SQL Developer for the
changes to take effect.
11.
Navigate to the Reports tab and select Shared
Reports.
12.
Expand the EmployeesReports node
and select Employees per Manager. Select HR_ORCL from
the drop list when prompted and run the report.
13.
Right-click on the Employees per Manager report.
Note that the context menu does not permit you to edit this report. You
can copy and recreate it as a User Defined report, but shared reports
are not editable.
Each object in the Navigator has a matching set of definition
tabs. These are based on queries against the Data Dictionary. Not all features
describing an object are displayed in these tabs. To create a Column Comments
tab for a table, perform the following steps:
1.
Select the Connections
tab and expand the HR_ORCL connection.
2.
Expand the Tables node
and select EMPLOYEES.
3.
The initial tab displayed is the Columns
tab. Select the Constraints tab and view the details
displayed.
4.
Click through the other tabs to see the
full details available for a table.
5.
Some users would like to display the
column comments for a table in a tab too. To do this you need to add in
an extension. Select Tools->Preferences.
6.
As before, select Database and User
Defined Extensions. Click Add Row.
7.
Select Editor from the
drop list for Type.
8.
Set the Location by browsing to your
working directory ((i.e. d:\wkdir) and select the comment_tab.xml
file.
9.
Ensure the Location is stored and click
OK.
10.
Shut down and restart Oracle SQL Developer for your
changes to take effect.
11.
Select the HR_ORCL connection. Expand
the tables node and select EMPLOYEES.
12.
Tab through to the last tab, Column Comments.
The new tab is added after the other tabs.
In order to add an XML extension to Oracle SQL Developer,
you need to create the XML file. In this section you review the structure of
the XML File.
1.
In Oracle SQL Developer, select
File ->Open and browse to your working
directory ((i.e. d:\wkdir) and select the comment_tab.xml
file.
2.
Double-click on the tab to
maximize the editor.
3.
The Oracle SQL Developer framework recognizes
the XML tags. The pieces of code you are interested in are Title and the
SQL Query. The Title here is Column Comments and is used
to name to tab.
4.
The SQL Query is querying
the Data Dictionary for the column comments for the OWNER
and the OBJECT_NAME, in this example the EMPLOYEES Table
for schema HR.
This is example is the same as the one just completed above.
In this instance you are going to add a tab to describe sub partitions. You
create a table with partitions and sub partitions to review the details. To
add additional tabs to the Table definitions, perform the following steps:
1.
Select Tools -> Preferences
to invoke the dialog. Expand the Database node and select
User Defined Extensions. Click Add Row.
2.
The Extension Type is Editor. Browse to
your working directory (i.e. d:\wkdir) and select the
subpartition_tab.xml file.
3.
Shutdown and restart Oracle
SQL Developer for your changes to take effect.
4.
Expand the HR_ORCL connection. Select
File ->Open and browse to your working directory (i.e.
d:\wkdir). Select the cust_part.sql file.
5.
Review the file. This script creates a
partitioned table, CUSTOMERS_PART. Select the HR_ORCL
connection from the drop list on the right of the SQL Worksheet.
6.
Click the Run Script (F5)
icon.
7.
Expand the Tables node in the Connections
Navigator and select the CUSTOMERS_PART table.
NOTE: If the tables node is already
expanded, you need to click Refresh, to see the new table.
8.
Select the Partitions Tab.
9.
The new SubPartitions
tab is added at the end of all the tabs. Navigate to the end of the tabs
and review the new details.
10.
Extra: Consider how you might add or remove
columns from this SubPartitions tab.
HINT: Review the file is subpartition_tab.xml
in your working directory.
In the above exercises, you added tabs to the table definition,
using XML files. You can add context menus using a similar approach. Context
menus can be added at any level to the existing context menu structure. In this
exercise you add a high-level context menu to set a transaction to READ ONLY.
To add a new context menu perform the following steps:
1.
Select File->Open. Navigate to
your working directory (i.e. d:\wkdir) and select the
readonly_menu.xml file.
2.
Double-click on the readonly_menu.xml
tab to maximize the editor.
3.
This XML file is more involved. Notice that in this
case there are two sections; one for Set Read Only and
the other for Set Read/Write. The image below highlights
one of the sections between the <item> tags.
4.
Notice the <prompt> and <confirmation>
tags.
5.
To add the extension to Oracle SQL Developer, select Tools->Preferences.
Expand the Database node and select User Defined
Extensions. Click Add Row.
6.
Select ACTION from the Extension
Type drop list.
7.
Select the Location field and browse to your working
directory (i.e. d:\wkdir) and select the readonly_menu.xml
file. Ensure the file location is selected and click OK.
8.
Shutdown and restart Oracle SQL Developer for your
changes to take effect.
9.
Select the HR_ORCL connection and
right-click to invoke the context menu. Scroll to the bottom and select
Set Read Only.
10.
The title "Set Read Only"is
displayed in the dialog header. Notice the Prompt. The text is from the
<prompt type="confirm">
<label>Confirm Setting Connection to Read Only.</label>
</prompt>
11.
Select the SQL tab. Find the equivalent
SQL code in the XML file. Click Apply. The final Confirmation
dialog is also driven by the XML file.
12.
Expand the Tables node for the connection HR_ORCL
and select EMPLOYEES. Select the data tab.
13.
Modify the FIRST_NAME for one of
the records.
14.
Select the Commit icon to commit
the changes.
15.
You are not able to commit the records due to the
READ ONLY transaction.
