ACCEPT is a great way to get input but validation has to happen elsewhere as there's no hooks to do something like validate a number in a range without running a sql or plsql that does the validation.

As I've covered in many examples on this blog and in our github repo of examples, SQLcl has the ability to do scripting. Using a few lines of javascript, I can prompt, validate, loop waiting for a better answer from the user. This is a very simple example that loops waiting for the correct answer printing a message with each failure. While this is trivial example it shows how easy it could be to have a custom script that prompts for values then with some scripting validate those values and put it into SQLcl's list of variable used for substitutions.

Thursday, November 17, 2016

This blog post brought to you by the letter M as in Martin . Follow @martindsouza . He asked me on twitter if SQLcl via Nashorn could use ECMA Script 6 features yet. The answer is yes. So, for the brave that want to try out the latest greatest ECMA Script 6 features it can be done with SQLcl.

This is a good reference for what's in version 6 that could be useful. The only caution is not everything is built into nashorn quite yet. For Example destructuring is not there.
http://es6-features.org/#StringInterpolation

The Template Literals are probably to me the most interesting thing since it cleans up all things like string concats and multi line string.

Here's how to get it working:
Step 1: Get Java 9 https://jdk9.java.net/download/
Step 2: Set the enviroment variable : APP_VM_OPTS="-Dnashorn.args=--language=es6"
Step 3: Go.

Complete side note as you can see in my default PS1, apple doesn't and has never understood you don't add an 's to a noun ending in a s.

However the script itself is much nicer to read ( to me anyway ) . What will stand out is multi line string with using a back tick. Also is the evaluating of variables inside the quoted string. No more string concatenations!

Here's that counterpart which is leveraging apex_application_install for setting options to be used. This screenshot is showing loading the command straight from github which I wouldn't recommend if there is ever a need to be offline using the command. There's a Download/Clone button right on the github space home page then all the file are local and be used anytime regardless of network connectivity.

When the command is called it echos out all the options being used. In the case of multiple workspaces and none being specified right or wrong it's going to use this sql to determine which to use:
select min(workspace_id) wsID from apex_workspace_schemas where schema = user

Tuesday, November 15, 2016

I got a question over the weekend if SQLcl could be leveraged as library from inside a Jython program. This may seem like it's the same thing as adding jython to SQLcl to use instead of Javascript but it's a significant difference. This method allows for the use of SQLcl in any existing program. That means when the program needs to interact with the database, all the code in SQLcl that is tried and true can be leveraged.

This is a simple example to prove it's possible. First the jar files need to be added to Jython's command line.

Now that the needed libraries are in the path. It's quite easy to start using the same methods and classes that have been shown in various javascript examples. This example gets a connection on it's own then instantiates SQLcl for programmatic use.

Checked if something is reserved seems like a nice add on for the tooling. This example adds a SQL>reserved command that checks all words passed to see if they are in the single source of truth which is v$reserved_words

The outcome is something like this.

The code is in the normal location on github. It follows the same CommandRegistry/CommandListener that was used in lots of the previous examples.