Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle SQLcl Overview, Celebrating 5 Years!

thatjeffsmith
October 14, 2021

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

thatjeffsmith

October 14, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Technology

Transcript

  1. 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.
  2. 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
  3. 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
  4. 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
  5. HELP, SET, & SHOW are your friends Copyright © 2020,

    Oracle and/or its affiliates | All Rights Reserved. 9
  6. Inline editing with vi command support & the statusbar Copyright

    © 2020, Oracle and/or its affiliates | All Rights Reserved. 11
  7. History persists between instances, except for FAILED statements/scripts History Copyright

    © 2020, Oracle and/or its affiliates | All Rights Reserved. 12
  8. Object names, keywords, commands, and files! Code Completion/Insight Copyright ©

    2020, Oracle and/or its affiliates | All Rights Reserved. 13
  9. Tell me more about my database objects DESC, INFO, &

    INFO+ Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 14
  10. 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
  11. Copyright © 2020, Oracle and/or its affiliates | All Rights

    Reserved. 16 Instead of Column Comments, Show Statistics INFO+
  12. 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
  13. 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
  14. 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
  15. 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
  16. …JUST show me the DDL for proposed table Load, New

    Table, but… Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved. 22
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. Alias Potpourri Copyright © 2020, Oracle and/or its affiliates |

    All Rights Reserved. 28 • Alias = shortcuts! • alias xyz=select 1 from dual;
  23. REPEAT Potpourri Copyright © 2020, Oracle and/or its affiliates |

    All Rights Reserved. 29 • Run a command… • Repeatedly • For X times with Y delay
  24. 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