Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Using XML Extensions in SQL Developer to Extend SYNONYM Support

6 Mins Read

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:

<?xml version="1.0" encoding="UTF-8"?>
<items>
  <item type="editor" node="TableNode" vertical="false">
    <title><![CDATA[SYNONYMS]]></title>
    <!-- add name for now - "should" have automagically used title -->
    <name><![CDATA[SYNONYMS]]></name>
    <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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

13 Comments

  2. Zane Reply

    Figuring out SQL Developer extensions has eluded me thus far. I can’t seem to find up-to-date documentation that relates to what I want to do. I’m starting to wonder if it’s possible.

    I guarantee I’m not the only one who has wanted some kind of auto-generated filter based on selected data from a table or result-set.

    A.K.A. I would like to highlight some data in the object viewer (Data view), and based on that selection, generate a “WHERE clause” or a filter that I can paste into the filter input of the current table or another table.

    If not on a copy event, a paste event would do perfectly. Or post-paste. I don’t really care. I would like to auto generate some kind of WHERE clause formatted like:

    [COLUMN NAME] IN (‘firstvalue’, ‘secondvalue’, ‘thirdvalue’)

    Or even without the column name part. Just a way to quickly format an IN (”,”) query.

    I can’t make sense of the documentation that I’ve found so far. Any help would be extremely appreciated.

  3. dnkb Reply

    Could this functionality be leveraged to auto-format integers to show commas a thousand grouping characters in data browser tabs and query result grids? Best would be if all of the existing functionality of these could be retained and this would be just and add-on…
    Thoughts?
    Thanks.

    • thatjeffsmith Post Author

      Hmmm, that should just be working, w/o any extension necessary. Might be a bug. I’m investigating, and I’ll let you know what I find out.

  4. Robert Reply

    I tried the first one (create synonym from context menu) and it worked like a charm. I can’t get the 2nd one (show all synonyms for the table) to work. I don’t get the extra tab. Please help. I am using SQL Developer 3.1.07.

    Thanks, Robert

    • Michael Reitsma

      Robert,

      A bit late… but you’ll need to replace the ’ with ‘ in your code.

  5. Tony Reply

    Cool stuff! Trying to convert our devs to use SQLdev (although plsql dev from AA is quite good aswell).
    One small thing: I would avoid using dba_ views (use all_) as not everybody has these privs.
    Cheers, Tony

    • thatjeffsmith Post Author

      Yeah, I hope folks aren’t relying on my actual code TOO much 😉 Use the views that work best for you and yours!

  6. Type Reply

    Seems like this feature can be pretty useful – but, is it documented? Personally, I’d like to take a good look at extending the editor functions, since I see this possibility at dropdown list, and a list of valid XML items for extensions with brief descriptions would also be nice. Can I find this info somewhere?

Write A Comment