Monday, February 29, 2016

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


Monday, February 01, 2016

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