Monday, March 14, 2016


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.

Friday, March 04, 2016

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

// 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

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;

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


// 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);  
  // run it;
 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

Thursday, January 07, 2016

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';
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");

// run it;

// 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");

Disqus for Kris' Blog