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.

Export APEX application with SQLcl

APEXExport has been around a long time for exporting an application and anything else like images, feedback,websheets,.. into a file system so that they can be version controlled.  This is a must if there is ever a need to rollback or see what the application was X days ago.  This is a java program that is part of the apex distribution.  The catch for some folks is that it's a java program and being mostly DB / APEX / PLSQL people java isn't something that's normally done which makes it a tad cumbersome.


Now, enter my long flight today with nothing much to do.

SQLcl and Javascript

There's not much that can't be done with SQLcl and Javascript.  Here's the latest example that is probably the most useful example for anyone using APEX.

The benefit is that this is just a command in SQLcl just like the new INFO command or the DESC or anything else.  Just another command for developers to use.  I took the existing APEXExport which was all java around 800+ lines of code and rewrote into javascript which cut the size in half!  The reason for this is that as a java program the APEXExport had to create a connection, take db username|password|tns.  Then it also had to deal with the low lever database calls for looping resultsets and closing them after.

Just as an example, here's the normal JDBC vs SQLcl way to get a single column from a single row into a variable to use.

PreparedStatement stmt = conn.prepareStatement("select user from dual");
            ResultSet rset = stmt.executeQuery();
            rset.next();
            String userName = rset.getString(1);
            rset.close();
            stmt.close();

VS

var user = util.executeReturnOneCol('select user from dual');

To load this just grab the script from github and load it into SQLcl. Once this is done SQLcl will now have the "apxexp" command.  This is a first pass as the port of the code.  If anyone finds an issue, log a ticket or just ping me on twitter @krisrice

SQL>script apxexp

Next I'll port the APEXExportSplitter over.

ORDS 3.0.7 more secure by default

Defaulting  PL/SQL Gateway Security

Oracle REST Data Services 3.0.7 went out yesterday.  There's an important change that went in to better secure installations by default.  It has always been the case that we recommend customers set the validations for the plsql gateway.  There has always been a validation configuration option to lock down what procedures are accessible which was outlined in this blog post http://krisrice.blogspot.com/2013/01/apex-listener-plsql-validations.html


The change is that starting in this patch when the plsql gateway is enabled AND the db username is APEX_PUBLIC_USER, ORDS setup will add in the configurations setting  security.requestValidationFunction  to be set to the apex security procedure  WWV_FLOW_EPG_INCLUDE_MODULES.AUTHORIZE

Customized PL/SQL Gateway Security

This is just a defaulted settings so if you have a custom procedure just edit the file as normal. Alsoin the blog post that describes how this works there are new features over this basic procedure approach such as binding values like flow_id ( app id ), flow_setup_id ( page id ).  The uses of this are you could have one install of ORDS that is external facing and expose only one set of applications based on the APEX application ID. Then an internal ORDS setup that doesn't limit at all to have intranet applications all hosted out of one production database.

There's also the ability to use javascript for securing the PL/SQL gateway which has been around since version 2.0.  The main benefit with this approach is there doesn't have to be a trip to the database to exec a plsql procedure.  That means ORDS can stop a request before the database is ever called in any capacity.  The variables available for this are outlined in this blog post: http://krisrice.blogspot.com/2012/12/apex-listener-20.html

Here's the note in the README

Important Changes to Note

APEX_PUBLIC_USER & wwv_flow_epg_include_modules.authorize

In line with security best practice and as recommended by the Oracle Application Express Documentation when a database pool is configured to use the APEX_PUBLIC_USER, Oracle REST Data Services will automatically set the value of thesecurity.requestValidationFunction setting to be:

    WWV_FLOW_EPG_INCLUDE_MODULES.AUTHORIZE
  

This activates the white list of callable procedures which ships with Oracle Application Express and prohibits calls to other procedures. Please consult the Oracle Application Express Documentation for more information about this procedure and how to customize it's behaviour.

Kill DB sessions the easy way with SQLcl

Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here.

It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command.

Here a sample one that takes in the 2 options:
  kill sid serial#
  Example :   kill 1 2
OR
  kill USERNAME
  Example: kill KLRICE

SQLcl and Query Change Notification

The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog https://oracle-base.com/articles/10g/dbms_change_notification_10gR2


Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen.  The script is below and posted.  The catch is QCN only works on Varchars and Numbers.

