How To Create an XML User Defined Extension

Skip to end of metadata
Go to start of metadata

You can create your extension in any plain text editor. Knowledge of the SQL Developer framework, SQL, and XML is all that is required to get started.

The Framwork Docs

Once you have your XML file completed, you need only add your user defined extension in the SQL Developer preferences and restart SQL Developer for the changes to take affect. Here are two learn by example samples:

1. Adding a Context Menu

Adding context menus in Oracle SQL Developer requires a combination of SQL and XML. The SQL provides the command you are attempting to execute, and the XML provides the framework for the command. Let's assume, for this example, that Oracle SQL Developer does not provide you with a graphical method for dropping a table and that you would like to add the ability to right-click a table and select DROP.

<items>
<folder type="TABLE">
<name>UserDefined ContextMenus</name>
<item type="TABLE" reloadparent="true">
<title>MyDrop Menu</title>
<prompt type="check">
<label>Cascade Constraints</label>
<value>cascade constraints</value>
</prompt>
<prompt type="confirm">
<label>Are you sure you want to drop this table? You will not be asked again!</label>
</prompt>
<sql><![CDATAdrop table "#OBJECT_OWNER#"."#OBJECT_NAME#" #0#]> </sql>
<help>This action drops the selected table.</help>
<confirmation>
<title>Confirmation</title>
<prompt>Table "#OBJECT_NAME#" has been dropped</prompt>
</confirmation>
</item>
</folder>
</items>

Copy the code in Listing 1 and paste it into a new file such as dropTable.xml. Then, using Oracle SQL Developer, select Tools ->Preferences . Expand the Database node, and select User Defined Extensions . Click Add Row , click in the Type field, and select ACTION from the list. Click in the Location field, and click Browse to add the location of the dropTable.xml file. Restart Oracle SQL Developer to register this new extension.

Next, open a user connection and expand the Tables node. Select a table, and right-click to open the context menu. The code in your dropTable.xml fileadds a new category, UserDefined ContextMenus , to the Tables context menu. Navigate to the new context menu, and select the new submenu, MyDrop Menu , to invoke the Drop dialog box.

Click Help to review the help. Select the SQL tab to review the code that will be executed. Click Cancel to exit the dialog box without dropping the table.

Examining the Code

The purpose of the MyDrop context menu is to drop the selected table for the current user. This means that you want to execute a SQL query that looks like this:

drop table HR.DEPT cascade constraints

You want the user to be able to decide whether to cascade constraints, so the string cascade constraints is variable. 

Let's drill down into the code and see the impact of the various pieces.

The <NAME> adds the new context menu category to the main context menu. You can omit this if you want to add context menus at the top level. If you are going to add several of your own menus, it is worth identifying them as your own additions, either by labeling them clearly or, as in the example, by creating a completely separate context menu category.

Inputs

The ordinal number (n) of the prompt within the item is used to access its value. For direct substitution, use #n#, for binding, :n.

Each piece of user input is numbered sequentially, starting with 0. So the first user input variable is #0#, the second #1#, and so on.

Substitution Replacement

  • #name# with value
  • #"name"# with "value"
  • #,name# with ,value when not null and nothing when null
  • #,"name"# with ,"value" when not null and nothing when null
  • #esc(name)# with value with sql escaped chars 

In the example, the input value is controlled by use of a check box, <prompt type = "check"> , which has a label, Cascade Constraints . The check box itself determines whether an empty string or cascade constraints is passed to the later SQL statement, thus providing the value for #0#).

The <sql> contains the SQL that is executed and is also displayed in the SQL tab in the dialog box.

The context menu knows the current database connection and table selected when you invoke the menu. This information is stored in the variables #OBJECT_OWNER# and #OBJECT_NAME# . The value for #0# is provided by the Cascade Constraints check box created by the code.

Code Options

You can control how Oracle SQL Developer extension users provide input with different methods.

Allow users to simply type in a value

This is necessary if you need to provide a new value or string, such as a new name for a table. To do this, create a label and an empty field for the user input, as follows:

<prompt>
<label>New Table Name</label>
</prompt>

Provide a list that is statically or dynamically created

 Here is an example of a static list:

<prompt>
<label>Logging:</label>
<value><![CDATASTATIC:LOGGING:NOLOGGING]>
</value>

Here is an example of a dynamic list powered by the results of a SQL statement:<prompt>
<label>USER</label>
<default>
<![CDATAselect user from dual]>
</default>
<value>
<![CDATA[select distinct username
from sys.all_users order by 1]]>
</value>
</prompt>

Much like in HTML CGI, you can provide a default value for the selector. This is done with a DEFAULT tag. Note the query returns only a single value. Additional values are supplied by an additional VALUE tag. The sorting is controlled via the SQL statement.

2. Adding New Tabs for An Object Browser

When clicking on an object in the connection tree, the object details are loaded in the object viewer. Tables have 'Data', 'SQL', 'Columns', and other pages to show additional detail. You can add your own tab or page.

These tabs are of ITEM type 'editor.'

You can simply provide a query and have the information displayed in a grid, much like a report.

<items> <item type="editor" node="TableNode" vertical="true">
<title><![CDATAColumn Comments]></title>
<query>
<sql><![CDATA[select * from ALL_COL_COMMENTS
where owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]>
</sql>
</query>
</item>
</items>
In the same way you added the previous extensions, select Tools -> Preferences , expand the Database node, and select User Defined Extensions . Add the new user-defined extension, but this time the user-defined extension type is EDITOR. Set the location of the colcomm.xml file. Shut down and restart Oracle SQL Developer to register the new extension.

Open a database connection, such as HR, and expand the Tables node. Select the LOCATIONS table, and select the newColumn Comments tab to see the listing of all comments stored.

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. May 21, 2012

    The first example contains an error in the line:

    <sql><![CDATAdrop table "#OBJECT_OWNER#"."#OBJECT_NAME#" #0#]> </sql>

    This should be:
    <![CDATA[drop table "#OBJECT_OWNER#"."#OBJECT_NAME#" #0#]]></sql>

    A similar error exists in the first CDATA of the second example.

Sign up or Log in to add a comment or watch this page.


The individuals who post here are part of the extended Oracle community and they might not be employed or in any way formally affiliated with Oracle. The opinions expressed here are their own, are not necessarily reviewed in advance by anyone but the individual authors, and neither Oracle nor any other party necessarily agrees with them.