sqlcl_js_returning.js

//
//create table todo(id NUMBER GENERATED by default on null as IDENTITY, todo_for varchar2(200));
//

var DBUtil = Java.type("oracle.dbtools.db.DBUtil");
var binds={};
binds.name="Oliver";

sql = "insert into todo(todo_for) values(:name)";

// String array of returning columns
retCols = Java.to(["ID",],"java.lang.String[]");

// prep passing return cols
var ps = conn.prepareStatement(sql,retCols);
// helper class to do the binds
 DBUtil.bind(ps,binds);

 // execute
if (ps.executeUpdate() > 0) {

    // getGeneratedKeys() returns result set of keys that were auto
    var generatedKeys = ps.getGeneratedKeys();

    // generatedKeys is a resultSet 
    if (null != generatedKeys && generatedKeys.next()) {
        // data type of return
        ctx.write(generatedKeys.getMetaData().getColumnTypeName(1) + "\n")
        // return value !
         ctx.write( generatedKeys.getInt(1)  + "\n" ) ;
    }
}
/*

KLRICE@klr> select * from todo;

no rows selected
KLRICE@klr> script sqlcl_js_returning.js
NUMBER
17
KLRICE@klr> select * from todo;

   ID TODO_FOR
_____ ___________
   17 Oliver


*/