Creating User Defined Extensions in Oracle SQL Developer

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to add XML extensions to Oracle 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.

Prerequisites

Before starting this tutorial, you should:

Install Oracle SQL Developer 2.1 early adopter from OTN here. Follow the readme instructions here.
Install the Oracle Database 10g and later.
Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
alter user hr identified by hr account unlock;
Download and unzip the sqldev.zip file that contains all the files you need to perform this tutorial.

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 the following steps, you can move straight to Sharing Reports.

.	Open Oracle SQL Developer.
.	In the Connections tab, right-click Connections and select New Connection. Note: If this tab is not visible, select View > Connections.
.	Enter the following and click Test: Connection Name: hr_orcl Username: hr Password: hr Select Save Password checkbox Hostname: localhost Port: 1521 SID: orcl
.	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.
.	Once the connection is saved, you will see the database in the list. When a connection is created, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

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:

.	Select the Reports tab. Note: If this tab is not visible, select View > Reports.
.	Expand each of the nodes. There are a selection of shipped reports under the Data Dictionary Reports node.
.	There are also shipped Data Modeler and Migration Reports. Any report you create is added under the User Defined Reports node.
.	To add a Shared Reports extension, select Tools > Preferences.
.	Expand the Database node and select User Defined Extension.
.	Click Add Row.
.	Click in the field under Type and select REPORT from the list.
.	Click in the field under Location and click Browse.
.	Browse to the directory where you unzipped the files from the Prerequisites and select the employees_reports.xml file. Then click Save.
.	Ensure that the location is selected and click OK.
.	Exit and restart Oracle SQL Developer for the changes to take effect.
.	The Shared Reports node is added in the Reports tab (View > Reports). Note: If you do not see the Shared Reports node, then the extension was not saved properly in the previous steps.
.	Expand the Shared Reports and EmployeeReports nodes and select Employees per Manager.
.	Select the hr_orcl connection, if prompted, to run the report.
.	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.

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 create a Column Comments tab for a table, perform the following steps:

.	Select the Connections tab and expand the hr_orcl connection. Note: If this tab is not visible, select View > Connections.
.	Expand the Tables node and select EMPLOYEES.
.	The initial tab displayed is the Columns tab. Select the Constraints tab and view the details displayed.
.	Click through the other tabs to view the details available for a table.
.	You can also display the column comments for a table in a tab. To do this you need to add an extension. Select Tools > Preferences.
.	As before, expand the Database node and select User Defined Extensions. Click Add Row.
.	Click in the field under Type and select EDITOR from the list.
.	Click in the field under Location and click Browse.
.	Browse to the directory where you unzipped the files from the Prerequisites and select the comment_tab.xml file.
.	Ensure that the location is selected and click OK.
.	Exit and restart Oracle SQL Developer for the changes to take effect.
.	Expand the hr_orcl connection from the Connections tab. Expand the Tables node and select EMPLOYEES.
.	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.

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

.	In Oracle SQL Developer, select File > Open.
.	Browse to the directory where you unzipped the files from the Prerequisites. Select the comment_tab.xml file and click Open.
.	Double-click on the tab to maximize the editor.
.	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.
.	The SQL query is querying the Data Dictionary for the column comments for the OWNER and OBJECT_NAME; in this example, the EMPLOYEES Table for the HR schema.

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:

.	In Oracle SQL Developer, select File > Open.
.	Browse to the directory where you unzipped the files from the Prerequisites. Select the cust_part.sql file and click Open.
.	Review the file. This script creates a partitioned table, CUSTOMERS_PART.
.	Select the hr_orcl connection from the drop-down list on the right of the SQL Worksheet.
.	Click the Run Script (F5) icon.
.	To add the extension, select Tools > Preferences.
.	Expand the Database node and select User Defined Extensions. Click Add Row.
.	Click in the field under Type and select EDITOR from the list.
.	Click in the field under Location and click Browse.
.	Browse to the directory where you unzipped the files from the Prerequisites and select the subpartition_tab.xml file.
.	Ensure that the location is selected and click OK.
.	Exit and restart Oracle SQL Developer for the changes to take effect.
.	Expand the hr_orcl connection in the Connections tab. Expand the Tables node and select the CUSTOMERS_PART table.
.	Select the Partitions Tab.
.	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.

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:

.	In Oracle SQL Developer, select File > Open.
.	Browse to the directory where you unzipped the files from the Prerequisites. Select the readonly_menu.xml file and click Open.
.	Double-click on the tab to maximize the editor.
.	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.
.	Notice the <prompt> and <confirmation> tags.
.	To add the extension, select Tools > Preferences.
.	Expand the Database node and select User Defined Extensions. Click Add Row.
.	Click in the field under Type and select ACTION from the list.
.	Click in the field under Location and click Browse.
.	Browse to the directory where you unzipped the files from the Prerequisites and select the readonly_menu.xml file.
.	Ensure that the location is selected and click OK.
.	Exit and restart Oracle SQL Developer for the changes to take effect.
.	Select the hr_orcl connection and right-click to invoke the context menu. Scroll to the bottom and select Set Read Only.
.	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>
.	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. Click OK.
.	Expand the hr_orcl connection and expand the Tables node. Select EMPLOYEES. Select the Data tab.
.	Modify the FIRST_NAME for one of the records.
.	Select the Commit icon to commit the changes.
.	You are not able to commit the records due to the READ ONLY transaction.