Christoph's 2 Cents

A Backup for My Brain!

JavaScriptOracle DevelopementOracle ToolsPL/SQL

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'

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

    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++) { = args[ndx];
    print('\nFetching ',;

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