Purpose

This tutorial shows you how to develop a Java defined extensions for Oracle SQL Developer which performs an action on multiple selected objects.

Time to Complete

Approximately 30 minutes

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created. Developed in Java, Oracle SQL Developer runs on Windows, Linux and the Mac OS X. This is a great advantage to the increasing numbers of developers using alternative platforms. Oracle SQL Developer is built on an extensible framework and, as such, is extensible itself.

Users can create basic XML extensions or more involved Java extensions to add utilities or other functionality to the product.
This tutorial demonstrates how a Java defined extensions can be created to perform an action on multiple selected objects in the database navigator

Prerequisites

Before you perform this tutorial, you should:
1.
Install Oracle Database 11g
Note: You can use any Oracle Database above 9.2.0.1

2.
Install Oracle SQL Developer 1.5.1.

Note: Oracle SQL Developer is available for download for FREE from OTN.
To install Oracle SQL Developer, unzip it into any directory on your machine.

3.
Install Java 1.5 or higher. A Java IDE is recommended.

4.
Install the latest version of ANT. JDeveloper or Eclipse Java IDEs will have this.

5.
Download and unzip java_simple_tablespace.zip into your working directory (i.e. d:\wkdir)
ftp://ftp.oracle.com/svrtech/outgoing/sqldev_sdk/java_multi_select_nav.zip

Extension XML Hook

Extensions are loaded into SQL Developer during its startup. SQL Developer looks in its extension directory for jar files.
Referencing each jars meta-inf\extension.xml

The extension.xml defines the extension to be loaded.

<extension xmlns="http://jcp.org/jsr/198/extension-manifest"
id="@@extension.id@@"
version="@@extension.version@@"
esdk-version="1.0">

<name>Oracle Multi Select Action Project</name>

<owner>Oracle Corporation</owner>

<dependencies>

<import>oracle.sqldeveloper</import>

<import>oracle.javacore</import>

</dependencies>

<hooks>

<jdeveloper-hook xmlns="http://xmlns.oracle.com/jdeveloper/1013/extension">

<context-menu-listeners>

<site idref="navigator">

<listener-class>oracle.dbtools.multiselect_nav.action.MultiSelectContextMenuListener
</listener-class>

</site>

</context-menu-listeners>

</jdeveloper-hook>

<feature-hook>

<description>Demonstrates a multiselect action</description>

<optional>false</optional>

</feature-hook>

</hooks>

</extension>

ContextListener

package oracle.dbtools.multiselect_nav.action;

import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JMenu;

import oracle.dbtools.raptor.RaptorDBAddin;
import oracle.dbtools.raptor.utils.DBObject;
import oracle.ide.Context;
import oracle.ide.Ide;
import oracle.ide.controller.ContextMenu;
import oracle.ide.controller.ContextMenuListener;
import oracle.ide.controller.Controller;
import oracle.ide.controller.IdeAction;
import oracle.ide.model.Element;

public class MultiSelectContextMenuListener implements ContextMenuListener,
Controller {

private JMenu _menu;
public static final String DROP_SCRIPT_CMD = "DatabaseNavigator.DROP_SCRIPT_CMD";
public static final int DROP_SCRIPT_ID = Ide.findOrCreateCmdID(DROP_SCRIPT_CMD);
public static final IdeAction DROP_SCRIPT_ACTION = IdeAction.get(DROP_SCRIPT_ID, "Build Drop Script", 1);

private void init() {
if (_menu == null) {
_menu = new JMenu("My Custom Actions");

_menu.add(DROP_SCRIPT_ACTION);
DROP_SCRIPT_ACTION.addController(this);
}
}

public void menuWillShow(ContextMenu contextMenu) {
init();
DBObject dbo = null;
boolean add = true;
Element[] els = contextMenu.getContext().getSelection();
if (els.length == 0){
add = false;
}
// see if whole selection is supported
for (Element e : els) {
dbo = new DBObject(e);
if (false)
add = false;
}

if (add){
contextMenu.add(_menu);
}
}

public boolean handleEvent(IdeAction action, Context ctx) {
if (action.getCommandId() == DROP_SCRIPT_ID) {
saveToWorkSheet(ctx);
return true;
}
return false;
}

public boolean update(IdeAction action, Context ctx) {
return false;
}

private void saveToWorkSheet(Context ctx) {
Ide.getWaitCursor().show();
try {
DBObject dbo = new DBObject(ctx.getSelection()[0]);
List<DBObject> dbobjectList = getList(ctx);
RaptorDBAddin.getSqlOpener().openNewEditor(
getDropScript(dbobjectList), dbo.getConnectionName());
} catch (Exception e) {
Logger.getLogger(getClass().getName()).log(Level.WARNING,
e.getStackTrace()[0].toString(), e);
} finally {
Ide.getWaitCursor().hide();
}
}

private String getDropScript(List<DBObject> dbobjectList) {
StringBuffer scriptBuffer = new StringBuffer();

for (DBObject obj : dbobjectList) {
String dropStmt = " DROP " + obj.getObjectType() + " "
+ obj.getSchemaName() + "." + obj.getObjectName();
if (obj.getObjectType().equalsIgnoreCase("Table")) {
dropStmt = dropStmt + " CASCADE";
}
dropStmt = dropStmt + ";\n";
scriptBuffer.append(dropStmt);
}
return scriptBuffer.toString();
}

private List<DBObject> getList(Context ctx) {
ArrayList<DBObject> ret = new ArrayList<DBObject>();
Element[] els = ctx.getSelection();
// see if whole selection is supported
for (Element e : els) {
ret.add(new DBObject(e));
}
return ret;

}

public boolean handleDefaultAction(Context contextMenu) {
return false;
}

public void menuWillHide(ContextMenu contextMenu) {
}
}

Conclusion

Hopefully the above examples have outlined how to create your own SQL Developer extensions.
Please add to this wiki any notes you would like to share regarding SQL Developer extension framework.

Resources

File to build this sample extension
ftp://ftp.oracle.com/svrtech/outgoing/sqldev_sdk/java_multi_select_nav.zip

Similar, Oracle By Example paper for SQL Developer 1.2.1
http://www.oracle.com/technology/obe/sqldev_obe/extension/extensions.htm

Extending SQL Developer 1.2.1
http://www.oracle.com/technology/oramag/oracle/07-jul/o47sql.html