SQLcl custom Input prompt and validations

Another quick twitter inspired blog post inspired by the SQLcl account itself.

Asked and answered: Do you support the ACCEPT command? YES. pic.twitter.com/NchmQ6Eegs

— SQLcl (@oraclesqlcl) November 29, 2016

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.

ECMA Script 6 / Nashorn / Java 9 and SQLcl

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.

kriss-MacBook-Pro:bin klrice$ export APP_VM_OPTS="-Dnashorn.args=--language=es6"
kriss-MacBook-Pro:bin klrice$ ./sql klrice/klrice

SQLcl: Release 4.2.0 Production on Thu Nov 17 09:22:35 2016

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

Last Successful login time: Thu Nov 17 2016 09:22:36 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> script /Users/klrice/workspace/raptor_common/examples/sql.js
Using Binds:[object Object]

  ***************************************************************
  *****    SIMPLE LOOP OF LIST OF LIST                ***********
  ***************************************************************
 OBJECT_TYPE    OBJECT_NAME 
 TABLE    EMPLOYEES 



  ***************************************************************
  *****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********
  ***************************************************************
 TABLE    EMPLOYEES  
SQL> 

Now this probably looks just like the script from my first blog on how to use javascript http://krisrice.blogspot.in/2015/10/sqlcl-oct-13th-edition.html


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!

Import APEX apps now easier with SQLcl

Hopefully by now everyone knows there's the ability in SQLcl to use Javascript and the same javascript can be used to make custom commands extending what SQLcl offers out of the box.

Also, I hope everyone knows we are posting examples of this to github as a base for anyone to learn and try it out.  Just in case here's the link to the GitHub location https://github.com/oracle/oracle-db-tools/tree/master/sqlcl

The last example I did was for ApexExport which took the tried and true ApexExporter java utility that ships with Apex and converted it to JavaScript. http://krisrice.blogspot.com/2016/10/export-apex-application-with-sqlcl.html

One of first questions was from Vito about an import counterpart.

@krisrice Is there an equivalent apximp to the apxexp in the pipeline? #sqlcl

— Vito Campanelli (@VitoDCampanelli) October 19, 2016

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

If that's not correct, no problems just pass in -workspaceid

KLRICE@orcl >apximp  -file f102.sql -offset 123 -name Kris -alias rice  -installSupportingObjects
*** USING DEFAULT WORKSPACE *** 
*** KLRICE *** 
** Importing with the following options **
Application Alias :rice
Workspace ID :1930920493850173
Application Name :Kris
Offset :123
****

The script is here:

SQLcl as a library in existing programs

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.

# all files in sqlcl/lib/*
# adjust to your install ( unzip ) of sqlcl
LIB=/Users/klrice/workspace/raptor_common/sqlcl/built/lib/
CP=
for  f in $(ls $LIB/*.jar); do
 echo $f
 CP=$CP:$f
done
echo --- $CP ---

#Start up jython 
jython -J-cp $CP 

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.

#import various things
from java.sql import DriverManager
from oracle.dbtools.db import DBUtil
from oracle.dbtools.raptor.newscriptrunner import *

#plain ol jdbc connection
conn  = DriverManager.getConnection('jdbc:oracle:thin:@//localhost:1521/orcl','klrice','klrice');

#get a DBUtil but won't actually use it in this example
util  = DBUtil.getInstance(conn);

#create sqlcl
sqlcl = ScriptExecutor(conn);

#setup the context
ctx = ScriptRunnerContext()

#set the context
sqlcl.setScriptRunnerContext(ctx)
ctx.setBaseConnection(conn);

#change the format
sqlcl.setStmt('set sqlformat json');
sqlcl.run();

#run the sql
sqlcl.setStmt('select * from emp');
sqlcl.run();

The output of this is simply the json output.

Adding Reserved command in SQLcl

I saw Stephen's example of checking reserved words in the database from Vertan's day and figured I'd do the same in SQLcl.

#595 #plsql Is it a reserved word? PL/SQL procedure to help you sort that out. Dyn PLSQL example! @oraclelivesql https://t.co/M10kVnsQ3y pic.twitter.com/XFFHOVzNCK

— Steven Feuerstein (@sfonplsql) November 11, 2016

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.