---
title: "Bridge the SQL-Java-JavaScript Divide with Nashorn and Oracle SQLcl"
slug: "bridge-the-sql-java-javascript-divide-with-sqlcl"
date: 2017-09-09T21:32:28Z
author: "Justin Biard"
tags:
  - "oracle sqlcl"
  - "java"
  - "javascript"
description: "Learn about writing Java classes and JavaScript programs for use with Oracle SQLcl and other Nashorn-based applications that run on the JVM."
draft: false
archive: true
---

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](http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html) 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](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html) 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](https://docs.oracle.com/javase/9/whatsnew/toc.htm#JSNEW-GUID-EF16133E-F5C2-4DEB-AA4E-A0C73E92E2C3) 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:

- Install JDK 8: [http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html](http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html)
- Install Netbeans: [https://netbeans.org/downloads/](https://netbeans.org/downloads/) (separately)

Or...

- Install JDK 8 with Netbeans (all in one): [http://www.oracle.com/technetwork/java/javase/downloads/jdk-netbeans-jsp-142931.html](http://www.oracle.com/technetwork/java/javase/downloads/jdk-netbeans-jsp-142931.html)

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.

![netbeans_download](https://icodealot.com/img/bb314e1a/netbeans_download.jpg)

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.

![Netbeans Startup](https://icodealot.com/img/bb314e1a/netbeans_startup.jpg)

#### 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](http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html)

![SQLcl Download](https://icodealot.com/img/bb314e1a/sqlcl_download.gif)

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

![SQLcl Install](https://icodealot.com/img/bb314e1a/sqlcl_install.gif)

3. 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.

![SQLcl Path](https://icodealot.com/img/bb314e1a/sqlcl_path.gif)

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.

4. 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.

![SQLcl Test](https://icodealot.com/img/bb314e1a/sqlcl_test.gif)

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:

```javascript
// 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`:

```javascript
...
// 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](https://twitter.com/krisrice/status/906498467232710656) for the tip about `print(...)` vs. `ctx.write(...)`._

3. Open your DOS or MacOS / Linux terminal and enter the following commands:

```batch
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.

![SQLcl Run Hello](https://icodealot.com/img/bb314e1a/sqlcl_run_001.gif)

4. 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)

```batch
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.

```javascript
...
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](https://www.w3schools.com/js/js_scope.asp).

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](https://github.com/oracle/oracle-db-tools/tree/master/sqlcl).

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(...)`.

```javascript
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)

![Create Project](https://icodealot.com/img/bb314e1a/hellosqlcl_create.jpg)

2. 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)

![Create Package](https://icodealot.com/img/bb314e1a/hellosqlcl_package.jpg)

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

![Create Class](https://icodealot.com/img/bb314e1a/hellosqlcl_create_class.jpg)

![Name Class](https://icodealot.com/img/bb314e1a/hellosqlcl_class_name.gif)

4. 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.

```java
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)";
    }

}

```

5. 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.)

![Clean and Build](https://icodealot.com/img/bb314e1a/hellosqlcl_cleanandbuild.jpg)

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`)

```batch
...
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](https://docs.oracle.com/javase/tutorial/deployment/jar/build.html) 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.

![SQLcl Packages](https://icodealot.com/img/bb314e1a/sqlcl_packags.gif)

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:**

```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

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

```

A `public` method

```java
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:

```javascript
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.:

```javascript
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.

![SQLcl CLASSPATH](https://icodealot.com/img/bb314e1a/sqlcl_classpath.jpg)

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`**

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

```

![SQLcl Run 2](https://icodealot.com/img/bb314e1a/sqlcl_run_002.gif)

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.

- [http://krisrice.blogspot.com/search/label/sqlcl](https://krisrice.blogspot.com/search/label/sqlcl)

- [https://docs.oracle.com/javase/8/docs/technotes/guides/scripting/nashorn/api.html](https://docs.oracle.com/javase/8/docs/technotes/guides/scripting/nashorn/api.html)

- [https://docs.oracle.com/javase/9/nashorn/nashorn-java-api.htm#JSNUG112](https://docs.oracle.com/javase/9/nashorn/nashorn-java-api.htm#JSNUG112)

- [http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html](http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html)


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

Cheers!

_Cover Photo by [Lucas Alexander](https://unsplash.com/photos/LOlMe8HfofI?utmsource=unsplash&utmmedium=referral&utmcontent=creditCopyText) on Unsplash_