SQLcl,
The new kid on the block

Jorge Rimblas

Age: 13 years!
Staff: 80+ employees
68 consultants/developers
2015: 60% Growth
APEX Solutions: 12 Years!
Largest APEX practice in North America
Oracle Center of Excellence

Information

show version

show connection

show jdbc

show tns

show login

help

scripting

• Command: script
• Uses Nashhorn: Meaning JVM languages
  • groovy,perl,javascript,pascal

scripting

ctx.write("Hold my beer, I'm going to try something\n\n");
 
ctx.write('hi from javascript\n');
 
ctx.write('This is a count of my objects');
sqlcl.setStmt('select count(1) from user_objects; ');
sqlcl.run();
 
var ps1= 'set sqlprompt "@|blue  _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue 🍼 >|@"'
 
ctx.write('Now javascript will change the sqlprompt\n');
sqlcl.setStmt(ps1);
sqlcl.run();
 
sqlcl.setStmt('@numbers.sql');
 
sqlcl.run();

http://krisrice.blogspot.com/2015/10/sqlcl-oct-13th-edition.html

scripting

load('https://raw.githubusercontent.com/oracle/Oracle_DB_Tools/master/sqlcl/lib/helpers.js');
 
/* Clean out the table just to prove it's not a baked demo */
sqlcl.setStmt("truncate table k");
sqlcl.run();
 
/* File name */
var files= helpers.exec('find . -maxdepth 1 -type f ').stdout.split('\n');
 
/* bind map for reuse */
var binds = helpers.getBindMap();
 
for(f in files ) {
   //  ctx.write("Loading : " + files[f] + "\n");
  /* load the blob */
  blob = helpers.getBlobFromFile(files[f]);
 
  /* assign the binds */
  binds.put("path",files[f]);
  binds.put("b",blob);
 
 /* Just do it */
  var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);
}
 
/* print the results to see if it worked */
sqlcl.setStmt("select path,dbms_lob.getlength(blob_content) from k order by when desc;");
sqlcl.run();

Loading files as blobs