Extending the SQL Developer functionality in JDeveloper 11g

Lucas Jellema 1
0 0
Read Time:5 Minute, 42 Second

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

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Extending the SQL Developer functionality in JDeveloper 11g

  1. Hi Lucas,

    Glad somebody actually reads my stuf ;-). Creating these XML files would be a lot easier if there was some more (detailed) documentation, because the Wiki’s are very poor on that subject. It takes a lot of trial-and-error to get the – more or less – desired output….

    Cheers
    Roel

Comments are closed.

Next Post

Code refactoring…

Steven Feuerstein wrote an entry on his Toadworld blog where he checks the refactoring capabilities of SQL-Developer. I am trying the same things in PL/SQL Developer to see if this does do the trick. Related posts: Uitnodiging: Kom kennismaken met AMIS ADF 11g – Invoke a Popup from a Popup […]
%d bloggers like this: