Sunday, 30 September 2018

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:



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:


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.

Wednesday, 26 September 2018

Setting up SQL*Plus with the help of an Oracle Docker container

Working with Oracle Database's I like to install the Oracle instant client - probably the biggest reason for me is that it gives me access to SQL*Plus. Personally, I think the installation is very straight forward, but the number one complaint I hear is its too difficult to set up.

So, how can you get access to SQL*Plus without this "difficult" installation process?

Say hello to the Oracle instant client docker container.

If you head over to https://container-registry.oracle.com, navigate to the database section, you will notice 3 repositories:



So, you will see the second one in the list is named "instantclient" - exactly what we need!

side note: this container is a little out of date, being last updated over a year ago, but I'd say the latest version isn't crucial for most needs.

So, if you click into instantclient, you will need to sign in first so that you can accept the license agreement. You will also need to sign into the registry from your shell. Once that is done, we can grab that image with:

docker pull container-registry.oracle.com/database/instantclient:latest

With that installed, you will now want to create an alias on your OS. So, go ahead an run the following in your shell:

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

Now, if you run:

type -a sqlplus

You should see that SQL*Plus is now aliased to run that docker container.

I happen to have a VirtualBox VM running with an Oracle XE database, with the schema vmtest. It is on my local network with the IP address 192.168.0.104. So, now if I run:

sqlplus vmtest/vmtest@//192.168.0.104/xe


I'll be connected to the database, which the following screenshot demonstrates.



How simple is that! I didn't need to go through any rigorous process to install the instant client, and I now have SQL*Plus at my disposal.

Sunday, 16 September 2018

Clearing error state of an Interactive Grid column


On a recent project, there was a requirement that when they save data they don't want any validations to come into play, and it's only when they go to submit the transaction that validation errors should appear.

So I had a set up where the Save button (AJAX) was removed from interactive grid toolbar, and the form page form has two buttons: "SAVE" and "SUBMIT".


Following this design pattern, if I click SUBMIT with a validation failure in the data, the relevant row would be highlighted in the interactive grid:


Now as a user I decide I don't want to submit the page anymore, but rather just do a save.

However, clicking save I won't be able to get the page submitted without changing the value of that field (or as an alternate situation, the validation may be dependent on some other page item, which I could have corrected but still the error state of the grid/line will be present). APEX will not submit the page and I would get an error that I need to correct any errors first.


So, what we need to do is change the button behaviour to first clear the error state to ensure I can submit the page.

If you are using interactive grid, you hopefully know it is built up of multiple components, and one such component is the model (apex.model). When our validation caused a failure, the model has flagged it as not valid. So, before we submit/save the page, we need to clear that validity state which will enable the page submission to occur.

So, rather than a straight submit button, we can alter our button to run the following JavaScript code (submitting the page only after our data is set to a valid state):

var gridView = apex.region("emp").widget().interactiveGrid("getViews", "grid"),
    model = gridView.model;

model.forEach(function clearIgErrorState(record, recordIndex, recordId){
    model.setValidity("valid", recordId, "COMM")
});

apex.page.submit("SAVE")

In this example, I am specifically making it so that the "COMM" column is no logger flagged as having an error.

To see this function specs, refer to the documentation to see what other inputs it accepts.