# Setting up Oracle SQLcl under GraalVM 17

## Downloading

I downloaded the Windows version of GraalVM for Java 17: I Surfed to [https://www.oracle.com/java/technologies/downloads/](https://www.oracle.com/java/technologies/downloads/). There I chose the tab labeled `GraalVM for JDK 17` (so not `JDK 20`, `JDK 17` nor `GraalVM for JDK 20`)

The subpage indicates that  
*GraalVM for JDK 17 binaries are free to use in production and free to redistribute, at no cost, under the GraalVM Free Terms and Conditions. (*[*https://www.oracle.com/downloads/licenses/graal-free-license.html*](https://www.oracle.com/downloads/licenses/graal-free-license.html)*)*

The reason I chose 17 over 20, is that the 17 is a Long Term Release (until September 2024), while the 20 is not (only until September 2023, next month, when GraalVM for JDK 21 comes out. (It looks like 21 is the next LTS.))  
Afterward, I also read somewhere that SQLcl does not support version 20 yet.

I choose `Windows` to get a download for that platform. That resulted in the file `graalvm-jdk-17_windows-x64_bin.zip`.

I also found out that I would be needing a plugin to run Javascript in GraalVM. (Since SQLcl can make use of that.)  
I hopped over onto [https://www.oracle.com/downloads/graalvm-downloads.html](https://www.oracle.com/downloads/graalvm-downloads.html). There, I once again indicated version 17 for Windows and somewhere in the middle of the resulting page is a link to download the `JavaScript Runtime` That resulted in `js-installable-jdk-17-windows-amd64-23.0.1.jar`. After trying the installation a first time, I found out that the `JavaScript Runtime` depends on two other plugins: `ICU4J Plugin` and `Tregex Plugin`. Downloading these (from that same page as the first plugin) resulted in `icu4j-installable-jdk-17-windows-amd64-23.0.1.jar` and `regex-installable-jdk-17-windows-amd64-23.0.1.jar`.

## Installing GraalVM

...boils down to unzipping the zip somewhere, adding its bin directory to the `PATH` and setting the main directory as `JAVA_HOME`. The zip contains one folder which is best preserved in its entirety (including its name). So, I made folder `C:\pf\GraalVM` and dragged `graalvm-jdk-17.0.8+9.1` from the zip into there, resulting in `C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin` (and others).

The plugins were still in my downloads folder: `C:\Users\jvdboss1\Downloads*-installable-jdk-17-windows-amd64-23.0.1.jar` They need to be installed into Graal using its `gu` command (GraalVM Component Updater), which resides in its `bin`\-folder:

```powershell
cd C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin
gu -L install "C:\Users\jvdboss1\Downloads\icu4j-installable-jdk-17-windows-amd64-23.0.1.jar"
gu -L install "C:\Users\jvdboss1\Downloads\regex-installable-jdk-17-windows-amd64-23.0.1.jar"
gu -L install "C:\Users\jvdboss1\Downloads\js-installable-jdk-17-windows-amd64-23.0.1.jar"
```

Running `gu list` confirms that I have GraalVM, icu4j, regex and js installed, all version 23.0.1.

At this moment, from that `bin` directory, running `java -version` returned `Java(TM) SE Runtime Environment Oracle GraalVM 17.0.8+9.1 (build 17.0.8+9-LTS-jvmci-23.0-b14)` OK :)

## Settings

Running `echo %path%` or just `path` showed that another Java was there: `C:\ProgramData\Oracle\Java\javapath`. This is probably something installed by my employer (I don't write any Java programs for my job). It turns out to be a shortcut-folder ("junction") and it contains (only) `java.exe`, `javaw.exe` and `javaws.exe`...  
The junction points to its sibling: `C:\ProgramData\Oracle\Java\javapath_target_474953`  
I decided to leave that in the `PATH`, but put the GraalVM `bin`\-folder first. So now there is `...;C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1\bin;C:\ProgramData\Oracle\Java\javapath;...`

While being in the Environment Variables GUI of Windows, I also set `JAVA_HOME` and gave it the value `C:\pf\GraalVM\graalvm-jdk-17.0.8+9.1`.

After restarting the command line prompt `echo %path%` and `echo %java_home%` confirm that these settings are there. From the default directory, running `java -version` tells me that GraalVM 17 is being used :)

I already had a SQLcl installation. I went to its `bin` directory and started it:  
`cd C:\pf\sqlcl\bin sql /nolog`  
And it still started. :) Doing `show java` confirmed that it had picked up the `JAVA_HOME` setting.

## Upgrading SQLcl

Installing a new version of SQLcl is also just about unzipping a zip. From [https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/](https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/) I downloaded `sqlcl-23.2.0.178.1027.zip` After making sure no more SQLcl was running and all command windows were no longer in the folder, I renamed my existing `C:\pf\sqlcl` to `C:\pf\sqlcl_22_3` (since that was the old version I had).  
Next, I dragged the `sqlcl` folder from the zip to my `C:\pf` folder.

And that is how you upgrade SQLcl :)

You can always use [https://download.oracle.com/otn\_software/java/sqldeveloper/sqlcl-latest.zip](https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip) to directly get the *latest* version, whatever it is. Ideal for DevOps.

SQLcl falls under the [Oracle Free License](https://www.oracle.com/downloads/licenses/oracle-free-license.html)

I already had `C:\pf\sqlcl\bin` in my path, so running `sql /nolog` from anywhere starts SQLcl :)

## login.sql

While SQLcl works fine out of the box, obviously, it has many settings which you may want to apply each time. To do that, you can create a `glogin.sql` or `login.sql` script, just like for SQL\*Plus.

Running `show login` shows you where that script can reside so that SQLcl picks it up. In my case (having an old fat client installed too) it was:

* `C:\Oracle\product\12.2.0\client_1\sqlplus\admin\glogin.sql`
    
* `.\login.sql`
    
* `C:\Oracle\product\12.2.0\client_1\dbs\login.sql`
    

Notice that the 2nd entry implies that you can have different `login.sql` scripts per project/environment/..., just by starting SQLcl from a certain directory.

I set up the statusbar, syntax highlighting and DDL generation config. I relaxed the history filter a bit, confirmed the sqlformat (query output), allow empty lines in SQL-scripts and set up the Trivadis Formatter.  
Also very important: I explicitly turn autocommit off, but exitcommit on.

That results in the following `login.sql` script

```plaintext
SET statusbar ON
SET statusbar editmode linecol java cwd encoding username dbid txn timing

SET highlighting ON
--SET highlighting DEFAULT RESET --duh
SET highlighting COMMENT foreground GREEN
--SET highlighting COMMENT background DEFAULT --same as default
SET highlighting STRING foreground MAGENTA
--SET highlighting STRING background DEFAULT --same as default
SET highlighting NUMBER foreground MAGENTA
--SET highlighting NUMBER background DEFAULT --same as default
--SET highlighting PUNCTUATION foreground DEFAULT --same as default
--SET highlighting PUNCTUATION background DEFAULT --same as default
SET highlighting KEYWORD foreground YELLOW
--SET highlighting KEYWORD background DEFAULT --same as default
SET highlighting IDENTIFIER foreground CYAN
--SET highlighting IDENTIFIER background DEFAULT --same as default
--SET highlighting ERROR foreground RED     --same as default
--SET highlighting ERROR background DEFAULT --same as default
--SET highlighting ERROR underline ON       --same as default

SET ddl CONSTRAINTS_AS_ALTER off
SET ddl FORCE off
SET ddl SEGMENT_ATTRIBUTES off
SET ddl SIZE_BYTE_KEYWORD off
SET ddl STORAGE off

-- Include connect and show in the history
-- Note that connect implies a security risk when passords are entered in the connect statement.
-- But normally, only stored connections are used.
-- When exceptionally making a manual connection, don't include the password in the connect statment.
-- Provide it separately. SQLcl will ask for it.
SET history FILTER history clear

SET sqlformat ANSICONSOLE
--SET sqlformet JSON-FORMATTED
--SET sqlformat delimited ; " "

-- Allow empty lines in SQL statements (Even catproc.sql has them)
SET sqlblanklines ON

-- Use command tvdformat to call the Trivadis formatter
script C:\Repos\Trivadis-PLSQL-Formatter-Settings\sqlcl\format.js --register
-- Set up tformat alias to call tvdformat with our own settings to format the buffer
alias tformat=tvdformat * xml=C:\Users\jvdboss1\Documents\Modified_trivadis_formatter.xml arbori=C:\Repos\Trivadis-PLSQL-Formatter-Settings\settings\sql_developer\trivadis_custom_format.arbori ;

SET autocommit OFF
SET exitcommit ON
```