Monday, 18 May 2015

Building ORDS plugins

Oracle Request Data Services (ORDS) released version 3 recently. If you didn't notice, this version allows you to develop plugins (with Java) that can be imported into your ORDS instance, and provide extended functionality.

You want to refer to the Oracle REST Data Services Plugin API, located here:, which itself includes a couple of nice documents:
  1. Getting started guide
  2. Developer guide
This post just re-iterates a lot of what has already been covered in the getting started guide, with a focus on using IntelliJ IDEA.

Setting the project up in IntelliJ IDEA

So the first step is to set up a new project. First however, you will want to verify what version of Java you are using on your server (assuming you are developing on a separate workstation). This can be verified with the command java -version.

[user@host ~]# java -version
java version "1.7.0_79"
OpenJDK Runtime Environment (rhel- u79-b14)
OpenJDK 64-Bit Server VM (build 24.79-b02, mixed mode)

So, in my instance, I'm running on Java 1.7, so I'll want to target that same JVM when setting up the new project.

After setting up the project, you will want to include the library files required for developing plugins. The libraries can be found in ORDS_INSTALL_FOLDER/examples/plugins/lib. This is done in IDEA by opening the Project Structure tool window (File --> Project Structure... or Ctrl+Alt+Shift+S). Select the Libraries nodes and add all the specified files.

Now that the libraries are available to our project, we can starting coding our plugin (or in this example case, just add in the Demo java file as from ORDS_INSTALL_FOLDER/examples/plugins/plugin-demo/src/

The next step is to get the build process to generate the jar file. In IDEA, this is typically done by setting up an Artifact (again through the Project Structure tool window). 

The only problem is that this method on its own doesn't seem to generate the oracle.dbtools.plugin.api.di.providers file (inside the META-INF folder), which contains a list of classes that define a Provider. The @Provider annotation in the source file in effect advertises the class the D.I. frame work

However, building through Ant does include this necessary file. Here, you can make a copy of the build file in the ORDS_INSTALL_FOLDER/examples/plugins/plugin-demo folder, and tweak as necessary. This requires you to know where the libraries are stored on your file system (since IDEA doesn't actually copy them into your project folder, but references the file location when you set them up). 

The other strategy is to get IDEA to build the ant build file for you. So you would still set up the Artifact so that the generated build file includes the build jar task. After the artifact has been set up, generate the build file.

On the build menu, select the option Generate Ant Build...

Once the build file has been generated, you need to set it up as an ant build script. This is done by opening the Ant tools window (View --> Tool Windows --> Ant Build), and then adding that build script.

Then, we can generate our jar file by running the all ant target, which will clean, build and generate the artifact, which is output to PROJECT_FOLDER/out/artifacts/ARTIFACT_NAME (by default). 

Loading the plugin into ords.war

Now that we have a built plugin file (the jar), the next step is to embed it into the ords.war file. This is done with the plugin command. 

java -jar ords.war plugin Demo.jar

Where Demo.jar is the filename of the jar file that was generated from the java program. You will see output like:

May 17, 2015 5:14:43 PM oracle.dbtools.cmdline.plugin.UpdatePlugin execute
INFO: The plugin jar named: Demo.jar was added to or updated in ords.war

What this in-effect does is load your plugin into the ords.war/WEB-INF/lib folder. If a library with the same name already exists, it will simply overwrite the existing library. So, if you need to remove your plugin (I'm unaware of any official method) - you should be able to do so with the command: zip -d ords.war WEB-INF/lib/<plugin_name.jar> (I say cautiously not basing this off any ORDS documentation).

Testing the plugin

This demo in effect demonstrates connecting to the schema name that is the first part of the path in the URL, after the ords portion, and issuing a query before outputting a hello string combining the schema name and the query string parameter.

First set up a schema that you would like to test with, granting it the connect and resource privileges, as well enabled ords access to the schema.

SQL> create user demo_user identified by demo_user;

User created.

SQL> grant connect,resource to demo_user;

Grant succeeded.

SQL> connect demo_user
Enter password: 

SQL> exec ords.enable_schema;

PL/SQL procedure successfully completed.

Then, in the browser, if you go to the path: /ords/demo_user/plugin/demo?who=thomas (as defined in our plugin code), you will see the message:

DEMO_USER says hello to: thomas

In the above example, where we enabled the schema, this creates a URL mapping pattern to the same name of the schema (with the default parameters). However, this can be customised to another URL pattern by setting the parameter p_url_mapping_pattern on the call to ords.enable_schema to something else (refer to ORDS PL/SQL package documentation for more information). E.g. if we want to map it to demo_plugin instead of demo_user:

exec ords.enable_schema(p_url_mapping_pattern => 'demo_plugin');

The URL mapping pattern is important, because if you have an APEX workspace with the same name as the URL mapping pattern, you will get a clash and the workspace will generally get the priority.