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:

 Overview
 Prerequisites
 Creating a Database Connection
 Sharing Reports
 Adding an Extra Tab to Display Column Comments
 Reviewing the XML File structure
 Adding the Tab to Describe Sub partitions
 Adding a Context Menu
 Summary

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.

 

 E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy
Left Curve
Popular Downloads
Right Curve
Untitled Document