ORDS and PL/SQL

Seems I've never posted about PL/SQL based REST endpoints other than using the OWA toolkit.  Doing the htp.p manually can give the control over every aspect of the results however there is an easier way.

With PL/SQL based source types, the ins and outs can be used directly without any additional programming.  Here's a simple example of an anonymous block doing about as little as possible but should get the point across of what's possible.

The interesting part is on the Parameters tab.  There is where to define the IN , OUT, or IN/OUT.  These are a INs can be from the URI or from the HTTP Header.  This means if you want USER_AGENT to be useful in the the plsql block just define it and assign it to a bind variable.  Here is the :ct and :myval being defined.  The :ct is bound to the HTTP HEADER Content-Type.  The :myval is bound to RESPONSE. More robust support for UDTs is in the works.



Hopefully, this is no surprise as to what the output looks like.

DIY SQCL Commands

As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable.  To learn more on Nashorn itself there's a lot of great write ups such as http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html  So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands.

Here's an example of creating a brand new command only from javascript.  This is a pretty simple one that for ALL command will snapshot the start time and print the elapsed time.  It also adds the new command "kris".

Just to show this is really nothing that new to sqlcl, here's a blog post from 2006 about how to make a Java based CommandListener in SQL Developer. This hasn't changed since then.

This all adds up to if we forget to add some feature, you want to override a command, perform something before or after commands, it's very simple to DIY your sqlcl.

// SQLCL's Command Registry
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

// CommandListener for creating any new command
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

// Broke the .js out from the Java.extend to be easier to read
var cmd = {};

// Called to attempt to handle any command
cmd.handle = function (conn,ctx,cmd) {
   // Check that the command is what we want to handle
   if ( cmd.getSql().indexOf("kris") == 0 ){
       ctx.write("Hi Kris, what up?\n");

       // return TRUE to indicate the command was handled
       return true;
    }
   // return FALSE to indicate the command was not handled
   // and other commandListeners will be asked to handle it
   return false;
}

// fired before ANY command
cmd.begin = function (conn,ctx,cmd) {
   var start = new Date();

   // stash something for later like the start date
   ctx.putProperty("cmd.start",start);
}

// fired after ANY Command
cmd.end = function (conn,ctx,cmd) {
   var end = new Date().getTime();
   var start = ctx.getProperty("cmd.start");
   if ( start ) {
      start = start.getTime();
      // print out elapsed time of all commands
      ctx.write("Elapsed Time:" + (end - start) + "\n");
   }
}

// Actual Extend of the Java CommandListener

var MyCmd2 = Java.extend(CommandListener, {
        handleEvent: cmd.handle ,
        beginEvent:  cmd.begin  ,
        endEvent:    cmd.end
});

// Registering the new Command
CommandRegistry.addForAllStmtsListener(MyCmd2.class);

SQLCL Monitoring itself with Longops

Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl.




The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at anytime instead of in the login.sql

script longops.js klrice klrice

The results

Putting SQL in the corner with Javascript in SQLCL

Here's a pretty small javascript file that allows for running sql in the background of your current sqlcl session.  This is a trivial example of a sql script that has a sleep in it to simulate something taking time. It also prints the SID to show it's a different connection than the base.

select 'DB SID ' ||sys_context('USERENV','SID') || ' is going to sleep' bye from dual;

begin 
  dbms_lock.sleep(15);
end;
/
select 'DB SID ' ||sys_context('USERENV','SID') ||' is  awake' hi from dual;

Here's the script as of now and it's in GitHub here

// declare the 2 java files needed
var DriverManager = Java.type("java.sql.DriverManager");
var ScriptExecutor  = Java.type("oracle.dbtools.raptor.newscriptrunner.ScriptExecutor");

var BGsql="";
for(var i=1;i>args.length;i++){
  BGsql = BGsql + " " + args[i];
}

// Create a new connection to use for monitoring
// Grab the connect URL from the base connection in sqlcl
var jdbc = conn.getMetaData().getURL();
var user = 'klrice';
var pass = 'klrice';

runme(BGsql);

