Tutorial 3 – Extended 1:1 View Generator (Xtend)

In this tutorial we build a JAR file with a client generator implementing the Java interface. A client generator runs in the same JVM as SQL Developer and does not require a server side installation. Hence a client generator is immediately ready for use within all database connections.

In tutorial 2 we’ve written a generator in PL/SQL. We rewrite this generator now in Xtend. In this tutorial we use command line tools and simple text editors as common denominators. However, feel free to use the Java IDE of your choice.

1. Download Maven

Download Apache Maven 3.5.0 or later from here.

2. Install Maven

Install Maven as described here.

3. New Xtend generator

To generate an initial Xtend generator using a standard Maven directory layout, select Xtend generator from the New… context menu of an oddgen node.

In the generator dialog enter ExtendedView for class name and choose an output directory for the generated files and press the Generate to worksheet button.

This will produce an output similar to the following:

4. Replace generator

Open the ExtendedView.xtend file created in the previous step and replace with the following content (please ensure that the editor uses UTF-8 encoding without BOM).

The implementation looks very similar to the final PL/SQL variant of tutorial-2. In difference to the PL/SQL interface we have to implement the Java interface completely. There is no default behaviour for getName, getDescription, getFolders, etc.

Another difference is, that every method gets the active database connection as parameter conn. See lines 110 and 111 how this connection is used to access the database via Spring’s JdbcTemplate. There is also a isSupported method to ensure that an Oracle Database version >= 9.2 is used (see lines 54-68).

A further difference is that the that parameters are based on a LinkedHashMap and therefore sorted by entry (See line 87). Hence no additional method to sort parameters is required.

But the most significant difference is the use of template expressions. The following screenshot shows the lines 156 to 160 in the Xtend editor within the Eclipse IDE:

On line 156 the template is initiated with tripple single quotes and terminated on line 160 with tripple single quotes. Within the template boilerplate text such as “CREATE OR REPLACE VIEW” is shown in grey background colour. Please note that the whitespaces before “CREATE OR REPLACE VIEW” are ignored. This allows a reasonable formatting of code. Xtend expressions are put in guillemets. For example «col.toLowerCase» on line 158. In this case the col variable of the for loop is converted to lower case using the standard java.lang.String method toLowerCase(). Xtend expressions give you access to the full Java stack and are compiled, this means better performance and less runtime errors.

5. Build JAR

Open a *nix terminal window or a Windows command window and run the commands in the To build the plugin section. In my case these are:

It will take a while to download all the dependencies into your local Maven repository. However, it will be faster for subsequent calls.

At the end of the build you should see a success message similar to the following:

6. Install JAR

Open a *nix terminal window or a Windows command window and run the command in the To install the plugin section. In my case this is:

This will copy the plugin into the SQL Developer extension directory.

You do not need to restart SQL Developer, just open or refresh a connection in the Generators window and the new client generator should show up as in the following screenshot:

7. Generate from user HR

Establish a connection to any user in the database such as the demo user HR and generate the views as shown in the next images.

8. Wrap-up

Generator Template

Create a Xtend generator based on the “New…” template. This template implements the oddgen Java interface fully and makes it easier to change the behaviour according your needs.

Powerful Template Expressions

Xtend template expressions leverage the underlying Java ecosystem and compile into Java. Templates do not need to be interpreted at runtime, this makes the execution faster and leads to less runtime errors. The excellent IDE support in Eclipse and IntelliJ IDEA makes the development of generators a pleasure.

oddgen in Maven Central

oddgen and all dependencies for the plugin development are available in public Maven repositories such as Maven Central. This makes it easier to build oddgen plugins with Maven or any other build system.

Copy JAR to Install

To install an oddgen plugin, you just need to copy the JAR file with the client generator into the SQL Developer extension directory.

Ready for all Connections

A client generator does not require a server side installation. Hence a client generator is immediately ready for use on all database instances.


Creating a SQL Developer extension based on the “New…” template is quite similar. In fact the ExtendedView.xtend is identical. Try it out.