By Sue Harper
Build extensions for Oracle SQL Developer with SQL and XML.
There are times when a tool does exactly what you need it to do and times when you feel that a tool would suit you better if a few extra features had been implemented. Suppose a tool you use regularly does not include some key functionality you need, and now suppose the tool is extensible so that you can include that functionality. Oracle SQL Developer provides the framework for you to do just that.
Whether you are a DBA or a database developer, you can add context menus in Oracle SQL Developer to execute a SQL statement or more-involved wizard-driven dialog boxes that step you through a process. You can extend Oracle SQL Developer in several ways, including
Creating extensions by using XML, SQL, and the Oracle data dictionary
Creating extensions by using Java
This column introduces extensions to Oracle SQL Developer, describing how to share reports and then examining how to create an XML-based extension in which you add a context menu in the Connections Navigator. (An understanding of XML is helpful for creating this XML-based extension).
Oracle SQL Developer includes several supplied system reports and also provides the ability to create your own. (See "Now Reporting" in the May/June 2007 issue of Oracle Magazine, for more information.)
To share your reports with members of your team, create a shared-reports folder. A shared-reports folder is probably the easiest extension to add to Oracle SQL Developer, because you do not need to write any code.
To share reports, start by exporting them. You can export all of your user-defined reports at once by category or export each individual report. Best practice suggests that you should group similar reports into categories. For example, you can find all the included system reports that relate to object and system privileges under Data Dictionary Reports -> Security -> Grant and Privileges .
Assume, for example, that you have a set of user-defined security reports in a folder called Team Security. To export these reports, select that folder, right-click to invoke the context menu, and select Export . In the dialog box, give the file a meaningful name such as TeamSecurity.xml and set the location in the file system on a server that is accessible to the users who need to run the reports.
To access your team's shared reports, set up the shared-reports folder by first selecting Tools -> Preferences . Expand the Database node, and select User Defined Extensions . Figure 1 shows the User Defined Extensions dialog box. Click Add Row , click in the Type field, and then select REPORT from the list. Click in the Location field, and click Browse to add the file location. Shut down and restart Oracle SQL Developer to register the extension. Finally, open the Reports navigator and note that the additional Shared Reports node is now available. You can add multiple folders or individual reports by using the same approach.
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 . The code in Listing 1 adds this functionality to Oracle SQL Developer.
Code Listing 1: Code for MyDrop context menu
<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> <![CDATA[drop 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. As you did to register the shared-reports extension, restart Oracle SQL Developer to register this new extension.
Next, open a user connection such as HR and expand the Tables node. Select a table, and right-click to open the context menu. The code in Listing 1 (and your dropTable.xml file) adds 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. Figure 2 shows the new context menu category and the new submenu.
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.
Using Listing 1 as a guide, let's drill down into the code and see the impact of the various pieces.
Listing 1, #1. The first section, Listing 1, #1, 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.
Listing 1, #2. In a context menu item, 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. The code in Listing 1, #2, controls the first and, in this example, only user input value: cascade constraints.
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# in Listing 1, #3).
Listing 1, #3. Listing 1, #3, is 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 in Listing 1, #2.
You can control how Oracle SQL Developer extension users provide input with different methods. You can 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>
You can also provide a list that is statically or dynamically created. Here is an example of a static list:
<prompt> <label>Logging:</label> <value><![CDATA[STATIC:LOGGING:NOLOGGING]]> </value>
Now, using the building blocks from the dropTable.xml example, consider the following SQL:
<sql> <![CDATA[create table "#0#".#1# as select * from "#OBJECT_OWNER#"."#OBJECT_NAME#" where '1' = '#2#']]> </sql>
Can you determine what the context menu is going to execute and how the input values might be provided?
As before, the connection, #OBJECT_OWNER# , and table, #OBJECT_NAME# , are driven by the context of the menu that is invoked. Note that this query expects three input values: 0, 1, and 2. To see the dialog box for the context menu that drives this query, open a connection, such as HR, in Oracle SQL Developer. Expand the Tables node. Select a table, DEPARTMENT . Right-click and select Table -> Copy to invoke the dialog box, shown in Figure 3.
Variable #0# , the new table owner, is controlled by a list that has a dynamic query driving it.
<prompt> <label>New Table Owner</label> <default> <![CDATA[select user from dual]]> </default> <value> <![CDATA[select distinct username from sys.all_users order by 1]]> </value> </prompt>
Variable #1# , the new table name, is an input field as described above, and Variable #2# , the choice for including data, is a check box.
|Figure 1: Creating a shared-reports extension
|Figure 2: User-defined context menus
|Figure 3: Copy dialog box
Using Oracle SQL Developer, select a connection, such as HR; expand the Tables node; and select EMPLOYEES . Depending on the preference you set, a click or a double-click opens a series of tabs that describe this object. The tabs displayed depend on the type of object you select and the database version you are connected to.
You can add extra tabs to display more information about the selected object. All you need to know is which data dictionary view holds the additional detail. To add an extra tab to the table definition to display column comments, create a file called colcomm.xml and add the code in Listing 2.
Code Listing 2: Code for colcomm.xml
<items> <item type="editor" node="TableNode" vertical="true"> <title><![CDATA[Column 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 new Column Comments tab to see the listing of all comments stored.
Oracle SQL Developer can help you export and share reports across your team, add custom context menus, add extra information tabs for displayed objects, and also provide more-complex wizard-driven utilities. You can build Oracle SQL Developer extensions easily, with no code or by using only SQL and simple XML.