//
// running the actual sql
//
function main(arg){
 function inner(){
    // make a new connect
  var conn2  = DriverManager.getConnection(jdbc,user,pass);  
  var sqlcl2 = new ScriptExecutor(conn2);  
  
  sqlcl2.setStmt(arg);
  // run it
  sqlcl2.run();
  conn2.close();
 }
 return inner;
};

// make a thread and start it up
// runs later
function runme(arg){
 // import and alias Java Thread and Runnable classes
 var Thread = Java.type("java.lang.Thread");
 var Runnable = Java.type("java.lang.Runnable");

 // declare our thread
 this.thread = new Thread(new Runnable(){
    run: main(arg)
 });

 // start our thread
 this.thread.start();
 return;
}

Tuning SQL with Javascript in SQLCL

In case anyone missed it, #sqlcl has the ability to run javascript. This opens a lot of options.  Here's a simple example that shows how to using javascript.

• Open a new Database Connection
• Collect stats on the base connection
• Do work on the main connection
• Collect stats again
• Only Print the stats that changed

In SQL Developer, the autotrace feature has for a long time selected the session statistics and display them.  Then you can diff those to another run and see what changes.  Jeff of course has blogs on this That's great but where's it leave a #sqlcl user? Enter JavaScript.

Here's the end result.  I ran a select count on all_objects filtered to DBA_%.  The things to notice is the Work Session's SID and Monitor Session's SID is different and printed to show I didn't cheat.  The command to execute is just passed as arguments to the script command.  The result is the count ran. I got the stats before and after.  Then it's just a simple loop comparing them to see what changed and print on the far right the actual difference.

The JavaSript is below and has comments enough to follow hopefully.  I'll post it to our #sqlcl github examples directory shortly.

// declare the 2 java files needed
var DriverManager = Java.type("java.sql.DriverManager");
var DBUtil  = Java.type("oracle.dbtools.db.DBUtil");


// Create a new connection to use for monitoring
var jdbc = conn.getMetaData().getURL();
var user = 'klrice';
var pass = 'klrice';
//connect
var conn2 = DriverManager.getConnection(jdbc,user,pass);
var util2 = DBUtil.getInstance(conn2);

// probably a better way to rpad a string
function pad(s,n){
   s = s.toString() + "                                                                    ";
   return ( s.substring(0,n));
}

//
// Simple function to grab the session stats and put them into JSON
//
function stats2json(sid){
     var binds = {};
     binds.SID = sid;

     var list = util2.executeReturnList("select name,value from v$statname sn,v$sesstat  s where sn.statistic# = s.STATISTIC# and s.SID= :SID",binds);
     var ret={};
     for (var i = 0; i < list.length; i++) {
             ret[list[i].NAME] = parseInt(list[i].VALUE);
     }
   return ret;
}

//
// Simple function to grab the session details and put them into JSON
//
function getSession(util){
      /* Current active SQL Connection */
      var results = util.executeReturnList("select sys_context('USERENV','session_user') u,sys_context('USERENV','SESSIONID') sessionID,sys_context('USERENV','SID') sid from dual",null);

      var ret = {};
          ret.user      = results[0].U;
          ret.sid       = results[0].SID;
          ret.sessionid = results[0].SESSIONID;

      ctx.write("\tUser:" + ret.user + "\n");
      ctx.write("\tSID:" + ret.sid + "\n");
      ctx.write("\tSession:" + ret.sessionid + "\n");

     return ret;
}

// rebuild the arguments which are the commands to run
var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];
}

// print the sessions to prove they are different
ctx.write("--Work Session--\n");
var session = getSession(util);

ctx.write("--Monitor Session--\n");
var session2 = getSession(util2);

// grabt the stats before
var before = stats2json(session.sid);

// rebuild the sql from the arguments
ctx.write("Command:\n\t"+ sql + "\n\n");
sqlcl.setStmt(sql);

// run it
sqlcl.run();

// get the after session stats
var after = stats2json(session.sid);

ctx.write("Session Stat Changes:\n\n");

// walk the stats and print the deltas
for(var key in before){
   // print only stats that changed
   if ( before[key] !=  after[key] ) {
     ctx.write( pad(key,64) + "\t\t "+ pad(before[key],12) + " --- "+ pad(after[key],12) + "\t "  );
     ctx.write( pad((after[key]-before[key]),12) + "\n");
   }
}