SQLcl JavaScript: Using Bind Variable in Queries

Here are some quick examples of using bind variables in SQL queries in JavaScript inside SQLcl.

Usage: Copy the examples into a file with a .js extension: example.js

Execute the script in SQLcl using:

SQL> script example.js

//Create and empty object
var binds = {} 

//Create multiple bind values
binds.job = 'CLERK';
binds.sal = 1000;

//Return the results into ret variable
var ret = util.executeReturnList("select empno,ename,job, sal from emp where job =upper(:job) and sal < :sal",binds);
 
//Loop over results and print details
for (idx=0;idx<ret.length;idx++){
    print( "$" + ret[idx].SAL  + "\t" + ret[idx].ENAME+ "\t" + ret[idx].JOB);
}

Example for processing a comma separated list and setting the binds for each value

SQL> script example.js

var binds = {} 
var str = 'king,blake,clark'
str=str.split(',');

for (ndx=0;ndx<str.length;ndx++){
    binds.name = str[ndx];
    print('\nFetching ',binds.name);

    var ret = util.executeReturnList("select empno,ename,job from emp where ename =upper(:name)",binds);
    
    for (idx=0;idx<ret.length;idx++) {
        print( ret[idx].EMPNO  + "\t" + ret[idx].ENAME+ "\t" + ret[idx].JOB);
    }
}

This example uses command line arguments as inputs for the binds.

SQL> script example.js miller king smith

var binds = {} 

//loop starts at index 1
for (ndx=1;ndx<args.length;ndx++) {

    binds.name = args[ndx];
    print('\nFetching ',binds.name);

    var ret = util.executeReturnList(
        "select empno,ename,job " + 
        "  from emp" + 
        " where ename =upper(:name)"
        ,binds
    );
    
    ret.forEach(function (row) {
        print( row.EMPNO  + "\t" + row.ENAME+ "\t" + row.JOB);
    })
};