User-Defined Context Menus in SQLDeveloper

“What are those birds ?”, Deb asked as we lay drowsing by the pool. “Must be seagulls”, I replied confidently.
We may have been in Tenerife rather than Southend, but they were definitely gulls, and we were right next to the sea.
“They’re not seagulls”, Deb stated emphatically, “if they were, they’d be squawking like a bunch of Welsh women fighting over the last pair of shoes in the sale!”
I forbore to comment on this. After all, I’d had recent, painful, experience of Welsh squawking the previous evening.
We’d arrived at the hotel, just in time to watch England come second in the Rugby…to Wales.
Let me tell you squawking wasn’t the half of it.
As usual my emergency backup nationality ( born in Auckland) was no defence against the joyous derision pouring forth from my better half.

Fortunately, the Spanish aren’t big on Rugby so I’ve had a week of relative quiet on that front.
As you’ve no doubt noticed by now, I’m not much of an ornithologist, so I’ve had to find something else to help while away the long hours beside the pool.

Step forward something that I’ve been puzzling over for some time, namely, just what options are available if you want to put together a User-Defined Context Menu for SQLDeveloper.

About now, someone is bound to mention Java. However, my Java is more rusty than a 1973 Ford Cortina that’s been parked in Cardiff Bay for two weeks.

Besides, it’s perfectly possible to knock up some fairly respectable SQLDeveloper extensions using just XML and some SQL and PL/SQL. However, when it comes to Context Menus there doesn’t seem to be one single place for definitive documentation.
Therefore, after much trial and error, I’m going to take this opportunity to set out the options that I have managed to get working, together with examples.

What I’m going to cover is :

  • How to add a context menu to SQLDeveloper
  • Where on the Navigator Tree you can add it
  • How to get more than one item on a Context Menu
  • The various types of input field you can code
  • How to use Context Menus to execute SQL statements and PL/SQL blocks

Before we go any further, I think I should state that these examples were written and tested on SQLDeveloper 4.0.3 running against an Oracle XE 11g database.

Adding a context menu

First of all, we need some code to create the menu we’re going to add.
We’ll start off with a fairly simple example in which the user enters their name and receives a cheery greeting…

<items>
    <folder type="USER">
        <name>Simple Custom Menu Demo</name> <!-- Name of the menu as it will appear in the right-click menu list -->
        <item>
            <title>Insert Name Here</title> <!-- Menu Item -->
            <prompt>Enter your Name :</prompt>
            <sql><![CDATA[select '#0#' from dual]]></sql> <!-- Statement won't do much...but will tell us what value has been entered -->
            <confirmation> <!-- After the action is completed, pop-up a cheery greeting -->
                <title>Welcome Message</title>
                <prompt>Hello, #0#... have you heard about Wales thrashing England in the Rugby ?</prompt>
            </confirmation>
            <help>This is a simple demonstration of a User Defined Menu Item</help>
        </item>
    </folder>
</items>

I’ve saved this to a file called simple.xml.
It will generate a menu called Simple Custom Menu Demo with a single entry called Insert Name Here.
The sql code doesn’t actually do anything but it’s there so that the menu item will work.

Available Tree Nodes for the Menu

The first thing to notice about this script is the intended location.
In the course of my research on this subject, I’ve noticed that virtually all of the examples I’ve seen are for adding the menu to the Table node.
So, just because we can, we’re going to put this on the Other Users node of the SQLDeveloper Object Navigator Tree.

Incidentally, I found a fairly comprehensive list of possible locations here.
Since Oracle appear to be doing a large-scale re-organisation of their content at the moment, and that this link may disappear at any time as a consequence, I’ve taken the liberty of reproducing the list of possible menu locations :

  • APEX[_FOLDER]
  • CATALOG (schema)
  • COLUMN
  • CONNECTION
  • DATABASE LINK
  • DIRECTORY[_FOLDER]
  • FUNCTION[_FOLDER]
  • INDEX
  • JAVA[_FOLDER]
  • JOBS[_FOLDER]
  • MATERIALIZED VIEW[_FOLDER]
  • MATERIALIZED VIEW LOG
  • PACKAGE[_FOLDER]
  • PACKAGE BODY
  • PACKAGE_PARENT
  • PROCEDURE[_FOLDER]
  • PUBLIC DATABASE LINK
  • PUBLIC SYNONYM
  • PUBLIC_SYNONYM_FOLDER
  • RECYCLEBIN
  • SEQUENCE
  • SYNONYM[_FOLDER]
  • TABLE[_FOLDER]
  • TRIGGER[_FOLDER]
  • TYPE[_FOLDER]
  • USER[_FOLDER]
  • VIEW[_FOLDER]
  • XDB_SUBFOLDER
  • XML SCHEMA

