19 April 2007

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!

7 comments:

Ghassan said...

Sue,
how do you add these extensions to the sqldev exchange?

Mark said...

Both of your XML snippets are missing a right-square-bracket from the second CDATA chunk.

Sue said...

Ghassan,

I am ahead of my time. We want to have a node for adding these extensions to the Exchange. Once we get the next release out, we'll look into this more.

Mark,
Thanks for the heads up, I have corrected that syntax now.

Sue

Raj said...

Excellent feature enabling us to add new tabs with additional info as required.

Adrian said...

Hi Sue

I have used this for adding tabs for tables but also have the need to add a new tab for packages/procedures/functions. I'm not sure what the node= should be set to for these ones.

I.e. item type="editor" node="???????Node" vertical="true"

I couldn't find this on the web anywhere.

Regards,
Adrian

Philippe Gibone said...

Hi
First thanks for the samples, but where can I find the list of available variables (like :OBJECT_OWNER) and more generally the list of features we can use (list of item_type, of node ...)

Sue said...

The XSDs have been added to the Oracle Wiki for SQL Developer 2.1.

http://wiki.oracle.com/page/SQL+Dev+SDK+How+Tos

Sue