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

Versioning your Oracle Database with SQLcl and Liquibase

February 05, 2020

Versioning your Oracle Database with SQLcl and Liquibase

Overview of Liquibase, and how we enhanced it in Oracle SQLcl, your modern Oracle Database command line interface.

SQLcl will generate the changelogs for you, order them properly, and automatically generate rollback scnearios for you - all in your familiar query tool.

And, it's all free!

The 5 minute video demonstration can be viewed here


February 05, 2020

More Decks by thatjeffsmith

Other Decks in Programming


  1. • Your application schema(s) • Tables • Views • Stored

    Procedures • Everything in a schema! Versioning what, exactly?
  2. • Home grown solution? • Bespoke scripts? • Liquibase |

    Flyway? • Something else? How are you handling upgrades today?
  3. • Less coding • Familiar, friendly interface • Database object

    dependency ordering is taken care of • Updates automatically generate rollback scenarios Our solution: leverage Liquibase + EASY For FREE
  4. • Open source project • Deploys versions of your database

    schema/user • Supports popular DBMS solutions, including Oracle • Installs, upgrades, or rollbacks Liquibase – Source Control for your DB
  5. • Executable via command line, • Apache Ant, • Apache

    Maven, • servlet container, • Spring Framework. Liquibase – How it runs
  6. • …YOU code the upgrade AND rollback changelogs • YOU

    worry about the order of changelogs to be applied A lot of power, but…
  7. SQL Developer Family of Solutions 1 2 Time Tested Launched

    in 2005 An Industry Standard 5M+ Oracle DBAs & Developers Always Improving Quarterly Releases 4
  8. • Replaced the Oracle library for Liquibase • Mounted it

    as ‘lb’ command in SQLcl What we did SQLcl: a modern CLI for your Oracle Database
  9. • 25MB download, only requires a JRE • Supports SQL*Plus

    commands, adds many more • 11gR2 & higher • Avail in OCI YUM, OTN, DB $ORCLE_HOMEs SQLcl: manage & query your Oracle DB
  10. • Leverages DBMS_METADATA to gen XML changelogs • Operates as

    connected USER, writes to $SQL_PATH • Parses XML, determines object creation order How it works
  11. • GEN schema/object as LB changelogs • Automatically sorted for

    creation • Easily rollback to previous state • Preview UPDATE SQL SQLcl and the LB command
  12. 1. Create a directory to hold v1 changelogs 2. Login

    as app schema in DEV 3. >lb genschema 4. Login as app schema in PROD/TEST 5. >lb update <controller.xml> false Generate and Update
  13. Generating changelog for our object Preview the DB work to

    be applied via ‘lb updatesql’ command
  14. • We handle the code for rollback • DESC >

    INFO for terse screenshots ☺ Oops…ROLLBACK!

    Try running an APP In-house scenarios (ORDS + APEX) Spoiler: It worked.
  16. • Shuffle in changelogs at the page level vs application

    level • Granular updates vs completely rebuilding an App DEEP APEX Support Available
  17. • LB command added for version 19.2.1 of SQLcl •

    Follow @oraclesqlcl for product news and updates Availability - NOW