SQLcl 24.1 Is Out! Here’s Everything You Need To Know and More

SQLcl and Liquibase
Written By Zachary Talke

[Download SQLcl 24.1: HERE]


Hope everyone is having a great day! SQLcl 24.1 is now officially out. Here’s what’s new.


Summary

We’ve got some great stuff in store for you. Along with our usual crusade to squash the bugs that you help us find and we encounter in our own testing, we also have some really cool new features this release!

Our spotlight addition is the new ability to background tasks that you run in SQLcl. Additionally, you now have the option to replace existing files while running your Database automation pipelines using the Liquibase feature. More on these and other additions in the details below.

For logging any bugs and issues you encounter you can do so through the My Oracle Support portal and if you have any specific SQLcl questions you can always reach out to me at zachary.talke@oracle.com


The Cliff Notes

  • You can get the download HERE

  • SQLcl users now have the ability to run tasks in the background. The three new commands related to this are:

    • background | bg {OPTIONS} <commandspec>

    • jobs | jb {SUBCOMMAND} {OPTIONS}

    • wait4 | w4 {OPTIONS} {PARAMETERS}

  • Color highlighting for keywords and errors in SQLcl are now enabled by default (controlled by set highlighting command).

  • We have two new parameters with the Liquibase functionality:

    • -overwrite-files parameter has been added to the Liquibase generate commands for the option to replace existing files.

    • -show-summary parameter has been added to the Liquibase update commands. It provides the option of displaying a list of any changes not applied during an update and why.

  • Tweaks have been made to the Liquibase functionality to better support long term growth and stay in sync with open-source Liquibase.

    • All parameters that take Boolean values must now be explicitly stated as either true or false if they are referenced in a command.

    • Listed parameters have been adjusted back to 23.3 definitions to better suit long term consistency (23.3 -> 23.4 -> 24.1).

      • -secure-parsing -> -no-secure-parsing -> -secure-parsing

      • -runonchange -> -dontrunonchange -> -runonchange

      • -runalways -> -dontrunalways -> -runalways

      • -skipexportdate -> -keepexportdate -> -skipexportdate

      • -exporiginalids -> -dontexporiginalids -> -exporiginalids

    • The parameter -fail-on-error now defaults as false.

    • The parameter -verbose now defaults as true.

    • Starting in 23.4, changelog files generated before SQLcl 23.4 may require regeneration or manual updates for cross-schema use. More info below and in the docs.

  • Bugs have been fixed. See list at the end of this post.


The Details

Background Tasks

You now have the ability to background tasks in SQLcl. There are three new commands related to this.

background | bg {OPTIONS} <commandspec>

  • Run a SQLcl command in the background as a task.

Ex. background -taskname merch ddl merchandise

jobs | jb {SUBCOMMAND} {OPTIONS}

  • List and manage the background tasks running.

Ex. jobs

Ex. jobs logs -id 2

wait4 | w4 {OPTIONS} {PARAMETERS}

  • Wait for one or more background tasks to finish before continuing with processing.

Ex. wait4 generate-emps -delay 5000

Color Highlighting

Color highlighting for keywords and errors in SQLcl is now enabled by default.

  • You can control your highlight settings with the set highlighting command and view them with show highlighting.

Liquibase Parameter Updates

We’ve added two new parameters within the Liquibase functionality:

  • -overwrite-files parameter for the Liquibase generate commands. This allows existing files with the same name to be replaced when generating changelogs.

    • This is helpful for file and directory management with your database schema automation pipelines.

  • -show-summary parameter for the Liquibase update commands. This produces a list of any changes not applied during an update and why they were skipped.

    • There are three levels of detail with this parameter: OFF -> SUMMARY -> VERBOSE

For Liquibase commands, all parameters that take Boolean values must now be explicitly stated as either true or false if they are referenced in a command. Under the hood, this allows us to better stay in sync with open-source Liquibase parameter changes moving forward.

  • Ex. 23.4 Command: liquibase update -output-default-schema

  • Ex. 24.1 Command: liquibase update -output-default-schema true

