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

 

Do you sql*plus?

Then you SQLcl!

Connections

EZConnect

TNS

LDAP

Wallet

Information

show version

show connection

show jdbc

show tns

show login

help

set sqlformat

  • Try: set sqlformat ansiconsole
  • Try: set sqlformat csv
  • Try: set sqlformat json
  • Try: set sqlformat insert

info & info+

  • All objects
  • Try with packages
  • Try with package.procedure

history

  • Try: history time
  • Try: "history #" to load buffer

inline editing

  • Up/Down/Left/Right Arrows
  • ESC: back to SQL prompt
  • CTRL-w: Beginning of buffer
  • CTRL-a: Beginning of current line
  • CTRL-e: End of current line
  • CTRL-r: Execute buffer
  • TAB: Complete current word

ddl

  • set ddl storage off
  • set ddl pretty on

CTAS

  • Uses DBMS_METADATA.GET_DDL

alias

  • Try: alias list {name}
  • Try: alias name=select...

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

Links

SQLcl,
The new kid on the block

Jorge Rimblas

rimblas.com/blog/presentations

Twitter: @rimblas