Adding the menu to SQLDeveloper

To add our new menu :

  1. Fire up SQLDeveloper and navigate to the Tools/Preferences Menu.
  2. Expand the Database node in the tree and click on User Defined Extensions
  3. Click on the Add Row button
  4. Click on the Type field of the newly created row and select ACTION from the drop-down
  5. add_menu1

  6. Click on the Location field of the new row, hit the Browse button and select our file – simple.xml
  7. Finally, click OK.

add_menu2

SQLDeveloper reads any user-defined extensions when it starts up so any changes we make will only be effective once we re-start the tool.

Reviewing your handiwork

After re-starting, navigate to the Other Users node in the tree.
Right-click on a user (any user) and we can see our Menu :

user_menu1

Click on the Insert Name Here Menu Item and we’re presented with a dialog :

enter_name1

Click on the Help button and you’ll see the text from the help tags in our file is displayed

enter_name2

Type in your name. If you then click on the SQL tab, you will see that SQLDeveloper has picked up the text that you’ve entered :

enter_name3

Finally, hit Apply and you will be rewarded with evidence that Deb has collaborated on this code…

enter_name_output

OK, this example doesn’t really do much… apart from rub salt in the wound. However, if you were to have a DDL or DML statement, or even a PL/SQL block in the tags it would be able to execute code based on user input.
We’ll look at some examples of this later in the post. In the meantime, we’re going to explore the different input methods that we can present to the user.

Once again, the examples of Custom Menus that I’ve seen are all single item menus. We’re going to expand our menu to contain several items, starting with…

User Confirmation

Now, ideally, the code for each menu item would be in it’s own file. Unfortunately, I couldn’t find a way to make this work.
Separate files with the same menu name resulted in the Menu being listed twice. Therefore, we’re going to add our new menu item into the existing file.
On the plus side, this means we won’t need to add the menu as an extension to SQLDeveloper again. However, we will need to re-start SQLDeveloper each time we make a change in order to see it.

In simple.xml, we’re adding the following code :

        <!-- Our second menu item - a confirmation message -->
        <item>
            <title>Are you sure about that ?</title>
            <prompt type="confirm">
                <label>Are you sure you want to kick to touch ?</label>
            </prompt>
            <sql><![CDATA[select null from dual]]></sql>
            <confirmation><prompt>Ha ha, Wales won the lineout...and the game!</prompt></confirmation>
            <help>Example of a confirmation prompt</help>
        </item>

Note that we’ve specified an attribute value in the prompt tag -i.e. type=”confirm”. This time, rather than presenting a field for user input, this extension will simply show the label text for the prompt and give the user the option to continue or cancel.

The entire file now looks like this :

<items>
    <folder type="USER">
        <name>Simple Custom Menu Demo</name> <!-- Name of the menu as it will appear in the right-click menu list -->
        <item>
            <title>Insert Name Here</title> <!-- Menu Item -->
            <prompt>Enter your Name :</prompt>
            <sql><![CDATA[select '#0#' from dual]]></sql> <!-- Statement won't do much...but will tell us what value has been entered -->
            <confirmation> <!-- After the action is completed, pop-up a cheery greeting -->
                <title>Welcome Message</title>
                <prompt>Hello, #0#... have you heard about Wales thrashing England in the Rugby ?</prompt>
            </confirmation>
            <help>This is a simple demonstration of a User Defined Menu Item</help>
        </item>
        <!-- Our second menu item - a confirmation message -->
        <item>
            <title>Are you sure about that ?</title>
            <prompt type="confirm">
                <label>Are you sure you want to kick to touch ?</label>
            </prompt>
            <sql><![CDATA[select null from dual]]></sql>
            <confirmation><prompt>Ha ha, Wales won the lineout...and the game!</prompt></confirmation>
            <help>Example of a confirmation prompt</help>
        </item>
    </folder>
</items> 

After re-starting SQLDeveloper, if we now navigate to our menu, we can see the second option :

user_menu2

Click on the “Are you sure about that ?” option and we’ll get our request for confirmation :

confirm1

Click on Cancel and the menu item exits. If you click on Apply however…

confirm_output

For the sake of brevity, from this point on I’ll just include the code for each individual menu item as I cover it.
I will provide a full listing once we’ve finished adding items.

For the sake of marital harmony, I have attempted to escape from the shadow of Welsh patriotism and listen to the radio…

Radio Buttons

