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();
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
Twitter: @rimblas
SQLcl, The New On The Block
By Jorge Rimblas
SQLcl, The New On The Block
- 2,619