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. Jeff Smith

    View Slide

  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.

    View Slide

  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

    View Slide

  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

    View Slide

  5. Accessible from anywhere, easily
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    5

    View Slide

  6. Commands, new from SQL*Plus are Underlined
    6

    View Slide

  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

    View Slide

  8. set sqlformat or /*format*/
    Formatting
    Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.

    View Slide

  9. HELP, SET, & SHOW are your friends
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    9

    View Slide

  10. Edit Mode, Prompt, Statusbar
    Editing
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    10

    View Slide

  11. Inline editing with vi command support & the statusbar
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    11

    View Slide

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

    View Slide

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

    View Slide

  14. Tell me more about my database objects
    DESC, INFO, & INFO+
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    14

    View Slide

  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

    View Slide

  16. Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    16
    Instead of Column Comments, Show Statistics
    INFO+

    View Slide

  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

    View Slide

  18. Change directory…
    Commons tasks: CD
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    18

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  22. …JUST show me the DDL for proposed table
    Load, New Table, but…
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    22

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  29. REPEAT
    Potpourri
    Copyright © 2020, Oracle and/or its affiliates | All Rights Reserved.
    29
    • Run a command…
    • Repeatedly
    • For X times with Y
    delay

    View Slide

  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

    View Slide