After much trial and error, I can confirm that, if you want to use radio buttons in your menu items, this should do the trick :

        <!-- A radio button example -->
        <item>
            <title>Preferred Station</title>
            <prompt type="radio">
                <label>Select your station</label>
                <value><![CDATA[STATIC:Classic FM:Rock Radio:BBC World Service]]></value>
            </prompt>
            <sql><![CDATA[select '#OBJECT_FOLDER_TYPE#' from dual]]></sql>
            <confirmation><prompt>You're listening to #0# where no-one talks about Rugby.</prompt></confirmation>
        </item>

Aside from the radio button itself (prompt type=”radio”), I’ve introduced a couple of new concepts here.
First of all, we have the value tag, which contains a static list of values. In this context, each of these values will be presented as a radio button within
the group. In other contexts, you can use this value syntax to provide items in a drop-down list. We’ll come onto that in a bit.

Standard Substitution Variables

You’ll also notice that, whilst it still doesn’t do anything very interesting, the sql references a substitution variable called #OBJECT_FOLDER_TYPE#.
Once again, the examples you see of SQLDeveloper extensions tend to reference #OBJECT_OWNER# and #OBJECT_NAME#.
From the link I mentioned earlier, it would appear that there are other standard substitution variables available, depending on the context you’re in :

  • COLUMN_NAME (or CHILD_NAME)
  • CONN_TYPE (connection type)
  • CONNECTION_NAME
  • OBJECT_FOLDER_TYPE
  • OBJECT_ID
  • OBJECT_NAME
  • OBJECT_OWNER (schema)
  • OBJECT_TYPE

NOTE – I haven’t experimented with all of these so I can’t say for certain where and when they are appropriate, or even if they all work as you’d expect.

Any way, re-starting SQLDeveloper again, we select our Menu and then the new option, Preferred Station and we see :

radio1

Now, if we click on the SQL tab, we can see that the #OBJECT_FOLDER_TYPE# has been evaluated :

radio_sql

Back to the Prompts tab, select a station, click Apply and …

radio_output

Checkboxes

Next up, a checkbox example…

<item>
    <title>Bird Watching</title>
    <prompt type="check">
        <label>Seagull</label>
        <value>sea</value>
    </prompt>
    <sql><![CDATA[select '#0' from dual]]></sql>
    <confirmation><prompt>That was a #0#gull that just flew past</prompt></confirmation>
</item>

We set the prompt to have type=”check”. In this context the value will be assigned to #0# if the checkbox is checked. Otherwise #0# will be null.
NOTE: the first input value is assigned to #0#, the second #1# and so on.
To test this, navigate to the Bird Watching menu item and…

bird_watch1

If we look at the SQL tab without checking the box we get

bird_watch_null

…if we do check the box then the value is included in the statement …

bird_watch_checked

Interestingly, the variable is never read in the confirmation message. I’m not sure why this is. However, it shouldn’t matter too much as you’re likely to want the variable value to be available at the point the code executes. We’ll see an example of this in action later on.

In the meantime though, let’s round off the various input types available to us (well, those that I’ve managed to get working), with…

Drop-down lists

I may be on holiday bit my oh-so-British obsession with the weather remains…

<item>
    <title>Normal drop-down</title>
    <prompt>
        <label>Weather Forecast</label>
            <value><![CDATA[STATIC:RAINY:SUNNY:SCORCHIO]]></value>
    </prompt>
    <sql>select '#0#' from dual</sql>
    <confirmation><prompt>Today will be #0#</prompt></confirmation>
</item>                        

Notice that we don’t specify a type for the prompt tag on this occasion. What makes this field a drop-down list is the value tags.

Open this up and we can see…

weather1

Select a value, hit Apply and…

weather_out

That about wraps it up for the various input prompts that I’ve managed to get working. Our simple.xml file, in it’s entirety, now looks like this :

