How to Add Tabs to SQL Developer

Kris has blogged about this, so you may have solved all your extra tab requirements already, but I have seen a few request for features that will make your lives easier, and that you can add into SQL Developer today, without waiting for us. So if you haven't worked out how to do this yet, then take a read.

Suppose you want to add a new tab to your tables definitions that shows all the column comments, or more partitioned information, or sub-partitined information. Today if you need this information, you might use SQL*Plus or the SQL Developer SQL Worksheet and you'd query the Dictionary Tables. You might might say something like:
SELECT COLUMN_NAME,
COMMENTS
FROM ALL_COL_COMMENTS
WHERE OWNER = :OBJECT_OWNER
AND TABLE_NAME = :OBJECT_NAME;

So let's put this code into an XML file:
Open a blank page in something like Notepad or Wordpad and add the following:

<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[Column Comments]]></title>
<query>
  <sql><![CDATA[select COLUMN_NAME, COMMENTS
from ALL_COL_COMMENTS
where owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Did you notice that same piece of SQL in the middle of the XML tags? Yup, that's our Column Comments SQL. Save this file to a sensible location. I have an Extensions folder, but you might want to add this to a folder under your Documents and Settings. Now open Oracle SQL Developer 1.1.2.25.79 and navigate to Tools -> Preferences. Expand the Database Node and select User Defined Extensions.
Select Add Row and use the drop-list under type to add a new EDITOR type. For the location, browse to your saved file location and select the file.

Shut down SQL Developer and when you restart it, navigate to a connection, expand your tables node and select a table. The new tab appears after your SQL tab and if your table has comments, you'll see them.

So that's easy, now you can do more: Let's look at the sub-partition request I had.

What do I need to know about Sub Partitions? You might want to start by describing the table to see the kinds of detail you are interested in. You'll see that if you describe all_table_subpartitions that it has table_owner, not just owner, like the previous query. I was interested in these columns:

Select PARTITION_NAME, SUBPARTITION_NAME,
HIGH_VALUE, HIGH_VALUE_LENGTH,
SUBPARTITION_POSITION, TABLESPACE_NAME
from ALL_TAB_SUBPARTITIONS
where table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME;

I'd suggest you try that in the SQL Worksheet first and then once again add that code into a new empty file.
The XML file would look something like this:

<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[My Sub Partitions]]></title>
<query>
  <sql><![CDATA[Select PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE,
HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME
from ALL_TAB_SUBPARTITIONS
where table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Once you have saved that file, add a new User Defined Extensions of Type EDITOR as you did before.
You'll need to restart SQL Developer to register the new extension. When you restart you should see the tabs as follows:

Let us know how that goes, what extensions you have added and let's add them to the SQL Developer Exchange!

Conferences or not...

Looking at the next few months, it looks like April, May, June is conference season, and that's just the spring conference season. I remember when conference season was Feb, June and November, now it seems that conference season is pretty much all the time.

The first one you might be aware of is Collaborate. This is in Las Vegas, starting on the 15th April. It looks like there's a lot on, so if you're in the area, take a look at the agenda and get on down. Not one I can make, so report back if you get to go!

The next one was to be held here in Europe. The EOUC was to be held in Amsterdam in early May, but we have been told and have now seen the announcements that this event has been cancelled.

This means that you really have to look out for Oracle Develop! You may remember events similar to these in the past, when Oracle organized 2-day events in lots of cities around the world. These events are focused on developers and should give you the technical detail you are looking for in your subject area.
There will be hands on sessions and an opportunity to meet members from the development organization, depending on which city you go to. I'll be doing 2 SQL Developer papers in London, in June. See the agenda, get registered! See you there.