0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } function MM_openBrWindow(theURL,winName,features) { //v2.0 window.open(theURL,winName,features); } //-->
Creating XML Extensions for Oracle SQL Developer
Purpose
This tutorial shows you how to add XML extensions to Oracle SQL Developer.
Time to Complete
Approximately 30 minutes
Topics
This tutorial covers the following topics:
Viewing Screenshots
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.
Back to Topic List
Prerequisites
Before you perform this tutorial, you should:
| 1.
|
Install
Oracle Database 11g
Note: You can use any Oracle Database above 9.2.0.1
|
| 2.
|
If you are not using Oracle Database 11g, install Oracle SQL Developer 1.2.1.
Note: Oracle SQL Developer is available for download for FREE from
OTN. To install Oracle SQL Developer, unzip it into any directory on your machine.
|
| 3. |
Download and unzip
extensions.zip
into your working directory (i.e. d:\wkdir)
|
| 4.
|
Have access to the shipped
HR schema.
If not already done, a DBA user needs to unlock the schema to provide access. This can be done with the following commands:
ALTER USER hr ACCOUNT UNLOCK;
ALTER USER hr IDENTIFIED BY hr;
|
Back to Topic List
Creating a Database Connection
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.
|
Back to Topic List
Sharing Reports
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.
|
Back to Topic List
Adding an Extra Tab to Display Column Comments
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.
|
Back to Topic List
Reviewing the XML File structure
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.
|
Back to Topic List
Adding a Tab to Describe Sub-Partitions
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.
|
Back to Topic List
Adding a Context Menu
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.
|
Back to Topic List
Summary
In this tutorial, you learned how to:
 |
Creating a Database Connection |
| |
Sharing Reports |
| |
Adding an Extra Tab to Display Column Comments |
| |
Reviewing the XML File structure |
| |
Adding the Tab to describe Subpartitions |
| |
Adding a Context Menu |
Back to Topic List
Move your mouse over this icon to hide all screenshots.
|
testcontent
Printer View