<items>
    <folder type="USER">
        <name>Simple Custom Menu Demo</name> <!-- Name of the menu as it will appear in the right-click menu list -->
        <item>
            <title>Insert Name Here</title> <!-- Menu Item -->
            <prompt>Enter your Name :</prompt>
            <sql><![CDATA[select '#0#' from dual]]></sql> <!-- Statement won't do much...but will tell us what value has been entered -->
            <confirmation> <!-- After the action is completed, pop-up a cheery greeting -->
                <title>Welcome Message</title>
                <prompt>Hello, #0#... have you heard about Wales thrashing England in the Rugby ?</prompt>
            </confirmation>
            <help>This is a simple demonstration of a User Defined Menu Item</help>
        </item>
        <!-- Our second menu item - a confirmation message -->
        <item>
            <title>Are you sure about that ?</title>
            <prompt type="confirm">
                <label>Are you sure you want to kick to touch ?</label>
            </prompt>
            <sql><![CDATA[select null from dual]]></sql>
            <confirmation><prompt>Ha ha, Wales won the lineout...and the game!</prompt></confirmation>
            <help>Example of a confirmation prompt</help>
        </item>
        <!-- A radio button example -->
        <item>
            <title>Preferred Station</title>
            <prompt type="radio">
                <label>Select your station</label>
                <value><![CDATA[STATIC:Classic FM:Rock Radio:BBC World Service]]></value>
            </prompt>
            <sql><![CDATA[select '#OBJECT_FOLDER_TYPE#' from dual]]></sql>
            <confirmation><prompt>You're listening to #0# where no-one talks about Rugby.</prompt></confirmation>
        </item>
        <!-- A checkbox -->
        <item>
            <title>Bird Watching</title>
            <prompt type="check">
                <label>Seagull</label>
                <value>sea</value>
            </prompt>
            <sql><![CDATA[select '#0#' from dual]]></sql>
            <confirmation><prompt>That was a #0#gull that just flew past</prompt></confirmation>
        </item>
        <!-- Drop-down list of static values -->
        <item>
            <title>Normal drop-down</title>
            <prompt>
                <label>Weather Forecast</label>
                    <value><![CDATA[STATIC:RAINY:SUNNY:SCORCHIO]]></value>
            </prompt>
            <sql>select '#0#' from dual</sql>
            <confirmation><prompt>Today will be #0#</prompt></confirmation>
        </item>                        
    </folder>
</items> 

Menu Item that Runs a SQL Statement

Now we’ve looked at how to create the various input fields, it would be good to find some practical application for all of this.
It’s fair to say that most generic actions ( e.g. compiling a PL/SQL object) are already available in the tool. Therefore, anything you do want to do in a custom menu is more likely to be specific to your application or database.
What follows therefore are examples of executing SQL and PL/SQL from a Menu item. Whilst the functionality may well already be available, the techniques used should provide a guide for how to implement something a bit more interesting.

To start with then, We’ll create a Menu on the Table node.
The first action we’re going to implement is creating a backup copy of a table with a _BKP suffix.
We’ll give the user the option to create this copy with or without data.

Our new menu file is called custom_table.xml :

<items>
    <folder type="TABLE">
        <name>Custom Table Actions</name>
        <item>
            <title>Create Backup of Table</title>
            <prompt type="radio">
                <label>Copy Data ?</label>
                <value><![CDATA[STATIC:YES:NO]]></value>
            </prompt>
            <sql>
                <![CDATA[
                    create table #OBJECT_NAME#_bkp as 
                        select * 
                        from #OBJECT_NAME#
                        where 1 = case '#0#' when 'YES' then 1 else 2 end
                ]]>
            </sql>
            <confirmation>
                <prompt>Copy of #OBJECT_NAME# created.</prompt>
            </confirmation>
            <help>Create a copy of this table with a _BKP suffix</help>
        </item>
    </folder>
</items>

You’ll notice that here, the statement between the tags IS doing something.
We add the menu as a User Defined Extension in SQLDeveloper in the same way as before.
Now, when we right-click on the EMPLOYEES table…

copy_tab1

Selecting the Create Backup of Table menu option we’re presented with…

copy_tab2

If you change the radio button you’ve selected and then look at the SQL tab, you can see the change is reflected in the statement that will be run.

copy_tab_sql

Incidentally, it looks like the indentation used in the xml file gets pulled through into the SQL tab.

Once you’ve hit Apply and received the confirmation message, you can check that the new table now exists…

select *
from user_tables
where table_name = 'EMPLOYEES_BKP'
/

…or simply refresh the tables view in the Object Navigator.

backup_tab

Let’s just consider what’s happened for a moment.
The backup table has been created in the HR schema only because I was connected as HR when I ran the menu.
Connected as another user, the table would have been created in that user’s schema.

Of course, it may be that this is by design and you want to hold all the backup tables you create in a schema separate from the application schema. If this isn’t what you’re after then you can use the #OBJECT_OWNER# substitution variable to ensure that the backup table is created in the same schema as the table you’re backing up.

One other point to note is that we’ve just issued a DDL statement. In the normal course of events, this will commit any pending transaction in the current session.
So, the question is – does our menu Action run in the same session as our SQLDeveloper connection ?
To answer this…

A Menu Item that executes a PL/SQL block

