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;
}