SQLcl 23.4 Is Out! Here’s Everything You Need To Know and More
[Download SQLcl 23.4: HERE]
Happy New Year everyone! SQLcl 23.4 is now officially out. Here’s what’s new.
Summary
This release is centered on bug fixes so we can start off 2024 with some smooth sailing. You can see a selection of the fixes we made in the bug fixes section below. As a heads up, this isn’t a complete list and if you don’t see your specific issue check My Oracle Support.
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
Other than that we do have some feature updates such as the new alias search command and command completion functionality so continue reading to learn about everything new!
For our SQLcl Liquibase feature, we’ve updated the underlying open-source Liquibase version from 4.18 to 4.24.
Certain commands have been updated in terms of names and parameters and the command syntax for help has been updated to a standardized help [Optional: Command Name] [Optional: Subcommand Name] format so you are going to want to pay attention to that. You can keep up to date on the latest command formatting with the help command.
The Cliff Notes
You can get the download HERE
This is a big bug fix release for both SQLcl and its Liquibase feature
The help command has been standardized to help [Optional: Command Name] [Optional: Subcommand Name] and certain SQLcl command names and parameters have been updated.
The new alias search command allows you to search for aliases and return a resulting list in the syntax of alias search [search string]. The search string will check the contents of your aliases’ name, description, and query.
Aliases that ship with SQLcl have been updated to have nulldefaults set to on by default for bind variables. For user defined aliases, the user still needs to include the -nulldefaults optional parameter when creating their alias for this to be set to on
Introduced command completion for the commands:
Alias [ALIAS]
APEX [APEX]
Advanced Queuing [AQ]
Background [BACKGROUND]
Connect [CONNECT]
Connection Manager [CONNMGR]
Jobs [JOBS]
Liquibase [LIQUIBASE]
Migration Advisor [MIGRATEADVISOR]
Secret [SECRET]
SQLcl Liquibase’s underlying open-source Liquibase version has been updated from 4.18 to 4.24
The Details
The Help Command
In SQLcl if you type help it will give you a list of all available commands.
Then, if you want to see info on a specific command you type help [command name].
HOWEVER, in SQLcl 23.3 and prior, for commands like liquibase with a set of subcommands, to see info on that subcommand and its set of parameters, the help syntax would then be switched up to liquibase help [subcommand] (rather than a consistent help liquibase [subcommand]).
With 23.4 and going forward we’ve standardized the syntax to help [command] [subcommand] for syntax consistency.
Aliases
With the alias command, you can save a SQL, PL/SQL, or SQL*Plus command script and assign it a shortcut command.
The new alias search subcommand for alias allows you to search for aliases and return a resulting list in the syntax of alias search [search string]. The search string will check the contents of your aliases’ name, description, and query.
From there you can take a look at the details of an alias you were looking for with alias details.
To learn more use the help alias command in SQLcl.
Also new with aliases in 23.4, behavior with the nulldefaults option has been updated.
With aliases you can declare bind variables in the definition in the format of :name
That way when you run the alias, you can add specific inputs on the spot. This gives flexibility to what you query.
P.S. To get the output of the dbms_output.put_line from above to display, you need to run set serveroutput on in the SQLcl prompt.
By default, if you don’t specify a value when you run an alias with a bind variable present in it, the alias won’t run.
However, when creating your alias, there is an optional parameter -nulldefaults you can set.
What this does is if you don’t specify a value for the bind variable when running your alias, it will use a default value of NULL. This will allow your alias to still be run successfully with aliases like the enabled_triggers_nulldefaults one defined above.
In 23.4, aliases that ship with SQLcl have been updated to have nulldefaults set to on by default for the bind variables present in them. This helps make these aliases more accessible to users where providing a value for the bind variable in them isn’t mandatory.
For user defined aliases, the user still needs to include the -nulldefaults optional parameter when creating their alias for this to be set by default as on.
Command Completion
With this release, we are beginning to roll out command completion and are starting with the commands:
Alias [ALIAS]
APEX [APEX]
Advanced Queuing [AQ]
Background [BACKGROUND]
Connect [CONNECT]
Connection Manager [CONNMGR]
Jobs [JOBS]
Liquibase [LIQUIBASE]
Migration Advisor [MIGRATEADVISOR]
Secret [SECRET]
Press TAB while typing to autocomplete command names, show available parameters, and cycle through parameter options.
SQLcl Liquibase
With SQLcl 23.4, SQLcl Liquibase has been upgraded from using open-source Liquibase 4.18 to 4.24
SQLcl Liquibase is Oracle’s enhanced Liquibase feature for database schema automation that is embedded in SQLcl. SQLcl Liquibase is built on top of the open-source Liquibase platform - containing all of the open-source platform’s features with our additional enhancements and optimizations exclusively for the Oracle Database.
SQLcl Liquibase commands and parameters have been updated to support the open-source Liquibase 4.24 upgrade.
For information on open-source Liquibase 4.24 and the changes between 4.18-4.24, you can find info here: https://docs.liquibase.com/start/release-notes/liquibase-release-notes/liquibase-4.24.0.html
With Liquibase command changes, the most glaring thing you will probably notice is that the liquibase generate-object command for capturing and deploying database objects on a one off basis has now been updated to the name liquibase generate-db-object.
Bug Fixes:
Here is a list of issues fixed in 23.4. This list isn’t comprehensive so if you don’t see your specific issue check My Oracle Support.
Thick Driver Support - Changed -oci to -thick on commandline. sql -thick <url>
MKStore command upgraded to removed Java SecurityManager restriction
JDBC upgraded to 21.12.0.0.230906
SQLCL now checks for java in this order: Embedded JRE > JAVAHOME > $ORACLEHOME > PATH
SQLCL checks Java version when using $ORACLE_HOME before loading thick client jars
TNSAdmin search order fixed to: $HOME > $CWD > (windows registry) > $ORACLE_HOME
SQL Parser fully supports Database 23c updated syntax
SQL Error positions now correctly reported
Help for commands now standardized to help <command>
liquibase update -changelog-file controller.xml fails to update table object at target if constraints are deleted at source
SQLcl liquibase changelog tables showing in the controller if they are renamed
SQLcl liquibase removing schema name from strings inside pl/sql objects
liquibase generate-schema can fail to order scripts correctly based on dependency; e.g. w/ popular logger_user
Generated trigger SQL file create invalid trigger due to enable trigger statement at the end
liquibase update fails with npe when the user has the binary_ci default collation
liquibase generate-apex-object adds spaces to trigger code
liquibase update overwrites sequences
SQLcl Liquibase default search path should include the directory in which Liquibase is run
liquibase generate-apex-object command not placing apex_install.xml in the specified directory when using the -dir parameter
liquibase generate-schema fails to give an error message when the -filter parameter is used with an invalid filter