Listed parameters have been adjusted back to 23.3 definitions to better suit long term consistency (23.3 -> 23.4 -> 24.1).

  • -secure-parsing -> -no-secure-parsing -> -secure-parsing

  • -runonchange -> -dontrunonchange -> -runonchange

  • -runalways -> -dontrunalways -> -runalways

  • -skipexportdate -> -keepexportdate -> -skipexportdate

  • -exporiginalids -> -dontexporiginalids -> -exporiginalids

The parameter -fail-on-error now defaults as false and the parameter -verbose now defaults as true.

Important Update With Liquibase Files

For cross-schema use, changelog files generated before SQLcl 23.4 may require regeneration or manual updates. The need for these alterations are only necessary if you are changing schema names between your export and import with these pre 23.4 changelogs.

  • If you don’t regenerate your changelogs or make the manual adjustments described below:

    • Changelogs containing schema names will only be able to be applied to the schema named in them regardless of provided parameters.

    • Changelogs not containing schema names will only be able to be applied to the schema you are currently connected to with SQLcl regardless of provided parameters.

  • 23.4 introduced a %USER_NAME% replacement for schema name stored in changesets.

  • To manually update your changelogs with the proper %USER_NAME% substitution, there are two types of changes:

    • For changelogs with the <SCHEMA></SCHEMA> XML element, replace the content inside with %USER_NAME%

      • Example: <SCHEMA>%USER_NAME%</SCHEMA>

    • For changelogs that utilize SQL within the CDATA field, attach "%USER_NAME%". to the front of all database object references. If a schema name is in these locations, replace it with "%USER_NAME%".

      • Example:  <n0:source><![CDATA[CREATE OR REPLACE EDITIONABLE PROCEDURE "%USER_NAME%"."P_SQLCLERROR_PROCEDURE" ...

      • Example:  <n0:source><![CDATA[ALTER TABLE "%USER_NAME%"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "%USER_NAME%"."JOBS" ("JOB_ID") ENABLE;]]></n0:source>

For more information see the documentation section “Important 23.4 Update with Liquibase Changelog Files”.

Bug Fixes:

Here is a list of issues fixed in 24.1. This list isn’t comprehensive so if you don’t see your specific issue check My Oracle Support.

  • Can’t connect to database with SQLcl using both -name and @[script] parameters.

  • DESCRIBE packages command running slow on larger packages.

  • SQLcl ignores JAVA_HOME if JAVA is found in path.

  • Liquibase Functionality in SQLcl

    • Global parameters not being properly applied to all Liquibase commands.

      • Examples: -log, -debug

    • Liquibase changelogs cancelled with a substitution error still result with changelog marked as ran in databasechangelog table.

    • Liquibase unable to generate tables with foreign key constraints.

    • liquibase data command giving data definition language (DDL) XML content instead of data XML.

    • Specific supporting objects for Liquibase are not being generated when liquibase update command is ran for the first time in a schema under certain circumstances.

      • DATABASECHANGELOG_ACTIONS_PK INDEX

      • DATABASECHANGELOG_ACTIONS TABLE

      • DATABASECHANGELOG_ACTIONS_TRG TRIGGER

      • DATABASECHANGELOG_DETAILS VIEW

    • Liquibase error messaging and the output for -debug and -log parameters lacking enough detail in certain circumstances.

    • Only one -search-path parameter can be used at a time with Liquibase.

    • Index changes not being deployed to target with Liquibase.

    • Folders names created with the -split parameter using liquibase generate-schema generated as uppercase instead of lowercase.

    • liquibase generate-schema failing at times with error JAVA.SQL.SQL.EXPECTION: ORA-12899: VALUE TOO LARGE FOR COLUMN.

    • Liquibase failing with JAVA.SQL.SQLEXCEPTION: ORA-31604: INVALID NAME PARAMETER when using -grants parameter with liquibase generate-schema.

    • Liquibase consistency issues with runOracleScript/runApexScript changes of sourceType=File and realtiveToChangelogFile=true.

    • Liquibase issue where SQL errors in runoraclescript changesets do not stop liquibase update execution.

    • SQLcl Liquibase not outputting open-source Liquibase version info when running.

Zachary Talke
Next

REST API 101 Webinar Resources