As I said in my previous post, one of the really cool things about SQLDeveloper is that it is easy to add your own custom extensions.
Confession – I’ve knocked this together using SQLDeveloper 1.5.4 running against Oracle XE on…er…Windows Vista.
I know, after all that stuff about how great Ubuntu is. Look, I just haven’t got round to installing Oracle yet. All these annoying little things like going to work keep getting in the way. Look It’s on the list OK ?
Anyway, back to SQLDeveloper.
Take table information as an example. By default SQLDeveloper will show constraint information for a table, but it won’t display the relationship between columns in the child and parent tables of a Foreign Key relationship.
Getting this information from the data dictionary can be done with the following query :-
SELECT cons.constraint_name, fkcols.column_name, pkcols.table_name as "Referenced Table", pkcols.column_name as "Referenced Column" FROM all_constraints cons, all_cons_columns fkcols, all_cons_columns pkcols WHERE cons.owner = fkcols.owner AND cons.table_name = fkcols.table_name AND cons.constraint_name = fkcols.constraint_name AND cons.r_owner = pkcols.owner AND cons.r_constraint_name = pkcols.constraint_name AND fkcols.position = pkcols.position AND cons.constraint_type = 'R' AND cons.owner = :OBJECT_OWNER AND cons.table_name = :OBJECT_NAME ORDER BY fkcols.column_name, fkcols.position
Simple enough, but it would be nice to see this for every table without having to type in and run this query every time.
To add this query as a permanent tab in SQLDeveloper, all you need to do is create an XML file containing the text of the query.
<items> <item type="editor" node="TableNode" vertical="true"> <title><![CDATA[FK Columns]]></title> <query> <sql> <![CDATA[SELECT cons.constraint_name, fkcols.column_name, pkcols.table_name as "Referenced Table", pkcols.column_name as "Referenced Column" FROM all_constraints cons, all_cons_columns fkcols, all_cons_columns pkcols WHERE cons.owner = fkcols.owner AND cons.table_name = fkcols.table_name AND cons.constraint_name = fkcols.constraint_name AND cons.r_owner = pkcols.owner AND cons.r_constraint_name = pkcols.constraint_name AND fkcols.position = pkcols.position AND cons.constraint_type = 'R' AND cons.owner = :OBJECT_OWNER AND cons.table_name = :OBJECT_NAME ORDER BY fkcols.column_name, fkcols.position]]> </sql> </query> </item> </items>
The are only two bits of this file you need to change :-
<title><![CDATA[FK Columns]]></title>
FK Columns will be the name of the tab displayed in SQLDeveloper.
The other bit obviously, is the SQL statement.
On the subject of the SQL, we’re using two standard SQLDeveloper placeholders – :OBJECT_OWNER and :OBJECT_NAME.
To add this extension into SQLDeveloper
1) Go to the Tools Menu and select Preferences
You will then be presented with the Preferences Dialog
2) From the Menu tree in the left-hand pane, select Database and then User Defined Extensions – as shown below
3) Click on the Add Row button.
4) Click on the row under the Type column and select EDITOR from the drop-down list.
5) Click on the row under the Location column and click the Browse button that appears.
6) Navigate to the XML file you’ve created and click OK.
You should now be looking at something like this
7) Now shutdown and re-start SQLDeveloper.
Now if you view a table, you should see your new tab ( FK Columns in this case).
And that’s it.
If you need any further information, there’s a fairly good ( and rather more comprehensive) tutorial at http://www.oracle.com/technology/obe/sqldev_obe/extension/extensions.htm
Update – a big thanks to Adrian Dillon ( see comment below), for pointing out my omission in the SQL on this one, which I’ve now corrected.
Nice example but there is a small problem in the SQL.
I have included a new line
AND fkcols.position = pkcols.position
so that it will match up the lines when there multiple parts to the FK.
New SQL is below.
SELECT cons.constraint_name, fkcols.column_name,
pkcols.table_name as “Referenced Table”,
pkcols.column_name as “Referenced Column”
FROM all_constraints cons, all_cons_columns fkcols,
all_cons_columns pkcols
WHERE cons.owner = fkcols.owner
AND cons.owner = fkcols.owner
AND cons.table_name = fkcols.table_name
AND cons.constraint_name = fkcols.constraint_name
AND cons.r_owner = pkcols.owner
AND cons.r_constraint_name = pkcols.constraint_name
AND cons.constraint_type = ‘R’
AND cons.owner = ‘SIS_OWNER’
AND cons.table_name = ‘PROPOSAL_INSTANCE_ITEM’
AND fkcols.position = pkcols.position
ORDER BY cons.constraint_name, fkcols.position, fkcols.column_name
Adrian,
you get the prize for spotting the deliberate mistake ( he said, desperately trying to retain an air of infallibility).
Incidentally, looking at this again, I’ve also noticed that I’ve got cons.owner=fkcons.owner in the predicate twice. Doh !
I tested your code suggestion and it works fine, so I’ll make the appropriate changes to this post.
Thanks again for the feedback.
Mike
Hi Mike, i added two user defined extensions of type ‘EDITOR’ (One for showing the child tables & the other one for showing parent tables) using 2 different XMLs. Somehow, only the first one is showing up not the second one. Is there a problem or bug in it?
Gopinadh,
Based on my own experience,
if the second file is not showing up there may be an issue in the xml.
If you remove the one that does show up, does the second one now appear ?
HTH
Mike
Here is the content of the second file. I named the file Parent_Table_References.xml.
Here is the content of the first file. I named the file Child_Table_References.xml.
@Tony, never mind. I found the issue myself. Stupid typo. I missed additional ] at the end, in the second XML. It is resolved now.
Another typo. @Mike, instead of Tony. 🙂
Gopinadh,
Glad you got it working in the end 🙂
Mike