SQLDeveloper XML Extensions and auto-navigation

It’s official, England are now the second best cricket team in the British Isles !
After all, Scotland were dispatched with ease and as for Wales…they didn’t even make it to the Cricket World Cup.
OK, technically they did because they’re part of England for the purposes of cricket…although you’d be hard pressed to get them to admit it.
Ireland are, of course, some way in front having actually managed to actually win the odd game against Test Playing Nations.
Whilst it takes quite some effort to find silver lining in the cloud of English Cricket’s latest debacle, the same cannot be said if SQLDeveloper is your Oracle Database IDE of choice …

Why I use SQLDeveloper

Now, I’m well aware that each developer has their own favourite IDE. TOAD and PL/SQL Developer are fine tools in their own right, each with their own strengths. Which of these tools you swear by, or swear at, is always likely to be based on some fairly subjective criteria.

One of the main reasons I have for using SQLDeveloper is that it’s so easy to extend by means of a judicious application of XML, combined with a bit of SQL and/or PL/SQL.

Fun with Foreign Keys

As with all of the mainstream IDE’s, SQLDeveloper displays a table’s constraints as standard. However, what it doesn’t show is which tables have Foreign Keys to the table you’re looking at.

If, for example, you want to find out which tables have a Foreign Key to HR.COUNTRIES, you need to run a query like this :

select owner, table_name, constraint_name
from all_constraints
where constraint_type = 'R'
and (r_owner, r_constraint_name) in 
(
    select owner, constraint_name
    from all_constraints
    where constraint_type in ('U', 'P')
    and owner = 'HR'
    and table_name = 'COUNTRIES'
)
order by owner, table_name, constraint_name
/

OWNER                          TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
HR                             LOCATIONS                      LOC_C_ID_FK

SQL> 

Wouldn’t it be handy if you could somehow add a tab onto the table view in SQLDeveloper and have this information
pop-up for the table you’re looking at.
Well, funny you should say that….

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
		<query>
			<sql>
				<![CDATA[
					select owner, table_name, constraint_name
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
					(
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					) 
					order by owner, table_name, constraint_name 
				]]>
			</sql>
		</query>
	</item>
</items>

It may not be immediately obvious how this xml code is going to help us.
The answer is that it’s the SQLDeveloper extension that we’ve just written. Yep, just that one xml file.

It’s probably worth taking a closer look at the structure of this file in terms of the XML…

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Enter the Tab Name Here]]></title>
		<query>
			<sql>
				<![CDATA[Enter your query here]]>
			</sql>
		</query>
	</item>
</items>

If you want to add a node to a different object type, it seems that you just need to change the node attribute of the item tag as appropriate.
For example, specifying “Viewnode” should enable you to add a Tab to your Views.

Anyway, to add this to SQLDeveloper, we just need to do a bit of pointing and clicking…

To start with, save the above code into an xml file. I’ve called mine deps_fk.xml.

In SQLDeveloper, go to the Tools menu and select Preferences

In the tree on the left hand side expand the Database node. Then click on User Defined Extensions.

sqld_add_ext1

Now click the Add Row button at the bottom of the Right Hand Pane and you should see a new Row appear under the Type and Location headings like this :

sqld_add_ext2

If you now click in the row you’ve just created under the Type heading, you should get a drop-down list of values.
Select Editor from the list :

sqld_add_ext3

In the Location field, you just need to tell SQLDeveloper where your xml file is :

sqld_add_ext4

Finally, hit the OK button at the bottom of the window to save your changes and then re-start SQLDeveloper.

Now, if we open the HR.COUNTRIES table, we can see that there’s now an additional tab called Child Tables.
When we go to this tab, we are rewarded with :

sqld_tab1

This stuff has been done before. As well as this post from Tony Andrews, I also posted something similar several years ago.
I must be getting old, I’m starting to repeat myself !

Whilst all this is quite handy, wouldn’t it be good to be to be able to link directly to the Child table ?

Linking to other objects

Jeff Smith’s post here might be just what we’re looking for.

Using Jeff’s linking technique, we can improve our extension a bit…

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
		<query>
			<sql>
				<![CDATA[
					select owner,  
						'SQLDEV:LINK:'
						    ||owner||':TABLE:'||table_name
						    ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' table_name,
						constraint_name
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
					(
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					) 
					order by owner, table_name, constraint_name 
				]]>
			</sql>
		</query>
	</item>
</items>

Now when I run this, the table_name appears in blue.

sqld_ext_final1

By clicking it, I can navigate directly to the child table…

sqld_ext_final2

When the code is run, in this example, the string generated for the table_name column is :

SQLDEV:LINK:HR:TABLE:LOCATIONS:oracle.dbtools.raptor.controls.grid.DefaultDrillLink

You can link to all sorts of things using this technique. In fact, it would seem to be that you can link to any object that has a node in the SQLDeveloper Navigator Tree. If you want to play around, just select the string from dual in SQLDeveloper (using F9 to execute).

For example :

select 'SQLDEV:LINK:HR:TRIGGER:UPDATE_JOB_HISTORY:oracle.dbtools.raptor.controls.grid.DefaultDrillLink'
from dual;

…returns a link to the UPDATE_JOB_HISTORY trigger in the results grid.
Note that the link syntax is case sensitive in that the owner, object type and object name all need to be in uppercase for this to work.

Another point worth noting, specifying a type of PACKAGE takes you to the package header.
If you want to link to the body, then specify PACKAGE BODY.

Other SQLDeveloper Extension Types

There’s a whole bunch of stuff you can do to customise SQLDeveloper using nothing more than a bit of boilerplate xml.
In addition to Jeff’s blog, there is an Oracle Wiki which contains, among other things,

It’s definitely worth a look, especially if, like me, you need something to take your mind off the cricket.

Advertisements

5 thoughts on “SQLDeveloper XML Extensions and auto-navigation

  1. Hi,
    It doesn’t seem to work for a package body. The logging pane registers this:
    SEVERE 1134 7791 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 No registered action with id if mod(l_alt,2)=0 then at oracle.javatools.ui.RichHintLabel$HyperlinkL.hyperlinkUpdate(RichHintLabel.java:383)
    I am using the latest 4.1 final release. Did you have this as well ?

    • Michael,
      I’m currently running version 4.0.3.
      I don’t get this error.
      The format of the command I’m using to test this is :

      select 'SQLDEV:LINK:OWNER:PACKAGE BODY:PACKAGE NAME:oracle.dbtools.raptor.controls.grid.DefaultDrillLink'
      from dual;
      

      Note that the package name and ( I think), the owner, need to be in uppercase.
      I’ve played around with it and haven’t been able to re-create your issue so far.
      Is your query in the same format as this one ?

      Thanks,

      Mike

      • Hi Mike,

        I am using this:

        SELECT
        owner,
        NAME,
        type,
        line+1 line,
        ‘SQLDEV:LINK:’||upper(owner)||’:’||upper(TYPE)||’:’||upper(NAME)||’:’||(line + 1)||’:0:’||SUBSTR(text, 0, 100)||’:oracle.dbtools.raptor.controls.grid.DefaultDrillLink’ drill_to
        FROM dba_source
        WHERE
        UPPER(text) LIKE upper(‘%goto%’);
        Note that it doesn’t work in 4.1 with package body but with other types it does.

      • Michael,

        thanks for the update.
        It’s probably worth posting this on the SQLDeveloper OTN Forum.
        From my experience, the SQLDeveloper team are very proactive in fixing stuff like this.

        Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s