SQL*Plus, Node and Docker

This post is a continuation of my post from earlier in the week, in which I showed how to set up SQL*Plus, through a docker container that Oracle provides in their container registry.

In that post, I set up the sqlplus command through an alias. Almost all examples I've seen in the past use an alias when setting up a command line program that is in effect a docker container.

However, I have an extension in VSCode that I use that compiles my code which in effect is calling sqlplus and running my script. Even though I was able to connect in gnome-terminal and use SQL*Plus effectively, in my extension, no output was coming through.

My code looked like this:

var ChildProcess = require('child_process');

var sqlplusArgs = ["vmtest/vmtest@//192.168.0.104", "@test.sql"];

var sqlplusProcess = ChildProcess.spawn("sqlplus", sqlplusArgs);

sqlplusProcess.stdout.on('data', (data) => {
    console.log( data.toString() );
});

The first issue here is this is only set up to display the standard output channel - we need to also give the user feedback when any errors occurred. So we can tweak this slightly to become:

var ChildProcess = require('child_process'); 

var sqlplusArgs = ["vmtest/vmtest@//192.168.0.104", "@test.sql"]; 

var sqlplusProcess = ChildProcess.spawn("sqlplus", sqlplusArgs);

function printOutput(data){
    console.log(data.toString());
}

sqlplusProcess.stdout.on('data', printOutput);
sqlplusProcess.stderr.on('data', printOutput);

So just a reminder, the alias command looked like this in my previous post:

alias sqlplus='docker run -ti --rm container-registry.oracle.com/database/instantclient sqlplus'

So now when we try and run this little node demo program, we get an exception:

events.js:167
      throw er; // Unhandled 'error' event
      ^

Error: spawn sqlplus ENOENT

So, it looks like node isn't finding the alias we set up, and per this logged issue: https://github.com/nodejs/node/issues/3916, bash doesn't expand aliases in non interactive shells.

Ok, so instead of using an alias we should consider wrapping our docker command in a shell script. So we end up with a script per:

#!/bin/bash
docker run \
    -it \
    --rm \
    container-registry.oracle.com/database/instantclient sqlplus \
    "$@"

I've already set this up on my path and removed my alias, so I can go ahead and try my little node program again. This time, we get the following error:

the input device is not a TTY

This is an easy fix. In our original docker run command we passed in the arguments -it. The -i is for interactive and the -t is for TTY. So we can safely go ahead and remove that from our shell script. So, line 3 in the shell script above would just read: -i \.

At this point, we are not entirely ready, because with SQL*Plus, we want to be able to compile scripts, and suppose I'm within the folder: /home/trent/Projects/blog/ and have a script test.sql. If I launch sqlplus, it won't have access to those files, which you will see if you try to run:

sqlplus vmtest/vmtest@//192.168.0.104/xe @test.sql

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SP2-0310: unable to open file "test.sql"
SQL>

So this solution to this in 2 parts. Firstly, exposing your files into the container. Personally, I think it makes enough sense that you should just expose your entire home folder where, at least for me, is most likely where all my source folders will be stored. So, I append the following flag to my docker run command:

-v $HOME:$HOME.

This alone will be perfect if I want to compile with absolute paths. e.g.

sqlplus vmtest/vmtest@//192.168.0.104/xe @/home/trent/Projects/blog/test.sql

But usually you will probably just be specifying a relative path. So, we need to set the working directory. The docker run command has a flag for this too, so we can add in:

-w $(pwd)

So finally, the docker run command in our shell script would look like:

#!/bin/bash
docker run \
    -i \
    --rm \
    -v $HOME:$HOME \
    -w $(pwd) \
    container-registry.oracle.com/database/instantclient sqlplus \
    "$@"

So that brings us to the next point. Performance.

You are surely aware by now that Oracle now has a new command line tool for connecting to the database named SQLcl, which is effectively a drop in replacement for SQL*Plus.

So after my last post, there was a reply from Jeff Smith:

You could just grab the 20mb @oraclesqlcl ...even easier/faster

— Jeff Smith 🥃 ☜ (@thatjeffsmith) September 27, 2018

One of the reasons I advocate having SQL*Plus installed is because of the plugin I already mentioned that I have in VSCode (there are similar ones for Atom and Sublime) makes use of SQL*Plus (or SQLcl) to connect to the database and compile the active script/file. Unlike say SQL Developer, the connection isn't kept alive. It's purely launching SQL*Plus (or SQLcl) to compile the script, and then exits. And the startup time is so much faster with SQL*Plus. From my experience, SQL*Plus is well under 1 second to startup and run the script, where SQLcl takes at least 3 seconds or more.

Since this post, and the last, were about running SQL*Plus through a docker container, a co-worker replied:

I’ve experienced this. It does make a big difference.
Trent, even through docker sqlplus is faster? That surprises me

— Jorge Rimblas (@rimblas) September 27, 2018

In my initial testing, it was still very fast to startup. But don't take my word for it, let's get some hard facts. We can make use of the time command to startup each environment, immediately issue an exit statement to see how long the round trip takes in each.

1. SQL*Plus installed natively

3 tests produced: 0.010s, 0.003s, 0.009s (in that order) for user time


2. SQL*Plus running through a Docker container

3 tests produced: 0.073s, 0.065s, 0.065s (in that order) for user time (a little longer than a native installation though probably not enough of a difference to be noticeable by the user)

3. SQLcl

3 tests produced: 3.513s, 3.399s, 3.560s (in that order) for user time

So as you can see, not a lot of difference with SQL*Plus installed natively or by running through a container.