Creating User-defined Extensions in Oracle SQL Developer

Purpose

This tutorial shows you how to add XML extensions to Oracle SQL Developer.

Time to Complete

Approximately 40 mins

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Database Connection
 Sharing Reports
 Adding a Tab to Display Column Comments
 Reviewing the XML File Structure
 Adding a 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

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle 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, 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. You 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 the Oracle Database 10g or later or Oracle Database XE.

2.

Install Oracle SQL Developer 1.5.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 the xmlext.zip file into your working directory (i.e.wkdir)

Back to Topic List

Creating a Database Connection

To create a database connection, perform the following steps:

Note: If you already have a database connection, you need not perform these steps.

1.

Open Oracle SQL Developer from the icon on your desktop.

 

2.

In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Note: If this tab is not visible, select View > Connections.

 

3.

Enter HR in the Connection Name field (or any other name that identifies your connection) and hr for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

4.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

5.

The connection is saved and you can see it listed under Connections in the Connections tab.

 

6.

Expand HR. A SQL Worksheet opens automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

 

Back to Topic List

Sharing Reports

You can share user-defined reports by adding them as an extension to Oracle SQL Developer. To do this, perform the following steps:

1.

Select the Reports tab.

Note: If this tab is not visible, select View > Reports.

 

2.

Expand each of the nodes. There are a selection of shipped reports under the Data Dictionary Reports node.

 

3.

There are also shipped Migration Reports. Any report you create is added under the User Defined Reports node.

 

4.

To add a Shared Reports extension, select Tools > Preferences.

 

5.

Expand the Database node and select User Defined Extension.

 

6.

Click Add Row.

 

7.

Click in the field under Type and select REPORT from the list.

 

8.

Click in the field under Location and click Browse.

 

9.

Browse the directory where your files are located and select the employees_reports.xml file.

 

10.

Ensure that the location is selected and click OK.

 

11. Exit and restart Oracle SQL Developer for the changes to take effect.
12.

Navigate to the Reports tab ( View > Reports). The Shared Reports node is added.

Note: If you do not see the Shared Reports node, then the extension was not saved properly (step 10). Repeat the steps from step 4 onwards.

 

13.

Expand the Shared Reports and EmployeeReports nodes and select Employees per Manager.

 

14.

Select the HR connection if prompted and run the report.

 

15.

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 a 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 illustrate this, this section shows you how to add a separate tab for Column Comments for a table. To add a new tab, perform the following steps:

1.

Select the Connections tab and expand the HR connection.

Note: If this tab is not visible, select View > Connections.

 

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 view the details available for a table.

 

5.

You can also display the column comments for a table in a separate tab. To do this you need to add an extension. Select Tools > Preferences.

 

6.

As before, expand the Database node and select User Defined Extensions. Click Add Row.

 

7.

Click in the field under Type and select EDITOR from the list.

 

8.

Click in the field under Location and click Browse.

 

9.

Browse the directory where your files are located and select the comment_tab.xml file.

 

10.

Ensure that the location is selected and click OK.

 

11. Exit and restart Oracle SQL Developer for the changes to take effect.
12.

Select the HR connection from the Connections tab. Expand the Tables node and select EMPLOYEES.

 

13.

Click through the tab till you reach the last tab. A new Column Comments tab is added after the other tabs.

Note: If you do not see the Column Comments tab, then the extension was not saved properly (step 10). Repeat the steps from step 5 onwards.

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 used to add the Column Comments tab extension (previous topic).

1.

In Oracle SQL Developer, select File > Open.

 

2.

Browse to the location where you have saved the files mentioned in the prerequisites. Select the comment_tab.xml file and click Open.

 

3.

Double-click on the tab to maximize the editor.

 

4.

The Oracle SQL Developer framework recognizes the XML tags. The tags you are interested in are the title and the SQL query tags. The title here is Column Comments and is used to name the tab.

 

5.

The SQL query is querying Data Dictionary for the column comments for the OWNER and OBJECT_NAME; in this example, the EMPLOYEES Table for the HR schema.

 

Back to Topic List

Adding a Tab to Describe Sub-partitions

You can add a tab to describe sub partitions within a table. In this example, you create a table with partitions and sub partitions and add an extension to review the details. To do this, perform the following steps:

1.

In Oracle SQL Developer, select File > Open.

 

2.

Browse to the location where you have saved the files mentioned in the prerequisites. Select the cust_part.sql file and click Open.

 

3.

Review the file. This script creates a partitioned table, CUSTOMERS_PART.

 

4.

Select the HR connection from the drop-down list on the right of the SQL Worksheet.

 

5.

Click the Run Script (F5) icon.

 

6.

To add the extension, select Tools > Preferences.

 

7.

Expand the Database node and select User Defined Extensions. Click Add Row.

 

8.

Click in the field under Type and select EDITOR from the list.

 

9.

Click in the field under Location and click Browse.

 

10.

Browse the directory where your files are located and select the subpartition_tab.xml file.

 

11.

Ensure that the location is selected and click OK.

 

12. Exit and restart Oracle SQL Developer for the changes to take effect.
13.

Expand the HR connection in the Connections tab. Expand the Tables node and select the CUSTOMERS_PART table.

 

14.

Select the Partitions Tab.

 

15.

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.

 

Back to Topic List

Adding a Context Menu

In the above examples, 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 example, 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.

In Oracle SQL Developer, select File > Open.

 

2.

Browse to the location where you have saved the files mentioned in the prerequisites. Select the readonly_menu.xml file and click Open.

 

3.

Double-click on the tab to maximize the editor.

 

4.

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..

 

5.

Notice the <prompt> and <confirmation> tags.

 

6.

To add the extension, select Tools > Preferences.

 

7.

Expand the Database node and select User Defined Extensions. Click Add Row.

 

8.

Click in the field under Type and select ACTION from the list.

 

9.

Click in the field under Location and click Browse.

 

10.

Browse the directory where your files are located and select the readonly_menu.xml file.

 

11.

Ensure that the location is selected and click OK.

 

12. Exit and restart Oracle SQL Developer for the changes to take effect.
13.

Select the HR connection and right-click to invoke the context menu. Scroll to the bottom and select Set Read Only.

 

14.

The title Set Read Only is displayed in the dialog header. Notice the Prompt. The text is from the following part of the xml file.

<prompt type="confirm">
<label>Confirm Setting Connection to Read Only.</label>
</prompt>

 

15.

Select the SQL tab. Find the equivalent SQL code in the XML file. Click Apply.

 

16.

The final Confirmation dialog is also driven by the XML file. Click OK.

17.

Expand the Tables node for the connection HR and select EMPLOYEES. Select the Data tab.

18.

Modify the FIRST_NAME for one of the records.

19. Select the Commit icon to commit the changes.

 

20.

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:

 Create a Database Connection
 Share Reports
 Add a Tab to Display Column Comments
 Review the XML File Structure
 Add a Tab to Describe Sub-partitions
 Add a Context Menu

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document