Purpose

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

Time to Complete

Approximately 30 minutes

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.
This tutorial demonstrates the ease of which XML defined extensions can be created. Outlining how SQL Developer can be extended to suit your needs without the need to write java.

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.
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 xml_simple_tablespace into your working directory (i.e. d:\wkdir)
ftp://ftp.oracle.com/svrtech/outgoing/sqldev_sdk/xml_simple_tablespace.zip

Extension Framework

SQL Developer database navigator, database object editors, database object actions and reports can be extended easily using XML.

TODO : Expose the xsd files
The XML for each type of extension (NAVIGATOR, EDITOR,ACTION,REPORT) can be found here in xsd format.
Use these as a reference after completing the examples below.

• navigator.xsd
• editor.xsd
• action.xsd
• report.xsd

The XML extension can be added to SQL Developer using the
Tools -> Preferences. Expand the Database node, and select User Defined Extensions.



Add a new Row for each XML extension and select the extension type and location of the XML extension file.
SQL Developer needs to be restarted for the extension to take affect

Extend the Navigator

This example demonstrates the ability to define a new node for the database navigator using XML.

<?xml version="1.0" encoding="windows-1252" ?>
<navigator RESOURCE_FILE="oracle.dbtools.raptor.navigator.OracleNavigatorResource"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="navigator.xsd">

<objectType connType="Oracle" id="TABLESPACE" weight="100.0" includesSyns="true">

<folder>

<icon RSKEY="TABLE_FOLDER_ICON"/>

<label RSKEY="Tablespaces"/>

<queries>

<query minversion="8">

<sql constrained="true">

<![CDATA[SELECT tablespace_name FROM USER_TABLESPACES]]>

</sql>

</query>

<columns>

<column filterable="true" sortable="true" id="TABLESPACENAME">

<colName>

<![CDATA[TABLESPACE_NAME]]>

</colName>

</column>

<column filterable="true" sortable="true" id="NAME">

<colName>

<![CDATA[TABLESPACE_NAME]]>

</colName>

</column>

</columns>

</queries>

</folder>

<node>

<icon RSKEY="OracleIcons.TABLE"/>

</node>

</objectType>

</navigator>


Copy the code above and paste it into a new file such as tablespace_nav.xml.

• Open Oracle SQL Developer, select Tools -> Preferences. Expand the Database node, and select User Defined Extensions.
  
• Click Add Row, click in the Type field, and select NAVIGATOR from the list.
• Click in the Location field, and click Browse to add the location of the tablespace_nav.xml file.
• Restart Oracle SQL Developer to register this new extension.

You should now see that you can browse a Tablespace node in the databases navigator.

Although the list of tablespaces is now available in the database navigator, nothing shows up on the right hand side when a tablespace is selected.
The next section outlines how to create a new Editor for our Tablespace nodes.

Extend the Editors

This example demostrates the ability to define a new editor for a node using XML.
Lets display some information about the select tablespace.

<?xml version="1.0" encoding="UTF-8"?>
<displays>

<display type="editor" style="null" enable="true" class="Oracle#TABLESPACE" objectType="TABLESPACE">

<name><![CDATA[Details]]></name>
<query>

<sql>

<![CDATA[SELECT * FROM USER_TABLESPACES WHERE UPPER(TABLESPACE_NAME) =UPPER(:TABLESPACENAME)]]>

</sql>

</query>
<CustomValues>

<TYPE>vertical</TYPE>

</CustomValues>

</display>

</displays>

Copy the code above and paste it into a new file such as tablespace_editor.xml.

• Open Oracle SQL Developer, select Tools -> Preferences. Expand the Database node, and select User Defined Extensions.
  
• Click Add Row, click in the Type field, and select EDITOR from the list.
• Click in the Location field, and click Browse to add the location of the tablespace_editor.xml file.
• Restart Oracle SQL Developer to register this new extension.

You should now see that you can browse a Tablespace node in the databases navigator and when you select a tablespace its details are displayed in an "editor" on the right hand side.

Now we can browse the tablespaces and view their details. Lets create a new action so we can manage them more effectively.

Extend the Actions

This example demonstrats the ability to define a new action using XML.
The XML below defines a rename action for Tablespaces

<?xml version="1.0" encoding="UTF-8"?>
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="dialogs.xsd">

<item type="TABLESPACE" reload="true" removeFromParent="true">

<title>Rename</title>
<iconName>oracle.ide.resource.IdeArb:RENAME_ICON</iconName>

<prompt>

<label>New Column Name</label>

</prompt>

<prompt type="confirm">

<label><![CDATA[Are you sure you want to rename this TABLESPACE?]]></label>

</prompt>

<sql>

<![CDATA[ALTER TABLESPACE #TABLESPACENAME# RENAME TO #0#]]>

</sql>
<help>Renames the selected tablespace.</help>
<confirmation>

<title>Confirmation</title>
<prompt>Procedure #TABLESPACENAME# has been renamed to #0#</prompt>

</confirmation>

</item>

</items>

Copy the code above and paste it into a new file such as tablespace_action.xml.

• Open Oracle SQL Developer, select Tools -> Preferences. Expand the Database node, and select User Defined Extensions.
  
• Click Add Row, click in the Type field, and select ACTION from the list.
• Click in the Location field, and click Browse to add the location of the tablespace_action.xml file.
• Restart Oracle SQL Developer to register this new extension.

Now you can right click on a tablespace in the navigator and rename it, if you have the priveleges.

Extend the Reports

This example demonstrates the ability to define a new Report using XML

Reports are the easiest extension to create. Simply define your report inside SQL Developer using a query and then export it to an XML file.
This Report XML file can then be shared between users. Heres a report that lists the tablespaces and their initial extents in a pie chart.

<?xml version="1.0" encoding="UTF-8" ?>
<displays>

<display id="746a9173-011b-1000-8011-0aa970397d7a" type="" style="Chart" enable="true">

<name><![CDATA[Tablespace Initial Extent]]></name>
<description><![CDATA[Tablespace Initial Extent]]></description>
<tooltip><![CDATA[Tablespace Initial Extent]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>

<LEGEND>true</LEGEND>
<STYLE>Default</STYLE>
<THREED>true</THREED>
<GRID>true</GRID>
<GRADIENT>true</GRADIENT>
<TYPE>PIE_MULTI</TYPE>

</CustomValues>
<query>
<sql><![CDATA[select 'Tablespaces', tablespace_name, initial_extent from user_tablespaces]]></sql>
</query>

</display>

</displays>

Note that the above file was generated by SQL Developer after exporting a user defined report. When loaded into SQL Developer it will be available under the
Shared Reports folder


Copy the code above and paste it into a new file such as tablespace_initial_extents_report.xml.

• Open Oracle SQL Developer, select Tools -> Preferences. Expand the Database node, and select User Defined Extensions.
  
• Click Add Row, click in the Type field, and select REPORT from the list.
• Click in the Location field, and click Browse to add the location of the tablespace_initial_extents_report.xml file.

Conclusion

Hopefully the above examples have outlined how to create your own SQL Developer extensions.
Please add to this wiki any notes you would like to share regarding SQL Developer extension framework.

Resources

Similar, Oracle By Example paper for SQL Developer 1.2.1
http://www.oracle.com/technology/obe/sqldev_obe/extension/extensions.htm

Extending SQL Developer 1.2.1
http://www.oracle.com/technology/oramag/oracle/07-jul/o47sql.html