Extending the SQL Developer functionality in JDeveloper 11g

One thing that always draws me in tools, libraries and frameworks: what are the hooks, the backdoors, the extension points, the APIs, the event listener registration facilities etc. Today I came across a blog article by Roel Hartman, about showing Oracle Designer objects in the SQL Developer database navigator. I could not resist looking into the extension mechanism used for this. And of course the best way to understand this, is by doing it yourself. So in this article my brief description of how to extend the Database Navigator in JDeveloper (which is the same thing as in stand alone SQL Developer).

My extension is primitive and pointless in the extreme. It has the navigator show a new node type called Employees. The child nodes are the employees specified in table EMP. The interesting part of course is that you can any information found in the database – either the data dictionary or your own tables and views. This allows you to integrate Business Rules if they are defined in tables. Or Tasks, Issues, Projects and Team Members – if they are in tables. Or the results of calling WebServices – if these results are exposed through Views.

Creating the extension is nothing more complex than creating an XML file and registering it using a very simple interface inside JDeveloper. Creating the XML file is slightly more difficult, as there is no code completion or insight/schema validation for the XML file, and typos are made oh so easily. I had a few good resources to help me (see at the end of this blog).

Any query or set of related queries can be visualized like this in the database navigator. To achieve this, a few steps are required:

1. create an XML file in which the queries and nodes are configured
2. register this XML document as a user defined extension to the SQL Developer component in JDeveloper
3. restart JDeveloper to have the registration take effect
4. open the database navigator for a database connection to see the new extension in action

Step 3: Go to Tools, Preferences. In the Preferences navigator, select Database and under Database the Node User Defined Extensions. In the pane shown on the right hand side, press Add Row. Select NAVIGATOR as the type of extension – Report, Action and Editor are other options. Then browse to the XML document that contains the Extension Definition.

The XML document required for the Departments & Staff extension looks like this:

<?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="HumanResourceNode" weight="100.0"
              includesSyns="true">
    <folder>
      <!-- level1 -->
      <icon RSKEY="TABLE_FOLDER_ICON"/>
      <label RSKEY="Departments &amp; Staff"/>
      <queries>
        <!-- level2 -->
        <query minversion="8">
          <sql constrained="true">
            <![CDATA[SELECT ''||dname name, deptno FROM DEPT ]]>
          </sql>
        </query>
        <columns>
          <column filterable="true" sortable="true" id="NAME">
            <colName>
              <![CDATA[name]]>
            </colName>
          </column>
          <column filterable="true" sortable="true" id="DEPTID">
            <colName>
              <![CDATA[deptno]]>
            </colName>
          </column>
        </columns>
      </queries>
    </folder>
    <node>
      <!-- level2's details -->
      <icon RSKEY="OracleIcons.TABLE"/>
      <childType id="Level3" nodeType="Level3">
        <!-- Level3 -->
        <icon RSKEY="OracleIcons.COLUMN"/>
        <queries>
          <query>
            <sql>select ename name, empno from emp where deptno = :DEPTID</sql>
          </query>
          <columns>
            <column filterable="true" sortable="true" id="NAME">
              <colName>
                <![CDATA[name]]>
              </colName>
            </column>
            <column filterable="true" sortable="true" id="EMPID">
              <colName>
                <![CDATA[empno]]>
              </colName>
            </column>
          </columns>
        </queries>
      </childType>
    </node>
  </objectType>
  <objectType connType="Oracle" id="Level3" weight="">
    <node>
      <icon RSKEY="OracleIcons.COLUMN"/>
      <childType id="Level4" nodeType="Level4">
        <icon RSKEY="OracleIcons.TABLE"/>
        <queries>
          <query>
            <sql>select ename name , empno from emp where mgr = :EMPID</sql>
            <columns>
              <column id="NAME">
                <colName>
                  <![CDATA[NAME]]>
                </colName>
              </column>
              <column id="EMPID">
                <colName>
                  <![CDATA[EMPNO]]>
                </colName>
              </column>
            </columns>
          </query>
        </queries>
      </childType>
    </node>
  </objectType>
  <objectType connType="Oracle" id="Level4" weight="">
    <node>
      <icon RSKEY="OracleIcons.COLUMN"/>
      <childType id="Level5" nodeType="Level5">
        <icon RSKEY="OracleIcons.TABLE"/>
        <queries>
          <query>
            <sql>select ename name from emp where mgr = :EMPID</sql>
            <columns>
              <column id="NAME">
                <colName>
                  <![CDATA[NAME]]>
                </colName>
              </column>
            </columns>
          </query>
        </queries>
      </childType>
    </node>
  </objectType>
  <objectType connType="Oracle" id="Level5" weight="">
    <node>
      <icon RSKEY="OracleIcons.TABLE"/>
    </node>
  </objectType>
</navigator>

Note how the three connected queries – one for Departments, one for Employees and one for Subordinates – each define a Node Type. The child queries can use bind-variables that refer to the columns of the their immediate parent (for example select ename name from emp where mgr = :EMPID).

Resources

Oracle WIKI: http://wiki.oracle.com/page/SQL+Dev+SDK+How+To+create+an+XML+User+Defined+Extension?t=anon

Roel Hartman’s Blog – my original trigger – http://roelhartman.blogspot.com/2008/12/oracle-designer-extension-for-sql.html

Oracle by Example (Tutorial): http://www.oracle.com/technology/obe/sqldev_obe/extension/extensions.htm

Oracle WIKI on Childnodes: http://wiki.oracle.com/page/SQL+Dev+SDK:How+To+add+a+new+sub+folder+to+the+database+navigator?t=anon

Download the XML document for the Departments & Staff Extension: employeenavigator.zip