At some point we’re going to want to tidy up all those backup tables we have lying around.
This time, we’ll create the Menu on the USER node…

<items>
    <folder type="USER">
        <name>Cleanup Table Copies</name>
        <item>
            <title>Drop Backup Tables</title>
            <prompt type="confirm">
                <label>Click Apply to drop all _BKP tables from this schema</label>
            </prompt>
            <sql>
                <![CDATA[
                    begin
                        for r_table in
                        (
                            select table_name
                            from all_tables
                            where owner = '#OBJECT_OWNER#'
                            and table_name like '%\_BKP' escape '\'
                        )
                        loop
                            execute immediate 'drop table #OBJECT_OWNER#.'||r_table.table_name;
                        end loop;
                    end;
                ]]>
            </sql>
            <confirmation>
                <prompt>Backup tables dropped</prompt>
            </confirmation>
            <help>Drop all tables in this schema that have names ending _BKP</help>
        </item>
    </folder>
</items>

This file is called cleanup_tabs.xml and we add it as a User Defined Menu in the same way as before.

To come answer our question regarding whether the Menu Action runs in the same session as the SQLDeveloper connection, I’ve run the following before calling the menu…

create table should_be_empty( dummy varchar2(1));

insert into should_be_empty values( 'Y');

select * from should_be_empty;

So, we now have one record in our table but the transaction containing the INSERT statement uncommitted.

before_cleanup

If we now run our new menu ….

cleanup_menu

cleanup1

cleanup2

Well, we’ve dropped the tables. However, if we now try to rollback our insert…

rollback;

select * from should_be_empty;

…we can see that the change has already been committed…

after_cleanup1

From this, we can conclude that a Menu Action executes in the same session as the SQLDeveloper connection.

Therefore, it’s probably a good idea to isolate any such actions in their own transactions.
We can do this by using an Autonomous Transaction…

<items>
    <folder type="USER">
        <name>Cleanup Table Copies</name>
        <item>
            <title>Drop Backup Tables</title>
            <prompt type="confirm">
                <label>Click Apply to drop all _BKP tables from this schema</label>
            </prompt>
            <sql>
                <![CDATA[
                    declare
                        pragma autonomous_transaction;
                    begin
                        for r_table in
                        (
                            select table_name
                            from all_tables
                            where owner = '#OBJECT_OWNER#'
                            and table_name like '%\_BKP' escape '\'
                        )
                        loop
                            execute immediate 'drop table #OBJECT_OWNER#.'||r_table.table_name;
                        end loop;
                    end;
                    -- Make sure that the transaction completes
                    commit;
                    exception when others then
                        -- ...one way or the other
                        rollback;
                        raise;
                ]]>
            </sql>
            <confirmation>
                <prompt>Backup tables dropped</prompt>
            </confirmation>
            <help>Drop all tables in this schema that have names ending _BKP</help>
        </item>
    </folder>
</items>

If we now re-run the same test as before, we can see that the insert statement can be rolled back as the DDL statements have all executed in a separate transaction…

after_cleanup2

References

There are several places where you can find more information about SQLDeveloper User Defined Extensions, some of which I’ve listed below.

Jeff Smith has written a number of articles on this – for example, How To Add Custom Actions to your User Reports.

There’s an excellent Oracle Magazine article by Sue Harper on this topic. Although it was written a while ago, it’s still relevant now.

I’ve also found documentation on the XML Schema that SQLDeveloper uses.
Once again, this is an older version (3.1), but may prove helpful.

Lastly, there is the official documentation for SQLDeveloper.

Conclusion

A major advantage of SQLDeveloper is the ability to customise it to suit your needs without needing to step outside of the core competencies of your average PL/SQL programmer.
With a little XML and judicious use of Autonomous Transactions, it’s possible to write powerful, yet well-presented custom extensions, even in the face of National Sporting Humiliation !

Advertisements

5 thoughts on “User-Defined Context Menus in SQLDeveloper

  1. Pingback: » More on XML Extensions in Oracle SQL Developer

  2. Hi! I have an editor that displays the triggers defined on a table. I am using “display” tag to accomplish this. In between “display” tag I have added a context menu using “item” tag with which I am trying to disable a selected trigger. I am passing as parameter “#TRIGGER_NAME#”, but this is not replaced with the trigger name value when I am clicking the action menu. If I take the whole XML code and make it a report instead of a editor, then it works; the action menu knows to replace #TRIGGER_NAME# with a real trigger name value. Why is that? Who can I sent value of #TRIGGER_NAME# to an action menu from an editor associated with a table? Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s