Oracle SQL developer - custom extension - context menu item - display query/sql result

[mikeinfodoc]

I would like to create a custom context menu option/item in sql developer
which would show a right-click menu option which would show the size of a table.

So something like this:

I am not sure HOW to show the result.

I assume it would be via a "confirmation" window/popup, but again, I do NOT know HOW to show the query result. (in this case the size of the table via the alias "MEGABYTES" column)

Can someone help me with this?

[bjeffrie]

From the example, I assume you are wanting to create a simple xml action.

<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs
                        http://xmlns.oracle.com/sqldeveloper/3_1/dialogs.xsd"
    xmlns="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs">

	<item type="TABLE" connType="Oracle">
		<title>Table size</title>
		<prompt>
			<label>Size</label>
			<value>
			    <![CDATA[select single value via some sql]]>
			</value>
		</prompt>
		<help>Shows table size</help>
	</item>	
</items>

See the xml schema referenced from the xml example page for more cool things you can do.

[bjeffrie]

I believe using <prompt type="confirm">...<prompt> will make it read only

[mikeinfodoc]

thank you.

The following does show the table size.
However it shows it in a dropdown menu.
Interestingly, the drop down appears to have a blank entry (which is shown by default,)
and another/2nd entry with the size info.

So I must click the drop down, and then select the 2nd item in the dropdown

<items> <folder type="TABLE"> <name>Docs context menu</name> <item type="TABLE" connType="Oracle"> <title>Table size</title> <prompt> <label>Size</label> <value> <![CDATA[SELECT bytes/1024/1024||' MB' MEGABYTES FROM DBA_SEGMENTS where OWNER='#OBJECT_OWNER#' and SEGMENT_NAME='#OBJECT_NAME#']]> </value> </prompt> <help>Shows table size</help> </item> </folder> </items>

[bjeffrie]

Turns out the magic word is default. I also added a section="0.1f" to the folder so your sub menu shows up 1st in the context menu.

``` xml  <-- FYI pretty prints the pasted in xml (these are escaped here so you can see it. There are unescaped ones before/after the xml.

<?xml version="1.0" encoding="UTF-8"?>
<!-- DocsContextSubMenu.xml -->
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs
                        http://xmlns.oracle.com/sqldeveloper/3_1/dialogs.xsd"
    xmlns="http://xmlns.oracle.com/sqldeveloper/3_1/dialogs">

    <folder type="TABLE" connType="Oracle" section="0.1f">
    <name>Docs context sub menu</name>
        <item type="TABLE" connType="Oracle">
            <title>Table size</title>
            <prompt>
                <label>Size</label>
                <default>
                    <![CDATA[SELECT bytes/1024/1024||' MB' MEGABYTES FROM DBA_SEGMENTS where OWNER='#OBJECT_OWNER#' and SEGMENT_NAME='#OBJECT_NAME#']]>
                </default>
            </prompt>
            <help>Shows table size</help>
        </item>	
    </folder>
</items>

```

[bjeffrie]

You could also throw in a dummy sql so hitting Apply doesn't cause an error. E.g.,

<item ...>
    <sql><![CDATA[SELECT 'noop' from dual]]></sql>
</item>

You'll get a success dialog after pressing Apply but IMHO that's better than a sql exception dialog.

[mikeinfodoc]

ok thank you.

This one works (Based on your feedback/direction)

 <items><folder type="TABLE"> <name>Doc`s context menu</name>
   <item type="TABLE"> <title>Show table size</title>
     	<prompt type="confirm"><label>Get table size in MB</label></prompt>
	    <sql> <![CDATA[select max('ignore this') from dual ]]> </sql>
	    <help>table size..</help>
	    <confirmation> <title>Table size in MB</title>
		    <prompt>Table "#OBJECT_OWNER#"."#OBJECT_NAME#" size #0.MEGABYTES# </prompt>
		    <sql> <![CDATA[select sum(bytes/1024/1024)||' MB' MEGABYTES from DBA_SEGMENTS where OWNER='#OBJECT_OWNER#' and SEGMENT_NAME='#OBJECT_NAME#' ]]> </sql>
	    </confirmation>	
   </item>
</folder></items>

also, I had to use an aggregate function (such as max, min, sum, etc...)
so that sql developer would actually return the result (so it could be guaranteed to return only 1 row)

[bjeffrie]

In my example, the "answer" is displayed right away when the dialog appears so users could hit cancel or close [x] (or accept) while in yours, they must hit accept to see the (more nicely formatted) answer. Either way, if you're happy, I'm happy. Feel free to open another issue if you have more questions.

Just in case you weren't aware, the table viewer has a details tab with all sorts of information. (I don't think your particular metric is there.) If you have a list of metrics to get, you may want to consider making your own viewer.