Bridge the SQL-Java-JavaScript Divide with Nashorn and Oracle SQLcl

Justin Biard -

What do SQL, Java, Forrest Gump, and JavaScript all have in common? They run everywhere!

Buh-dum-duh... Last joke in this post, I promise. Stay with me. Oracle makes a piece of Software written in Java called SQLcl and this post is actually all about code.

I assume you are here for one of two reasons:

  1. You already know what SQLcl is and you want to learn more about how it works (under the hood) and... something about JavaScript
  2. You have heard of Nashorn (the software, not the tank destroyer) and want to learn how this scripting engine is used in real life

If you have never programmed before or heard of Java, Java Virtual Machines (JVM), JavaScript or the Oracle database then some of this post might not make sense initially but please continue reading if you want to start your learning here.

What are we talking about...

In addition to contextual information, I will cover:

  • Installing the software (Java 1.8, Netbeans 8.2 and Oracle SQLcl 17.2)
  • Writing a simple JavaScript program
  • Writing a simple Java class
  • Using Java classes within SQLcl (via Nashorn and JavaScript)

You can skip ahead to writing code if you already installed all the the software mentioned above.

As a result of going through this process, you will gain a basic understanding of how SQLcl works under the hood. You will learn how to write Java classes and use other Java libraries in your JavaScript programs with SQLcl. You will see Nashorn in action with SQLcl as the glue that enables JavaScript programs to run inside the JVM.

Installing the software

You will need access to a computer running Oracle-supported versions of Windows, Linux or Mac OS. You will need to install and run Java software. If your computer supports the latest Oracle SQL Developer then you should be good. I recommend using Java Development Kit (JDK) 8 for this post but you can optionally run JDK 9 which is still in an early access state as of September, 2017 (i.e.: for development and testing only.)

You would primarily use JDK 9 if you want to experiment with the latest JavaScript (ES6) language features supported by Nashorn.

Access to an Oracle database is not required.

Note: If you are using Linux with a desktop environment you should be able to follow along but I did not test this process outside of Windows or Mac OS.

Install Netbeans

Begin by installing Netbeans. It has a pre-requisite of Java so this is will ensure that your Java environment is ready to go.

You have choices about how you proceed from here:

Or...

Either option should follow a next -> next -> next type of installation. I did not customize anything when I went through the installs. Just for reference, I used the Netbeans installer that included "All" options on the far right.

After you have Netbeans installed, run it and make sure you don't get any errors or warnings about Java. You can close or minimize Netbeans after testing this.

Install SQLcl

SQLcl is created and maintained by the same team that develops Oracle SQL Developer. You can find links to download this from Oracle.

  1. Download the SQLcl ZIP archive from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

  1. Unzip the archive to your hard drive (I chose C:\Oracle\sqlcl)

  1. Optionally, add the ...sqlcl/bin directory to your PATH variable

This allows you to run the sql commands from the current directory in your terminal without having to specify the full path every time.

Set your path variable according to your operating system or Linux shell and for the location you extracted into. On MacOS, I added this to my PATH variable in the ~/.bash_profile script so that it will persist between logins.

  1. Open a DOS or Linux / MacOS terminal and test it out. You may need to run sql (shell script) for Mac or Linux and sql.bat or sql.exe for Windows depending on how you have things setup.

If everything goes well you should see something like the above prompt in Windows or MacOS / Linux terminal. If you get errors it will most likely be related to your Java installation or environment variables so start your troubleshooting there.

With all that taken care of, we should be good to go. Let's code!

Write a simple JavaScript program

We are going to write JavaScript code into files with a .js file extension. The program will eventually be executed by SQLcl (via Nashorn). "Hello, World!" programs are often used to test functionality and get a feel for things when learning about new tools or languages.

Let's do that now:

  1. Create a new directory or use an existing directory. I am using a directory called C:\Oracle\hello for this post
  2. Use Netbeans (File -> New File -> Other -> JavaScript File) or open your favorite text editor and create a new JavaScript file called hello.js and enter the following source code:
// declare variables
var message = "Hello, World!";

// say hello
ctx.write(message + "\n");

Note: If you don't include a line feed character "\n" at some point in your calls to ctx.write(...) you may not see any text flush to the console in your SQLcl session. The line feed forces the text to flush -or- if you don't care about SQLcl's spooling or buffered output, you can just use print(...) and it will print to STDOUT:

...
// say hello
print(message);

In practice you should not mix ctx.write(...) with print(...)

