Scripting SQLcl

JDK 11 required

Versions newer than 26.2 require JDK 11 or newer. Unfortunately, this JDK does not include the script engine anymore so that script does no longer work, see The Return of SQLcl's SCRIPT Command.

Global objects

The scripting environment of SQLcl has some global objects (members of this):

Object	Type	JAR file
`args`	`java.lang.String[]`
`commands`
`conn`	`oracle.jdbc.driver.T4CConnection`
`ctx`	`oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext`	`oracle.dbtools-common.jar`
`engine`	`jdk.nashorn.api.scripting.NashornScriptEngine`
`out`	`oracle.dbtools.raptor.newscriptrunner.WrapListenBufferOutputStream`
`sqlcl`	`oracle.dbtools.raptor.newscriptrunner.ScriptExecutor`	`oracle.dbtools-common.jar`
`util`	`oracle.dbtools.db.OracleUtil`	`oracle.dbtools-common.jar`
… ?

args

The variable args is an array that stores the values of parameters that are passed to a script that was invoked with the script command. The value of the first element (args[0]) stores the name of the script.

The following trivial script prints the script name and the values of the arguments that were passed to it:

print('Number of arguments: ' + args.length)
print('The values of the arguments are:');
for (argNo in args) {
  print('- ' + args[argNo]);
};

Github repository about-SQLcl, path: /script/global-objects/print-args.js

util

script
print(util.getClass())
/
class oracle.dbtools.db.OracleUtil

util.executeReturnOneCol

The following example uses the pseudo column user to determine the currently connected user name. (Of course, this name can more easily be found via conn.getUserName())

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

if (whoAmI == 'RENE') {
  print('yeah');
}
else {
  print('no');
}
/

Using bind variables:

script
print(util.executeReturnOneCol('select :B from dual', {B: 'hello world'}));
/

util.executeReturnList

script
var objs = util.executeReturnList(
    "select                       " +
    "  object_name name,          " +
    "  owner                      " +
    "from                         " +
    "  all_objects                " +
    "where                        " +
    "  object_name like :name and " +
    "  object_type = :type        "
    , 
    { name: '%RS%',
      type: 'TABLE'
    });

objs.forEach( function(obj) {
   print(obj.NAME + ' (' + obj.OWNER + ')');
});

/

Unfortunatetly, multiline strings (aka template literals) don't seem to be supported in the Nashorn interpreter, therefore I needed the clumsy addition of substrings to make the select statement visually appealing.

conn

conn is the connection object.

script
print(sqlcl.getClass())
/
class oracle.jdbc.driver.T4CConnection

Apparently, oracle.jdbc.driver.T4CConnection derives from oracle.jdbc.driver.PhysicalConnection which in turn implements the interface oracle.jdbc.OracleConnection.

script
print(conn.getUserName())
/
RENE

Show connection URL:

connUrl = conn.getMetaData().getURL()
print(connUrl)

sqlcl

sqlcl seems to be a class that represents SQLcl itself:

script
print(sqlcl.getClass())
/
class oracle.dbtools.raptor.newscriptrunner.ScriptExecutor

Execute an arbitrary SQL statement:

sqlcl.setStmt('select username, created from dba_users order by created')
sqlcl.run()

However, executing oradebug is not supported:

script
sqlcl.setStmt('oradebug help')
sqlcl.run()
/
not supported.

ctx

The global(?) object ctx is an instance of the oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext class.

script
print(ctx.getClass())
/
class oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext

Among others, this class allows to set the prompt of SQLcl:

script
ctx.setPrompt(conn.getUserName() + '> ');
/

There are also a handful of properties (that also could be set):

script
print(ctx.getProperty('script.runner.serveroutformat'));
print(ctx.getProperty('script.runner.setnumwidth'));
print(ctx.getProperty('script.runner.autocommit.setting'));
/

Request data (input) from the user:

var x = ctx.getPromptedFieldProvider().getPromptedField(ctx, 'What? ', false);
print('x = ' + x);

Accessing the history

script
var hist = Java.type('oracle.dbtools.raptor.console.MultiLineHistory').getInstance();
print('max history size: ' + hist.getMaxSize());
hist.setMaxSize(500);
print('max history size: ' + hist.getMaxSize());
/

java.lang.Math

java.lang.Math is available:

script
print(java.lang.Math.sqrt(2));
/

load()

load(url|filename) loads and executes another script.

Command handlers

A user defined command handler can be defined with three functions to be called before a command is executed, after a command was executed and a handler that actually executes the command:

Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry").addForAllStmtsListener( 
   Java.extend(Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener"),
   {
      handleEvent: function(conn, ctx, cmd) {
   
         if ( cmd.getSql().trim().equals('sysdt') ) {
             sqlcl.setStmt('select sysdate from dual');
             sqlcl.run()
   
             return true; // cmd was handled
         }
         return false;    // cmd was not handled
      },
   
      beginEvent: function(conn, ctx, cmd) {
         ctx.writeln('begin handler, cmd.getSql = ' + cmd.getSql())
      // ctx.write('type: ' + cmd.getClass() + '\n'); // class oracle.dbtools.raptor.newscriptrunner.SQLCommand
      },
   
      endEvent:   function (conn, ctx, cmd) {
         ctx.writeln('end handler, cmd.getSql = ' + cmd.getSql())
      }
   
   }).class
);

TODO

The global object seems to be referencable with this:

script
print(this);
/
[object global]

There's an engine object:

script
print(new javax.script.ScriptEngineManager().getEngineFactories()[0].getEngineName());
print(engine);
/
Oracle Nashorn
jdk.nashorn.api.scripting.NashornScriptEngine@1e7a35c

print(ctx.getDate('xyz'))