Oracle SQL Developer is written using a Java framework, provided by Oracle JDeveloper. This underlying integrated development environment is commonly referred to as the JDeveloper IDE, or more recently, the Fusion Client Platform (FCP). The framework provides the underlying structure onto which developers of both Oracle JDeveloper and Oracle SQL Developer add their features. This framework is extensible and is available to development engineers and end users to add functionality. The nature of these extensions can range from including a single XML report, to adding complex Java extensions bundled as JAR files. In this chapter, we'll look at the range of support available for adding XML extensions, a task that is easily within the reach of any database developer with SQL and PL/SQL skills. While it is considered beyond the scope of the book to include information on building a Java extension, we will discuss working with existing Java extensions.
Extensibility, in terms of software development, means that the software can have components added without impacting or having to change the underlying code. Moreover, the underlying code provides "hooks" within the infrastructure, specifically designed to make for the addition of new components, thus allowing developers to add new or alternative capabilities not provided by the product.
The FCP is designed to allow users to build Java extensions that can be added to either JDeveloper or SQL Developer. You can build large complex features using Java. The Migrations and Versioning support within SQL Developer are examples of this, and there are external customers building Java extensions.
SQL Developer provides an additional infrastructure with code "hooks" for adding items using only XML with embedded SQL and PL/SQL. The extra item types that you can add are as follows:
- Display editors
- Context menus
- Reports
- Navigator nodes
We will review these XML extension types and consider various examples to illustrate them.
With the ability to support both XML and Java extensions, there is the opportunity to support different extension-building audiences. XML extensions are typically used by developers well versed in SQL and PL/SQL. These are database developers and DBAs who use tools and command-line interfaces to access the database. The extensions they build are often shared within a team or the company. Java extensions are more often developed by development teams in Oracle, Oracle Partners, and by other Java literate developers and engineers. Oracle TimesTen and the Versioning Navigator are Oracle-developed Java extensions, built by different teams and shipped as part of SQL Developer. Java bundled extensions, which could be a combination of XML and Java, lend themselves to be viable as commercial entities. There are a few public Java-based user-defined extensions; some are commercial and some are freely available.
Software products do not always provide the full functionality you need in your development environment. Being able to add a few additional components to those that are already provided means you can augment your environment with features that are specific to your needs. With SQL Developer extensions, you only need one person in the team to create the extension(s), which can then be shared. So, create an enhanced, yet company standard tool. The kind of missing functionality might be that grey area between being a developer and DBA.
In this next section, the extensions described focus on XML extensions and can be added by anyone who has a good grasp of SQL and PL/SQL. Although the documents we create are written in XML, they conform to a set of XML Schema Definition (XSD) standards, which you can think of as templates. For all the extensions added, whether you create Java extensions bundled as JAR files or XML extensions and add them individually or bundled as JAR files, you need to restart SQL Developer for the additions to take effect. In this next section, we'll look at the different types of XML extensions you can create in SQL Developer. We'll start with the easiest, progressing to the more complex.
Regardless of the XML extension you are adding, you add the files to SQL Developer in the same way. They are either as individual files or bundled as JAR files. When adding a single file or a few files, it's easier to add them individually.
If you have a large number of files, then it's easier to bundle them in JAR files and add the files. This is more efficient, however, you need a certain level of Java knowledge to create the JAR file, and so we'll add them individually. We will show you how to add Java extensions later.
The most straightforward of all extensions to create is the shared reports extension. Once you have created a set of your own reports, use the context menu to export them to an XML file. You can export reports at any level in the User Defined Reports section. You can select one of the following:
- The top-level User Defined Reports—this exports all sub-folders and reports
- Any subfolder—this exports all sub-folders, if there are nested folders, and reports
- Any report—this exports the selected report only
Regardless of the level you select, invoke the context menu and select Export (as shown in the following screenshot). Provide a report name and the file location:
When creating reports, SQL Developer creates the XML file in the correct format. Therefore, in order to create the XML file for shared reports, you only need to worry about creating the initial reports, using the Create Report dialog for assistance. Once you have exported the reports to an XML file, and you can create one or many files, move them to a central file or web server to be made available for more general use.
To include the shared reports, open the Database | User Defined Extensions preference and click on Add Row. Select REPORT from the drop-down list for the extension type, and browse to locate the XML file, as shown in the following screenshot:
By sharing reports, you can achieve the following:
- Save time and resources by having only one or a few team members create the reports and then share them with the group
- Use the best skills for the job by using those developers with the most knowledge of the Data Dictionary or of the application you are working with
- Use those best skilled at writing well-tuned SQL queries that others can take advantage of
Once the new file location has been added, restart SQL Developer. A new Shared Reports folder will display in the Reports navigator. These reports, like those in the Data Dictionary Reports node, are not updateable. To review or edit the SQL, you can copy and paste them into a new report in the User Defined Reports node, or run the report and copy the SQL to the SQL Worksheet once run.
Another advantage of shared reports is that the next time you re-start SQL Developer, you pick up any updates to the existing reports or new reports added to the centrally stored file.
Adding display editors is a little more involved than adding shared reports, because you need to create the XML file yourself. You invoke a set of display editors each time you click on an object in the Connections navigator. The set of view display editors is highlighted in the following screenshot:
The contents of these display editors are the results of queries run against the data dictionary. For example, to get the kind of detail in the Triggers or Columns display editors shown in the previous screenshot, you can write queries against the USER_TRIGGERS or USER_TAB_COLUMNS dictionary views. For more detail, you need to write more complex queries, possibly joining many dictionary views.
The list of examples is potentially long, and we offer a few here only to give you an idea of the kind of content you can add. Naturally, any additional displays that you add will depend on your areas of focus.
If you work with partitioned tables, you can add a user-defined extension to display the subpartition's details.
Taking the idea of adding a subpartitions display, consider the following piece of code:
<displays>
<!-- OverView -->
<display type="editor" style="null" enable="true" class="Oracle#SUBPARTITION" objectType="TABLE">
<name><![CDATA[Subpartitions]]></name>
<query>
<sql>
<![CDATA[Select * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME = :TABLE_NAME]]>
</sql>
</query>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
</display>
</displays>By saving this code to an XML file, and then hooking that file into SQL Developer, you add another display editor to the set already defined for tables.
The structure for each display editor you add is the same. The first CDATA entry highlighted previously is for the title, displayed in the display editor tab. The second CDATA entry highlighted is the SQL query, which provides the content. The results of the new display editor are shown in the upcoming screenshot. Notice that the new editor is added at the end, after the SQL editor. Notice too that in the example, the table name is unnecessarily included in the list. Here, we have used a simple Select * FROM … query. Even for a simple query, just by specifying the columns in the select statement means that you can control the columns that display and avoid the unnecessary additional columns; in this case, the table name.
The example we have included is basic and is sufficient to add in additional display editors. If you are familiar with XML, then you'll want to see the XSD. The XSD, or XML Schema Definition, provides the syntax and structure of the way the elements should be defined in the XML file.
The SQL query you include in the XML file can be as simple as SELECT * FROM <a data dictionary view>, or a more complex query, joining multiple dictionary views.
Once you have created the XML file, save it to a file or web server location of your choice and open the User Defined Extensions preference. Click on Add Row, select EDITOR from the drop-down list for the extension type, and browse to locate the XML file, as shown in the following screenshot:
Tip
Group display editor topics into a single file
Instead of creating multiple individual XML files for each display editor, add all of the code for the display editors for a single object into one file. For example, create an ExtraTableEditor.xml file with all of the additional table related display editors.
Throughout SQL Developer, a right-click on the mouse invokes a context-sensitive menu. The menus in the Connections navigator are all DDL commands that provide actions such as dropping, adding, and setting privileges. For example, if you select a table and invoke the context menu, you can drill down to the Drop menu, as shown next:
Selecting the menu item invokes the dialog with two tabs. The pieces that are consistent for each of these context menu dialogs are the three buttons, Help, Apply, Cancel, and the two tabs, Prompts and SQL. The initial Prompts panel also displays the context you are in, the schema name (displayed as the Owner), and the selected object name. In the example shown in the following screenshot, we have selected a table, and so the Name displayed is that table name:
You can add these extra context menus at various levels in the Connections navigator tree, such as at the connection, object node, or instance level, so the name displayed depends on the node you have selected.
You can add the option to compile the schema currently selected. Consider the following piece of code:
<?xml version="1.0" encoding="UTF-8"?>
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="dialogs.xsd">
<item type="CONNECTION" reload="true">
<title>Compile Schema</title>
<prompt type="confirm">
<label>Confirm to compile all in the schema.</label>
</prompt>
<sql>
<![CDATA[BEGIN DBMS_UTILITY.COMPILE_SCHEMA
(:OBJECT_OWNER); END;]]>
</sql>
<help>Compiles all the objects in the schema. </help>
</item>
</items>This adds a new context menu when you right-click on the connection. It invokes a dialog with two tabs: the first with all of the details of the action about to take place, and the second displays your SQL or, in this case, PL/SQL code. You can include help text and a confirmation dialog to follow the action.
As mentioned earlier, if you are familiar with XML, there is an XSD file to assist with the development of the XML file structure.
In the previous example, we only passed a single parameter: the object owner name. You can create context menus and provide empty fields, checkboxes, or drop-down lists, which allow the user to have more control over the SQL executed. In the Drop table example illustrated at the start of this section, the checkbox allows the user to include the Cascade Constraints option.
To provide the user with a fixed list of values, add the static list options after the <title>Compile Schema</title>. The code is shown next:
<prompt> <label>Compile All </label> <value><![CDATA[STATIC:TRUE:FALSE]]></value> </prompt>
Once you start passing in additional parameters, you can use them in the SQL as follows:
<sql>
<![CDATA[BEGIN
DBMS_UTILITY.COMPILE_SCHEMA('#OBJECT_OWNER#', #0#);
END;]]>
</sql>Here, the parameter is referred to by the value, #0#. You can pass a number of additional values to the SQL statement, referencing each in turn by using the values #0#, #1#, #2#, and so on.
You will see the result of our code, including the drop-down list. Notice that we do not need to code for the Owner or the Name values displayed. These are exposed in the dialog through the extensions framework. If you do not see them, no connection has been made.
The extent of what the context menu does is up to you. You can create a simple menu to drop or rename an object, or you can create more complex menus. You don't need to limit yourself to a single command either (whether basic or complex). Instead, you can bundle a set of commands to create a small utility, like the Normalize menu, provided by SQL Developer.
The context menu is available off Columns | Normalize, when you select a table.
In the dialog previously displayed, you can see that the code requires four free form values to be added, and one selected from a drop-down list.
This little utility is very useful if you have imported a spreadsheet to create a new table populated with that data. Invariably, there are redundancies and the table is better suited to be split into two or more. You manually need to create a new table and move the duplicate values to the new table, populating a Primary Key automatically while you do so. Then, you need to create a Foreign Key constraint to link the two tables. This utility does all of the steps, wrapped in PL/SQL.
In the code shown in the following screenshot, you can see a subset of the code, displaying the steps that you need to follow when carrying out the process manually:
In the same way as you have done for the previous examples, once you have created the XML file, save it to a file or web server location of your choice and open the User Defined Extensions preference. Click on Add Row and select ACTION from the drop-down list for the extension type, and browse to locate the XML file, as shown in the following screenshot:
In this case, the code adds an extra menu item in the Connections context menu.
When adding context menus in the Connections navigator, you can add individual menus, or a menu with a number of submenus. There are many such menus in SQL Developer, such as the Column context menu with its set of submenus. To include a collection of context menus for one node, add all the pieces of XML code into the same file.
The last of the XML user-defined extension types available in SQL Developer is the Navigator. The name is a little misleading because the "Navigator" user-defined type allows you to add a new node into the list of currently available nodes in the Connections navigator. Currently, these nodes are predominantly made up of Data Dictionary object types such as Table, View, and Index. The list includes the Application Express node that allows you to browse the detail of your Oracle Application Express applications. In this case, you are browsing the metadata that defines the application.
You can add nodes for database objects that are not yet supported in the navigator, such as tablespaces, or you can add new nodes to browse external application metadata stored in database repositories. For example, you can create an Oracle Designer node to query the Oracle Designer Workareas. Alternatively, you can add a node to query instance data in applications.
Adding extra nodes to the Connections navigator is a little more involved than for the preceding examples. You still create a single file, and in the same way as you have done for each of the previous examples, once you have created the file, save it to the file or web server location and open the User Defined Extensions preference. Click on Add Row, and in this instance, you should select NAVIGATOR from the drop list for the extension type and browse to locate the XML file.
To investigate this example, we'll break up the file and code into sections and then build it up in phases. You can start by creating the skeleton, adding it into SQL Developer and restarting the product. Then, you only need to refresh the navigator by restarting SQL Developer after any additional changes to the XML file.
In the following screenshot, a number of additional nodes have been added. The new nodes are added above the shipped nodes, which are sorted by typical usage priority:
The structure that you generally want to replicate in the tree is a top-level node (for example, Tables), and after that, you want to display the list of tables. Next, we have the columns that are associated with each of those tables. Using this example, we have the following structure:
Extrapolating from the previous structure, which you know, call the Tables node Level 1, the actual table listing Level 2, and the columns Level 3:
Tables: This would be Level 1COUNTRIES: This would be Level 2COUNTRY_ID: This would be Level 3COUNTRY_NAME: This would be Level 3
Once again, there is an XSD file to assist with the development of the XML file structure.
To add a new top-level folder, consider the following code:
<?xml version="1.0" encoding="windows-1252" ?>
<navigator RESOURCE_FILE="oracle.dbtools.raptor.navigator.OracleNavigatorResource"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="navigator.xsd">
<objectType connType="Oracle" id="MyApplication"
weight="50.0" includesSyns="true">
<folder> <!-- level1 -->
<icon RSKEY="TABLE_FOLDER_ICON" />
<label RSKEY="My Application Node" />
<queries> <!-- level2 -->
<query minversion="8">
<sql constrained="true"><![CDATA[]]></sql>
</query>
</queries>
</folder>
</objectType>
</navigator>The only piece you are really interested in changing is the label, which is the name of the new Connections navigator node that you are creating. The other value that you can change when creating your own copy of this file is the id. It is a good idea to keep this id as descriptive as you can.
There is currently no query listed in the CDATA section. Therefore, if you add this code and open SQL Developer, you'll only get the new node; there is no tree of objects below the node, and so, no detail of any interest at this stage. To list the elements under that first node, you need an initial query. The example we use here is trivial (to illustrate the concept) and does not take into account other objects that you might want listed, or even any privileges.
In addition to including a SQL query to populate the list, you also need to add a <columns> section. This references the columns in the query. Notice that the following query includes an alias, which is referenced in the <columns> section. The following code listing is just that portion of the full file that lies between the <queries>…</queries> tags in the previous section of code:
<queries> <!-- level2 -->
<query minversion="8">
<sql constrained="true">
<![CDATA[SELECT TABLE_NAME NAME FROM TABS]]></sql>
</query>
<columns>
<column filterable="true" sortable="true" id="NAME">
<colName><![CDATA[name]]></colName>
</column>
</columns>
</queries>Once again, you have an id field; in this case, id="NAME". It is a good idea to keep these id values meaningful, as they can be used as bind variables for future sub-nodes or to link to the display editors.
To finish off that node, you still need to add the icon detail. Add this detail to the file below the </folder> tag, as shown next:
<node> <!-- level2 details --> <icon RSKEY="OracleIcons.TABLE" /> </node>
In this example, we are referencing the icons shipped as part of SQL Developer. The icons are available in the sqldevelopersqldeveloperextensions folder, in the oracle.sqldeveloper.jar file. If you browse this file, you can see the images are in the folder structure, /oracle/dbtools/raptor/images/, which you can reference as follows:
<icon RSKEY=" /oracle/dbtools/raptor/images/snippets.png" />
Instead of using the shipped SQL Developer icons, you can create your own set in a JAR file, and add these as a Java extension.
The output of what we have achieved until now is shown next. Notice that there is a list of tables based on that SQL query, but we don't have any columns, which would complete this node in the navigator:
To complete the node, you need to add the third-level query. The structure here is as before, using the same queries and columns tags as shown here:
<queries>
<query>
<sql> </sql>
</query>
<columns>
</columns>
</queries>This third level is included in the second level's <node> details, with the extra code shown as follows:
<node> <!-- level2 details -->
<icon RSKEY="OracleIcons.TABLE" />
<childType id="Level3"> <!-- Level3 -->
<icon RSKEY="OracleIcons.COLUMN" />
<queries>
<query>
<sql> SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = :PARENT_NAME
</sql>
</query>
<columns>
<column filterable="true" sortable="true" id="NAME">
<colName><![CDATA[column_name]]></colName>
</column>
</columns>
</queries>
</childType>
</node>Notice how this SQL now includes the bind variable to ensure that you only get the required detail records. In this case, we have not used an alias in the query, so we need to ensure that the full column name is referenced in the <column> section.
The addition to this <node> detail results in the output shown in the following screenshot:
You have now reached the point where you can return to the start of this chapter and add all of the display editors and context menus for each of the nodes as required.