When in doubt, just stick with ctx.write(... + "\n") in your SQLcl JavaScript programs (with the added line-feed) and you should be fine.

Thank you Kris Rice for the tip about print(...) vs. ctx.write(...).

  1. Open your DOS or MacOS / Linux terminal and enter the following commands:
cd C:\Oracle\hello
sql.bat /nolog
script hello.js

Adjust the path for the first command cd <path> as needed for MacOS / Linux or the directory you chose to use.

This will start the SQLcl command prompt without trying to login to an Oracle database. (We don't need to login to Oracle for anything that follows.) The last SQLcl command will tell SQLcl to load and execute a JavaScript program called hello.js.

This program will simply write Hello, World! to the console.

  1. While still in your terminal, press the Up Arrow on your keyboard and re-run the script command as follows: (but first delete the .js extension)
script hello

By default, if no file extension is supplied SQLcl will assume you are loading a JavaScript file and it will attempt to find hello.js.

SQLcl Global References (ctx, args, Etc.)

You might have noticed that our JavaScript program included a reference to an object (ctx) that we did not create.

...
ctx.write(...);

The reference to ctx was pre-created for us and pushed into SQLcl's scripting global variable context. I won't cover basic programming concepts here but if you are new to the terminology, do some research on Variables and Local vs. Global scope.

There are several global object references that you can access from your JavaScript programs running in SQLcl, including:

  • ctx
  • args
  • sqlcl
  • utils
  • out (not yet documented)

You can find the latest notes on using these object references in your SQLcl JavaScript programs at the SQLcl page on GitHub.

You should mostly ignore the last object reference for out noted above. However, for completeness, it is something that you might use if you need to explicitly flush buffered text after calls to ctx.write(...).

ctx.write("Something without a new-line");
out.flush();

If you find inconsistent results with out.flush() then you can try adding a print(""); statement at the end of your script. And as noted earlier, when in doubt, just stick with ctx.write(... + "\n") in your SQLcl JavaScript programs (with the added line-feed) and you should be fine.

Write a simple Java class

Now that we have SQLcl working with a simple JavaScript program we will write an equally simple Java class, compile it and bundle it into a JAR file using Netbeans.

  1. Open Netbeans and create a new Java Class Library project (File -> New Project -> Java -> Java Class Library) and name it. (HELLOSQLCL)

  1. Create a new package in your project (HELLOSQLCL -> Source Packages -> <default package> -> Right-click -> New -> Java Package) and give it a name. It is conventional for Java packages to be named according to the Internet root domain of the Organization that created them. (com.icodealot)

  1. Create a new Java class file in your project (Right-click on your package -> New -> Java Class) and give it a name. (ExampleClass)

  1. Add the following Java code to your class (adjust the Java package com.icodealot or Class name ExampleClass as needed if you chose different names) and then save the file.
package com.icodealot;

public class ExampleClass {
    
    // Class methods
    public static String helloClass(String addressee) {
        return "Hello, " + addressee + "!";
    }
    
    // Instance methods
    public String helloObject(String addressee) {
        return "Hello, " + addressee + "! (object)";
    }
    
}
  1. The final step here is to Clean and Build your project. You can do this in Netbeans many different ways. (Shift+F11, Right-click on your project -> Clean and Build, Run menu, Etc.)

In Netbeans this will do several things for you but the most important part shows up in the last 2 messages in the Output. (You can see this by Window -> Output or Ctrl+F4)

...
Building jar: C:\Users\Owner\Documents\NetBeansProjects\HELLOSQLCL\dist\HELLOSQLCL.jar
...
BUILD SUCCESSFUL (total time: 0 seconds)

If you see BUILD SUCCESSFUL then everything worked as expected. If you see errors then you will need to address those before moving on. Also, take note of the path to your .jar file. We will need this for the final part of this process.

So what did we just do?

  • We wrote a Java class with two methods, both of which just return a concatenated string to say "Hello" to an addressee that is passed in as an argument.
    • helloClass() -- a static method
    • helloObject() -- an instance method
  • We compiled the .java file to JVM executable byte code (a .class file)
  • Finally, we archived the .class file into a .jar file

Now we can distribute our Java class library or use it in other Java programs.

If you are not familiar with managing libraries of compiled Java classes using Java Archive (JAR) files then you should research those topics further to understand fully what is happening.

Java classes within SQLcl

SQLcl is a Java program. Therefore, SQLcl runs inside the Java Virtual Machine (JVM) along with many other Java class libraries that all come together to make SQLcl work. If you inspect your SQLcl installation you will find a lib/ directory where SQLcl's compiled Java classes and packages are located.

Did you notice all of the .jar files? (Similar to the .jar file we created.)

The JVM is able to incorporate libraries of Java classes at runtime and make them available to other Java programs by reference. This is very similar to dynamic linking in C/C++ or importing modules in JavaScript.

Use our own class within SQLcl

The cool part about this is, through Nashorn and the JVM, your JavaScript programs also have access to use external Java packages. Let's do that now by using our own Java class in SQLcl from our JavaScript program.

1. Open hello.js in your favorite text editor or in Netbeans and replace the code with the following JavaScript:

// declare variables
var ExampleClass  = Java.type("com.icodealot.ExampleClass");
var exampleObject = new ExampleClass(); // create instance of ExampleClass
var addressee     = "World";

// Say hello
ctx.write(ExampleClass.helloClass(addressee) + "\n");
ctx.write(exampleObject.helloObject(addressee) + "\n");

What happened here?

At a high-level, the first assignment declares a JavaScript variable that stores a reference to the Java class. The second assignment declares a variable and creates a new object (an instance) of type ExampleClass.

This muddies the water a little bit with similar concepts from JavaScript. It's important to remember that these are Java language and JVM features bridged from and to the JVM by Nashorn. Remember that we declared 2 different kinds of methods in our Java class earlier.

A public static method

public static String helloClass(String addressee) { ...

A public method

public String helloObject(String addressee) { ...

In Java you can call static methods from classes without creating an instance of the class (ExampleClass.helloClass(...)), however, you cannot call instance methods (i.e.: non-static methods) without first creating a new object of that type. So for example, this code would throw an exception:

ExampleClass.helloObject(...)

This errors because the JVM does not recognize this method as a valid static class method. Java assumes that non-static methods are associated with objects (instances of classes) to be allocated on the heap before use. i.e.:

instance = new ExampleClass();

2. We need to tell SQLcl how to find our .jar file. There are several different ways to do this.

  • Update your CLASSPATH environment variable to add your .jar files (assuming your sql.bat or shell script still uses this variable)
  • On MacOS / Linux, create a folder under .../sqlcl/lib called extensions (assuming your sql shell script still uses this folder)
  • Modify the SQLcl shell or batch scripts to include your .jar file as part of the call to start Java with -cp

I am just going to pick the first approach and then call sql.bat to run SQLcl.

After you make any of the above changes you will need to stop and restart your SQLcl shell for the changes to take effect.

Note: I was not able to get sql.exe in Windows to include my .jar file. In this case you should just use the sql.bat script to launch SQLcl.

Keep this in mind. If you run into troubles with SQLcl finding your .jar file after making any changes you might need to add some echo statements to your SQLcl batch or shell scripts to ensure that it is picking up the right paths for the -cp flag to Java.

3. Now that SQLcl has access to our .jar file re-start your DOS or MacOS / Linux terminal and restart SQLcl to run hello.js

cd C:\Oracle\hello
sql.bat /nolog
script hello.js

This new program shows that SQLcl was able to load our custom Java class and also call methods from the object using JavaScript.

Thank you Nashorn!

If you run into problems or exceptions here such as java.lang.ClassNotFoundException: com.icodealot.ExampleClasss then the JVM was not able to find your class. This could mean that you need to look at your CLASSPATH configuration, your -cp settings in your sql batch or shell script or as in this example, you might have a spelling error in your JavaScript for the Java class that you created.

Conclusion

We covered a lot of ground in this post so let's recap what we learned here.

  • We started off by installing the necessary software (Java 1.8, Netbeans 8.2 and Oracle SQLcl 17.2)
  • We then moved on to writing a simple JavaScript program that printed "Hello, World!" to the SQLcl console thanks to Nashorn
  • Next we wrote a simple Java class, compiled it and created a Java Archive (JAR) that we can re-use
  • Finally, we took a look at using Java classes within SQLcl (via Nashorn and JavaScript)

This post was an introduction to the topic of using JavaScript with Oracle SQLcl and also how to interact with external Java libraries. Now that you have an understandig of what SQLcl is doing behind the scenes, there are great resources available on the Internet that will help you take things a step further. Look for practical examples of using JavaScript in SQLcl while connected to the Oracle database.

This combination of technologies makes things pretty interesting. I hope you found this post to be useful introduction.

Cheers!

Cover Photo by Lucas Alexander on Unsplash