ThatJeffSmith

Using XML Extensions in SQL Developer to Extend SYNONYM Support

This is a pretty old topic as far as SQL Developer goes. There are public articles going back to 2007 that show you how to extend the functionality in SQL Developer via SQL and XML. But I’m going to guess that for many of you that this is a new subject.

Knowing that extensions exist versus being able to create one is an important distinction. I want this post to help you realize both! We’re going to build two very simple extensions:

  1. A context menu item for Tables – Create SYNONYM
  2. A SYNONYMS page for the Table object viewer

These extensions will allow you to create a SYNONYM (PUBLIC or otherwise) with just a right-click and then also view all synonyms associated with the current table.

How XML Extensions Work

The SQL Developer product has a framework that allows for an XML file to be used to define or extend functionality. Your custom XML extension is ‘sucked into’ SQL Developer at startup after supplying the XML file location in the Preferences dialog -

After you have created your extension XML file, you'll 'load' it here

After you have developed your XML source, save it to a file, then tell SQL Developer where to find it. On a restart, SQL Developer will see your new extension and load it into the tool. If you have made any serious mistakes such that SQL Developer can’t parse the XML, the extension will fail to load.

Adding a Context Menu Item

Context menu is a fancy dancy developer term for the menu you see when you right-click on something. The context of the menu is determined by WHERE you click. Anyways, you can add your own operations to these menus in SQL Developer.

For example I am very lazy and I want the tool to automatically create synonyms for me. This is handy when you have very long or difficult to type schema and table strings. So I’m going to take 10 minutes to code an extension so I can save 30 seconds every time I want to build a synonym. How often do you build a synonym?

The good news is that I don’t need to know any JAVA code to build this, and I can do all of the development directly inside of SQL Developer.

First you need some XML code.

<items>
 <folder type="TABLE">
 <name>UserDefined ContextMenus</name> 
 <item TYPE="TABLE" reloadparent="true">
 <title>Create SYNONYM</title>
  <prompt type="check">
       <label>PUBLIC</label>
       <value>PUBLIC</value>
 </prompt>
 <prompt>
   <label>NEW SYNONYM NAME</label> 
 </prompt>
<sql>
 <![CDATA[CREATE #0# SYNONYM  #1# for "#OBJECT_OWNER#"."#OBJECT_NAME#"]]>
</sql>
<help>
 This action create a SYNONYM (optionally public) for the selected table.</help>
<confirmation>
   <title>Confirmation</title>
   <prompt>SYNONYM "#1#" for "#OBJECT_NAME#" has been created.</prompt>
 </confirmation>
</item>
</folder>
</items>
</prompt>

A few notes:

  • The XML tags are case sensitive, so don’t capitalize the SQL tag – won’t work!
  • ITEM TYPE=”TABLE” tells SQLDev where to put the context menu – any table context menu
  • Prompts are inputs, and you can have several kinds:
    • Check for checkbox
    • NULL for text input

You can also provide a list of values to choose from in an input. That’s achieved with a VALUE tag inside the PROMPT section. We’ll cover all of the different permutations of scripting and form development in later posts.

Input values are assigned variables and are numbered 0..N. So if you have 3 prompts, the values of each would be #0#, #1#, and #2#. These can be referenced in the SQL that is generated and executed via the form we are developing. #OBJECT_OWNER# and #OBJECT_NAME# are system variables that SQLDev uses to refer to the object that the context menu is fired from.

You can code confirmation and help dialogs if you want to be nice. Try to be nice.

Note that I did not quote the new synonym name – I don’t believe in case-sensitive object names in Oracle. But I DID quote them in the SCHEMA.TABLE portion of the statement as I could be needing to create a synonym on an object that IS case sensitive or contains a reserved word.

The value of #0# will be NULL if the box isn’t checked. So when I build my CREATE statement I can use the #0# value in there regardless. If the input is checked, the value will be assigned to ‘PUBLIC’, otherwise NULL text is inserted and it works just fine.

Ok, so assuming you don’t see any bugs in my code, save your worksheet to an XML file in a directory you can remember for at least 30 seconds.

Now go into the Preferences and add this file to the User Defined Extensions as an ‘Action.’

Restart SQL Developer and right-click on a table. You should see this

I add mine to a custom context menu so I know what's mine and what's default SQLDev

Click that and you’ll get the following modal dialog

There's even help if you need it!

And if you want to see the SQL being generated before it runs

The SQL behind the SQL and XML

You’ll get the normal confirmation dialogs. Success if it works, ORA- if it doesn’t with the chance to copy out your error text.

Now I want to see all the synonyms I have on my table

Now I could walk the SQLDev connection tree to the SYNONYM and PUBLIC SYNONYM items and filter on TABLE_NAME. But no, I’m lazy, remember? So I’m going to code up a new page attached to my table viewer called SYNONYMS that lists both my private and public synonyms AND lets me auto-navigate to said synonym if I want to drop or rename it.

So guess what, we’re going to need another XML file!

Here’s your code:

<items>
 <item type="editor" node="TableNode"  vertical="true">
    <title><![CDATA[SYNONYMS]]></title>
      <query>
         <sql><![CDATA[SELECT 'SQLDEV:LINK:'
     ||ao.owner
     ||':'
     ||'SYNONYM'
     ||':'
     ||ao.SYNONYM_name
     ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' SYNONYM_NAME,
     ao.owner
FROM dba_synonyms ao
WHERE TABLE_OWNER = :OBJECT_OWNER
AND table_name = :OBJECT_NAME
AND owner <> 'PUBLIC'
UNION ALL
SELECT 'SQLDEV:LINK:'
     ||ao.owner
     ||':'
     ||'SYNONYM'
     ||':'
     ||ao.SYNONYM_name
     ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' SYNONYM_NAME,
     ao.owner
FROM dba_synonyms ao
WHERE TABLE_OWNER = :OBJECT_OWNER
AND table_name = :OBJECT_NAME
AND owner = 'PUBLIC'
order by 1     ]]></sql>
      </query>
 </item>
</items>

And now some notes – we’re creating an ‘Editor’ for ‘Tables.’ This will create a new page called ‘SYNONYMS’ on any table I click on in SQL Developer. It’s basically just going to give me a grid and all I need to supply is the query to feed the rows into it. And I could do that with a quick SELECT, but let’s step it up a notch and make the SYNONYM names hot-clickable. So if I click on a name, SQLDev will automatically open that synonym. We just need to apply the lessons learned in my SQL Developer Query & Grid Tricks post.

File > Save to XML.

Go back to the preferences and add another user defined extension, but this time as an ‘Editor.’

Restart SQL Developer, click on a table. You should see a new page or tab at the very end.

Click on the synonym name to open that synonym in SQLDev

You can do much, much more with these extensions

I just got my hands on the source and I’m going through all of it to pick and choose what makes sense to share here and what makes sense to include in a ‘cookbook’ that we publish for our 3rd party developers that want to build their own extensions and share or sell them. So stay tuned! If you have a question about how to implement a specific feature via extension feel free to post that here or just email me.