SQLcl script - set sql prompt and add container name and session id to status bar

statusbar.sql

set scan off
set define off
script

  // SQLCLs 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")
  
  // SQLCommand for command types
  var SQLCommand = Java.type("oracle.dbtools.raptor.newscriptrunner.SQLCommand")

  // DBUtil for obtaining instance for current runtime connection!
  var DBUtil = Java.type("oracle.dbtools.db.DBUtil")

  // Get data from USERENV context and store it
  function getUserEnv(util) {
    var results = util.executeReturnList("select sys_context('USERENV','SESSION_USER') SU, sys_context('USERENV','CON_NAME') CN, sys_context('USERENV','SID') SID from dual",null);
   
    var ret = {};
        ret.sess_user = results[0].SU;
        ret.con_name  = results[0].CN;
        ret.sid       = results[0].SID;

    return ret;
  }

  // Store user env
  function storeUserEnv(ctx,env) {
    ctx.write("\nUser:" + env.sess_user);
    ctx.write("\nContainer:" + env.con_name);
    ctx.write("\nSID:" + env.sid + "\n");
    ctx.putProperty("sess_user",env.sess_user);
    ctx.putProperty("con_name",env.con_name);
    ctx.putProperty("sid",env.sid);
  }

  // Clear user env
  function clearUserEnv() {
    var ret = {sess_user:"", con_name:"", sid:""};
    return ret;
  }

  // Set SQL prompt
  function setPrompt(ctx, p) {
    var prompt = (p.sess_user == "" ? "" : p.sess_user + "@") + p.con_name + ">";
    ctx.setPrompt(prompt);
    ctx.setBasePrompt(prompt);
  }

  // Refresh status bar
  function refreshStatusBar(ctx) {
    ctx.getConsoleService().getStatusBarService().refresh();
  }

  var cmd = {};

  // Called to attempt to handle command
  cmd.handle = function (conn,ctx,cmd) {
     return false;
  }

  // fired before any command
  cmd.begin = function (conn,ctx,cmd) {}

  // fired after CONNECT command
  cmd.endConnect = function (conn,ctx,cmd) {
    if ( conn != null ) {
       var util = DBUtil.getInstance(conn);
       var uenv = getUserEnv(util);
       setPrompt(ctx, uenv);
       storeUserEnv(ctx, uenv);
       refreshStatusBar(ctx);
    }
  }

  // fired after DISCONNECT command
  cmd.endDisconnect = function (conn,ctx,cmd) {
       var uenv = clearUserEnv();
       setPrompt(ctx, uenv);
       storeUserEnv(ctx, uenv);
       refreshStatusBar(ctx);
  }

  // fired after ALTER command
  cmd.endAlter = function (conn,ctx,cmd) {
    if ( conn != null ) {
      if ( cmd.getSql().trim().toLowerCase().startsWith("alter session set container") ) {
         var util = DBUtil.getInstance(conn);
         var uenv = getUserEnv(util);
         setPrompt(ctx, uenv);
         storeUserEnv(ctx, uenv);
         refreshStatusBar(ctx);
      }
    }
  }

  // Actual Extend of the Java CommandListener
  var ConnectCommand = Java.extend(CommandListener, {
      handleEvent: cmd.handle,
      beginEvent:  cmd.begin,
      endEvent:    cmd.endConnect
  });

  // Registering the new Command
  CommandRegistry.addListener(ConnectCommand.class, SQLCommand.StmtSubType.G_S_CONNECT);

  // Actual Extend of the Java CommandListener
  var DisconnectCommand = Java.extend(CommandListener, {
      handleEvent: cmd.handle,
      beginEvent:  cmd.begin,
      endEvent:    cmd.endDisconnect
  });

  // Registering the new Command
  CommandRegistry.addListener(DisconnectCommand.class, SQLCommand.StmtSubType.G_S_DISCONNECT);

  // Actual Extend of the Java CommandListener
  var AlterCommand = Java.extend(CommandListener, {
      handleEvent: cmd.handle,
      beginEvent:  cmd.begin,
      endEvent:    cmd.endAlter
  });

  // Registering the new Command
  CommandRegistry.addListener(AlterCommand.class, SQLCommand.StmtSubType.G_S_ALTER);

  // Create and register new StatusBar components

  var StatusBarComponent = Java.type("oracle.dbtools.raptor.console.StatusBarComponent");

  var comp_conname = new StatusBarComponent() {
    getName: function() {
      return "con_name";
    },
    getDescription: function() {
      return "Displays the current container name of the connected database.";
    },
    update: function(context) {
      context.append("CON:" + ctx.getProperty("con_name"));
    }
  };

  var comp_sid = new StatusBarComponent() {
    getName: function() {
      return "sid";
    },
    getDescription: function() {
      return "Displays the current session ID of the connected database.";
    },
    update: function(context) {
      context.append("SID:" + ctx.getProperty("sid"));
    }
  };

  ctx.getConsoleService().registerStatusBarComponent(comp_conname);
  ctx.getConsoleService().registerStatusBarComponent(comp_sid);
/

set statusbar add con_name
set statusbar add sid
set scan on
set define on