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

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