Oracle SQLcl Overview, Celebrating 5 Years!

Overview of the compelling features brought to you by Oracle SQLcl, your modern command-line interface for the Oracle Database.

Want the DDL for a table, view, or stored procedure? That's a simple DDL command!

Want your output in json or xml or INSERT statements? Again, just a simple 'set sqlformat' command.

See all this LIVE in our video recording of our 5 year birthday party webinar here
https://www.youtube.com/watch?v=a9qeXw1PVDE

1. Jeff Smith
2. Modern Tooling Empowers Users, Increases Productivity Use the right tool for the right job Desktop REST APIs CLI WEB Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.
3. User Friendly Interface ▪ UFI 1979 SQLDev 1.1 ▪ SQL Worksheet with Script Engine (F5) ▪ Basic SQL*Plus Support ▪ Ships with 11gR1 SQL*Plus ▪ UFI Advanced ▪ Ships with Oracle v5 2007 1985 2008 2015 2016 SQLcl Early Adopter ▪ takes existing SQL*Plus engine in SQL Developer to a new CLI sqlplusW & iSQL*Plus discontinued ▪ no longer shipped as of Oracle Database 11g 1998 iSQL*Plus ▪ Web Based SQL*Plus ▪ Ships with 8i (Internet) 1993 SQL*Plus GUI ▪ sqlplusW.exe ▪ Windows ONLY SQLcl Released ▪ support tied to Database ▪ stand-alone or bundled ▪ ships with 12cR2
4. Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved. • Best of SQLDev & SQL*Plus • Auto-Formatting • Persistent Query History • Power of JavaScript • Custom Commands • vi & Emacs • Liquibase • OCI Integration • Data Pump & Data Guard https://www.oracle.com/sqlcl SQLcl
5. Accessible from anywhere, easily Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 5
6. Commands, new from SQL*Plus are Underlined 6
7. It’s Automatic! Query Output Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 7 • Fit to screen, default (ANSICONSOLE) • Color Coded • CSV • DELIMITED • HTML INSERT • JSON • JSON-FORMATTED • XML
8. set sqlformat or /*format*/ Formatting Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.
9. HELP, SET, & SHOW are your friends Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 9
10. Edit Mode, Prompt, Statusbar Editing Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 10
11. Inline editing with vi command support & the statusbar Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 11
12. History persists between instances, except for FAILED statements/scripts History Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 12
13. Object names, keywords, commands, and files! Code Completion/Insight Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 13
14. Tell me more about my database objects DESC, INFO, & INFO+ Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 14
15. Object Metadata, Comments, Primary Keys, Indexes, and Foreign Keys INFO(RMATION) – DESC but so much Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 15
16. Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 16 Instead of Column Comments, Show Statistics INFO+
17. We print the ANON BLOCK required to execute said program INFO for PL/SQL Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 17
18. Change directory… Commons tasks: CD Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 18
19. We grab ALL the DDL for the TABLE, just not the COLUMNs Create TABLE as SELECT Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 19
20. Use SET DDL to ‘shape’ how the code is generated DDL – generate DDL for any object Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 20
21. New or existing tables, file can be local or in Oracle Object Store (OSS) LOAD – take delimited data and load to a table Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 21
22. …JUST show me the DDL for proposed table Load, New Table, but… Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 22
23. Data Pump (21.4) Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved. • Very simple commands, e.g., dp export • Send Exports to Oracle Object Store, automatically! • Easy Imports for Databases in Oracle Cloud
24. Extending the toolkit with JavaScript Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved. script // issue the SQL var binds = {} var ret = util.executeReturnList('select id,file_name,content from media',binds); // loop the results FOR (i = 0; i < ret.length; i++) { // debug IS nice ctx.write( ret[i].ID + "\t" + ret[i].FILE_NAME+ "\n"); // GET the BLOB stream var blobStream = ret[i].CONTENT.getBinaryStream(1); // GET the path/file handle TO WRITE TO var path = java.nio.file.FileSystems.getDefault().getPath(ret[i].FILE_NAME); // dump the file stream TO the file java.nio.file.Files.copy(blobStream,path); } / !dir
25. Extract all BLOBs from a TABLE to local files on your OS 8 lines of js… Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 25
26. Free Liquibase Technology Enhanced for better Oracle Support • Built into SQLcl via Liquibase (lb) command • Automatically generate changeLogs • changeLogs automatically sorted for creation order • Automated rollbacks • 100% Oracle data type, schema, and SQL scripting support CI/CD, Schema Versioning with Liquibase Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 26
27. Interact with your OCI tenancies Oracle Cloud Support (OCI) Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 27 • Object Store [read | write] • SDK/APIs • DBCCRED Manage credentials for DBMS_CLOUD
28. Alias Potpourri Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 28 • Alias = shortcuts! • alias xyz=select 1 from dual;
29. REPEAT Potpourri Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 29 • Run a command… • Repeatedly • For X times with Y delay
30. Thanks everyone! Download: oracle.com/sqlcl Current Release :21.3 Next Release :21.4 (December 2021) Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 30